Tuwilt’s Blog

type

status

date

slug

summary

MySQL索引（二） — 索引为何失效

虽然我们在MySQL索引（一） — 理解索引中深入理解了索引的概念和存储情况，但索引也绝非是万能的。

如果查询语句导致了索引失效，而不得不走全表扫描时，即使查询到的结果无误，但是效率却会大大降低。

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

具体原因就是因为索引 B+ 树是按照 “索引值” 进行有序排序存储的，因此只能根据前缀进行比较。

例如面对这个索引 idx_id_price 时

假设我们要查找的 id 字段前缀为 "0001" 的数据，即id LIKE '0001%'，就可以通过索引的形式一步步往下匹配找到。

但如果使用的是 id LIKE '%001' 的形式，查询到的结果就有可能包含 '0001', '0012', '0019' 等等情况，是无法得知从哪个索引开始比较的，所以只能全表扫描来查询。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

但为什么会这样呢？

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

在《MySQL实战45讲》中，有一个简单的测试方式，就是通过 SELECT “10” > 9 的结果来知道MySQL 的数据类型转换规则是什么：

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

测验结果如下：