联合索引怎么用

InnoDB中，除了聚簇索引和单个列的辅助索引外，我们还可以将多个列建立联合索引：

ALTER TABLE table ADD INDEX abc(a, b, c)

通过上面一行SQL，我们就可以建立a、b、c之间的联合索引。

底层原理

我们知道InnoDB中索引的底层结构是B+树，按照索引值大小从左往右，按序排列。

对于(a, b, c)的联合索引，我们是怎么比较大小的呢？

答案是先按照a拍序，a等值的情况下再按照b排序，b等值的情况下再按照c排序。同样地，如果我们对(b, a, c)建立联合索引，那么就是先按照b排序，再按照a排序，最后按照c排序。

也就是说，排序的顺序和我们建立索引时指定列的顺序是一致的。

匹配规则

说到联合索引，我们就要提到最左匹配规则。

所谓最左匹配，即检索数据时从联合索引的最左边开始匹配。比如(a, b, c)联合索引可以匹配a |
a、b | a、b、c三种查询条件，无法匹配b | c | b、c三种查询条件。 同时遇到范围查询(>、<、between、like)就会停止匹配。

注：至于为啥存在最左匹配规则，仔细揣摩前文提到的联合索引底层排序原理即可理解。

不过需要注意的是：查询的时候如果3个条件都用上了，但是顺序不同，比如select * from user where b=xx, a=xx，虽然和索引顺序不同，但是 查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

那什么情况下联合索引会（部分）失效呢？

1. 通用索引失效场景

   比如字符串使用左或左右模糊匹配、使用or查询条件、使用了函数、存在隐式类型转换等。

2. 非最左匹配

   也就是前文提到的，(a, b, c)建立联合索引的情况下，使用b | c | b、c这三种条件进行查询。

3. 遇到范围查询

   最左匹配遇到范围查询（ >、<、between、like）就会停止匹配。

打赏