【技术分享实录】MySQL索引,你真的会用吗?

原创
01/20 12:28
阅读数 3.2K

我是架构精进之路,点击上方“关注”,坚持每天为你分享技术干货,私信我回复“01”,送你一份程序员成长进阶大礼包。   

 

大约一周之前我分享了:如何做好一场技术分享,100%纯干货输出

大家可能觉得还是停留在理论层面,现在趁热打铁,将自己最近的一次技术分享PPT+准备文稿 分享给大家,在此抛砖引玉,希望能够切实帮助到大家,感受到技术分享的魅力。

 

第一页演讲文稿:

大家好,我是架构精进之路,今天给大家带来一个主题为《MySQL索引,你真的会用吗?》,关于MySQL索引的应用分享。

第二页演讲文稿:

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

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

第三页演讲文稿:

接下来我会通过一个MySQL查询应用问题为出发点,来逐步通过问题分析、疑点跟进、引入官方材料证明来分析探究,最终明确索引应用原则,将结论探究清楚。

主要原则:问题驱动

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

第四页演讲文稿:

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

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

这事成功的引起了我的注意。

第五页演讲文稿:

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

发现Case1中的SQL应用到了一个名为'uniq_city_code'的索引,而第二个走了全表扫描查询。

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

第六页演讲文稿:

那其实还存在几个疑问点:

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

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

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

第七页演讲文稿:

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

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

1)对比的是MyISAM引擎方式,如右图所示:

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

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

2)InnoDB聚簇索引和辅助索引(非聚簇索引)的对比左图所示。

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

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

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

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

第八页演讲文稿:

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

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

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验证一下。

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

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

第十页演讲文稿:

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

我们先来看看MySQL官方的解释... 其实说了这么多,本质就是最后一句,这样做可以使查询更快!

 

好了,我们现在来个互动小问题:

给大家出个问题:既然主键索引包含所有数据列,那么使用主键索引一样可以做到索引覆盖,为什么优化器不选择使用主键索引?

有没有同学愿意尝试分析一下呢?

......

回答的基本正确,我们按照这个同学的思路来继续分析MySQL到底是如何实现的呢?

 

第十一页演讲文稿:

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

这个问题的答案就是:索引长度不同,有多个可选索引时,MYSQL会优先选择较短的索引。

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

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

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

 

第十二页演讲文稿:

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

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

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

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

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

重点提炼:

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

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

 

第十三页演讲文稿:

好了,现在是问答时间,大家对上述讲述内容有什么疑问可以与我进行沟通。

......

大家如果还有后续问题,可以关注我微信公众号,获取我个人微信信息,咱们保持联系沟通,期待与您的共同成长。

第十四页演讲文稿:

感谢大家的聆听,希望对你有所帮助,谢谢~

 

注:大家如需要PPT件,可以关注公众号添加我个人微信,联系获取~

坚持原创学习与分享不易,在此抛砖引玉,希望能够切实帮助到大家,感觉不错请点击在看、转发分享给更多的人,感谢大家的支持~

 

- END -


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


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

 

Thanks for reading!

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

展开阅读全文
打赏
0
4 收藏
分享
加载中
可能细心的同学也发现了,还有就是语句2查询计划中Extra字段为Using index 这个写错了是语句1
另外你这说的就是索引下沉 如果select 中的字段命中索引也会走索引
01/23 11:22
回复
举报
多谢指正,已更正
01/25 11:32
回复
举报
更多评论
打赏
2 评论
4 收藏
0
分享
返回顶部
顶部