（2）MySQL索引失效的底层原理

在跟着周阳老师学习索引优化的时候接触了大量的索引失效的情况，但是只是知道结论。今天来分析一下其为何失效。
首先在第一节的时候我们知道msql的索引数据是使用的B+树作为数据结构去存储，这种数据结构是使用二分查找去检索的，而对于二分查找我们知道这个需要数据大致有序或者完全就是有序的。基于这个原理我们来展开下面的一个分析，为什么索引会失效呢？

一般索引失效是对复合索引说的，你单值索引一般也不怎么失效，上图就是一个复合索引在mysql内部的一个数据结构的存储图。这个复合索引由两个字段组成，就像下面这句sql,
create index idx_a_b on table(a,b);这就创建了一个复合索引，字段是a和b。它存在B+树上的叶子节点中，你建索引是（a,b）这样的顺序和内容，它存在树上也是先按照a字段排序，然后a字段相同的数据再按照b字段排序，所以我们看到上图的叶子节点的顺序a字段是1，1，2，2，3，3是有序的，但是b无序，但是b字段在a相同的组中是有序的。这就是它在底层的存储的一个简单的例子。基于这个意思我们来分析一下索引为何失效。

1、遵循最佳左前缀

select * from table where a=1 and b=2;
我们看到这个sql是遵循了索引检索的最佳左前缀原则的，检索条件就是索引值，而且顺序一样。此时它先去按照a条件检索，因为a是索引排序的，有序就能用二分法去查询检索，所以很快就能定位到a的元组，此时因为a有序的情况下，b也是有序的，所以b也能用二分检索，自然就快，所以最佳左前缀原则其实就是让检索依照二分查询在索引树上能正常进行下去。

2、去掉第一个检索条件

#此时我们去掉第一个检索条件，破坏最佳左前缀法则，sql变成：
select * from table where b = 2;
此时explain分析sql就发现性能降低，而且全表扫描，十分的孤儿。这就不对了，因为它上去没按a排序去找，它直接找的b，而在失去a排序的情况下，b的顺序是1，2，1，4，1，2这是无序的，自然也就不能按照二分检索，还是选择了全表扫描，自然索引就失效了。

3、范围符号之后的索引全部失效

#此时我们写一个范围的sql，sql变成：
select * from table where a > 1 and b = 2;
经过explain分析sql得到结果，这个b=2的索引失效了，这是为何？
因为你用a>1在树中定位数据，大体上能排除一部分没关系的数据，减少查询范围，提高效率，这就是range的来由，但是你定出的结果是（2，1）（2，4）（3，1）（3，2）此时这些数据中b的值是1，4，1，2是无序的自然不能二分了，铁孤儿。所以就失效了。

4、like匹配%a%,%a索引失效

此时我们用like进行模糊查询，sql变成：
select * from table where a like "%1";
或者是
select * from table where a like "%1%";
这两种是一样的，我们都发现索引失效了，没有用到索引，为什么呢，这个其实和上面那个是一个意思，你用like百分号查的实质是:%1是检索所有以1结尾的数据，%1%是检索所有1在中间的数据，这种你检查出来其实你是用不到索引的排序的，你怎么二分去查这种呢。二分没办法比较这种情况去检索，所以上来就没办法遍历树了，直接就失效了，可以模拟一下二分查找试试。

###########################不失效############################################
当select * from table where a like "1%";
此时explain分析sql发现索引正常了，这是因为我们这种情况其实是在树上二分查询以1开头的，这个是可以比较的，就是看第一位么，这就能拿到数据在树上往下走做比较了。所以这种不失效。

此外像字符串也是这样存得，第一位排序，之后就按第二位排，一样的。还有那些什么in or 失效一个意思，此处就不说了。