【面试】MySQL索引失效分析

2019-09-22

字数统计: 974字 | 阅读时长≈ 4分

本文主要向大家介绍了MySQL数据库之索引会失效的原因分析及解决索引失效的方法，通过具体的内容向大家展现，希望对大家学习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';

mark

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

mark

上面的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这个索引没有用到：

mark