MySQL联合索引底层存储结构和原理

联合索引概念

有两列及多列索引称为联合索引

底层存储结构

例如有以下数据
CREATE TABLE test_table_union_index (
id bigint(20) NOT NULL AUTO_INCREMENT,
merchant_id int(20) NOT NULL,
order_id int(20) NOT NULL,
PRIMARY KEY (id),
KEY merchant_id_order_id (merchant_id,order_id) USING BTREE
)

image.png

索引merchant_id_order_id底层存储结构

image.png

通过以上索引结构可以看出，联合索引特点：

• B+树通过索引首列值构建，如 merchant_id_order_id_union_index 根据 merchant_id 构建
• 叶子节点拥有联合索引中的所有字段以及主键字段，且叶子节点数据局部有序，如我们有一个三个字段的联合索引（a,b,c）：
  叶子节点（1）：
  a,b,c（1,3,3）
  a,b,c（1,3,4）
  a,b,c（1,4,1）
  a,b,c（1,4,2）
  叶子节点（2）
  a,b,c（2,3,3）
  a,b,c（2,3,4）
  a,b,c（2,4,1）
  a,b,c（2,4,2）
  a 列在B+树整体有序，a 列相同的情况下 b 列数据按序排列，但 c列不一定有序。

查询过程

• 最左匹配

联合索引遵循最左匹配原则，只能从左往右依次搜索联合索引字段，否则索引字段不生效
例如索引是 key_index （a,b,c）。 可以支持 a 、a,b 、a,b,c 3种组合进行查找，但不支持 b,c 、c 进行查找

• 查询过程
  SELECT * FROM test_table_union_index WHERE merchant_id = 3 AND order_id = 2;

image.png

联合索引遵循最左匹配原则，以上述查询 SQL 为例，联合索引先根据 merchant_id = 3 在构建的B+树索引上进行查询数据，找到叶子节点：

image.png

然后根据 order_id = 2 查询定位数据，查询到数据对应的主键 ID = 2，最后进行回表查询

联合索引优点

• 支持复杂查询

联合索引能够加速包含多个条件和多个列的查询。这对于联接多个表或需要在多列上进行过滤的查询非常有用。

• 索引覆盖查询

联合索引可以覆盖多个查询中的列，从而减少了数据库的I/O负载。这意味着数据库不必访问数据行，而可以直接使用索引来满足查询条件

• 提高排序和分组性能

如果你的查询需要排序或分组结果，联合索引可以在这方面提供显著的性能改进，特别是当排序或分组涉及索引中的列时

• 减少索引数量

使用联合索引可以减少索引的数量，这对于大型数据库来说是一个重要考虑因素，因为每个额外的索引都会增加数据库维护的开销

常见问题分析

• 为什么遵循最左匹配原则

从联合索引的底层存储结构我们可以知道，联合索引是根据字段从左往右组织的，不从左边的字段开始查询无法使用索引。

• 联合索引中字段范围查询为什么会导致后续联合索引字段可不用

从联合索引的底层存储结构我们可以知道，叶子节点数据局部有序。联合查询中范围查询会导致后续字段数据无序，导致联合索引中后续索引字段失效。