【第224期】MySQL索引相关面试演练

来源：www.sychzs.cn/developer_chan/p/9223671.html 0.准备工作 #1.创建测试表（测试表）。 如果存在则删除表 test;创建表 test(id int 主键 auto_increment,c1 varchar(10),c2 varchar(10),c3 varchar(10),c4 varchar(10),c5 varchar(10)) ENGINE=INNODB 默认字符集=utf8； 插入测试（c1，c2，c3，c4，c5）值（'a1'，'a2'，'a3'，'a4'，'a5'）;插入测试(c1,c2,c3,c4,c5)值('b1','b2','b3','b4','b5');插入测试(c1,c2,c3,c4,c5 ) 值('c1','c2','c3','c4','c5');插入测试(c1,c2,c3,c4,c5)值('d1','d2','d3 ','d4','d5');插入测试(c1,c2,c3,c4,c5)值('e1','e2','e3','e4','e5'); #2.创建索引。 1.根据以下Case分析索引的使用 情况1： 分析： ①创建复合索引的顺序为c1、c2、c3、c4。 ②上面四组explain执行的结果是一样的：type=ref, key_len=132, ref=const, const, const, const。 结论：在执行常量等值查询时，改变索引列的顺序并不会改变explain的执行结果，因为底层MySQL优化器会进行优化，但建议按照索引列的顺序来编写SQL语句。 案例2： 分析： 当出现range时，type=range，key_len=99，比没有range的key_len=66高，说明使用了索引。但对比Case1的执行结果，发现c4上的索引无效。结论：范围右侧的索引列无效，但范围当前位置（c3）的索引有效，这可以从key_len=99证明。 案例2.1： 分析： 对比上面解释的执行结果，key_len=132表示使用了4个索引，因为MySQL底层优化器会对SQL语句进行优化：范围右侧的索引列无效（范围右侧没有索引列） c4的右侧），注意索引的顺序（c1,c2,c3,c4），这样c4的右侧不会有无效的索引列，所以四个索引都被使用。 结论：范围右侧无效索引列的顺序为：c1、c2、c3、c4。如果c3有范围，则c4无效；如果c4有范围，则不存在无效索引列，因此将使用所有索引。 案例2.2： 分析： 如果c1处使用了range，则type=ALL，key=Null，索引无效，违反全表扫描。这里违反了最佳左前缀规则，领头大哥死了，因为c1主要用于范围，而不是查询。 解决方案是使用覆盖索引。 结论：在最佳左前缀规则中，如果最左列（领先的兄弟）中的索引失败，则所有后续索引都将失败。 案例3： 分析： 使用最佳左前缀规则：中间的兄弟不能被打破，所以使用c1和c2索引（搜索）。从key_len=66,ref=const,const开始，排序过程中使用了c3索引列。 案例3.1： 分析： 从explain的执行结果来看：key_len=66,ref=const,const，所以只使用c1和c2索引进行搜索，使用c3索引进行排序。 案例3.2： 分析： 从explain: key_len=66, ref=const, const的执行结果来看，查询使用了c1和c2索引，因为使用了c4进行排序，跳过了c3，出现了Using filesort。 案例4： 分析： 搜索仅使用索引c1，c2和c3用于排序，并且没有使用filesort。 案例4.1： 分析： 执行结果与案例4中解释的相同，但出现Using filesort是因为索引创建顺序是c1、c2、c3、c4，但排序时c2和c3的位置颠倒了。 案例4.2： 分析： 查询时添加了c5，但explain的执行结果是一样的，因为c5没有创建索引。 案例4.3： 分析：与Case 4.1相比，Extra中没有出现Using filesort，因为c2是常量，并且在排序时进行了优化，所以索引没有反转，也没有出现Using filesort。 案例5： 分析： 仅使用c1上的索引，因为c4在中间被中断。根据最佳左前缀规则，key_len=33，ref=const表示只使用一个索引。 案例5.1： 分析： 与案例5相比，c2和c3的位置在group by过程中交换了。结果出现了Usingtemporary和Usingfilesort，这是极其糟糕的。原因：c3和c2创建索引的顺序相反。 总结： 通过对上述案例的分析，总结如下： ①最佳左前缀规则。 在等效查询中，改变索引列的顺序不会影响explain的执行结果，因为mysql底层会进行优化。 使用order by时，要注意索引顺序、常量以及可能导致使用filesort的情况。 ②group by 很容易产生，使用临时的。 ③通俗理解公式： 完整值符合我的最爱，并且必须尊重最左边的前缀； 为首的大哥不能死，二哥不能断； 对索引列的计算较少，范围后面的所有内容都无效； LIKE百分比写在右边，覆盖索引不写星星； 不等于空值and or，索引失败要少用。 结尾 推荐十期 【第201期】面试官：String的长度有限制吗？多少？幸运的是，我已经看到了 【第202期】面试官：GET和POST请求的本质区别是什么？ （没有本质区别） 【第203期】彻底理解Java IO：字节流、字符流、缓冲流 【第204期】面试官：你有没有用手写过阻塞队列的经历？ 【第205期】面试官：数据量很大，对于分页查询有什么优化方案吗？ 【第206期】面试官：你们的项目如何处理重复请求/并发请求？ 【第207期】19张图带你看懂SpringCloud系统中的重要知识点！ 【第208期】敲黑板，聊聊如何设计闪购系统（要点） 【第209期】架构设计与分发&数据结构与算法面试题（2020最新版） 【第210期】面试官：我们来说说Redis和zk这两种常用的分布式锁的比较。 而不是在网上搜索问题？还不赶快关注我们吧~版权声明：本文内容由网友自愿贡献，本文所表达的观点仅代表作者自己的观点。本网站仅提供信息存储空间服务，不拥有任何所有权，也不承担相关法律责任。如果您发现本站有任何涉嫌侵权/非法内容，请发送邮件举报。一经核实，该网站将立即删除。 本文由斑马博客整理。本文链接为：https://www.sychzs.cn/index.php/post/7859.html