mysql索引失效的原因

十一月 10, 2020

mysql索引失效的原因

部分索引失效的情况在mysql5.6和mysql8.0测出来的不一样

之前文章讲述过，在explain出来的表格中，有一个字段叫做possible keys，表示可能用到的索引，还有另一个字段叫key，表示真正用到的索引。没用到的索引就相当于是失效了。
配上复合索引的底层结构介绍，帮助理解:
https://blog.csdn.net/feichitianxia/article/details/107997795

图片p34staff表

上面这个表建了一条索引ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);

在保证符合这几条规则的情况下，还要注意order by和group by的使用。下面分别介绍这些情况。

1.最左前缀原则

* from staffs where name

1	```select * from staffs where name='July' and age=25;

* from staffs where name

1
2
3

上面三个语句都会用到索引，前两个只用了一部分，第三个用了全部。
但是下面这么写
```select * from staffs where age=25;

并不会用到索引，这就是最左前缀原则： 要从索引的最左边列开始查询，并且不能跳过中间的列

2.不要在索引列上做计算，函数，类型转换操作

计算，函数，类型转换操作都会让索引失效，变成全表扫描。
比如select * from staffs where left(name,4)='July';

3.范围之后的索引都会失效

* from staffs where name

name，age能用到，但是pos索引就用不到了。范围有大于，小于，between，like等。

### 4.尽量使用覆盖索引，不使用select*
查询的列最好就是索引列。因为mysql查完索引只会拿到主键，还需要再查一遍主键索引得到整行记录。
对于上面的例子，```select name,age,pos from staffs where name='July';``` 是最好的。

### 5.使用!=时无法使用索引，会导致全表扫描

比如这个sql，```select * from staffs where name!='July';

explain的结果：
图片p38不等于号索引失效

mysql5.0是这个结果，mysql8.0的type会显示range

6.使用is null和is not null也会让索引失效

不举例了，和上面类似。但是8.0和5.6结果是一样的。

7.like以通配符开头会让索引失效，变成全表扫描

比如select * from staffs where name like '%July';,就会让type列变成all，全表扫描。
但是select * from staffs where name like 'July%';,就不会，type会是range。

那么我就要’%July%’查询怎么办?

使用覆盖索引就行，例如这个sqlexplain select id,name,pos from staffs where name like '%July%';

8.使用字符串的时候不加单引号会导致索引失效

如果对于原本是字符串的类型，我们输入数字，name=2000，不加单引号的话mysql会自己进行类型转换，这违背了第二条规则。

9.or也会导致索引失效

在5.6explain的结果：如果不是覆盖索引，都会整个表查询，type为all。若是8.0，两次type都是range。

下面介绍order by和group by导致的问题。

order by导致索引失效和filesort

一个表的五列，为c1~~c5,但是只对c1~~c4创建了复合索引，并且顺序是c1~c4.看下面的几条sql

select * from test where c1='a1' and c5='a5' order by c3,c2
使用explain得到查询结果会出现filesort。原因是order by的顺序与索引的顺序不一致。
select * from test where c1='a1' and c5='a5' order by c2,c3
使用索引中没有的列并不会导致索引失效。
select * from test where c1='a1' and c2='a2' and c5='a5' order by c3,c2
与1不同的是，本次explain结果不会出现filesort。因为本次语句已经确定了c2，所以order by c2不起作用。
select * from test03 where c1>'a1' order by c2本次查询虽然索引不会失效，但是会出现filesort。
select * from test03 where c1>'a1' order by c2,c1索引不失效，order by的顺序与索引顺序不一致，出现filesort。

group by导致索引失效和filesort

表同上。
group by实际上是先排序，再分组。并且遵照最佳左前缀原则。

select * from test where c1='a1' and c4='a4' group by c2,c3因为c1后面直接就是c4了，根据最左前缀原则，c4断掉了，索引用不上，只用上了c1。
select * from test where c1='a1' and c4='a4' group by c3,c2把方案一的group by调换位置，extra字段会出现using temporary，出现这个字段我们就知道性能下降了。
filesort的情况和order by一样。

额外提一点索引失效的情况，如果Innodb的update语句，where的条件没有索引，就会把正常的行锁升级为表锁。

查看评论