跳过第一列就扫全表:B+ 树给联合索引排的那套序
B+ 树索引把键按同一个全序排进多级页里,非叶子页只存键和指针,记录全部落在由链表串起来的叶子页。联合索引 (a,b,c) 的“最左前缀”并非优化器额外加的规矩,而是键元组先比 a、再比 b、最后比 c 形成的唯一顺序:缺了 a,b 和 c 在整棵树里就是散的。很多人因此误以为联合索引的每一列都能单独加速,或者以为范围条件之后的列彻底没用。读完你能讲清页的扇出与定位过程,判断一条 SQL 用得上索引的哪一段,并按真实查询模式排出列序。
是什么
B+ 树索引是数据库为一张表维护的有序目录:所有键按同一个全序排好,非叶子页只保存键和指向子页的指针,完整记录(聚簇索引)或主键值(二级索引)放在叶子页,叶子页之间再用链表按序相连。直觉上它像一本先按姓、再按名排序的电话簿——你能迅速翻到“张”,是因为整本书先按姓排过;如果只知道名字,这套排序就帮不上忙。
把多个列放进同一个索引,就得到联合索引。它在树里的键是一个元组,比较时先看第一列,相等再看第二列,以此类推。所谓最左前缀,说的正是:只有从元组最左边开始的一段连续条件,才能把这棵树上的记录圈成一个连续区间。典型场景是按“店铺 + 状态 + 时间”取订单、按“分类 + 价格”筛商品、按“租户 + 创建时间”做分页。
核心原理
先看树的形状。InnoDB 的页默认 16 KB,非叶子页里每个槽位是一份键加一个子页指针。键通常只有几字节到十几字节,一页因此能容纳上千个分叉,三层树就足以覆盖千万级行。根页通常常驻缓冲池,一次点查一般只碰根、一层分支和一个叶子页,页访问次数是个位数;叶子页之间又按序相连,所以“定位到起点后顺着链表扫一段”非常便宜,范围查询和 ORDER BY 都吃这个红利。
这也解释了为什么常用 B+ 树而不是哈希表或普通二叉树:哈希只能等值命中,给不了区间与顺序;二叉树每个节点只有两个分叉,同样行数下树高得多,页访问次数随之上涨。B+ 树把分叉做大、把数据压到叶子,是用“一次页读换上千个候选”的思路在跟磁盘打交道。
真正要讲透的是联合索引的键。(shop_id, status, created_at) 在树里不是三列各自有序,而是按元组做字典序比较:先比 shop_id,相同再比 status,还相同才比 created_at。于是整棵树只保证一件事——键按这个全序排列。给定 shop_id = 42 AND status = 1,所有满足条件的键在排序里是连成一片的,优化器只要二分出这片区间的左右边界,再顺着叶子链扫即可;而只给 status = 1,不同店铺的记录会交错出现,created_at 在这片区域里也不再单调,树无法用一次区间圈住它们,只能退化成全表或全索引扫描。
范围条件的作用同理。等值列负责把区间收窄到唯一一段,范围列负责在这段里定出上下界;一旦遇到范围,它后面的列在这段区间内已经不再有序,也就无法继续用来定位。注意“不能定位”不等于“完全没用”:索引条件下推可以让存储引擎在遍历索引时先用后面的列过滤,减少回表;如果查询只需要索引里已有的列,还能走覆盖索引,连回表都省掉。
还有一个常被忽略的细节:InnoDB 会自动把主键追加到每个二级索引末尾,所以索引 (d) 在内部更像 (d, id)。这让 WHERE d = ? ORDER BY id 能直接利用索引顺序,但它救不了“缺最左列”的查询——主键排在 d 后面,没有 d 仍然进不了这片有序区。
MySQL 8.0.13 起还有索引跳跃扫描(skip scan):当最左列的不同值很少时,优化器可以把 WHERE b = ? 拆成“对每个 a 值各做一次 b 的区间查找”再合并。它不是推翻了最左前缀,而是用多次区间查找把缺失的前缀补齐,代价随最左列基数增长,所以只在前缀基数很低时才划算。
有人会追问:给每一列各建一个单列索引,让优化器做索引合并不就行了?可以,但索引合并要分别扫描多个索引再求交或求并,回表和去重的成本都高,执行计划也更脆;一条按真实访问路径排好序的联合索引,通常比几条单列索引的组合更稳。索引的数量不是越多越好,每多一条都要在写入时维护、在页满时分裂。
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
shop_id INT NOT NULL,
status TINYINT NOT NULL,
created_at DATETIME NOT NULL,
KEY idx_shop_status_time (shop_id, status, created_at)
) ENGINE=InnoDB;
-- 等值前缀把区间收窄,再按时间取范围,排序也顺带省掉
EXPLAIN SELECT id FROM orders
WHERE shop_id = 42 AND status = 1
AND created_at >= '2026-11-01'
ORDER BY created_at;
-- 缺了最左列,树序接不上,通常退化为全表扫描
EXPLAIN SELECT id FROM orders WHERE status = 1;
常见考点与误区
- “联合索引的每一列都能单独加速”:错。没有最左列,后面的列在整棵树里是散开的;
(a,b,c)上的b = ?一般用不上,除非触发跳跃扫描这类特例。 - “范围条件后面的列彻底没用”:不准确。它不能参与定位区间,但仍可被索引条件下推用来过滤,或随覆盖索引一起避免回表;
EXPLAIN里Using index condition与Using index说的就是这两件事。 - “WHERE 里条件的顺序要和索引一致”:错。优化器会重排等值条件,
WHERE status = 1 AND shop_id = 42与反过来等价;固定不变的是索引定义里的列序,不是 SQL 的书写顺序。 - “区分度最高的列放最左准没错”:片面。最左列应当匹配最主要的访问路径,通常等值列在前、范围列在后、排序列收尾;区分度只是在这套约束内做的微调,放错位置会让整条索引作废。
- “索引列上做函数或隐式转换没关系”:危险。
DATE(created_at) = ?、字符串列与数字比较、两侧字符集排序规则不一致,都会让那一列失去可定位性,等于把最左前缀从中间截断。 - “树越高越慢,所以层数是关键”:抓错了成本。三层覆盖千万行很常见,根页通常还常驻内存;真正的开销多在回表的随机读和扫描行数,而不是多下探一层。
实践建议
- 先列出真实查询,再定列序:等值条件在前、范围条件在后、
ORDER BY的列收尾;一条索引服务一组相似查询,不要指望一条索引包打天下。 - 用
EXPLAIN的key_len、rows与Extra验证,而不是凭感觉:key_len变大通常说明用上了更多列,Using index表示覆盖,Using index condition表示下推过滤。 - 长字符串用前缀索引,但先算选择性:比较
COUNT(DISTINCT LEFT(col, n)) / COUNT(*)与完整列的比例,再决定前缀长度,避免前缀太短导致回表次数暴涨。 - 定期清理冗余索引:
(a)通常被(a,b)覆盖,多出来的索引只会增加写放大与页分裂;改索引前用真实数据分布跑一遍执行计划。 - 深分页别直接大 OFFSET,改用“记住上一页最后一个键”的书签式翻页,或延迟关联先取主键;排序方向要与索引的列序和升降序一致。
- 上线前用
EXPLAIN ANALYZE看实际行数与耗时,别只看估算;统计信息陈旧时,优化器可能放着好索引不用。
一句话总结:最左前缀不是人为规定,而是 B+ 树只按一个全序排键的必然结果。