MySql 索引失效、回表解析，java后端开发入门

失效 四

========

索引字段作为查询条件时，使用了计算或者函数

请看示例SQL ，索引字段使用了计算：

EXPLAIN SELECT * FROM userinfo WHERE userAge +2= 37;

索引字段因使用计算，失效分析结果：

再看示例，索引字段使用函数：

EXPLAIN SELECT * FROM userinfo WHERE TRIM(userName) = ‘陈七’;

索引字段因使用函数，失效分析结果：

失效 五

========

使用 OR ，条件字段中包含有未设索引字段列

请看示例，表内userProfession字段没有添加索引， 而 userAge添加了索引：

执行SQL使用OR ，同时使用userProfession ，userAge作为条件查找：

EXPLAIN SELECT * FROM userinfo WHERE userProfession=‘教师’ OR userAge = 25;

索引未命中分析结果：

扩展补充，

如果 使用 OR ，作为查找条件的字段都已经添加了索引 ，会是什么情况？

例如 userAge 我们使用了索引，

EXPLAIN SELECT * FROM userinfo WHERE userAge = 23 OR userAge = 35;

这样会命中索引么？

注意了，分情况，因为我们的userAge添加的不是唯一索引，因为年龄嘛，总会有重复：

再看当前数据，可以看的 userAge 为 35 ，userAge为 23 的 都只有一条数据，也就是对应目前表内数据，其实是唯一数据：

这时候，索引分析结果，索引命中了：

可以看的，索引命中了。

那么接着，我们把一个userAge查找条件换成25， 数据表内数据 25 的有两条，不唯一：

EXPLAIN SELECT * FROM userinfo WHERE userAge = 25 OR userAge = 35;

这时候，索引分析结果，索引失效了：

ps： 也就是，若把索引设置成为唯一索引，那么数据库内也就不存在重复数据了，这时候如果使用or 查询同个索引字段列，那么就是命中的。当时往往有时候，就是类似文中的情况。userAge就是设置为不唯一索引，那么就是使用 UNION ALL 去解决或者从代码层面分开查询。

配上使用 UNION ALL的方式：

EXPLAIN

SELECT * FROM userinfo WHERE userAge = 25

UNION ALL

SELECT * FROM userinfo WHERE userAge = 35

索引分析结果，是命中的：

失效 六

========

联合索引**，不满足最佳左前缀原则，导致索引失效**

请看示例，表内 userWeight 、userHeight、userSight 三个字段：

给 userWeight 、userHeight、userSight 这三个字段建立联合索引 ：

注意顺序，最左为 userWeight

这时候，如果SQL 在使用这些字段索引查找时，先看单个字段查找：

单个使用userWeight ：

EXPLAIN SELECT * FROM userinfo WHERE userWeight=‘50’;

索引分析结果，索引命中，因为满足了最佳做前缀原则：

那么我们换成单个使用 userHeight，

EXPLAIN SELECT * FROM userinfo WHERE userHeight=‘180’ ;

索引分析结果，索引失效了：

同样换成 userSight 也是一样，索引失效了，因为这种情形就是没有满足最佳做前缀原则。

对于使用联合索引，还没完。

当我们同时使用联合索引里面的2个或2个以上的字段列时：

使用 userWeight 和 userSight：

EXPLAIN SELECT * FROM userinfo WHERE userWeight=‘50’ and userSight=‘5.2’;

索引分析结果，索引命中（因为 userWeight=‘50’ ，满足了最佳左前缀原则）：

那么如果我们把这两个条件 换下前后顺序呢？

EXPLAIN SELECT * FROM userinfo WHERE userSight=‘5.2’ AND userWeight=‘50’;

索引分析结果，索引命中：

这是特意补充提一下的，这个最佳左前缀原则是对于建立的联合索引里面字段的顺序最左而言，不是sql语句写的条件顺序。

那么如果我们使用的是 userHeight 和 userSight 呢？

EXPLAIN SELECT * FROM userinfo WHERE userHeight=‘180’ and userSight=‘5.2’;

这个不做解释，连最左的边都没粘上，还想命中索引？  怎么敢的。

失效 七

========

使用了 is not null  、 is null ，索引不生效

前排说明，这个失效场景并不是如表述所言！情况以下分析！

请看示例表数据，里面userName里面，包含了一条数据为NULL的情况：

然后SQL使用了 is not  null ：

EXPLAIN SELECT * FROM userinfo WHERE userName is not null;

索引分析结果，索引未命中：

那么SQL使用了 is null：

EXPLAIN SELECT * FROM userinfo WHERE userName is null;

索引分析结果，索引命中：

