【转】索引失效有哪些？

转自：
https://xiaolincoding.com/mysql/index/index_lose.html#%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E6%9C%89%E5%93%AA%E4%BA%9B

对索引使用左或者左右模糊匹配

当我们使用左或者左右模糊匹配时（即"like %xx"或者"like %xx%"），会造成索引失效。

但在使用前缀匹配时（即"like xx%"），却会走索引扫描。

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

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

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

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

MySQL 使用"like %xx"时，索引一定会失效吗？

使用左模糊匹配并不一定会走全表扫描，关键还是要看数据表中的字段情况。

如果数据表中的字段只有主键 + 二级索引，那么即使使用左模糊匹配，也不会走全表扫描（type = all），而是会走全扫描二级索引树（type = index）。

同时，联合索引要遵循最左匹配原则才能走索引，但同样的，如果数据表中的字段全都是索引的话，即使在查询过程中没有遵循最左匹配原则，也是会走索引扫描的（type = index）。

这是因为数据表中没有非索引字段，所以 select * 就相当于 select id, name，然后这个查询的数据都在二级索引的 B+ 树上，因为二级索引的 B+ 树的叶子节点包含了索引值 + 主键值，所以查找二级索引树就能够查到所需的数据了，即发生覆盖索引。

执行计划里的 type = index，代表着是通过全扫描二级索引的 B+ 树的方式来查询数据的，即遍历了整个索引树。

为什么选择全扫描二级索引树，而不是扫描全表（聚簇索引）呢？

因为二级索引树里记录的东西很少，只有索引值 + 主键值，而聚簇索引里记录的东西更多，比如记录了主键值、事务 id、用于事务和 MVCC 的回流指针以及所有的剩余列。再加上当前的 select * 不需要回表查询，所以 MySQL 优化器认为直接遍历二级索引树会比遍历聚簇索引树的成本更小。

为什么数据表加上非索引字段，执行同样的查询语句后，走的是全表扫描了呢？

因为加了非索引字段后，要查询的数据就不能只在二级索引树里找到了，还得需要回表查询，再加上是模糊匹配，无法有效利用索引树的有序性来快速定位数据，所以得遍历二级索引树后，获取主键值，再通过聚簇索引检索到对应得数据行。

MySQL 优化器认为这样的查询过程成本太高了，于是就直接选择全表扫描的方式。

对索引使用函数

如果查询条件中对索引字段使用了函数，就会导致索引失效。

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

// name为二级索引
select * from t_user where length(name)=6;

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

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

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

下面这条语句，对 length(name) 的计算结果建立一个名为 idx_name_length 的索引。

alter table t_user add key idx_name_length ((length(name)));

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

对索引进行表达式计算

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

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

explain select * from t_user where id + 1 = 10;

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

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

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

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

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

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

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

对索引进行隐式类型转换

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

在 t_user 表增加 phone 字段，是二级索引并且类型是 varchar。

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

select * from t_user where phone = 1300000001;

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

假设 id 是整型，但是下面这条语句还是走了索引扫描。

explain select * from t_user where id = '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 函数是作用在了输入参数上，因此是可以走索引扫描的。

联合索引非最左匹配

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

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

创建联合索引时，我们需要注意创建时的顺序问题，因为联合索引 (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 字段会走索引，在联合索引找到主键值后，开始回表，到主键索引读取数据行，然后再比对 c 字段的值。

从 MySQL 5.6 之后，有一个索引下推的功能，可以在索引遍历的过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

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

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

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

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

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

WHERE 子句中的 OR

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

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

select * from t_user where id = 1 or age = 18;

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

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