常见索引失效问题实测总结

以下为工作中汇总常见导致索引失效的案例(除1、2)，

如图，在emp表中建立名为index_name_age_posi的组合索引字段为emp_name、emp_age、emp_posi，

1、全值匹配我最爱

索引字段跟筛选字段完全一致，这是最完美的情况，分步如下：

当emp_name匹配时走了索引，且索引的大小为302；

当emp_name、emp_age匹配时走了索引，且索引的大小为306；

当emp_name、emp_age、emp_posi全部匹配时索引大小为908.

2、最佳左前缀法则：带头大哥不能死，中间兄弟不能断

(1) 带头大哥死了

如图，直接用emp_age、emp_posi匹配时没有走索引(type为ALL说明是进行了全表扫描)；
(2) 中间兄弟断了

从key_len为302不难看出索引只匹配了emp_name。

3、不在索引列上做任何操作(计算、函数、(自动或手动)类型转换)，会导致索引失效

如图，虽然匹配字段为索引全部字段，但是在emp_age字段上进行减法运算时却只有部分匹配(这里只匹配到了emp_name)；但是我们换一种写法后又是另一种情况。

在等于号右边进行计算时不会导致索引失效。

4、范围之后全失效：范围之后的索引失效

查询emp_age大于52的数据时看到的key_len为306，细心的小伙伴会发现这不正好是匹配了emp_name、emp_age么，因此在使用 > < in 等时会导致范围后的索引失效。

5、尽量使用覆盖索引(索隐列和查询列一致)，减少select *

索引列和查询列一致时数据库会直接从索引中取数据，这种效率是最快的，mysql会自动将我们写的sql进行编排、优化。

6、mysql在使用不等于的时候无法使用索引导致全表扫描

如图，匹配字段为索引的全部字段，但是在emp_posi筛选时使用了不等于导致进行了全表扫描，因此在使用不等于时会直接导致索引全部失效，注意是全部失效。

7、is null, is not null 也无法使用索引，导致部分失效

如图，key_len为306，说明emp_posi没有匹配到索引， is not null 时 导致部分索引失效。

8、 like 以通配符开头 % 无法使用索引，导致部分失效

如图，like通配符 开头% key_len 为306，结尾% key_len为908，说明开头%会导致部分索引失效。

9、字符串不加单引号导致索引失效

10、用 or 无法使用索引导致全表扫描

如图，匹配字段为索引全部字段，在emp_posi 用 or 后 直接进行了全表扫描。

总结：以上总结导致索引失效场景中，只有使用不等于和使用or时会导致索引全部失效，其余为部分失效！

以上案例均为本人实测结果，如有不对之处请进来的小伙伴指出来，共同进步！