为什么，为什么索引命中了？  不是说使用了 is null 、is not null 会失效么？

这段话摘自mysql官方文档，is null 不会影响索引的使用：

MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.

那可能有很多看官，确实遇到了使用is null 失效的问题，就会很疑惑。

是玄学么？ 并不是。

==============

这就是本文想补充的一种索引失效的场景：

---

当执行的sql语句，mysql认为扫描全表都比使用索引快的时候，那么索引就不会被使用！

也就是mysql会去计算查询成本，那个成本低就选择哪种方式。

PS： 接下来我跑题了， 非常啰嗦，我跑出了 回表，聚集索引 ，非 聚集索引，索引覆盖 这些不符合文章主题的内容。不想阅读的看官可以点个赞就走了。

ok，继续回到跑题，那啥时候mysql才会有这种认为呢？

通常mysql有这种想法的时候，大多数情况是因为该sql 查询中回表数量太多。

那么引申出一个概念，回表。

什么是回表（跑题，但是无所谓了）？

想深入了解的看官，可以去摸索深入一下，这里我用我的小白文给大家简单讲一讲。

结合实例讲解（对了，该篇文章都是基于引擎InnoDB的）：

假如咱们现在有一张表，里面有   id （主键），userName（索引），userPorfession （无索引）

![](https://img-blog.csdnimg.cn/2020091615023839

《一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》

【docs.qq.com/doc/DSmxTbFJ1cmN1R2dB】 完整内容开源分享

9.png)

首先为了更好地解释回表， 我逼不得已又得引出两个 新概念   聚集索引 和 非 聚集索引 。

（哈哈麻烦了，感觉跑题越跑越远了，无所谓了）

聚集索引 ：

通常来说，就是咱们表的主键。

那如果这张表没有主键，那么第一个创建的唯一非空索引，就是该表的聚集索引 。

那么你说，又没主键，又没创建唯一非空索引，我明白，你想搞事。 但是mysql不会被你搞，这种情况mysql会隐式地为该表创建一个聚集索引 ，具体是啥，创建规则，我就不细说了。（因为对于我来说，我不想看到这种情况出现，就算没必要很多时候我也会去建一个主键id作为伪列。）

好了，聚集索引  我们已经了解它的由来。

那么它的作用：

简单一点来说，它就是这表数据的老大，只要命中这个家伙，其他字段它都能给你找齐，也就是说这个家伙是指向了整行数据的。

非聚集索引 ：

其他索引，类似这个表里，userName的索引 就是 非聚集索引 。

这个家伙的作用：

你找它，它只能帮你找到它的老大 聚集索引（主键），也就是这个家伙是指向聚集索引的。

ok，讲到这里，回到我们的表示例，讲讲啥是回表场景：

id （主键），userName（索引），userPorfession （无索引）

执行SQL：

SELECT id , userName, userProfession FROM userinfo WHERE id = 7;

执行计划分析：

这时候，索引命中的是id 主键 。

没错，命中了老大， 不会回表。因为在老大的索引树里面，啥玩意都能给你找齐了。

再看执行SQL：

SELECT id , userName, userProfession FROM userinfo WHERE userName = ‘刘二’;

执行计划分析：

这时候，命中的不是老大，是一个 非聚集索引 ,  这时候需要回表。

为什么啊？ 因为我们使用的是select * ，意思是我们还得查找 userPorfession  字段数据。

那么现在命中了userName 的索引 nameIndex，它还能帮我们找到老大 id，但是它无法帮我们直接找到 userPorfession  。

其实这里涉及到一个概念，叫 索引覆盖 。

什么是索引覆盖？ （不能再跑题了，这里我就提一句吧，就是从索引树里面指向的数据字段里已经包含了select  xx，xxx 这些字段，那么就是索引数据已经够用了，没必要回表查额外的数据了。）

快速看图了解：

执行分析结果 ：

回归刚刚讲到的，userName ，id 我们都能找到，但是为了找 userPorfession  ，我们只能在找到id之后，再根据id再去找一遍主键的索引树数据，找出与id绑定的userPorfession，这种情形就是 回表    。

什么叫回表？好的这里简单讲述完毕。

那又又又回到我们最早提到的问题，

mysql认为扫描全表都比使用索引快的时候，那么索引就不会被使用。

而通常mysql有这种想法的时候，大多数情况是因为该sql 查询中回表数量太多。

那么怎么尽可能避免这种情形呢？

刚刚已经讲了回表是啥原因导致的了，那么为了尽可能避免这种情形，那就是：

1.使用聚集索引 也就是主键进行查找

2. select 查找的字段列 被 命中的索引的索引树里的数据 包含，也就是索引覆盖。

3.升级索引