高频面试题-mysql索引失效的情况

mysql索引失效的情况

1：like通配符可能导致索引失效

并不是用了like通配符，索引一定失效，而是like查询是以%开头，才会导致索引失效。

select * from xxx where name like '%xx' #失效
select * from xxx where name like '%xx%' #失效
select * from xxx where name like 'xx%' #不失效
select name from xxx where name like '%xx%' #不失效(覆盖索引)

2：or语句前后没有同时使用索引。

select * from xxx where age = 1 or user_id = 2

当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

对于or+没有索引的age这种情况，假设它走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并如果它一开始就走全表扫描，直接一遍扫描就完事。 mysql是有优化器的，处于效率与成本，遇到or条件，索引可能失效，看起来也合情合理。

3：is null,is not null可能会导致索引失效

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

如果字段允许为空，则is null走 ref 类型的索引，而is not null走 range 类型的索引(千万注意，如果返回数据量过大，也会索引失效)

4：索引列上有计算,函数

#索引有计算
explain  select * from xxx where  height+1 =7;
#索引有函数
explain  select * from xxx where  SUBSTR(height,1,1)=8;

5：隐式类型转换导致索引失效

#不会使用name的索引
explain select * from xxx where name=123;
#使用到索引
explain select * from xxx where name='123';

name字段是VARCAHR类型的，但是比较的值是INT类型的，name的值会被隐式的转换为INT类型再比较，中间相当于有一个将字符串转为INT类型的函数。

6：不等于(!=,<>)，not in可能造成索引失效

#name字段为主键，索引不失效(range索引)
explain select * from xxx where name!='123';
explain select * from xxx where name not in (7,8);

#age字段不是主键，即使age字段设索引也会失效
explain select * from xxx where age!='18';
explain select * from xxx where age not in (7,8);

需要特别说明的是mysql5.7和5.8不同的版本效果不一样，5.7中这种情况sql执行结果是全表扫描，而5.8中使用了 range 类型索引。5.7中如果想使用索引该怎么办呢？答案：使用大于和小于代替不等于。(千万注意，如果返回数据量过大，也会索引失效)

7：联合索引不满足最左匹配原则

例如创建了联合索引 age,name,height,不满足最左匹配原则时，索引失效 :::tip 什么是最左匹配原则例如你建立一个索引：idx_code_age_name( code , age , name )联合索引

#where 条件后的字段包含了联合索引的所有索引字段，并且顺序是按照： code 、 age 、name
explain  select * from test1 where code='001' and age=18  and  name='张飞'
#可以看出已经走了联合索引idx_code_name_age,索引是使用充分的，索引使用效率最佳

#where 条件后的字段包含了联合索引的所有索引字段，顺序是不按照： code 、 age 、name。
explain  select * from test1 where code='001' and  name='张飞' and age=18
#可以看出执行结果跟第一种情况一样。注意：这种情况比较特殊，在查询之前mysql会自动优化顺序

#where 条件后的字段包含了联合索引中的： code 字段
explain  select * from test1 where code='001'
#也走了索引，但是索引长度有所变化，现在变成了 92 ， 92 = 30*3 + 2 ，只用到了一个索引字段code，索引使用不充分

#where 条件后的字段包含了联合索引中的： age 字段 或者 name 字段
explain  select * from test1 where age=18
explain  select * from test1 where name='张飞';
#全表扫描，所有的索引都失效了

#where 条件后的字段包含了联合索引中的： code 和 age 字段
explain  select * from test1 where code='001' and age=18;
#走了索引，但是索引长度变成了： 96 ， 96 = 30*3 + 2 + 4 ，只用到了两个索引字段code和age，索引使用也不充分。

#where 条件后的字段包含了联合索引中的： code 和 name 字段
explain  select * from test1 where code='001' and  name='张飞';
#索引长度跟第3种情况一样，长度也是 92 。也就是说只用到了一个索引字段 code ，而 age 字段的索引失效了

#where条件后的字段包含了联合索引中的：age 和 name 字段
explain  select * from test1 where age=18  and  name='张飞';
#全表扫描，所有的索引都失效了

:::

:::tip 如果中间出现断层，如： code、name ，只会走第一个索引code，从断层后的索引都会失效 SQL语句中字段的顺序不需要和联合索引中定义的字段顺序一致，查询优化器会自己调整顺序，mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。所以，当然是我们能尽量的利用到索引时的查询顺序效率最高咯，所以mysql查询优化器会最终以这种顺序进行查询执行 :::

8：查询数据量过大

mysql 通过索引扫描的行记录数超过全表的10%~30% 左右，优化器也可能不会走索引，自动变成全表扫描。

9：字符集不统一

字符集如果不同，会存在隐式的转换，索引也会失效，所有应该使用相同的字符集，防止这种情况发生。

10：范围索引列没有放到最后

例如联合索引 name,age,height 其中 age需要范围查询（14-18）岁的学生，这时候，要把age放到联合索引列的最后（范围查询放最后，指的是联合索引中的范围列放在最后，不是指where条件中的范围列放最后。如果联合索引中的范围列放在最后了，即使where条件中的范围列没放最后也能正常走到索引）