实例+分析带你掌握索引失效的常见原因&如何避免索引失效

说明：该篇文章主要是通过 explain 关键字分析索引是否失效，关于explain关键字各个关键字的说明可以查看： 一文看懂MySQL中explain关键字的作用

前置工作

# 建表
CREATE TABLE staffs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (24)  NULL DEFAULT '' COMMENT '姓名',
  age INT NOT NULL DEFAULT 0 COMMENT '年龄',
  pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位',
  add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) CHARSET utf8 COMMENT '员工记录表' ;
 # 插入数据
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());
SELECT * FROM staffs;
 # 创建索引，
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

各个索引列key_len的长度，后面通过这个参数来判断哪几个索引用到了，哪几个索引没有用到。

# 一个索引列
EXPLAIN SELECT * FROM staffs WHERE name='july';

# 二个索引列
EXPLAIN SELECT * FROM staffs WHERE name='july' and age = 23;

# 三个索引列
EXPLAIN SELECT * FROM staffs WHERE name='july' and age = 23 AND pos = 'dev';

1、最佳左前缀法则

带头大哥不能死，中间兄弟不能断(出自阳哥)：指的是匹配索引列时，要按照索引创建的顺序进行匹配，若直接跳过第一个，则所有的索引都将失效，若匹配第一个，跳过第二个，则第二个后面的索引都将失效。

# 跳过第一个索引列，直接匹配第二个和第三个
EXPLAIN SELECT * FROM staffs WHERE age = 23 AND pos = 'dev';

可以看到并没有用到索引，即索引失效

# 匹配第一个，跳过第二个，匹配第三个
EXPLAIN SELECT * FROM staffs WHERE name='july' AND pos = 'dev';

可以看到 key_len 为75，根据上面的对照 ，可以知道只有第一列的索引有用，第三列的索引失效

特殊情况：不按索引顺序进行匹配

# 不按索引的顺序进行匹配也可以使用到索引，只要不 断
# 因为MySQL有自己的优化器，但会加大性能的消耗
# 所以一般建议按照索引的的顺序进行匹配
EXPLAIN SELECT * FROM staffs WHERE age = 23 AND pos = 'dev' and name = 'july';

2、不要在索引列上做任何操作

不要在索引列上进行计算、函数、自动或手动的类型转换，会导致索引失效转而向全表扫描，无论是隐式的还是显式的。

explain select * from staffs where name='july';

可以看到，效果还不错

# 在索引列上进行操作
explain select * from staffs where left(name,4)='july'

可以看到进行了全表扫描，索引失效

隐式的数据类型转换，如字符串不加单引号会进行隐式的数据类型转换从而索引会失效

# varchar类型不加单引号会导致隐式类型转换，从而索引失效
EXPLAIN SELECT * FROM staffs WHERE name = 2000 and age =23 AND pos = 'dev';

3、范围后面全失效

>、< 、 != 会导致后面的索引失效

注意：>、< 、 != 是导致后面的索引失效

# 这是正常的情况
EXPLAIN SELECT * FROM staffs WHERE name = 'july' and age =23 AND pos = 'dev';

可以看到三个索引都用到了

# 范围匹配导致索引失效
EXPLAIN SELECT * FROM staffs WHERE name = 'july' and age > 20 AND pos = 'dev';

可以看到只用到了两个索引列

以 like 通配符开头会导致索引失效

# 先看不以like通配符开头
EXPLAIN SELECT * 
FROM staffs 
WHERE name like 'ju%ly%' and age =23 AND pos = 'dev';

# 以like通配符开头会导致索引失效，解决方法：覆盖索引
EXPLAIN SELECT * 
FROM staffs 
WHERE name like '%july' and age =23 AND pos = 'dev';

以 like 通配符开头的查询导致索引失效了

解决以 like 通配符查询的索引失效问题

解决方法：覆盖索引，即select后面查询的字段是索引列即可

# 覆盖索引，其中id为主键，name、age、pos为聚集索引
EXPLAIN SELECT id,name,age,pos 
FROM staffs
WHERE name like '%july' and age =23 AND pos = 'dev';

is null 和 is not null 会导致索引失效

我在测试的时候， MySQL5.7 中 会导致索引失效，
在 MySQL8.0 中又不会失效，这里就不上图了，具体原因我也不晓得（心情复杂）。

4、尽量写字段不写 *

• *会导致全表扫描

5、or会导致索引失效

# or 会导致索引失效
EXPLAIN SELECT * FROM staffs WHERE name = 'july' or age =23 AND pos = 'dev';

若有不当之处，望大佬指出！！！