【面试】MySQL 索引失效分析

先看下面的一个示例：

create table test_03(
    id int primary key not null auto_increment,
    c1 char(10),
    c2 char(10),
    c3 char(10),
    c4 char(10),
    c5 char(10)
);

insert into test_03(c1, c2, c3, c4, c5) values ('a1', 'a2', 'a3', 'a4', 'a5');
insert into test_03(c1, c2, c3, c4, c5) values ('b1', 'b2', 'b3', 'b4', 'b5');
insert into test_03(c1, c2, c3, c4, c5) values ('c1', 'c2', 'c3', 'c4', 'c5');
insert into test_03(c1, c2, c3, c4, c5) values ('d1', 'd2', 'd3', 'd4', 'd5');
insert into test_03(c1, c2, c3, c4, c5) values ('e1', 'e2', 'e3', 'e4', 'e5');

create index idx_test03_c1234 on test_03(c1, c2, c3, c4);

show index from test_03;

explain select * from test_03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
explain select * from test_03 where c4='a1' and c3='a2' and c1='a3' and c2='a4';

很显然最下面的 SQL 语句并没有按照索引的建立顺序来查询，但是由于 MySQL 优化器对这条 SQL 做了优化，使得优化后的语句和上面的是一样的顺序，这样就能顺利用到索引了！但是由于这样写 SQL 语句还是会让底层做一次 SQL 优化，所以还不如一开始就按照建立索引的顺序来写 SQL。

上面的 SQL 会导致 c4 这个索引失效，那么如果是 c4 的范围条件写前面呢？同样的道理，SQL 优化器会对 SQL 语句做优化，优化后的语句成了 explain select * from test_03 where c1='a1' and c2='a2' and c3='a3' and c4>'c4'; 所以自然会用到四个索引了！

再看看下面的 order by 的例子，很显然用于查找的索引有两个就是 c1、c2。c3 用于排序了而不是查找，c4 这个索引没有用到：

所以上面的 SQL 和 explain select * from test_03 where c1='a1' and c2='a2' order by c3; 一样的，和 c4 根本没有关系了

上面这个例子呢主要是说中间兄弟没了，也就是你都没有给我第三层梯子，我怎么通过第四层排序，所以只能硬着头皮完成任务，那就 Using filesort 呗！

用到了一个 c1 索引，但是 c2、c3 用于排序，无 filesort

用了 c1、c2 两个字段索引，但是 c2、c3 用于排序，无 filesort。看看下面这一种情况，为什么 explain select * from test_03 where c1='a1' and c2='a2' and c5='a5' order by c3, c2; 并没有产生文件内排序呢？

因为 c2 已经是常量了，在已知字段是常量的情况下需要排序吗？肯定不需要。所以即使你写成了 order by c3,c2 但是实际上只是对 c3 排序！

由上面的例子可以看出，用到了 c1 来查找，c4 由于中间兄弟的消失是失效的索引，c2、c3 用于分组，但是 c2、c3 如果不按照顺序分组的话就基本是是死掉了，出现了 using temporary，这个是什么意思呢？看这个 Explain 中的 Using temporary

group 表面上是分组，分组之前必排序，所以说和 order by 排序的法则和索引优化的原则是一致的！

定值、范围还是排序，一般 order by 是给个范围。group by 基本上都需要进行排序，会有临时表产生

一般性建议：

对于单键索引，尽量选择针对当前 query 过滤性更好的索引

在选择组合索引的时候，当前 Query 中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

在选择组合索引的时候，尽量选择可以能包含当前 query 中的 where 子句中更多字段的索引

尽可能通过分析统计信息和调整 query 的写法来达到选择合适索引的目的