Tomcat 微信公众号开发 图像处理 dictionary meteor controller jScroll vue滑动事件 前端项目实战 java三维数组 mysql更新多个字段 hash怎么下载 单片机开发板工作原理 mysql重启 centos定时任务 python开发教程 python中的if语句 java的基本类型 java中continue linux中grep linux系统启动过程 脚本之家 路由器有辐射吗 音频录制软件 volist 微信骰子表情包 c4d文字 文字图片制作 鬼灵战马 联盟练级路线 js文件上传插件 联想7450加粉清零 cad拉伸命令 下拉框默认选中 snmp网络管理软件 python去掉空格 excel箱线图 电脑微信官方下载 qq号码生成器 极光迪莫
当前位置: 首页 > 学习教程  > 编程语言

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

2021/1/28 23:58:21 文章标签:

我是架构精进之路,点击上方“关注”,坚持每天为你分享技术干货,私信我回复“01”,送你一份程序员成长进阶大礼包。 大约一周之前我分享了:如何做好一场技术分享,100%纯干货输出 大家可能觉得还是停留在理…

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

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

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

第一页演讲文稿:

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

第二页演讲文稿:

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

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

第三页演讲文稿:

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

主要原则:问题驱动;

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

第四页演讲文稿:

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

有一天我在执行https://groups.tianya.cn/post-211533-325ef73a034c42578757957142c6f567-1.shtmlSQL的时候(两个查询SQL一个是指定了字段 id,另一个未指定查询字段,而是利用了 *),发现两种情况下查询执行结果竟然不一样!

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

第五页演讲文稿:

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

发现Case1中的https://groups.tianya.cn/post-211533-fd5f0a34011845b4bb72d250304aa682-1.shtmlSQL应用到了一个名为’uniq_city_code’的索引,而第二个走了全表扫描查询。

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

第六页演讲文稿:

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

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

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

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

第七页演讲文稿:

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

如图所示,为https://groups.tianya.cn/post-211533-4f083a24c4ff4cf0aa75d01aefa5bf9e-1.shtmlInnodb、以及参考对比的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_https://groups.tianya.cn/post-211533-366555c86d6342c0a155af2c7d3503b3-1.shtmlcity_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 的查询确实存在索引覆盖情况 。

第十页演讲文稿:

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

我们先来看看Myhttps://groups.tianya.cn/post-211533-53086bf9073c4c9f8add752bc12e27b9-1.shtmlSQL官方的解释… 其实说了这么多,本质就是最后一句,这样做可以使查询更快!

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

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

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

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

第十一页演讲文稿:

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

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

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

关于find_https://groups.tianya.cn/post-211533-a0f6519192894752926e2a9a2f24a8fc-1.shtmlshortest_key()函数的解释,我们来看下官方解释,如图所示。

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

第十二页演讲文稿:

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

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

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

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

4)对于Myhttps://groups.tianya.cn/post-211533-f3677ab16b11410aa12a074898aba266-1.shtmlSQL为什么会存在这样的索引选取原则,我们最终发现是辅助索引一定是主键索引的子集,从节约IO的角度,在全表扫描时优先选择辅助索引。

重点提炼:

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

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

第十三页演讲文稿:

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

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

第十四页演讲文稿:

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

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

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


本文链接: http://www.dtmao.cc/news_show_650338.shtml

附件下载

相关教程

    暂无相关的数据...

共有条评论 网友评论

验证码: 看不清楚?