MySQL索引失效的底层原理

mysql的索引在使用不当情况下会失效.

    比如：使用最佳左前缀法则，大于号右边的索引会失效，使用like索引会失效，当准备面试的时候我们为了应付面试的的时候往往会去找到这些面试题目的答案，但是往往不会去思考，为什么会失效？

      今天文章就会仔细的分析下，什么情况下mysql的索引会失效，我们都知道，索引失效的情况下都是针对联合索引

如下图：

      一个联合索引的节点上面有两个键值对，现在假设联合索引的字段是有a和b组成的，那么现在从上面的图可以看到：2 和 4 就代表我们的a和b连个字段组合成了一个联合索引，然后可以仔细观察叶子节点，左边的叶子节点都是有序排列的，并且由小到大，所以可以看到a的优先级大于b的优先级，而右边的则是无序的

 

好的，现在我们可以分析下，为什么是最左前缀法则失效？为什么大于号右边的会失效？为什么like会失效？

 

我现在新建一个测试用户表;

创建了一个复合索引由 phone和len_id和region_id组成的idx_phone_lan_region，然后我们可是测试一下

首先我们测试一个遵循最佳做前缀法则；

执行结果如下；

explan可以看到这条sql是执行了索引的，rows等于1，type=ref

 

然后我们去掉 手机号再查询一次；

可以看到rows是扫描了一行数据，当然我这个用户表是没有数据的，所以只能看到rows=1，然后type=all，说明这条sql没有走索引，

所以我们分析下，为什么没有走索引，还是由上面的那个图我们可以看出；

我们知道联合索引再b+树上的排序是先排a，当a相等的情况下再排b，然后我们刚刚看到了，条件查询存在手机号的遵循最佳左前缀法则，首先a字段再b+树上面是有序的，就能定位到a所在的节点，就是通过二分查找发找到a，当我们查找了第一个字段，然后再来查找第二个字段b，从图可以看出，当a相同的情况下b也是有序的，这是时候，我们的a已经确定了，那么我们就可以再a的基础上用二分查找发去查找b，这种情况下b也是有序的，所以它也能查找到，所以这个遵循最佳做前缀法则的sql分析下来是没有问题的，然后我们来分析不遵循最佳最佳左前缀法则的sql；

问题出在哪里？

只有当我们a相等的情况下，b才是有序的，而上面的当我们把电话号码去掉的情况下，b就是无序的，缺少b执行的索引的存在条件，没有a的情况下，b肯定是无序的，所以在无序情况下我们无法找到b这个值，所以只能进行全表扫描，不会搜索引

这就是为什么要遵循最佳左前缀法则了

 

然后我们看看，范围查找的右边，为什么索引会失效;还是看这个图

首先我们看叶子节点，查找a>1 b=1的数据，可以看到a大于1的数据由2，3，4，分别在叶子节点可以看到，然后我们再去找b=1的数据，而a>1的数据对应的b的数据是没有序的，这个无序的不仅仅体现在叶子节点上而且还体现在非叶子节点上，所以这种情况下b无序，还是无法进行索引匹配，

当我们% 放在左边，放在右边都是不走索引的，那么这又是什么原因呢？

首先解释下% 的含义，首先我们这个%放在右边，是去查找以1 开头的数据，例如，111222，这个数据就可以查询出来，但是当我们变成222111，就查询不出来，所以这个%的意思就是这个意思，所以百分号分别在左边，右边，还是两边，分别叫做，前缀，后缀，中缀，当我们一个字符串存在b+树里面存储的时候，也是按照字母的大小去排序的，如下图

你去查找以a开头的字符串，可以按照a的顺序查找到。所以当你加上%就不是前缀法则了，所以这就是like失效的原理，所以这里就可以推理出in 为什么会失效，or 为什么会失效！

所以可以总结下；

1. 如果是复合索引，叶子节点不仅保存了复合索引的值，还有主键的值，这就是当你使用覆盖索引的时候，加上主键也会用到索引的原因

2. 如果是模糊查询，如果查询字段不包括索引字段，只有当%放到左边时候才会用到索引，但如果是覆盖索引，则会用到覆盖索引，

3. 返回查询如果复合做前缀法则，而且查询的数据比较少的情况下，即使没有用到覆盖索引，也会走索引，但是如果数据过多，则会全表扫描

凡事不能二分查找的情况下都属于索引失效的情况