第一篇讲过:B+树的叶子是按索引列排好序、串成一条链的。这篇把这句话用在复合索引上——索引 (a, b, c) 的叶子是按 a, b, c 依次排序的,不是三个独立的索引凑在一起。这个顺序决定了:从最左边的列开始、连续地给条件,索引才能真正帮上忙;跳过第一列直接查后面的列,索引基本上就是摆设。
下面全部是本机真实建表、真实 EXPLAIN 跑出来的结果。
建一个 KEY idx_abc (a,b,c),物理上只有一棵 B+树,叶子节点按 (a,b,c) 这个元组整体排序——先比 a,a 相等再比 b,b 也相等才比 c。
哪怕 5 > 1,只要 a 那一位 1<2,整个元组就排在前面——这和字典排序完全一样,先比第一个字,后面的字再重要也没用。
要利用这种排序做范围收缩,查询条件必须从 a 开始、连续地给,中间不能空——空了一列,后面的列在排好序的意义上就是"随机打散"的。
MySQL 每个索引列在源码里都有一个 store_length(占用字节数),用了几个列,EXPLAIN 里的 key_len 就是这些字节数的和——不用猜,数字直接告诉你。
sql/key.h · mysql-server @ mysql-9.7.1, L57 class KEY_PART_INFO { /* Info about a key part */ public: Field *field; ... /* Length of key part in bytes, excluding NULL flag and length bytes */ uint16 length; /* Number of bytes required to store the keypart value ... */ uint16 store_length; ... };
表 t(a,b,c,d),20 万行,复合索引 idx_abc (a,b,c),全部是 INT NOT NULL(每列 4 字节,不需要额外的 NULL 标记字节)。
| 查询 | type | key_len | Extra | 是否用上索引narrow范围 |
|---|---|---|---|---|
| WHERE a=5 | ref | 4 | — | 是(1 列) |
| WHERE a=5 AND b=10 | ref | 8 | — | 是(2 列) |
| WHERE a=5 AND b=10 AND c=100 | ref | 12 | — | 是(3 列,全用上) |
| WHERE a=5 AND c=100(跳过 b) | ref | 4 | Using index condition | 否,c 只能靠 ICP 过滤,不能收缩范围 |
| WHERE b=10(不带 a) | ALL | NULL | Using where | 否,索引完全没用上,全表扫描 |
第四行最能说明问题:条件里明明写了 a 和 c 两个索引列,但 key_len 还是只有 4——只有 a 真正参与了缩小索引扫描范围。c 能不能帮上忙,靠的是另一个机制:索引条件下推(ICP)——在 a=5 圈定的这 2000 行索引记录里,顺便把 c=100 也判断一遍,省下几趟不必要的回表,但没办法真正把索引扫描范围收窄到"只查 c=100 相关的记录"——因为在 a=5 内部,记录是按 b 排的,c 值是打散的。
第五行更直接:b 不是索引的第一列,单独拿 b 做条件,idx_abc 直接不在候选索引里——type=ALL,全表扫描 199,920 行。
用一棵玩具 B+树(键是 (a,b) 元组)直观看这件事:同样是"查一个值",从最左边的 a 开始查,和跳过 a 直接查 b,访问的叶子数量差多少。
玩具树一共 5 个叶子。WHERE a=2 能先用二分找到第一个可能匹配的叶子,然后只沿着链表往右走,一旦某个叶子里再也找不到 a=2 就立刻停下——因为排序保证了后面不会再出现;WHERE b=5 没有这种"停下来"的依据,b=5 的记录可能出现在任意一个叶子里,只能把 5 个叶子全部看一遍。
上一节的排序性质不只是帮着缩小扫描范围,还能省掉一次显式排序。
| 查询 | Extra |
|---|---|
| WHERE a=5 ORDER BY b | (无 filesort——叶子里 a=5 的记录本来就按 b 排好了) |
| WHERE a=5 ORDER BY c | Using filesort |
| SELECT a,b,c WHERE a=5 | Using index(覆盖索引,连聚簇索引都不用碰——第二篇的话题) |
ORDER BY b 不需要额外排序,因为 a=5 圈定的这段叶子记录,本来就是按 b 排好的——这是索引结构的副产品,不用额外花代价。但 ORDER BY c 就不行了:固定 a 之后,b 还在变化,c 只在"a 和 b 都固定"的情况下才有序,所以还是得老老实实排一次序。
结合前面几篇的内容,一个实用的排列思路: