39.联合索引失效原理

索引失效原理

联合索引

开局一张图，由数据库的a字段和b字段组成一个联合索引。

MySQL技术内幕 InnoDB存储引擎 第2版

从本质上来说，联合索引也是一个B+树，和单值索引不同的是，联合索引的键值对不是1，而是大于1个。

索引排序分析

a顺序：1，1，2，2，3，3

b顺序：1，2，1，4，1，2

大家可以发现:

a字段是有序排列，b字段是无序排列(因为B+树只能选一个字段来构建有序的树)

一不小心又会发现:

在a相等的情况下，b字段是有序的。也可以说在a字段值确定的情况下，b字段是有序的,比如a=1

重点:a字段是有序排列，b字段是无序排列。

在a相等的情况下，b字段是有序的。在a不相等的情况下，b字段是无序的。

只有在a相等的情况下，b字段是有序的。

大家想想平时编程中我们要对两个字段排序，是不是先按照第一个字段排序，如果第一个字段出现相等的情况，就用第二个字段排序。这个排序方式同样被用到了B+树里。

最佳左前缀原理

先举一个遵循最佳左前缀法则的例子

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

分析如下：

首先a字段在B+树上是有序的，所以我们可以通过二分查找法来定位到a=1的位置。

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

再来看看不遵循最佳左前缀的例子

select * from table where b=2

分析如下：

我们来回想一下b有顺序的前提：在a相等的情况下,即在a有确定值的情况下。

现在你的a都飞了，那b肯定是不能确定顺序的，既然无序就自然不能使用树的特性(左节点小于右节点等等)来进行查找了,在一个无序的B+树上是无法用二分查找来定位到b字段的。

所以这个时候，是用不上索引的。

范围查询右边失效原理

举例

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

分析如下：

首先a字段在B+树上是有序的，所以可以用二分查找法定位到1，然后将所有大于1的数据取出来，a可以用到索引。

b有序的前提是a是确定的值，那么现在a的值是取大于1的，可能有10个大于1的a，也可能有100个大于1的a。

大于1的a那部分的B+树里，b字段是无序的，所以b不能在无序的B+树里用二分查找来查询，b用不到索引。

like索引失效原理

where name like "a%" where name like "%a%" where name like "%a"

我们先来了解一下%的用途

• %放在右边，代表查询以"a"开头的数据，如：abc
• 两个%%，代表查询数据中包含"a"的数据，如：cab、cba、abc
• %放在左边，代表查询以"a"为结尾的数据，如cba

为什么%放在右边有时候能用到索引

• %放右边叫做：前缀
• %%叫做：中缀
• %放在左边叫做：后缀

没错，这里依然是最佳左前缀法则这个概念

大家可以看到，上面的B+树是由字符串组成的。

字符串的排序方式：先按照第一个字母排序，如果第一个字母相同，就按照第二个字母排序。。。以此类推

1.%号放右边

如果使用的表达式为 firstname like 'rMq%';那么其是可以用到 firstname 字段的索引的。

对于 firstname like '%Chu%';，其就无法使用 firstname 的索引。

对于 like 前缀，MySQL 底层实际上是使用了一个补全策略来使用索引的，比如这里 first_name like 'rMq%';

MySQL 会将其补全为两条数据：rMqAAAAA 和 rMqzzzzz，后面补全部分的长度为当前字段的长度。

在使用索引查询时，MySQL 就使用这两条数据进行索引定位，需要的结果集就是这两个定位点的中间部分的数据。

如下是使用 like前缀的一个示意图：

2.%号放左边

是匹配字符串尾部的数据，我们上面说了排序规则，尾部的字母是没有顺序的，所以不能按照索引顺序查询，就用不到索引。

3.两个%%号

这个是查询任意位置的字母满足条件即可，只有首字母是进行索引排序的，其他位置的字母都是相对无序的，所以查找任意位置的字母是用不上索引的。

总结

判断索引失效的主要条件是看索引能否做有序查找,只有保证索引的有序性才能保证用上索引,否则就是索引失效。

参考:索引失效原理，终于有人讲明白

参考:http://www.javashuo.com/article/p-scvvcuov-k.html