MySQL B+树如何实现联合索引

原创于 2021-03-09 01:10:55 发布 · 4.9k 阅读

28 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#MySQL联合索引 #联合索引结构 #联合索引B+树 #最左匹配原则

闲聊MySQL 专栏收录该内容

16 篇文章

订阅专栏

本文详细解释了MySQL中B+树如何实现联合索引，并介绍了联合索引的查找方式及最左匹配原则。

MySQL B+树如何实现联合索引

“同学，你来画一下MySQL的B+树如何实现联合索引的？”

“额，这个嘛……这个……俺不晓得……”

我是菜狗

之前大言不惭说对MySQL还算了解的我今天被这个问题糊的一脸懵逼，本着对问题的求知和探索精神，今天就来聊一聊这个问题，MySQL B+树如何实现联合索引。

MySQL索引的数据结构B+树

我们都知道在MySQL的InnoDB引擎中，数据的存储是基于聚簇索引来进行构建的，聚簇索引的数据结构为B+树，关于这部分的内容在之前的文章中已经讲过，这里我们再来回顾一下。
传送门：闲聊MySQL：（七）InnoDB之索引结构

B+树

上图就是一棵B+树的示意图，在InnoDB的B+树聚簇索引数据结构中，只有在数据页（即叶子节点）上存放的是完整的每行记录，而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录。

同时，从物理存储结构上来看，聚簇索引的存储并不是连续的，而是逻辑上连续的。

这其中有两点：

1、叶子节点是通过双向链表进行链接的，页按照主键的顺序排序；

2、每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。

而对于二级索引（辅助索引）来说，其数据结构同样是一样的B+树结构，不同的是，其叶子节点不包含记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签。该书签用来告诉InnoDB引擎哪里可以找到与索引对应的行数据。即其叶子节点存储的是执行聚簇索引键值的指针。

好了，前情回顾完毕，复习了一下聚簇索引和二级索引的B+树结构，下面进入正题，我们来说说联合索引的结构。

联合索引的B+树结构

首先我们来看一个问题，我们有一个表user，有几个字段id、c1、c2、c3、c4，其中id是主键，c1、c2、c3字段建立联合索引，那么执行下面几个SQL，来看一下索引执行情况：

select * from user where c1= 12 and c2= 14 and c3 = 3 // 索引全匹配
select * from user where c1= 12 and c2= 14 // 索引部分匹配
select * from user where c1= 12 and c3 = 3 // 索引部分匹配
select * from user where c2= 12 and c3 = 3 // 索引无法匹配

可以看到，对于联合索引，全部命中索引字段可以执行索引；部分命中并符合最左匹配原则，也可能会执行索引；不满足最左匹配原则，则无法命中索引，那么这是为什么呢？

我们来分析一下对于联合索引的B+树的数据结构。
联合索引数据结构
上图就是一个联合索引的B+树示意图，InnoDB会使用聚簇索引在B+树维护索引和数据文件，然后我们创建了一个联合索引name、age、point也会生成一个索引树，同样是B+树的结构，只不过它的data部分存储的是联合索引所在行的主键值。

对于联合索引来说只不过比单值索引多了几列，而这些索引列全都出现在索引树上。对于联合索引，存储引擎会首先根据第一个索引列排序，如上图我们可以单看第一个索引列，横着看，如，1 1 5 12 13…他是单调递增的；如果第一列相等则再根据第二列排序，依次类推就构成了上图的索引树，上图中的c1列都等于1时，则根据c2排序，此时c2列也相等则按c3列排序，如：1 1 4 ，1 1 5，c=4在c=5前面，以及13 12 4,13 16 1,13 16 5就可以说明这种情况。

联合索引的查找方式

当我们的SQL可以应用到联合索引的时候，比如select * from user where c1= 12 and c2= 14 and c3 = 3 。

存储引擎首先从根节点（一般常驻内存）开始查找：

第一个索引的第一个索引列为1,12大于1；
第二个索引的第一个索引列为56,12小于56；
于是从这俩索引的中间读到下一个节点的磁盘文件地址，从磁盘上Load这个节点，通常伴随一次磁盘IO，然后在内存里去查找。
当Load叶子节点的第二个节点时又是一次磁盘IO，比较第一个元素，b=12,c=14,d=3完全符合，于是找到该索引下的data元素即ID值，再从主键索引树上找到最终数据。

查找过程

最左匹配原则

好了，上面我们了解了联合索引的B+树检索过程，那么再来考虑另一个问题，为什么联合索引会有最左匹配原则？

之所以会有最左前缀匹配原则和联合索引的索引构建方式及存储结构是有关系的。

首先我们创建的c1、c2、c3索引，相当于创建了(c1)、（c1、c2）（c1、c2、c3）三个索引，看完下面你就知道为什么相当于创建了三个索引。
我们看，联合索引是首先使用多列索引的第一列构建的索引树，用上面的例子就是优先使用c1列构建，当c1列值相等时再以c2列排序，若c2列的值也相等则以c3列排序。我们可以取出索引树的叶子节点看一下。

索引的第一列也就是c1列可以说是从左到右单调递增的，但我们看c2列和c3列并没有这个特性，它们只能在c1列值相等的情况下这个小范围内递增，如第一叶子节点的第1、2个元素和第二个叶子节点的后三个元素。

由于联合索引是上述那样的索引构建方式及存储结构，所以联合索引只能从多列索引的第一列开始查找。所以如果你的查找条件不包含c1列如（c2, c3）、(c2）、(c3) 是无法应用缓存的，以及跨列也是无法完全用到索引如(c1, c3)，只会用到c1列索引。