MySQL索引失效总结

1、前言

本文是基于MySQL的Innodb引擎的结论

索引是MySQL中重要的一部分，良好的索引可以加快查询速度，但使用不当也会使索引失效。

在了解索引失效前，先来回顾几个MySQL中重要的知识点

2、主索引、辅助索引、回表

2.1 主索引

主索引即主键索引，Innodb引擎下每张表中都会有主键，即使没有指定主键，也会隐式生成主键。

主索引中存储着行所有信息

2.2 辅助索引（二级索引）

简单理解的话，除了主索引外的索引，都可以称为二级索引，比如唯一索引、普通索引、联合索引等。

此外，辅助索引中存储着主键的值及建立索引的字段的值

2.3 回表

当使用二级索引查询时，如果查询的字段上没有索引的话，会导致回表，具体的说，会使用辅助索引中存储的主键的值再次查询数据，把缺少索引的字段的值查询出来。

比如，有a、b、c三个字段，其中a上有主键索引，b上有唯一索引，当使用select b,c from biao where b = 'xxx'查询时，由于c上没有索引，会导致回表查询

3、数据准备

假如存在这样一张表

CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

其中id上有主键索引，name上有普通索引，表中有1w+数据

4、失效分析

4.1 like模糊查询

只看前导模糊查询，其余情况都会走索引

只查询索引字段值时，type是index，会使用索引全表扫描

当加了age，即没有索引的字段时，type是ALL，此时没有走索引

4.2 隐式类型转换

只查询索引字段值时，type是index，会使用索引全表扫描

增加没有索引的字段时，type是ALL，没有使用索引

4.3 索隐列使用函数计算

只查询索引字段值时，type是index，会使用索引全表扫描

增加没有索引的字段时，type是ALL，没有使用索引

4.4 进行不等计算时（!=、<>）

只查询索引字段值时，type是range，会使用索引全表扫描

增加没有索引的字段时，type是ALL，没有使用索引

4.5 使用or连接没有索引的字段

即使只查询name，有索引的字段值，type也是ALL，没有使用索引。

4.6 组合索引

这里，将之前的name索引删除，建立name、age的联合索引

已经建立了name、age的联合索引，为了测试，再建立一个列sex列

很多文章中都写着联合索引使用最左前缀匹配，当查询时没有最前面的索引列时，索引会失效，这也需要是否查询了没有索引的列

此时没有利用最左前缀原则，只查询索引列的值时，type为index，会使用索引全表扫描

查询了没有索引的列的值，type为ALL，没有使用索引

5、总结

• 没有产生回表时，like前导模糊查询、隐式类型转换、索引列使用函数、使用不等（<>、!=）、组合索引不满足最左匹配规则时，都会使用索引全表扫描
• 产生回表时，上述几种情况会使用ALL全表扫描，有些许区别，至于哪个快，并不一定
• or查询时有使用没有索引的列时，索引会失效