mysql索引失效的常见原因和如何用好索引

mysql索引失效的常见原因和如何⽤好索引本篇主要介绍的是索引失效的常见原因和如何⽤好索引，跟上⼀篇正好承上启下，给有需要的朋友⼀个参考。

本⽂将从以下⼏个⽅便进⾏讲解：
1.索引失效常见原因：
2.索引失效常见误区：
3.索引设计的⼏个建议：
准备⼯作
查看当前 mysql 的版本：
select VERSION();
查出当前版本为：8.0.21
创建⼀张表 test1
CREATE TABLE `test1` (
`id` bigint NOT NULL,
`code` varchar(30) NOT NULL,
`age` int NOT NULL,
`name` varchar(30) NOT NULL,
`height` int NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_code_age_name` (`code`,`age`,`name`) USING BTREE,
KEY `idx_height` (`height`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
插⼊两条数据：
INSERT INTO `test1`(`id`, `code`, `name`, `age`,`address`) VALUES (1, '001', '张飞', 18,'7');
INSERT INTO `test1`(`id`, `code`, `name`, `age`,`address`) VALUES (2, '002', '关⽻', 19,'8');
查询⼀下：
select * from test1;
结果：
此外建⽴了两个索引：idx_code_age_name( code , age , name )联合索引和 idx_height(height )普通索引。

⼀. 索引失效常见原因
1.不满⾜最左前缀原则
第1种情况：
where 条件后的字段包含了联合索引的所有索引字段，并且顺序是按照： code 、 age 、name 。

where code='001' and age=18 and name='张飞' ;
结果：
从图中标红的地⽅可以看出已经⾛了联合索引idx_code_name_age，并且索引的长度是 188， 188 = 30 * 3 + 2 + 30 * 3 + 2 + 4 ，索引是使⽤充分的，索引使⽤效率最佳。

有些朋友可能会问：索引长度为什么是这样计算的？
答：请参考《 explain | 索引优化的这把绝世好剑，你真的会⽤吗？》，⾥⾯给出了⾮常详细的讲解。

第2种情况：
where 条件后的字段包含了联合索引的所有索引字段，顺序是不按照： code 、 age 、name。

执⾏sql如下：
explain select * from test1
where code='001' and name='张飞' and age=18;
结果：
从上图中看出执⾏结果跟第⼀种情况⼀样。

注意：这种情况⽐较特殊，在查询之前mysql会⾃动优化顺序。

第3种情况：
where 条件后的字段包含了联合索引中的： code 字段
执⾏sql如下：
explain select * from test1
where code='001';
结果：
从上图看出也⾛了索引，但是索引长度有所变化，现在变成了 92 ， 92 = 30*3 + 2 ，只⽤到了⼀个索引字段code，索引使⽤不充分。

第4种情况：
where 条件后的字段包含了联合索引中的： age 字段
执⾏sql如下：
explain select * from test1
where age=18;
结果：
从上图中看变成了全表扫描，所有的索引都失效了。

第5种情况：
where 条件后的字段包含了联合索引中的： name 字段
执⾏sql如下：
explain select * from test1
where name='张飞';
结果：从上图中看变成了全表扫描，所有的索引都失效了。

第6种情况：
where 条件后的字段包含了联合索引中的： code 和 age 字段
where code='001' and age=18;
结果：
从上图中看出还是⾛了索引，但是索引长度变成了： 96 ， 96 = 30*3 + 2 + 4 ，只⽤到了两个索引字段code和age，索引使⽤也不充分。

第7种情况：
where 条件后的字段包含了联合索引中的： code 和 name 字段
执⾏sql如下：
explain select * from test1
where code='001' and name='张飞';
结果：
从上图中看出⾛的索引长度跟第1种情况⼀样，长度也是 92 。

也就是说只⽤到了⼀个索引字段 code ，⽽ age 字段的索引失效了。

第8种情况：
where 条件后的字段包含了联合索引中的： age 和 name 字段
执⾏sql如下：
explain select * from test1
where age=18 and name='张飞';
结果：
从上图中看出变成了全表扫描，所有的索引都失效了。

⼩结：
1. code
2. code
3. 如果中间出现断层，如： code、name ，只会⾛第⼀个索引code，从断层后的索引都会失效。

4. name name
2.范围索引列没有放在最后
where 条件后的字段 age ⽤了⼤于等于，具体sql如下：
EXPLAIN select * from test1
where code='001' and age>18 and name='张飞' ;
结果：
从上图中看出索引长度变成： 96 ， 96 = 30*3 + 2 + 4 ，只⽤到了两个索引字段 code 和age ，⽽ name 字段的索引失效了。

如果范围查询的语句放到最后：
EXPLAIN select * from test1
where code='001' and name='张飞' and age>18 ;
结果：
什么⿁？怎么索引长度还是： 96 ？
这是⼀个⾮常经典的错误
范围查询放最后是指创建联合索引的字段顺序，现在的顺序是：
再执⾏上⾯的sql，结果：
从上图中看出索引长度变成： 188 ，索引使⽤充分了。

回过头再执⾏刚开始的那条sql：
EXPLAIN select * from test1
where code='001' and age>18 and name='张飞';
结果：
什么？
索引长度也是： 188 。

注意：范围查询放最后，指的是联合索引中的范围列放在最后，不是指where条件中的范围列放最后。

如果联合索引中的范围列放在最后了，即使where条件中的范围列没放最后也能正常⾛到索引。

3.使⽤了select *
其实在《阿⾥巴巴开发⼿册》中也明确说了，禁⽌使⽤select * ，这是为什么呢？
EXPLAIN select * from test1
结果：
从上图中看出⾛了全表扫描。

那么如果查询的是索引列：
EXPLAIN select code,age,name from test1
结果：
从图中可以看出这种情况⾛了全索引扫描，⽐全表扫描效率更⾼。

其实这⾥⽤到了：覆盖索引。

如果 select 的列都是索引列，则被称为覆盖索引。

如果 select 的列不只包含索引列，则需要回表，即回到表中再查询出其他列，效率相当更低⼀些。

select * ⼤概率需要查询⾮索引列，需要回表，因此要少⽤。

当然，本⽂中很多⽰例都使⽤了 select * ，主要是我表中只有两条数据，为了⽅便演⽰，正常业务代码中是要杜绝这种写法的。

4.索引列上有计算
执⾏sql如下：
explain select * from test1
where height+1 =7;
结果：从上图中可以看出变成全表扫描了，由此可见在索引列上有计算，索引会失效。

5.索引列上使⽤了函数
如果在索引列加某个函数，具体sql如下：
explain select * from test1
where SUBSTR(height,1,1)=8;
结果：从上图中可以看出变成全表扫描了，由此可见在索引列上加了函数，索引也会失效。

6.字符类型没加引号
废话不多说直接上sql：
从图中看出⾛的全表扫描，索引失效了。

为什么索引会失效呢？
这⾥有些朋友可能会有点懵。

答： name 字段是字符类型，⽽等于号右边的是数字类型，类型不匹配导致索引丢失。

所以在使⽤字符类型字段做判断时，⼀定要加上单引号。

类型不匹配导致索引丢失问题，是我们平时⼯作中⾮常容易忽视的问题，⼀定要引起⾜够的重视7.⽤is null和is not null没注意字段是否允许为空前⾯创建的test1表中height字段是⾮空的。

查询sql如下：
explain select * from test1
where height is null;
explain select * from test1
where height is not null;
结果都是：从上图中看出都是全表扫描，索引都失效了。

如果height字段改成允许为空的呢？
上⾯第⼀条sql执⾏结果：
从上图中看出⾛了 ref 类型的索引。

上⾯第⼆条sql执⾏结果：
从上图中看出⾛了 range 类型的索引。

⼩结
1. 如果字段不允许为空，则is null 和 is not null这两种情况索引都会失效。

2. 如果字段允许为空，则is null⾛ ref 类型的索引，⽽is not null⾛ range 类型的索引。

8.like查询左边有%
like查询主要有三种情况：
like '%a'
like 'a%'
like '%a%'
先看看第⼀种情况：
explain select * from test1
where code like '%001';
结果：
从上图看出⾛的全表扫描，索引失效了。

再看看第⼆种情况：
explain select * from test1
从上图看出⾛的 range 类型的索引。

最后看看第三种情况：
explain select * from test1
where code like '%001%';
结果：从上图看出⾛的全表扫描，索引也失效了。

从这三种结果看出 like 语句只有 % 在右边才能⾛索引。

如果有些场景就是要使⽤ like 语句 % 在左边该怎么办呢？
答案：使⽤覆盖索引
具体sql如下：
explain select code,age,name from test1
where code like '%001%';
结果：
从上图看出⾛的 index 类型的全索引扫描，相对于全表扫描性能更好。

当然，最佳实践是在 sql 中要避免 like 语句 % 在左边的情况，如果有这种业务场景可以使⽤es 代替 mysql 存储数据。

⼩结：
like '%a' 索引失效
like 'a%' ⾛range类型索引
like '%a%' 索引失效
9.使⽤or关键字时没有注意
⽤法如下：
explain select * from test1
where height = 8 or height = 9;
结果：
从上图中看出⾛了 range 类型的索引，不是没问题吗？
再把sql改⼀下：
explain select * from test1
where code = '001' or height = 8;
结果：
从上图中可以看出变成了全表扫描，索引失效了。

我们不妨单独查询⼀下：
explain select * from test1
where code = '001';
结果：
和
explain select * from test1
where height = 8;
结果：
两种单独查询的情况都⾛了 ref 类型的索引，但是使⽤ or 关键字后sql的索引会失效。

那么，我们在想使⽤ or 的场景，⼜想让索引有效，该怎么办呢？
没错，使⽤ union 关键字，但是跟 or 关键字的语法稍微有点区别，不过查询的数据结果是⼀样的。

上⾯sql执⾏结果如下：
我们看到⾛了 ref 类型索引。

or关键字会让索引失效，可以⽤union代替
⼆. 索引失效的常见误区
1.使⽤not in会导致索引失效
⽤法如下：
explain select * from test1
where height not in (7,8);
结果：
从上图中看出是⾛了 range 类型索引的，并没失效。

需要特别说明的是mysql5.7和5.8不同的版本效果不⼀样，5.7中这种情况sql执⾏结果是全表扫描，⽽5.8中使⽤了 range 类型索引。

2.使⽤不等于号会导致索引失效
⽤法如下：
explain select * from test1
where height!=8;
结果：
从图中看出⾛了 range 类型的索引。

需要特别说明的是mysql5.7和5.8不同的版本效果不⼀样，5.7中这种情况sql执⾏结果是全表扫描，⽽5.8中使⽤了 range 类型索引。

5.7中如果想使⽤索引该怎么办呢？答案：使⽤⼤于和⼩于代替不等于。

在这⾥温馨的提醒⼀声，不等于号不只是 != ，还包括 <> 。

3.order by索引字段顺序不当导致索引失效
sql中除了 where 后⾯的字段能⾛索引之外， order by 后⾯的字段也能⾛索引。

EXPLAIN select * from test1
where code='001' order by age,name;
结果：
从上图中看出⾛了 ref 类型的索引，索引长度是 92 ，并且没有额外信息。

但是如果把 order by 后⾯的条件改成如下两种排序：
EXPLAIN select * from test1
where code='001' order by name;
EXPLAIN select * from test1
where code='001' order by name,age;
结果：
从上图中看出还是⾛了 ref 类型的索引，索引长度是 92 ，但是额外信息中提⽰： Using filesort ，即按⽂件重排序。

上⾯两个例⼦能够看出有没有使⽤索引跟 where 后⾯的条件有关，⽽跟 order by 后⾯的字段没关系。

⽽需不需要按⽂件重排序，则跟 order by 后⾯的字段有直接关系。

问题来了，额外信息中提⽰： Using filesort 这种该如何优化？
三. 索引设计的⼏个建议：
1. 优先使⽤唯⼀索引，能够快速定位
2. 为常⽤查询字段建索引
3. 为排序、分组和联合查询字段建索引
4. ⼀张表的索引数量不超过5个
5. 表数据量少，可以不⽤建索引
6. 尽量使⽤占⽤空间⼩的字段建索引
7. ⽤idx_或unx_等前缀命名索引，⽅⾯查找
8. 删除没⽤的索引，因为它会占⼀定空间
四. 彩蛋：
特别说明：索引失效除了上述的常见问题之外， mysql 通过索引扫描的⾏记录数超过全表的10%~30% 左右,优化器也可能不会⾛索引，⾃动变成全表扫描。

送给⼤家⼀个避坑⼝诀：
全职匹配我最爱，最左前缀要遵守
带头⼤哥不能死，中间兄弟不能断
索引列上少计算，范围列后全失效
like百分写最右，覆盖索引不写*
不等空值还有or ，索引影响要注意；
字符字段引号不能丢，sql优化有诀窍。