索引失效

左匹配或者左右匹配

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

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

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

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

假设我们要查询 name 字段前缀为「林」的数据，也就是 name like ‘林%’，扫描索引的过程（这里中文都是按照某种排序，不知道是不是按照utf-8的编码值比较的，但是这个规则不影响寻找到逻辑）：

节点1判断出“林”小于“周”并且大于“陈”（这个步骤是通过页目录进行搜索的，回顾一下：假设这里头指针是一个分组，陈是一个分组，剩下两个和尾指针是一个分组，二分查找目录项，由于目录项指向分组的最大值，那么“林”是大于陈小于周的，按照目录项的规则，从包含尾指针的那个分组开始，但是由于存的是最大值也就是尾指针，为了从头开始遍历，就要找到上一个槽的指针，往后数一个就是当前的最小值，结果发现当前的最小值“周”还是大于“林”，这样就定位在“陈”和“周”之间，按照存储规则，非叶子节点存的是下一个节点的最小值，那就要在“陈”这个节点找）
节点2继续比较：节点2的第一个索引值中的陈字的拼音大小比林字小，所以继续看下一个索引值，发现节点2有与林字前缀匹配的索引值，于是就往叶子节点查询，即叶子节点4
节点4查询比较：节点4的第一个索引值的前缀符合林字，于是就读取该行数据，接着继续往右匹配，直到匹配不到前缀为林的索引值。

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

总而言之，索引是从左边开始匹配的，如果左边第一个都不能确定，那就要全部遍历

对索引使用函数

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

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

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

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

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

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

总而言之，索引保存的是原始值，使用函数后重新计算导致不能索引到，就失效了，但是如果对这个函数也建立一个索引，那也可以走索引

数据类型强制转换

这个印象比较深刻：首先举一个例子，select “10” > 9，通过这个例子可以看出来在mysql是怎么处理字符串的

如果是结果是1，也就是select 10>9，那么内部会把字符串处理成数字

如果结果是0，也就是select “10” > “9”，字符串是比较首位，1肯定小于9，所以这个结果是0，那么内部把数字处理成字符串
结果是1，也就是mysql内部把字符串变成数字进行比较

假设phone在数据库是用varchar存的，有了这个结论，那么

1	select * from t_user where phone = 1300000001;

这个代码就会变成：

1	select * from t_user where CAST(phone AS signed int) = 1300000001;

因为内部会把索引的字符串变成整数进行比较，相当于给索引使用了函数，所以就会失效

如果查询条件是字符串，id在数据库用int存的

1	select * from t_user where id = "1";

那么就会变成：

1	select * from t_user where id = CAST("1" AS signed int);

也就是说，只有这个查询条件会要用这个函数，索引没有用，索引就不失效

对索引进行表达式计算

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

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

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

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

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

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

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

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

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

联合索引的左匹配问题

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

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

创建联合索引时，我们需要注意创建时的顺序问题，因为联合索引 (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；

其实类似第一种情况，不管是字符串还是索引，都是按照最左匹配的，如果连第一个都没有，那就会导致索引失效，这里还有一个索引下推的机制，等以后再来补。

or导致的失效问题

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

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

or就是两个里面满足一个，也就是索引要有两个，才可以一起用索引，and其实是只要有一个是索引就可以走索引的，要注意

mysql-索引和索引失效

文章目录