MySql 索引失效、回表解析

àì夳堔傛蜴生んèń 2022-12-07 15:03 242阅读 0赞

# **前言** #

### 该篇内容： ###

1.  结合实例，介绍常见的几种索引失效场景，以及对每个场景会做一些额外的扩展说明.
2.   后面写着写着跑题了，为了想给大家讲下什么是回表...然后。。。。

# **正文** #

# 失效 一 #

### **查询的字段列未添加索引 ** ###

***ps：这...直接从失效二开始看吧，我承认我在啰嗦。***  
  
请看示例，表内 userProfession 列 存入的是人员的职业信息 ：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70][]

该字段列未使用索引，所以查询无法命中索引 ：

EXPLAIN SELECT * FROM userinfo WHERE userProfession='教师';

分析结果：

![20200916113403250.png][]

# **失效 二** #

### 建索引的字段 类型为   varchar ， 但是 查询的 传入值 没有使用   ‘ ’ ，即没加引号。 ###

请看示例，表内 userCardNum 列 存入的是职工的工号 ：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 1][]

userCardNum 设置的是**varchar** 类型   ：

![20200916111857298.png][]

给  userCardNum 添加了唯一索引 ：

![20200916112034406.png][]

**正常命中索引查询SQL语句分析（使用 EXPLAIN ）：**

EXPLAIN SELECT * FROM userinfo WHERE userCardNum= '10011';

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

![2020091611280857.png][]

**索引失效，传入值没有带  ‘  ’ 单引号：**

EXPLAIN SELECT * FROM userinfo WHERE userCardNum= 10011;

**索引失效分析结果：**

![20200916112732884.png][]

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

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

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

字段列 userAge：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 2][]

userAge类型为 int ：

![20200916114319923.png][]

给  userAge添加了索引 ：

![20200916114356669.png][]

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

EXPLAIN SELECT * FROM userinfo WHERE userAge  = '25';

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

![20200916114602378.png][]

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

# **失效三  ** #

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

请看示例，表内字段列 userName ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 3][]

给userName 添加索引：

![20200916131639219.png][]

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

EXPLAIN SELECT * FROM userinfo WHERE userName LIKE '%一';

**索引失效分析结果：**

![20200916131805899.png][]

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

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

请看 LIKE 右匹配：

EXPLAIN SELECT * FROM userinfo WHERE userName LIKE '王%';

索引命中分析结果：

![20200916132041134.png][]

# **失效 四** #

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

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

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

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

![20200916132451265.png][]

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

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

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

![20200916133523167.png][]

# **失效 五** #

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

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

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

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

索引未命中分析结果：

![20200916135313620.png][]

扩展补充，

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

例如 userAge 我们使用了索引，

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

这样会命中索引么？

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

![20200916135743959.png][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 5][]

这时候，索引分析结果，索引命中了：  
![20200916140031759.png][]

可以看的，索引命中了。

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

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

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

![20200916140216823.png][]

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

配上使用 UNION ALL的方式：

EXPLAIN 
    SELECT * FROM userinfo WHERE userAge  = 25
    UNION ALL
    SELECT * FROM userinfo WHERE userAge  = 35

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

![20200916140740843.png][]

# **失效 六** #

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 6][]

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

**注意顺序，最左为 userWeight**  
![20200916141545513.png][]

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

单个使用userWeight ：

EXPLAIN	SELECT * FROM userinfo WHERE  userWeight='50';

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

![20200916141927640.png][]

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

EXPLAIN		SELECT * FROM userinfo WHERE  userHeight='180' ;

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

![20200916142128211.png][]

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

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

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

使用 userWeight 和 userSight：

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

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

![20200916142420714.png][]

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

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

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

![20200916142713433.png][]

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

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

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

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

# **失效 七** #

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

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 7][]

然后SQL使用了 is not  null ：

EXPLAIN SELECT * FROM userinfo WHERE userName is not null;

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

![20200916143554382.png][]

那么SQL使用了 is null：

EXPLAIN SELECT * FROM userinfo WHERE userName is  null;

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

![20200916144146782.png][]

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

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

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>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.</td> 
  </tr> 
 </tbody> 
</table>

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

# **是玄学么？ 并不是。** #

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

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

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

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

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

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

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

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

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

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

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

![20200916150238399.png][]

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

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

**聚集索引 ： **

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

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

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

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

那么它的作用：

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

**非聚集索引 ：**

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

这个家伙的作用：

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

ok，讲到这里，回到我们的表示例，讲讲啥是回表场景：  
  
 id （主键），userName（索引），userPorfession （无索引）

![20200916150238399.png][]

