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语句呢？
好了，索引的部分暂时告一段落，如果将来有遇到问题我会继续更新~
