前言：

上篇文章我们分析了慢sql如何排查，往往Mysql的索引失效是一个比较常见的问题，这种情况一般会在慢sql发生时需要考虑，考虑是否存在索引失效的问题。

在排查索引失效的时候，第一步一定是找到要分析的SQL语句，然后通过explain查看他的执行计划。主要关注type、key和extra这几个字段。

explain执行计划关键词

一个执行计划中，共有12个字段，每个字段都挺重要的，先来介绍下这12个字段

1. id:执行计划中每个操作的唯一标识符。对于一条查询语句，每个操作都有一个唯一的id。但是在多表join的时候，一次explain中的多条记录的id是相同的。
2. select type:操作的类型。常见的类型包括SIMPLE、PRIMARY、SUBQUERY、UNION等。不同类型的操作会影响查询的执行效率。
3. table:当前操作所涉及的表。
4. partitions:当前操作所涉及的分区。
5. type:表示查询时所使用的索引类型，包括ALL、index、range、ref、eq ref、const等。
6. possible keys:表示可能被查询优化器选择使用的索引。
7. key:表示查询优化器选择使用的索引。
8. key len:表示索引的长度。索引的长度越短，查询时的效率越高。
9. ref:用来表示哪些列或常量被用来与key列中命名的索引进行比较。
10. rows:表示此操作需要扫描的行数，即扫描表中多少行才能得到结果。
11. filtered:表示此操作过滤掉的行数占扫描行数的百分比。该值越大，表示查询结果越准确。
12. Extra:表示其他额外的信息，包括Usingindex、Using filesort、Using temporary等。

是否走索引分析

通过key+type+extra来判断一条SQL语句是否用到了索引。如果有用到索引，那么是走了覆盖索引呢?还是索引下推呢?还是扫描了整颗索引树呢?或者是用到了索引跳跃扫描等等。

一般来说，比较理想的走索引的话，应该是以下几种情况:

• 首先，key一定要有值，不能是NULL
• 其次，type应该是ref、eqref、range、const等这几个
• 还有，extra的话，如果是NULL，或者usingindex，usingindex condition都是可以的

如果通过执行计划之后，发现一条SQL没有走索引，比如type=ALL，key=NULL,extra= Using where。

那么就要进一步分析没有走索引的原因了。我们需要知道的是，到底要不要走索引，走哪个索引，是MySQL的优G化器决定的，他会根据预估的成本来做一个决定。

那么，有以下这么几种情况可能会导致没走索引:

1. 没有正确创建索引:当查询语句中的where条件中的字段，没有创建索引，或者不符合最左前缀匹配的话，就是没有正确的创建索引。
2. 引区分度不高:如果索引的区分度不够高，那么可能会不走索引，因为这种情况下走索引的效率并不高。
3. 表太小:当表中的数据很小，优化器认为扫全表的成本也不高的时候，也可能不走索引
4. 查询语句中，索引字段因为用到了函数、类型不一致等导致了索引失效

上述对应情况逐一分析

1. 如果没有正确创建索引，那么就根据SQL语句，创建合适的索引。如果没有遵守最左前缀那么就调整一下索引或者修改SQL语句。
2. 索引区分度不高的话，那么就考虑换一个索引字段。
3. 表太小这种情况确实也没啥优化的必要了，用不用索引可能影响不大的
4. 排查具体的失效原因，然后针对性的调整SQL语句就行了。

可能导致索引失效的情况

创建一张表(msql5.7)

CREATE TABLEmytable(
id  int(11) NOT NULL  AUTO INCREMENT,
name varchar(50) NOT NULL,
age int(11) DEFAULT NULL,
create time datetime DEFAULT NULL,
 PRIMARY KEY (id)
UNIOUE KEY name(name),
KEY  age( age),
KEY create time (create time)
)ENGINE=INnODB DEFAULT CHARSET=utf8mb4;

insert into mytable(id,name,age,create time)values(1,"cw",20,now());
insert into mytable(id,name,age,create time)values(2,"cw1",21,now());
insert into mytable(id,name,age,create time)values(3,"cw2",22,now());
insert into mytable(id,name,age,create time)values(4,"cw3",20,now());
insert into mytable(id,name,age,create time)values(5,"cw3",15,now());
insert into mytable(id,name,age,create time) values(6,"cw4",43,now());
insert into mytable(id,name,age,create time)values(7,"cw5",32,now());
insert into mytable(id,name,age,create time)values(8,"cw6",12,now());
insert into mytable(id,name,age,create time) values(9,"cw7",1,now());
insert into mytable(id,name,age,create time)values(10,"cw8",43,now());

参与索引计算

以上SQL是可以走索引的，但是如果我们在字段中增加计算的话，就会索引失效:

如何以下形式计算可以走索引

对索引列进行函数操作

以上走索引的，增加函数操作的话，就会索引失效

使用or

select * from mytable where name = 'cw' and age>18;

但是如果使用or的话，并且or两边存在<或者>的使用，就会索引失效

select * from mytable where name = 'cw' or age>18;

如果OR两边都是=判断，并且两个字段都有索引，那么也是可以走索引的，如:

select * from mytable where name = 'cw' or age=18;

like操作

select * from mytable where name like '%cw%';

select * from mytable where name like '%cw';

select * from mytable where name like 'cw%';

select * from mytable where name like 'c%w';

隐式类型转换

select * from mytable where name = 1;

以上情况，name是一个varchar类型，但是我们用int类型查询，这种是会导致索引失效的。

这种情况有一个特例，如果字段类型为int类型，而查询条件添加了单引号或双引号，则Mysql会参数转化为int类型，这种情况也能走索引:

select * from mytable where age= '1';

不等于比较

以下可能走索引的

is not null

以下情况索引失效

order by

当进行order by的时候，如果数据量很小，数据库可能会直接在内存中进行排序，而不使用索引。

in

使用in的时候，有可能走索引，也有可能不走，一般在in中的值比较少的时候可能会走索引优化，但是如果选项比较多的时候，可能会不走索引:

select * from mytable where name in ('cw');

select * from mytable where name in ('cw','hshs','cww');

总结

本篇分析了索引失效的不同情况，旨在帮忙大家在工作中快速定位自己写的sql没走索引的情况分析，更快速的解决索引失效的问题。