MySql 索引失效、回表解析，一个月成功收割腾讯、百度、美团、网易offer

索引失效分析结果：

切记： 传入的值使用方式记得跟数据库表内列，索引设置字段保持一样的类型，这样万无一失。

扩展补充，为什么失效二情况 强调了 索引字段是 varchar ，传入 值使用不加引号 呢？

因为一部分人在理解这种情况 有错误的思想，理解为 涉及类型转换 ，以为是因为单纯的字段类型不对应 导致索引失效，这里必须补充一下一个示例：

字段列 userAge：

userAge类型为 int ：

给  userAge添加了索引 ：

SQL使用传入值，添加了引号 ：

EXPLAIN SELECT * FROM userinfo WHERE userAge = ‘25’;

结果分析 ,可以命中索引：

所以，咱们记住，这种失效场景是，数据库表内字段是varchar，给这字段添加了索引，传入值sql未使用引号，这时候涉及隐式转换，才会导致索引失效。 当然这也是为什么我让大家切记，保持与表内数据类型一致，这不管是啥都很稳妥。

失效三

=========

使用 like 进行 左模糊匹配查找  ‘%XXX’

请看示例，表内字段列 userName 

给userName 添加索引：

索引失效 ，使用LIKE 并使用的是左匹配（同样左右一起用也是失效的）：

EXPLAIN SELECT * FROM userinfo WHERE userName LIKE ‘%一’;

索引失效分析结果：

扩展补充，当使用LIKE ，索引就必然失效么？

并不是，这里强调了 左匹配。

请看 LIKE 右匹配：

EXPLAIN SELECT * FROM userinfo WHERE userName LIKE ‘王%’;

索引命中分析结果：

失效 四

========

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

请看示例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 （无索引）

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

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

聚集索引 ：

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

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

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

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

那么它的作用：

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

非聚集索引 ：

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

这个家伙的作用：