MySQL索引面试题分析

MySQL索引面试题分析

话不多说，先建立一个表testTable，其中id为自增主键

在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';

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

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

下面看看一下SQL语句

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

c3及以后的索引全失效，因此只用到了两个索引，c3、c4要进行排序查找

再看下面的SQL语句

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

从key_len=124可知，用到了四个索引。

再看下面的SQL语句

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

c3用于排序。

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

结果与上面的SQL一样，但是c3作用是排序而不是查找。
再看看下面的SQL语句

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

用到了c1,c2索引，但是c4是用于排序，中间的c3索引断了，MySQL会使用文件内排序给出查询结果，这样子就导致了性能下降。

再来看看：

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

只用到了c1索引，c2、c3用于排序

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

c2，c3排序的顺序倒过来了，MySQL需要用文件内排序才能查询出结果

导致了性能的下降。
再看看：

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

还很ok
再看：

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

没有什么问题
以上两条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;

竟然没有出现filesort，这是为什么呢？因为c2=‘a2’，order by中的c2字段已经是一个常量了，所以真正排序的字段就只有c3.

再看看下一个

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

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

用到了文件内排序。记住一句话，group by表面上是分组，但实际上分组的前提必须排序，且会有临时表排序。

那么分析了这么多索引失效的题目，我们应该如何建立好索引，写出性能较好的SQL语句呢？

好了，索引的部分暂时告一段落，如果将来有遇到问题我会继续更新~