mysql使用索引的原则
部分索引失效的情况在mysql5.6和mysql8.0测出来的不一样
之前文章讲述过,在explain出来的表格中,有一个字段叫做possible keys,表示可能用到的索引,还有另一个字段叫key,表示真正用到的索引。没用到的索引就相当于是失效了。
配上复合索引的底层结构介绍,帮助理解:
https://blog.csdn.net/feichitianxia/article/details/107997795
上面这个表建了一条索引ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
索引使用的规则
- 最左前缀原则
- 不要在索引列上做计算,函数,类型转换操作
- 范围之后的索引都会失效
- 尽量使用覆盖索引,不使用select*
- 使用!=时无法使用索引,会导致全表扫描
- 使用is null和is not null也会让索引失效
- like以通配符开头会让索引失效,变成全表扫描
- 使用字符串的时候不加单引号会导致索引失效
- or也会导致索引失效
在保证符合这几条规则的情况下,还要注意order by和group by的使用。下面分别介绍这些情况。
1.最左前缀原则
select * from staffs where name='July';
select * from staffs where name='July' and age=25;
select * from staffs where name='July' and age=25 and pos='dev';
上面三个语句都会用到索引,前两个只用了一部分,第三个用了全部。
但是下面这么写
select * from staffs where age=25;
并不会用到索引,这就是最左前缀原则: 要从索引的最左边列开始查询,并且不能跳过中间的列
2.不要在索引列上做计算,函数,类型转换操作
计算,函数,类型转换操作都会让索引失效,变成全表扫描。
比如select * from staffs where left(name,4)='July';
3.范围之后的索引都会失效
select * from staffs where name='July' and age>25 and pos='dev';
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的结果:
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
一个表的五列,为c1c5,但是只对c1c4创建了复合索引,并且顺序是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的条件没有索引,就会把正常的行锁升级为表锁。