踩坑 MySQL 索引,看看你真的会用吗?

原创
04/06 08:30
阅读数 1.2W

 

关于 MySQL 索引,对于研发同学,尤其是后端研发同学,一定不会陌生。我们工作中经常会用到 MySQL 数据库,就肯定会经常用到性能优化方面的设计和考量,常常用涉及到 MySQL 索引。但是关于 MySQL 索引,你真的用对了么?

 

对了,在开始正式知识点讲解之前,还需要来个不那么正式的自我介绍吧,哈哈哈~

我多年后端研发经验,“混迹”于多个互联网大厂,专注软件架构技术研究学习,希望能够持续发挥自身多余的热量,将自己工作中的问题和技术总结输出,分享影响到更多的人。

大家看我的头像图片像是一个陀螺,其实是寓意螺旋式上升,让技术和自我能够不断精进。

 

接下来,我会通过一个自己工作中真实遇到一个 MySQL 查询应用问题为背景,来逐步剖析分析,见招拆招,以科学理论为依据,分析探究,希望能带大家一起明确索引应用原则,最终将问题探究清楚。

主要原则:问题驱动;

主要流程:应用现象-问题分析-疑点跟进-层层探究-结论明晰。

 

那接下来,让我们利用约 15min 的时间,让自由的思路飞一会儿吧!

问题介绍

我们存在这样一张数据表(cities),记录了城市 code 和名称一些基本数据。

有一天,我在执行如下 SQL 的时候(一个是指定了字段 id,另一个未指定查询字段,而是利用了 *),发现两种情况下查询执行结果竟然不一样!

Case1:select id from cities limit 1;
 

查询结果:

id:2

Case2:select * from cities limit 1;

查询结果:

 

这事成功的引起了我的注意,那就继续搞起吧!

问题分析

按照之前的工作经验告诉我,遇事不要慌,先 explain 解释执行看看吧。

Case1:explain select id from cities limit 1;
执行结果:

 

Case2:explain select * from cities limit 1;

执行结果:

 

经过上面的执行计划查看,发现 Case1 中的 SQL 应用到了一个名为'uniq_city_code'的索引,而第二个走了全表扫描查询。

 

问题初步结论:也就是说两个 SQL 由于查询字段的不同,导致 MySQL 在具体执行时候选取了不同的索引策略,从而导致了查询结果的不同。

疑点跟进

其实经过上面的分析,其实还存在几个疑问点:

  • 为什么 Case1 查询中并没有出现 city_code 字段,却会使用其索引?

  • 为什么 Case2 查询就不会使用 uniq_city_code 的索引?

 

可能细心的同学也发现了,还有就是 Case1 查询计划中 Extra 字段为 Using index,说明满足了索引覆盖(索引中包含了所有满足查询条件的数据,无需从表中查询),可是 uniq_city_code 这个索引中并没有 id 这个字段,为何能以覆盖索引的方式执行?

 

带着上面的一脸疑问,我们先来一起回顾下 MySQL 引擎索引的实现方式吧。

如图所示,为 Innodb、以及参考对比的 MyISAM 引擎的索引实现图例。

 

1、InnoDB 聚簇索引和辅助索引(非聚簇索引)的对比图示

 

同时便于大家理解,我标记黄线、红线分别代表两种引擎方式的数据查询路径,大家可以参照图例,体会对比一下。

InnoDB 按聚簇索引的形式存储数据,所以它的数据布局有着很大的不同。

1)聚簇索引中的每个叶子节点包含 primary key 的值,事务 ID 和回滚指针(rollback pointer)——用于事务和 MVCC,和余下的列(如 col2)。

2)相对于 MyISAM,辅助索引与聚簇索引有很大的不同。InnoDB 的二级索引的叶子包含 primary key 的值,而不是行指针(row pointers),这减小了移动数据或者数据页面分裂时维护二级索引的开销,因为 InnoDB 不需要更新索引的行指针。

 

2、MyISAM 引擎方式索引图示

 

MyISAM 不支持聚簇索引,索引中每一个叶子节点仅仅包含行号(row number),且叶子节点按照 col1 的顺序存储。

在 MyISAM 中,primary key 和其它索引没有什么区别。Primary key 仅仅只是一个叫做 PRIMARY 的唯一,非空的索引而已。

 

好了,我们还是回到问题本身。

我们其实可以得出这样一个初步结论:

Case1:select id from cities limit 1;

因为 uniq_city_code 索引中包含 id 字段,此查询可以从 uniq_city_code 索引中直接取得数据,所以优化器选择走 uniq_city_code 索引;

Case2:select * from cities limit 1;

