索引失效(应该避免)
导致索引失效的一些准则,也算是一些规范,在实际开发中,在满足业务的条件下,可以尽量的避免,以达到优化的效果。
1.首先新建一张员工表,并插入数据,建立复合索引,执行语句,
1 | CREATE TABLE staffs( |
索引失效准则:
- 全值匹配我最爱
- 最佳左前缀法则:如果索引了多例,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
- 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
- 存储引擎不能使用索引中范围条件右边的列
- 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少
select *
- mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
is null
,is not null
也无法使用索引(早期版本不能走索引,后续版本应该优化过,可以走索引)- like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描操作
- 字符串不加单引号索引失效
- 少用or,用它连接时会索引失效
最佳左匹配法则:带头大哥不能死,中间兄弟不能断
概念:如果索引了多列,要遵守最左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引中的列,
只有第一个查询条件 name 的时候:
- type=ref,进行了优化
- key = index_staffs_nameAgePos 表明索引生效
- ref = const ,这个常量就是查询时的 ‘July’ 字符串常量
1 | EXPLAIN SELECT * FROM staffs WHERE name = 'July'; |
在name的基础上加上age查询条件的时候:
- type=ref,进行了优化
- key = index_staffs_nameAgePos 表明索引生效
- ref = const,const ,两个常量分别就代表了“July”和“23”,这两个查询条件
1 | EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 23; |
在name、age的基础上再加上pos查询条件:
- type=ref,进行了优化
- key = index_staffs_nameAgePos 表明索引生效
- ref = const,const,const ,三个常量分别就代表了“July”和“23”和“dev”,这三个查询条件
1 | EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 23 AND pos = 'dev'; |
并且可以注意到,key_len的数值也在变化:74->78->140,就算忘记了它的涵义,但是key_len那么明显,什么的长度,别忘了这是在干嘛,执行一条sql,并且使用索引,就算还是不知道是索引长度,就算把它作为sql的执行长度,也应该是越短越好,
尝试去掉name查询条件:
- type=ALL,毫无疑问,进行了全表扫描,
- key = null,表明索引并没有生效
- ref = null,说明ref没有索引字段生效,
1 | EXPLAIN SELECT * FROM staffs WHERE age = 23 AND pos = 'dev'; |
尝试去掉age查询条件:
- type=ALL,毫无疑问,进行了全表扫描,
- key = null,表明索引并没有生效
- ref = null,说明ref没有索引字段生效,
1 | EXPLAIN SELECT * FROM staffs WHERE pos = 'dev'; |
毫无疑问,当name条件去掉以后,索引就不再生效了,说明老大哥确实不能死,死了后面都不再生效,
那试试去掉age查询字段,留下name和pos呢:
- type=ref,毫无疑问,索引生效了,
- key = index_staffs_nameAgePos,表明索引生效
- ref = const,说明只有一个字段生效了,而查看它的key_len是为74的,而key_len代表的是索引的长度,并且这个数字很熟悉,因为在前面单独查询name的时候就为74,那就说明这个const代表的只会是“July”这个查询条件,
这又证明了当中间条件age去掉之后,后面的索引也不再生效了,说明了带头大哥不能死,中间兄弟也不能断,
不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
还是先只设置name一个查询条件:
- type=ref,毫无疑问,索引生效了,
- key = index_staffs_nameAgePos,表明索引生效
- ref = const,代表了”July”查询条件
1 | EXPLAIN SELECT * FROM staffs WHERE name = 'July'; |
对name使用left进行截断操作:
- type=ALL,毫无疑问,进行了全表扫描,
- key = null,表明索引并没有生效
- ref = null,说明ref没有索引字段生效,
1 | EXPLAIN SELECT * FROM staffs WHERE LEFT(name,4) = 'July'; |
而我们查看两条sql的查询结果:
1 | SELECT * FROM staffs WHERE LEFT(name,4) = 'July'; |
毫无疑问,结果是一致的,但是name进行了left截断操作的语句的索引失效了,所以,不要对索引的字段进行计算,将会导致索引失效,
存储引擎不能使用索引中范围条件右边的列
先看一下全值匹配,也就是三个查询条件都是=:
- type=ref,进行了优化
- key = index_staffs_nameAgePos 表明索引生效
- ref = const,const,const ,三个常量分别就代表了“July”和“23”和“dev”,这三个查询条件
1 | EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 23 AND pos = 'dev'; |
那试一试将age改为范围查询条件呢:
- type = range ,表示范围扫描
- key = index_staffs_nameAgePos 表示索引生效
- ref = NULL ,且key_len为78,表明了范围搜索使其后面的索引均失效
1 | EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age > 25 AND pos = 'dev'; |
可以看出来,在范围查询后的索引会失效,所以,不要使用索引范围右边(也就是后面)的字段(列),
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少 select *
先使用select * 来查询:
- type = ref,表示范围扫描
- key = index_staffs_nameAgePos 表示索引生效
- ref = const,const,const,分别代表了三个常量
1 | EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 25 AND pos = 'dev'; |
再单独查询字段:
- type = ref,表示范围扫描
- key = index_staffs_nameAgePos 表示索引生效
- ref = const,const,const,分别代表了三个常量
- 而这里的Extra变为了Using index,这是覆盖索引,表示使用索引列进行查询,很大程度的提高查询的效率,
1 | EXPLAIN SELECT name, age, pos FROM staffs WHERE name = 'July'AND age = 25 AND pos = 'dev'; |
所以,*最好使用覆盖索引,索引列和查询列保持一致,减少select * 查询*,
mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
在使用 != 会 <> 时会导致索引失效:
- type=ALL,毫无疑问,进行了全表扫描,
- key = null,表明索引并没有生效
- ref = null,说明ref没有索引字段生效,
1 | EXPLAIN SELECT * FROM staffs WHERE name != 'July'; |
有的时候确实一些业务要求这样写,不能为了索引而索引,该写还是要写,只能说在保证业务的情况下去进行优化,
is null,is not null也无法使用索引
使用is null,is not null导致索引失效:
- type=ALL,毫无疑问,进行了全表扫描,
- key = null,表明索引并没有生效
- ref = null,说明ref没有索引字段生效,
1 | EXPLAIN SELECT * FROM staffs WHERE name is null; |
like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描操作,所以like % 写在查询条件的最右边
使用like,前后都添加通配符:
- type=ALL,毫无疑问,进行了全表扫描,
- key = null,表明索引并没有生效
- ref = null,说明ref没有索引字段生效,
1 | EXPLAIN SELECT * FROM staffs WHERE name like '%July%'; |
使用like,前面加通配符,后面不加:
- type=ALL,毫无疑问,进行了全表扫描,
- key = null,表明索引并没有生效
- ref = null,说明ref没有索引字段生效,
1 | EXPLAIN SELECT * FROM staffs WHERE name like '%July'; |
使用like,后面加通配符,前面不加:
- type = range ,表示like是范围扫描
- key = index_staffs_nameAgePos 表示索引生效
- ref = NULL ,表明了范围搜索使其后面的索引均失效,那是不是代表
1 | EXPLAIN SELECT * FROM staffs WHERE name like 'July%'; |
由上面三种情况可以得出,like的通配符%最好加右边,但是有的时候就是需要前后加通配符,如何解决like ‘%字符串%’时索引不被使用的方法?
解决like ‘%字符串%’ 索引失效的问题:覆盖索引
1.创建一张user表,并插入部分数据,执行语句,
1 | CREATE TABLE `tbl_user`( |
2.新建一条复合索引,为了解决通配符问题,使用覆盖索引,
1 | CREATE INDEX idx_user_nameAge ON tbl_user(name, age); |
3.测试覆盖索引,也就是说name和age这两个字段,在作为索引的情况下,是否能解决通配符后不被引用的问题,
1 | EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE '%aa%'; |
查询name,age:
- type=index,索引生效了,
- key = idx_user_nameAge,表明索引生效
1 | EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE '%aa%'; |
查询id:
- type=index,索引生效了,默认主键索引
- key = idx_user_nameAge,表明索引生效
1 | EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%'; |
单独查询name和age:
- type=index,索引生效了,
- key = idx_user_nameAge,表明索引生效
1 | EXPLAIN SELECT name FROM tbl_user WHERE NAME LIKE '%aa%'; |
查询id和name、age的搭配查询的情况:
1 | EXPLAIN SELECT id, name FROM tbl_user WHERE NAME LIKE '%aa%'; |
- type=index,索引生效了,
- key = idx_user_nameAge,表明索引生效
上面的sql,查询的东西至少都是和索引有所关联,所以都生效了,那么再测试一个查询不属于索引的字段呢,
查询id, name, age, email 或者 *:
1 | EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%'; |
- type=ALL,毫无疑问,进行了全表扫描,
- key = null,表明索引并没有生效
- ref = null,说明ref没有索引字段生效,
从上述的测试可以看出,如果业务中非要用双百分号通配符like,而要实现索引生效的最好方式,就是使用覆盖索引,也就是查询的字段加入索引,
字符串不加单引号索引失效
先查看正确的情况,也就是正常使用单引号:
- type = ref,表示范围扫描
- key = index_staffs_nameAgePos 表示索引生效
- ref = const,代表了一个常量
1 | explain select * from staffs where name='2000'; |
而忘记写单引号的情况,那么 mysql 会为我们进行隐式的类型转换,但凡进行了类型转换,索引都会失效,如果还记得的话,在第三条准则有说:
- type = ALL,表示全表查询
- key = null表示索引失效
- ref = null,代表没有索引字段
1 | explain select * from staffs where name=2000; |
少用or,用它连接时会索引失效
使用or的情况;
- type = ALL,表示全表查询
- key = null表示索引失效
- ref = null,代表没有索引字段
1 | explain select * from staffs where name='z3' or name = 'July'; |
总结,十大失效准则,避免这些情况就是生效原则,一些常见的索引处理会使用到,
小demo;