【索引失效】MySQL索引失效场景

今天药忘吃喽~ 2024-04-23 04:45 109阅读 0赞

##### **1、对索引使用左或者左右模糊匹配** #####

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。

比如下面的 like 语句，查询 name 后缀为「林」的用户，执行计划中的 type=ALL 就代表了全表扫描，而没有走索引。

**// name 字段为二级索引**

**select \* from t\_user where name like '%林';**

![ae8de8b0cef24320a0330eb614ebf57c.png][]

如果是查询 name 前缀为林的用户，那么就会走索引扫描，执行计划中的 type=range 表示走索引扫描，key=index\_name 看到实际走了 index\_name 索引：

**// name 字段为二级索引**

**select \* from t\_user where name like '林%';**

为什么 like 关键字左或者左右模糊匹配无法走索引呢？

**因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。**

举个例子，下面这张二级索引图（图中叶子节点之间我画了单向链表，但是实际上是双向链表，原图我找不到了，修改不了，偷个懒我不重画了，大家脑补成双向链表就行），是以 name 字段有序排列存储的。

![3e04f696e1454cdf869b58489ddf36f6.png][]

假设我们要查询 name 字段前缀为「林」的数据，也就是 name like '林%'，扫描索引的过程：

*  首节点查询比较：林这个字的拼音大小比首节点的第一个索引值中的陈字大，但是比首节点的第二个索引值中的周字小，所以选择去节点2继续查询；
 *  节点 2 查询比较：节点2的第一个索引值中的陈字的拼音大小比林字小，所以继续看下一个索引值，发现节点2有与林字前缀匹配的索引值，于是就往叶子节点查询，即叶子节点4；
 *  节点 4 查询比较：节点4的第一个索引值的前缀符合林字，于是就读取该行数据，接着继续往右匹配，直到匹配不到前缀为林的索引值。

如果使用 name like '%林' 方式来查询，因为查询的结果可能是「陈林、张林、周林」等之类的，所以不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询。

想要更详细了解 InnoDB 的 B+ 树查询过程，可以看我写的这篇：[B+ 树里的节点里存放的是什么呢？查询数据的过程又是怎样的？(opens new window)][B_ _opens new window]

##### **2、对索引使用函数** #####

有时候我们会用一些 MySQL 自带的函数来得到我们想要的结果，这时候要注意了，如果查询条件中对索引字段使用函数，就会导致索引失效。

比如下面这条语句查询条件中对 name 字段使用了 LENGTH 函数，执行计划中的 type=ALL，代表了全表扫描：

**// name 为二级索引**

**select \* from t\_user where length(name)=6;**

![07cbce56b4db462db78b4add61f7caef.png][]

为什么对索引使用函数，就无法走索引了呢？

因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。

不过，从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。

举个例子，我通过下面这条语句，对 length(name) 的计算结果建立一个名为 idx\_name\_length 的索引。

**alter table t\_user add key idx\_name\_length ((length(name)));**

然后我再用下面这条查询语句，这时候就会走索引了。

![68e299aebbd6400f94c1524d39bc31d1.png][]

##### **3、对索引进行表达式计算** #####

在查询条件中对索引进行表达式计算，也是无法走索引的。

比如，下面这条查询语句，执行计划中 type = ALL，说明是通过全表扫描的方式查询数据的：

**explain select \* from t\_user where id + 1 = 10;**

![a43413f5e3a346e0a2d8d8ed88d9853a.png][]

但是，如果把查询语句的条件改成 where id = 10 - 1，这样就不是在索引字段进行表达式计算了，于是就可以走索引查询了。

![a1692e5d8e0e4e4bab2bf61c8b593d93.png][]

为什么对索引进行表达式计算，就无法走索引了呢？

原因跟对索引使用函数差不多。

因为**索引保存的是索引字段的原始值**，而不是 id + 1 表达式计算后的值，所以无法走索引，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。

有的同学可能会说，这种对索引进行简单的表达式计算，在代码特殊处理下，应该是可以做到索引扫描的，比方将 id + 1 = 10 变成 id = 10 - 1。