执行SQL：

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

执行计划分析：

![20200916152304873.png][]

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

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

再看执行SQL：

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

执行计划分析：

![20200916152446558.png][]

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

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

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

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

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

快速看图了解：

![2020091617572986.png][]

执行分析结果 ：  
![20200916175750493.png][]

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

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

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

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

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

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

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

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

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

3.升级索引

1.2都看懂了。3是什么意思呢？

就是说针对某些场景，例如目前SQL：

SELECT id , userName, userProfession FROM userName  = '刘二';

因为目前id有主键索引，userName有索引，但是 userProfession没索引导致需要回表。

那么我们把userName的单索引升级为 联合索引 （userName，userProfession）。

好吧，该篇就到此。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70]: /images/20221123/f0ba471515714b41b4db8c932ba08688.png
[20200916113403250.png]: /images/20221123/73c2801c62ab485c8e62b5d8f6c412c4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 1]: /images/20221123/4eb524ddaba64c80b8cf1037cf3e243b.png
[20200916111857298.png]: /images/20221123/4901704b75c640e49254afed2a9088e3.png
[20200916112034406.png]: /images/20221123/dbd266dd769e44cbbd354edcd97d88e3.png
[2020091611280857.png]: /images/20221123/022ab0c6e2ed4604a6d5347e0842c077.png
[20200916112732884.png]: /images/20221123/2a1cda9d657a4411a1287afc3d02ddbd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 2]: /images/20221123/72417b6b4aa54255816d5373ad80a6e6.png
[20200916114319923.png]: /images/20221123/1adb098da053414194876b085eaa5465.png
[20200916114356669.png]: /images/20221123/36a81b20305c4e06ad8d03e31f4f790c.png
[20200916114602378.png]: /images/20221123/af9896681bc941eabe475edfce2e34f5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 3]: /images/20221123/41e75601b8c941838633e74ddc2ab385.png
[20200916131639219.png]: /images/20221123/77e65950422343d7b60e52d321a7dce0.png
[20200916131805899.png]: /images/20221123/5fd7f90c20cd43439a2ffa971f0a8bff.png
[20200916132041134.png]: /images/20221123/ee5b75dbaea546efbeac342122efeb76.png
[20200916132451265.png]: /images/20221123/432e9ea976494594afdc2f79e985668f.png
[20200916133523167.png]: /images/20221123/fed54dff795d4af2b7252b947bf6fa63.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 4]: /images/20221123/9cbcf6914a01421ba1f540c439bfbdbd.png
[20200916135313620.png]: /images/20221123/2c7a7ddf38a140a595d746417ac44dd1.png
[20200916135743959.png]: /images/20221123/d0e9a1eaedb14664a241d4eaa5ccb0a3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 5]: /images/20221123/e35134c9c4884348b2a5285e0a3ec885.png
[20200916140031759.png]: /images/20221123/4acfc1a75d0f4b8c933525a4725cfedb.png
[20200916140216823.png]: /images/20221123/e5afcfb89f2746d98af163457c6497aa.png
[20200916140740843.png]: /images/20221123/6fcdcc99157b44f289f45fac1916b38b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 6]: /images/20221123/a1497d1154a7421590fca0c9b6c5eda2.png
[20200916141545513.png]: /images/20221123/e9042d3eafca4f35a9b0fe26924c7bf9.png
[20200916141927640.png]: /images/20221123/54e0c658f47d4a9fab31f790f6328a91.png
[20200916142128211.png]: /images/20221123/448bc7723f99491a84fb75f565b1bfb6.png
[20200916142420714.png]: /images/20221123/6a3475de4f974f168623c5361a74bc04.png
[20200916142713433.png]: /images/20221123/94aba5b0d993455dab5eb74a7c1ae000.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 7]: /images/20221123/143a8da3e1084c7494001f0aeb199fe7.png
[20200916143554382.png]: /images/20221123/db42b6ecdf0c4e8fb76a33cee16e05eb.png
[20200916144146782.png]: /images/20221123/94e3d85249754aa4b5eaed232c9d6bd1.png
[20200916150238399.png]: /images/20221123/cb3a5f72f4f841659e44c46a18f01093.png
[20200916152304873.png]: /images/20221123/dff751c5212e4068ac2440a09d337fa7.png
[20200916152446558.png]: /images/20221123/4d9ddd2afbc84f95ae51296f8ad58a73.png
[2020091617572986.png]: /images/20221123/4d328ccac9524fafa9e59238aee0669b.png
[20200916175750493.png]: /images/20221123/5b255127bb7f45df96f39da1234f114f.png