Mysql之索引失效原理分析

一. 前言

索引失效是针对的是联合索引，在没有遵守最佳左法则、使用like和%、or 等情况下索引会失效。

但是为什么索引会失效 ？

索引失效和 Innodb 引擎的 B+树 存储方式有关，本文会对失效的原理进行逐一分析。

二. B+树的索引

1. 单索引的B+树如下图：

单值索引在B+树结构里，一个节点只存一个键值对，键值对都是有序的。

2. 联合索引的B+如下图：

联合索引在B+树结构里，一个节点的键值对的数量不是1，而是大于等于2，键值对都是有序的。

(1) 该图是由数据库的 a 字段和 b 字段组成一个联合索引，对于查询 SELECT * FROM table WHERE a = xxx and b = xxx，就可以使用该联合索引。

(2) a，b排序分析：

1) a 排序：1，1，2，2，3，3(有序排列)

2) b 排序：1，2，1，4，1，2(无序排列)

直观的可以发现 a 是有序的，b 是无序的，再进一步发现在 a 确定的情况下，b 也是有序的；

比如需要对两个字段排序，都是先按照第一个字段排序，如果第一个字段出现相等的情况，就用第二个字段排序，B+树也采用了该方法进行排序。

这个就是联合索引命中的原理：即 a 本身有序，在 a 确定的情况下，b又是有序的，所以就相当于都是有序的。

三. 最佳左前缀法则

1. 遵循最佳左前缀法则：

select * from table where a = 1 and b = 1

该语句可以命中索引。

分析：

(1) a字段是有序的，所以可以通过二分查找法来定位到 a=1 的位置；

(2) 在a确定的情况下，b是相对有序的，所以同样可以通过二分查找法找到 b=2 的位置。

2. 不遵循最佳左前缀：

select * from table where b = 1

该语句不可以命中索引，索引失效。

分析：

(1) b本身是无序的，b有顺序的前提是在a确定的情况下才有序；

(2) 此时没有a，那b肯定是不能确定顺序的，所以无法用二分查找来定位到b字段的；

(3) Mysql 查找无序的数据就需要进行全表扫描；

四. 范围查找右边失效原理

select * from table where a > 1 and b = 2;

该语句不可以命中索引，索引失效。

分析：

(1) a字段是有序的，所以可以用二分查找法定位到1，然后将所有大于1的数据取出来，a可以用到索引；

(2) b有序的前提是a确定的值，那么现在a的值是取大于1的，此时的a可以为2，也可以为3，也可以是其他值，大于1的范围也不确定的；

(3) a大于1的那部分，b字段是无序的，所以b不能用二分查找来定位，b用不到索引；

(4) 大于1的部分就需要进行全表扫描。

五. like索引失效原理

select * from table where id like "a%"   // %放在右边，代表查询以"a"开头的数据，a叫前缀

select * from table where id like "%a%"  // 两个%%，代表查询数据中包含"a"的数据，a叫中缀

select * from table where id like "%a"   // %放在左边，代表查询以"a"结尾的数据，a叫后缀

该语句第一条可以命中索引，第二三条不可以命中索引，索引失效。

分析：

(1) %号放右边：匹配以a作为头部的所有数据，头部的字母是有顺序的，所有可以使用索引；

(2) %号放左边：匹配以a作为尾部的所有的数据，尾部的字母是没有顺序的，所以不能按照索引顺序查询，就用不到索引；

(3) 两个%%号：匹配任意位置的字母等于a即可，由于只有头部的字母是有序的，其他位置的字母都是相对无序的，所以用不到索引。

总结：

简单来说，索引查找就是二分查找，凡是不能二分查找的情况都属于索引失效的情况。