是的，是能够实现，但是 MySQL 还是偷了这个懒，没有实现。

我的想法是，可能也是因为，表达式计算的情况多种多样，每种都要考虑的话，代码可能会很臃肿，所以干脆将这种索引失效的场景告诉程序员，让程序员自己保证在查询条件中不要对索引进行表达式计算。

##### **4、对索引隐式类型转换** #####

如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，你会在执行计划的结果发现这条语句会走全表扫描。

我在原本的 t\_user 表增加了 phone 字段，是二级索引且类型是 varchar。

![55c2372dce0d49289ecf3209e61ade0b.png][]

然后我在条件查询中，用整型作为输入参数，此时执行计划中 type = ALL，所以是通过全表扫描来查询数据的。

**select \* from t\_user where phone = 1300000001;**

![8473f293c95143288c411e125a2f26e8.png][]

但是如果索引字段是整型类型，查询条件中的输入参数即使字符串，是不会导致索引失效，还是可以走索引扫描。

我们再看第二个例子，id 是整型，但是下面这条语句还是走了索引扫描的。

**explain select \* from t\_user where id = '1';**

![4e93a8b214d24f52ac5a4fc3582dd5ca.png][]

**为什么第一个例子会导致索引失效，而第二例子不会呢？**

要明白这个原因，首先我们要知道 MySQL 的数据类型转换规则是什么？就是看 MySQL 是会将字符串转成数字处理，还是将数字转换成字符串处理。

我在看《mysql45讲的时候》看到一个简单的测试方式，就是通过 select “10” > 9 的结果来知道MySQL 的数据类型转换规则是什么：

*  如果规则是 MySQL 会将自动「字符串」转换成「数字」，就相当于 select 10 > 9，这个就是数字比较，所以结果应该是 1；
 *  如果规则是 MySQL 会将自动「数字」转换成「字符串」，就相当于 select "10" > "9"，这个是字符串比较，字符串比较大小是逐位从高位到低位逐个比较（按ascii码） ，那么"10"字符串相当于 “1”和“0”字符的组合，所以先是拿 “1” 字符和 “9” 字符比较，因为 “1” 字符比 “9” 字符小，所以结果应该是 0。

在 MySQL 中，执行的结果如下图：

![732f3196bf4b46b4858678c3bb703179.png][]

上面的结果为 1，说明 **MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较**。

前面的例子一中的查询语句，我也跟大家说了是会走全表扫描：

**//例子一的查询语句**

**select \* from t\_user where phone = 1300000001;**

这是因为 phone 字段为字符串，所以 MySQL 要会自动把字符串转为数字，所以这条语句相当于：

**select \* from t\_user where CAST(phone AS signed int) = 1300000001;**

可以看到，**CAST 函数是作用在了 phone 字段，而 phone 字段是索引，也就是对索引使用了函数！而前面我们也说了，对索引使用函数是会导致索引失效的**。

例子二中的查询语句，我跟大家说了是会走索引扫描：

**//例子二的查询语句**

**select \* from t\_user where id = "1";**

这时因为字符串部分是输入参数，也就需要将字符串转为数字，所以这条语句相当于：

**select \* from t\_user where id = CAST("1" AS signed int);**

可以看到，索引字段并没有用任何函数，CAST 函数是用在了输入参数，因此是可以走索引扫描的。

##### **5、联合索引非最左匹配** #####

对主键字段建立的索引叫做聚簇索引，对普通字段建立的索引叫做二级索引。

那么**多个普通字段组合在一起创建的索引就叫做联合索引**，也叫组合索引。

创建联合索引时，我们需要注意创建时的顺序问题，因为联合索引 (a, b, c) 和 (c, b, a) 在使用的时候会存在差别。

联合索引要能正确使用需要遵循**最左匹配原则**，也就是按照最左优先的方式进行索引的匹配。

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

*  where a=1；
 *  where a=1 and b=2 and c=3；
 *  where a=1 and b=2；

需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

*  where b=2；
 *  where c=3；
 *  where b=2 and c=3；

