MySQL索引面试题分析

梦里梦外; 2023-07-06 06:25 89阅读 0赞

## MySQL索引面试题分析 ##

话不多说，先建立一个表testTable，其中id为自增主键  
![在这里插入图片描述][20200216213712809.png]  
在c1,c2,c3,c4上建立符合索引索引

CREATE INDEX idx_testTable_c1234 ON testTable(c1,c2,c3,c4);

现在的题目是：根据以下SQL分析索引使用的情况

1.SELECT * FROM testTable WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4';
    2.SELECT * FROM testTable WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1';

首先我们用explain语句来分析一下1，2条SQL语句

EXPLAIN SELECT * FROM testTable WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4';

![在这里插入图片描述][20200216214642807.png]

EXPLAIN SELECT * FROM testTable WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1';

![在这里插入图片描述][20200216214722852.png]  
在这里我们看到它们的结果都是一样的，这是为什么呢？  
其实，在MySQL逻辑架构中，在MySQL执行SQL语句之前，会经过一个查询优化器，会把where语句后条件的顺序调整为最佳顺序来进行查询。

下面看看一下SQL语句

EXPLAIN SELECT * FROM testTable WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4';

![在这里插入图片描述][20200216215151350.png]  
c3及以后的索引全失效，因此只用到了两个索引，c3、c4要进行排序查找

再看下面的SQL语句

EXPLAIN SELECT * FROM testTable WHERE c1='a1' AND c2='a2' AND c4>'a4' AND c3='a3';

![在这里插入图片描述][20200216215443908.png]  
从key\_len=124可知，用到了四个索引。

再看下面的SQL语句

EXPLAIN SELECT * FROM testTable WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3;

![在这里插入图片描述][20200216215739510.png]  
c3用于排序。

EXPLAIN SELECT * FROM testTable WHERE c1='a1' AND c2='a2'  ORDER BY c3;

![在这里插入图片描述][2020021621592552.png]  
结果与上面的SQL一样，但是c3作用是排序而不是查找。  
再看看下面的SQL语句

EXPLAIN SELECT * FROM testTable WHERE c1='a1' AND c2='a2'  ORDER BY c4;

![在这里插入图片描述][20200217092734142.png]  
用到了c1,c2索引，但是c4是用于排序，中间的c3索引断了，MySQL会使用文件内排序给出查询结果，这样子就导致了性能下降。

再来看看：

EXPLAIN SELECT * FROM testTable WHERE c1='a1' AND c5='a5'  ORDER BY c2,c3;

![在这里插入图片描述][20200217093301211.png]  
只用到了c1索引，c2、c3用于排序

EXPLAIN SELECT * FROM testTable WHERE c1='a1' AND c5='a5'  ORDER BY c3,c2;

c2，c3排序的顺序倒过来了，MySQL需要用文件内排序才能查询出结果  
![在这里插入图片描述][20200217093501617.png]  
导致了性能的下降。  
再看看：

EXPLAIN SELECT * FROM testTable WHERE c1='a1' AND c2='a2'  ORDER BY c2,c3;

![在这里插入图片描述][20200217093814303.png]  
还很ok  
再看：

EXPLAIN SELECT * FROM testTable WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2,c3;

![在这里插入图片描述][20200217094011542.png]  
没有什么问题  
以上两条SQL都是用到了c1，c2索引，但是c2、c3是用于排序，没有出现filesort，性能可以。  
但是，再看，如果将c2、c3的顺序倒置：

EXPLAIN SELECT * FROM testTable WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c3,c2;

![在这里插入图片描述][20200217094416193.png]  
竟然没有出现filesort，这是为什么呢？因为c2=‘a2’，order by中的c2字段已经是一个常量了，所以真正排序的字段就只有c3.

再看看下一个

EXPLAIN SELECT * FROM testTable WHERE c1='a1' AND c4='a4' GROUP BY c2,c3;

![在这里插入图片描述][20200217095100742.png]

EXPLAIN SELECT * FROM testTable WHERE c1='a1' AND c4='a4' GROUP BY c3,c2;

![在这里插入图片描述][20200217095126466.png]  
用到了文件内排序。记住一句话，group by表面上是分组，但实际上分组的前提必须排序，且会有临时表排序。

那么分析了这么多索引失效的题目，我们应该如何建立好索引，写出性能较好的SQL语句呢？  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ_size_16_color_FFFFFF_t_70]  
好了，索引的部分暂时告一段落，如果将来有遇到问题我会继续更新~

[20200216213712809.png]: https://img-blog.csdnimg.cn/20200216213712809.png
[20200216214642807.png]: https://img-blog.csdnimg.cn/20200216214642807.png
[20200216214722852.png]: https://img-blog.csdnimg.cn/20200216214722852.png
[20200216215151350.png]: https://img-blog.csdnimg.cn/20200216215151350.png
[20200216215443908.png]: https://img-blog.csdnimg.cn/20200216215443908.png
[20200216215739510.png]: https://img-blog.csdnimg.cn/20200216215739510.png
[2020021621592552.png]: https://img-blog.csdnimg.cn/2020021621592552.png
[20200217092734142.png]: https://img-blog.csdnimg.cn/20200217092734142.png
[20200217093301211.png]: https://img-blog.csdnimg.cn/20200217093301211.png
[20200217093501617.png]: https://img-blog.csdnimg.cn/20200217093501617.png
[20200217093814303.png]: https://img-blog.csdnimg.cn/20200217093814303.png
[20200217094011542.png]: https://img-blog.csdnimg.cn/20200217094011542.png
[20200217094416193.png]: https://img-blog.csdnimg.cn/20200217094416193.png
[20200217095100742.png]: https://img-blog.csdnimg.cn/20200217095100742.png
[20200217095126466.png]: https://img-blog.csdnimg.cn/20200217095126466.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200217095657194.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ==,size_16,color_FFFFFF,t_70