此查询中 select * 选取了在 uniq_city_code 索引中不包含的列,所以无法使用 uniq_city_code 这个索引。

 

为了验证一下我们刚刚得到的初步结论,我们来利用 Case3 验证一下。

Case3:select id, city_code from cities limit 1;

执行结果:

 

按照上述的理论依据,Case1(查询 id)与 Case3(查询 id+city_code)执行应用的查询计划应该是一致的。

通过验证实验我们可以确定一个结论:Case1 的查询确实存在索引覆盖情况。

官方辅证

我们再继续追问一下:为什么要用到索引覆盖呢?不用可不可以呢?

我们先来看看 MySQL 官方的解释...

 

其实说了这么多,本质就是最后一句,这样做可以使查询更快!

好了,大家可以一起来思考下这个问题:

既然主键索引包含所有数据列,那么使用主键索引一样可以做到索引覆盖,为什么优化器不选择使用主键索引?”

......

 

其实这个问题,就是典型的 MySQL 索引选取原则。

 

MySQL 在做全表扫描时,MySQL 会调用 find_shortest_key() 来选取最短的索引来扫描。

关于 find_shortest_key()函数的解释,我们来看下官方解释,如下所示:

所以,上面大家一起思考的这个问题,答案就是:索引长度不同,有多个可选索引时,MYSQL 会优先选择较短的索引。

 

到现在,那我们可以对整个问题做个总结了:因为辅助索引一定是主键索引的子集,从节约 IO 的角度,在全表扫描时优先选择辅助索引。

总结

好了,最后我们一起来对整个分享做下总结吧。

1)首先我们遇到一个查询问题,由于查询字段的不同导致我们的查询结果数据存在差异;

2)我们对问题进行追究,发现根据 select 的字段不同,MySQL 选取的索引策略不同,即结果数据不同;

3)对于是否存在索引覆盖问题,我们进行了 Case3 的验证,确认了存在索引覆盖的问题;

4)对于 MySQL 为什么会存在这样的索引选取原则,我们最终发现是辅助索引一定是主键索引的子集,从节约 IO 的角度,在全表扫描时优先选择辅助索引。

 

重点提炼:

不同引擎对于查询实现方式的不同、索引覆盖、MySQL 索引选取原则。

不同引擎对于查询实现方式的不同、索引覆盖、MySQL 索引选取原则。

不同引擎对于查询实现方式的不同、索引覆盖、MySQL 索引选取原则。

 

重要的问题说三遍,哈哈哈~

 

其实踩坑,也是一种成长!

其实面对任何问题,都不要一上来就急于给出结论,可以尝试多做些深入分析,了解本质问题之后再考虑解决办法进行解决,希望大家能够掌握问题分析以及解决的能力,去触类旁通,而不是仅仅了解一招一式,盲目照搬。

 

- END -


作者:架构精进之路,专注软件架构研究,技术学习与个人成长,关注并私信我回复“01”,送你一份程序员成长进阶大礼包,欢迎勾搭。


 

往期热文推荐:


 

「技术架构精进」专注架构研究,技术分享

 

Thanks for reading!

本文分享自微信公众号 - 架构精进之路(jiagou_jingjin)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
打赏
2
13 收藏
分享
加载中
请问图是用什么工具画的?免费开源的吗?
04/11 05:13
回复
举报
用 PPT 画的
前天 21:13
回复
举报
能不索引,就不索引,多用缓存,
要不然,mysql服务器,一打就挂了
04/10 10:41
回复
举报
索引是把双刃剑没错,但是缓存通常针对热点数据而言,否则内存成本较高,不值得
前天 21:20
回复
举报
上面明明说了"可是 uniq_city_code 这个索引中并没有 id ",最后来一个有这字段,还以为我的整个框架体系要崩塌了。我TM也是服服贴贴,被耍猴了
04/09 10:02
回复
举报
uniq_city_code 是建立在 city_code 字段的索引,没有id字段,但是作为辅助键索引存储时候,是会关联id主键数据的
前天 21:22
回复
举报
没明白uniq_city_code为什么包含了id字段,能把建表SQL放一下么。
04/09 09:26
回复
举报
看我回复上面同学的内容
前天 21:32
回复
举报
“可能细心的同学也发现了,还有就是 Case2 查询计划中 Extra 字段为 Using index” 这里是不是写错了,应该是Case1的Extra?
04/09 09:24
回复
举报
对, Case2-> Case1,已更正,很细心~
前天 21:23
回复
举报
可以把cities表的建表SQL贴一下么,谢谢。
04/07 11:37
回复
举报
文中已补充~
昨天 10:06
回复
举报
更多评论
打赏
12 评论
13 收藏
2
分享
返回顶部
顶部