MySQL 联合索引底层存储结构及索引查找过程解读

SELECT VERSION();

5.7.36-log

// 创建表
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_union_index` (`merchant_id`,`order_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

// 插入数据
INSERT INTO `test_table_union_index` (`merchant_id`, `order_id`) VALUES (3, 1);
INSERT INTO `test_table_union_index` (`merchant_id`, `order_id`) VALUES (3, 2);
INSERT INTO `test_table_union_index` (`merchant_id`, `order_id`) VALUES (4, 3);
INSERT INTO `test_table_union_index` (`merchant_id`, `order_id`) VALUES (4, 3);
INSERT INTO `test_table_union_index` (`merchant_id`, `order_id`) VALUES (5, 1);
INSERT INTO `test_table_union_index` (`merchant_id`, `order_id`) VALUES (5, 2);

// 查询 SQL EXPLAIN 分析
EXPLAIN SELECT * FROM test_table_union_index WHERE merchant_id = 3 AND order_id = 2;

// 查询 SQL EXPLAIN 分析
EXPLAIN SELECT * FROM test_table_union_index WHERE merchant_id = 3 AND order_id = 2;

- 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;

假设存在如下数据：
1(b=1,c=4,d = 10)
2(b=2,c=5,d = 6)
3(b=2,c=5,d = 7)
4(b=3,c=1,d = 2)
5(b=3,c=5,d = 1)

查询条件： b > 1 且 c = 5 , d = 6

先查找 b > 1 :
2(b=2,c=5,d = 6)
3(b=2,c=5,d = 7)
4(b=3,c=1,d = 2)
5(b=3,c=5,d = 1)

再查找 c = 5 , 此时 c 并不是有序的，因此无法使用联合索引字段 c，而是需要遍历所有4条数据（如果是有序：1,2,3,4,5,6,7,8 查找到5后就不再扫描）:
2(b=2,c=5,d = 6)
3(b=2,c=5,d = 7)
5(b=3,c=5,d = 1)

综上所述：联合查询中范围查询会导致后续字段数据无序，导致联合索引中后续索引字段失效。