MySQL-索引失效原理

一、联合索引的B+树

索引失效我们针对的是联合索引，我们之前有讲到过，在没有遵守最佳左法则或者使用like或者使用百分号的情况下索引会失效。但是到底为什么索引失效了并没有解释。索引失效和innodb引擎的B+树存储方式有关。我们知道单索引的B+树是这样的。

联合索引的B+树也相差不多，因为联合所有有多个字段，下面的图以两个字段为例子，比如两个字段为(a,b),其实和单值索引的不同至少他的键值对不是一个，而是多个

我们现在分析这个构建出来的树

当我们只分析a时,会发现a是有序的，1，1，2，2，3，3
当我们只分析b时,会发现b是无序的，1，2，1，4，1，2
但是如果我们先根据a排序，再来看b，就会发现在a确定的情况b其实也是有序的。
这个就是我们联合索引命中的原理。即a本身有序，在a确定的情况下，b又是有序的，所以就相当于都是有序的

二、最佳左前缀法则

根据上图我们就可以理解为什么在我们没有遵守最佳左前缀法则的时候无法命中索引。看下面的例子

select * from test where a=1 and b=2;
-- 上述语句是直接命中索引的，首先找a，a是有序的，在确定了a之后找b，在a确定后b也是有序的。

select * from test where b=2;
-- 上述语句未命中索引，此时在构建的树中，b是无序的，无法使用二分查找。mysql找无序的数据就是全表扫描。

三、范围查找右边失效原理

其实和上面相同，当你使用了范围查找，能够锁定部分的a值，但是去除掉了这些a之后，还有许多其他的a值绑定的b值依然是无序的。

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

要找到一个b有序的前提是缺点a的值，范围查找中大于a的值可能是一个，也可能是一百万个。

四、like索引失效原理

like一般要配合百分号，一般是查字符类型的。在mysql里字符会按照自己的算法排好，当然也是从小到大，如字母就是用ascii码表排序。

a%代表找以a开头的所有，a叫前缀
%a%代表任意包含a的值，a叫中缀
%a代表找以a结尾的所有，a叫后缀

根据我们之前是结论，要命中索引必须要确定a的值，那么只有a%是符合的，因为后两种都无法确定前面的值，但是这种仅仅只有一个a%其实也是很难命中索引的，因为a开头的值可能有一个，也可能一百万个。