有一个比较特殊的查询条件：where a = 1 and c = 3 ，符合最左匹配吗？

这种其实严格意义上来说是属于索引截断，不同版本处理方式也不一样。

MySQL 5.5 的话，前面 a 会走索引，在联合索引找到主键值后，开始回表，到主键索引读取数据行，Server 层从存储引擎层获取到数据行后，然后在 Server 层再比对 c 字段的值。

从 MySQL 5.6 之后，有一个**索引下推功能**，可以在存储引擎层进行索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，再返还给 Server 层，从而减少回表次数。

索引下推的大概原理是：截断的字段不会在 Server 层进行条件判断，而是会被下推到「存储引擎层」进行条件判断（因为 c 字段的值是在 (a, b, c) 联合索引里的），然后过滤出符合条件的数据后再返回给 Server 层。由于在引擎层就过滤掉大量的数据，无需再回表读取数据来进行判断，减少回表次数，从而提升了性能。

比如下面这条 where a = 1 and c = 0 语句，我们可以从执行计划中的 Extra=Using index condition 使用了索引下推功能。

![62d6cb41606345ffa90db2d662012345.png][]

**为什么联合索引不遵循最左匹配原则就会失效？**

原因是，在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。

也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引。

##### **6、WHERE 子句中的 OR** #####

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

举个例子，比如下面的查询语句，id 是主键，age 是普通列，从执行计划的结果看，是走了全表扫描。

**select \* from t\_user where id = 1 or age = 18;**

![59b8bf09541d42c29dffd315346d8572.png][]

这是因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。

要解决办法很简单，将 age 字段设置为索引即可。

![fedfd416d92b4cbf8b0fedaf101c0705.png][]

可以看到 type=index merge， index merge 的意思就是对 id 和 age 分别进行了扫描，然后将这两个结果集进行了合并，这样做的好处就是避免了全表扫描。

##### **7、如何字段类型是字符串，where 时一定用引号括起来，否则索引失效；** #####

如果不使用引号，数据库可能会对字符串进行隐式的类型转换，这可能导致索引失效

##### **8、mysql 估计使用全表扫描要比使用索引快,则不使用索引。** #####

[ae8de8b0cef24320a0330eb614ebf57c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/fa179c8a4eb048d6aecdf2f25d9fb697.png
[3e04f696e1454cdf869b58489ddf36f6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/b23718fa8df449039149273d600b86d2.png
[B_ _opens new window]: https://mp.weixin.qq.com/s?__biz=MzUxODAzNDg4NQ==&mid=2247502059&idx=1&sn=ccbee22bda8c3d6a98237be769a7c89c&scene=21#wechat_redirect
[07cbce56b4db462db78b4add61f7caef.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/17f82df1a0c248de8dc921c556b80f29.png
[68e299aebbd6400f94c1524d39bc31d1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/5e5741640f7a4c6b9e3d5cfb45ffb9e6.png
[a43413f5e3a346e0a2d8d8ed88d9853a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/8895ffe4eee246feab9dff8aa040403a.png
[a1692e5d8e0e4e4bab2bf61c8b593d93.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/efa74e40b0a54d3e9480a622c10fc4b4.png
[55c2372dce0d49289ecf3209e61ade0b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/7340bae1d97b41b08f4c238a7260cce4.png
[8473f293c95143288c411e125a2f26e8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/257f4c02c5344a51a7fd3099f6d9e7b0.png
[4e93a8b214d24f52ac5a4fc3582dd5ca.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/295109f5baf5424eacdfdbd296e5ae09.png
[732f3196bf4b46b4858678c3bb703179.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/76f48063ff264cff813110da51451fcb.png
[62d6cb41606345ffa90db2d662012345.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/80c1b3701a684c30b0e605b268fa2c9b.png
[59b8bf09541d42c29dffd315346d8572.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/c75c32546cc1471c8c71bdda573597a5.png
[fedfd416d92b4cbf8b0fedaf101c0705.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/2bd688d8a41e4325a2307e42175e8ee6.png