外观
12 - 联合索引怎么设计:最左匹配不是背口诀
摘要与阅读定位
查询“某店铺、已支付、某段时间内的订单”时,索引列该按店铺、状态、时间排列,还是店铺、时间、状态排列?两种索引都包含查询条件,却可能扫描不同数量的候选记录。关键在于列的先后顺序:它决定记录如何排列,进而影响扫描范围、过滤、排序和取数方式。本文先解释这套模型,再用同一组数据比较两种设计。
- 前置:11 B+ Tree 与回表、03 类型与比较契约。
- 后续:14 执行计划 → 13 统计与成本;应用:08 分页、15 排序临时空间。
- 基线:MySQL 8.0.16+ / 8.4、InnoDB;
EXPLAIN ANALYZE需 8.0.18+。实验方法见 A,具体执行计划和性能需在目标实例测量,不能预设访问算法或key_len的值。
初次先看第 1 节的排序直觉与五行验证,再用第 2—3 节比较候选集合。第 4 节开关对照、第 6 节特殊访问与第 7 节不可见索引是可选进阶;学习最左匹配不要求先修改优化器配置。
1. 原理:把“用索引”拆成四件事
联合索引是把多个列组成一个有先后顺序的键,像通讯录先按城市、再按姓名排列。先固定城市,剩下姓名就有序;只看姓名时,同一个姓名可能分散在不同城市段。这个“先比第一列,相等再比下一列”的规则称为字典序。
例如 shop_id 是店铺编号,status 是订单状态,created_at 是创建时间。索引 (shop_id,status,created_at) 先按店铺排列,同店铺内按状态排列,同状态内再按时间排列。每组列值称为一个键元组,下面省略日期,只展示排序关系:
text
(10,CREATED,09:00)
(10,PAID,08:00)
(10,PAID,09:00)
(11,CREATED,07:00)
(11,PAID,10:00)固定 shop_id=10 和 status='PAID' 后,两条记录集中在一起,且按时间排列;只固定店铺时,时间会在每个状态段内重新开始。因此 (店铺, 状态, 时间) 与 (店铺, 时间, 状态) 支持的访问方式不同。调换 WHERE 条件的书写顺序不会改变索引键顺序。
从索引开头连续固定为特定值的列,称为等值前缀,例如上面的店铺和状态。最左匹配的基本含义是:利用这样的前缀定位记录段,再沿后续列查找。后续列即使不能缩小扫描范围,也可能有其他用途。判断一个索引是否合适,需要分开看下面四件事:
| 作用 | 要回答的问题 | 不能混淆的计划线索 |
|---|---|---|
| 访问范围 | 从哪个键元组扫到哪个元组? | range/ref 和访问条件;不是只看 key_len |
| 索引内过滤 | 范围内哪些候选可在回表前排除? | Using index condition / ICP,不表示覆盖 |
| 提供顺序 | 固定前缀后剩余排序与 ORDER BY 是否一致? | 是否另需 filesort,不能只看 key 名 |
| 覆盖 | 所需列是否由索引记录提供? | Using index;MVCC 例外见 11 |
表中的回表指通过二级索引携带的主键继续读取完整行;覆盖指查询所需列都能由选中的索引提供。执行计划描述数据库准备怎样取数:range、ref 是访问类型,key 是所选索引,key_len 是所用键的长度线索。filesort 表示需要额外排序,并不意味着一定写磁盘。索引条件下推(ICP)在第 4 节展开;多版本并发控制(MVCC)带来的可见性检查例外见 11。
等值前缀固定后,下一列的范围条件通常能对应一个连续键区间。例如固定店铺和状态后,时间上下限就能确定扫描起止位置。若把时间放在状态前面,时间区间内会包含多个状态组,状态条件通常不能让每个中间时间组都只扫描目标状态;它仍可能用于过滤、覆盖或限定区间边界。IN、OR、行构造器(多个列值一起比较)及多个区间有各自的规则,不能概括为“遇到范围后全部失效”。
1.1 五行验证:全店按时间与固定状态按时间
在隔离实例、旧事务已明确结束的连接中执行,表已存在时先确认归属:
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET autocommit = 1;
CREATE TABLE composite_basic (
id BIGINT PRIMARY KEY,
shop_id BIGINT NOT NULL,
status VARCHAR(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
created_at DATETIME NOT NULL,
KEY idx_composite_basic (shop_id, status, created_at, id)
) ENGINE = InnoDB;
INSERT INTO composite_basic VALUES
(1, 1, 'PAID', '2026-09-01 10:00:00'),
(2, 1, 'CREATED', '2026-09-02 10:00:00'),
(3, 1, 'PAID', '2026-09-03 10:00:00'),
(4, 2, 'PAID', '2026-09-01 10:00:00'),
(5, 1, 'PAID', '2026-09-02 10:00:00');
SELECT id, status, created_at FROM composite_basic
WHERE shop_id = 1 ORDER BY status, created_at, id;
SELECT id, status, created_at FROM composite_basic
WHERE shop_id = 1 ORDER BY created_at, id;
SELECT id, created_at FROM composite_basic
WHERE shop_id = 1 AND status = 'PAID' ORDER BY created_at, id;推导预期 ID 顺序分别是 2,1,5,3、1,2,5,3、1,5,3。第一种按状态分段,第二种不分状态,所以不同;第三种固定店铺和状态,余下时间顺序与索引键一致。
这里通过显式 ORDER BY 验证两种需求,并不是凭未排序查询的偶然结果推断物理索引顺序。实际是否使用索引或额外排序,还需要执行计划;五行样本不证明某种索引一定更快。
2. 实现:每店每天两笔,状态与日期都有变化
接下来用 60 行比较两种索引的候选集合:三个店铺、十天、每店每天两笔,PAID 发生在第 1、5、9 天。同一天的两笔订单时间相同,用于检查排序边界。
在隔离库初始化 composite_* 教学表,先结束其他实验事务,并确认待删除表的归属。composite_digit 提供数字 0—9;三个数字表的交叉连接组合出店铺、日期和每日两笔订单,再用条件限制店铺数与每日笔数。
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET autocommit=1;
DROP TABLE IF EXISTS composite_order;
DROP TABLE IF EXISTS composite_digit;
CREATE TABLE composite_digit (digit INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO composite_digit VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE composite_order (
id BIGINT PRIMARY KEY,shop_id BIGINT NOT NULL,
status VARCHAR(16) NOT NULL,created_at DATETIME NOT NULL,
amount DECIMAL(10,2) NOT NULL,payload VARCHAR(200) NOT NULL,
KEY idx_composite_status_time (shop_id,status,created_at,id),
KEY idx_composite_time_status (shop_id,created_at,status,id)
) ENGINE=InnoDB;
INSERT INTO composite_order
SELECT 1+shop.digit*20+day.digit*2+duplicate.digit,shop.digit+1,
CASE WHEN MOD(day.digit,4)=0 THEN 'PAID' ELSE 'CREATED' END,
TIMESTAMPADD(DAY,day.digit,'2026-09-01 10:00:00'),
10+duplicate.digit,REPEAT('x',200)
FROM composite_digit AS shop CROSS JOIN composite_digit AS day
CROSS JOIN composite_digit AS duplicate
WHERE shop.digit<3 AND duplicate.digit<2;
SELECT shop_id,status,COUNT(*) AS rows_count
FROM composite_order GROUP BY shop_id,status ORDER BY shop_id,status;
ANALYZE TABLE composite_order;预期每店 CREATED=14、PAID=6,id 为 1—60。ANALYZE TABLE 更新表的统计信息,供优化器估算成本。优化器负责从可用访问方式中选择执行计划;数据很少时,它可能认为全表扫描更便宜。下节的 FORCE INDEX 提示用于限制候选索引,便于对照,不代表生产查询应该强制索引。
3. 同查询、两种列顺序
先执行普通 SELECT 核对结果;确认之后再单独运行两个 EXPLAIN ANALYZE。后者会实际运行查询,强制索引只用于隔离环境观察候选,不作为生产推荐。
sql
SELECT id,amount,created_at FROM composite_order
WHERE shop_id=1 AND status='PAID'
AND created_at>='2026-09-05' AND created_at<'2026-09-10'
ORDER BY created_at,id;
EXPLAIN ANALYZE
SELECT id,amount,created_at
FROM composite_order FORCE INDEX (idx_composite_status_time)
WHERE shop_id=1 AND status='PAID'
AND created_at>='2026-09-05' AND created_at<'2026-09-10'
ORDER BY created_at,id;
EXPLAIN ANALYZE
SELECT id,amount,created_at
FROM composite_order FORCE INDEX (idx_composite_time_status)
WHERE shop_id=1 AND status='PAID'
AND created_at>='2026-09-05' AND created_at<'2026-09-10'
ORDER BY created_at,id;结果预期 ID 为 9、10、17、18,金额依次为 10、11、10、11。时间条件使用左闭右开的区间:包含 9 月 5 日零点,排除 9 月 10 日零点。
idx_composite_status_time 固定店铺和状态后,只需考察该状态段内的时间区间。idx_composite_time_status 固定店铺后,先按时间确定区间,其中还包含 9 月 6、7、8 日的 CREATED 候选,需要进一步排除。
独立计数比较候选集合:
sql
SELECT COUNT(*) AS all_status_candidates,
SUM(status='PAID') AS paid_candidates
FROM composite_order
WHERE shop_id=1 AND created_at>='2026-09-05' AND created_at<'2026-09-10';预期输出为 10,4:店铺和时间条件匹配 10 行,其中 4 行为 PAID。这是逻辑候选集合的大小,不能直接当作某个计划读取的物理索引项数量。实际工作还受范围边界、ICP 及查询是否带 LIMIT 等因素影响,需要结合执行计划和语句级统计判断。
第二个索引过滤到 status='PAID' 后,剩余记录也按 created_at,id 排列,但候选范围仍可能更宽。因此,满足排序不等于扫描范围同样精确。若还经常查询同店铺所有状态的时间区间,时间在前的索引又可能被更多查询复用。
等值列的顺序也不能只按“整表中不同值最多的列放前面”决定。还要看哪些查询只使用部分前缀、它们出现的频率,以及列之间是否相关——例如某个店铺的状态分布是否明显不同于全表。
4. ICP:减少回表,不承诺减少全部索引扫描
可选进阶:如果索引已经读到状态,却还要从完整记录取金额,可以先在索引侧排除非 PAID 候选,再回表取金额。把这类条件交给存储引擎先判断,称为索引条件下推,缩写 ICP。它省的是被排除候选的后续访问,不代表所有这些索引项都没有读取过。
InnoDB 的二级索引保存索引列和主键,完整行组织在聚簇索引中。ICP 适用于二级索引的范围等访问:先用索引列判断可下推的条件,只有通过的候选才继续读取完整行。聚簇索引已经读取完整记录,不能用同样机制减少回表。子查询、存储函数、虚拟生成列二级索引等仍有支持限制。
sql
SET @composite_old_switch=@@session.optimizer_switch;
SET SESSION optimizer_switch='index_condition_pushdown=off';
EXPLAIN
SELECT id,amount FROM composite_order FORCE INDEX (idx_composite_time_status)
WHERE shop_id=1 AND status='PAID'
AND created_at>='2026-09-05' AND created_at<'2026-09-10';
SET SESSION optimizer_switch='index_condition_pushdown=on';
EXPLAIN
SELECT id,amount FROM composite_order FORCE INDEX (idx_composite_time_status)
WHERE shop_id=1 AND status='PAID'
AND created_at>='2026-09-05' AND created_at<'2026-09-10';
SET SESSION optimizer_switch=@composite_old_switch;对照传统 EXPLAIN 的 Extra 列;若另外查看 TREE 树形计划,还要区分访问条件与过滤条件。启用开关不保证采用 ICP,出现 Using index condition 才是相应线索。示例返回的 amount 不在索引中,因此仍需要回表,适合观察下推条件的作用。
结果相同不能证明成本相同,Using index condition 也不表示覆盖。计划中的迭代器是逐步产生记录的执行节点;ICP 后节点报告的 actual rows(实际输出行数)可能已经过滤,不能直接当作读取的全部原始索引项数量。统计边界见 14。
5. 排序与 Top N:不跳过未固定的中间列
Top N 指按指定顺序取前 N 行,这里是 LIMIT 5。若索引本身提供所需顺序,数据库可能在取得足够结果后停止;若需要额外排序,就不能仅凭最终返回五行判断扫描工作量。
sql
EXPLAIN
SELECT id,created_at FROM composite_order FORCE INDEX (idx_composite_status_time)
WHERE shop_id=1 ORDER BY created_at,id LIMIT 5;
EXPLAIN
SELECT id,created_at FROM composite_order FORCE INDEX (idx_composite_time_status)
WHERE shop_id=1 ORDER BY created_at,id LIMIT 5;第一索引的 status 未固定:全局先按 status 分段,不提供全店时间顺序,通常还需排序。第二索引按时间排,但同时间内 status 排在 id 前;本样本同一时间只有一种状态,偶然结果不证明索引一般满足 created_at,id。稳定核心需求应考虑 (shop_id,created_at,id),而不是依赖样本相关性。
用一个可回滚的同时间异状态样本直接揭示差异,执行完再继续后面的实验:
sql
START TRANSACTION;
INSERT INTO composite_order VALUES (61,1,'CREATED','2026-09-01 10:00:00',12,'counterexample');
SELECT id,status FROM composite_order
WHERE shop_id=1 AND created_at='2026-09-01 10:00:00'
ORDER BY created_at,id;
SELECT id,status FROM composite_order
WHERE shop_id=1 AND created_at='2026-09-01 10:00:00'
ORDER BY created_at,status,id;
ROLLBACK;预期第一条查询的 ID 顺序为 1、2、61;第二条按状态再按 ID 排列,为 61、1、2,与索引元组顺序一致。这说明时间相同时,插在时间和 ID 之间的状态列会改变顺序。两条查询都显式指定 ORDER BY;没有它就不能保证返回顺序。ROLLBACK 后仍为 60 行。
MySQL 8.0 支持降序索引。ASC 表示升序,DESC 表示降序:全升序索引可以反向扫描满足全降序需求;混合方向需要对应定义,或与索引整体反向后的顺序一致。例如 (shop_id,created_at DESC,id ASC) 可满足固定店铺后按时间降序、ID 升序的需求。时间可能重复时,ORDER BY 还需用唯一的 ID 等列确定先后,才能让同一份数据上的 LIMIT 结果稳定;跨请求的数据快照一致性另见 08。
6. 覆盖、Skip Scan 与 Index Merge 的边界
若核心查询仅返回 id、created_at,现有二级索引可以提供这些列,其中主键 id 也用于定位完整行。若还返回 amount,可考虑 (shop_id,status,created_at,id,amount) 作为覆盖候选。但增加列会使索引记录更宽,占用更多空间和缓存,相关数据写入时也要维护它。低频查询需要的 payload 等列,不宜因此加入每个索引。
缺少最左列条件时,仍可能扫描整个索引并从中取数;满足条件时,还可能采用 Skip Scan(跳跃扫描)。它枚举缺失前缀的不同值,再分别扫描后缀的子范围。例如未指定店铺时,可把访问拆成不同店铺下的后续范围。MySQL 8.4 手册的限制包括单表查询、引用列可由索引提供、无 GROUP BY 或 DISTINCT、后续列存在范围条件等,不能认为任意只查后两列的查询都能高效使用它。
sql
EXPLAIN
SELECT shop_id,status,created_at,id FROM composite_order
WHERE status='PAID' AND created_at>='2026-09-05' AND created_at<'2026-09-10';计划可能显示 Using index for skip scan,也可能选择其他路径。缺失前缀的不同值越多,需要枚举的子范围可能越多,成本也可能更高。**Index Merge(索引合并)**则合并同一表多个索引的扫描结果;它并未把两个单列索引变成按多列排列的联合键,不能默认提供查询所需的 ORDER BY 顺序。
7. 不可见索引与删除评估:查询试验不等于写入节省
可选进阶:不可见是暂时不让通常的优化器选择这个索引,并不是删除。即使计划不用它,写入仍要维护它。先掌握这个区别,再考虑下面的 DDL 对照。
只在教学库操作,结束其他事务后执行:
sql
SET @composite_visibility_switch=@@session.optimizer_switch;
SET SESSION optimizer_switch='use_invisible_indexes=off';
ALTER TABLE composite_order ALTER INDEX idx_composite_status_time INVISIBLE;
EXPLAIN SELECT id,amount FROM composite_order
WHERE shop_id=1 AND status='PAID' AND created_at>='2026-09-05';
ALTER TABLE composite_order ALTER INDEX idx_composite_status_time VISIBLE;
SET SESSION optimizer_switch=@composite_visibility_switch;不可见索引仍需维护,如果是唯一索引也仍执行唯一性约束,因此不会省去索引写入成本。主键及特定充当主键的唯一索引不能设为不可见。ALTER TABLE 属于 DDL(数据定义语句),还涉及 MDL(保护表结构的元数据锁)及隐式提交:不能把它当作普通查询或可随事务回滚的改动。边界见 03、19。
删除短前缀索引前检查:唯一性、外键依赖、覆盖、排序、键宽、查询复用和全部工作负载;长索引“包含前缀”不自动证明短索引冗余。13继续讨论成本与强制索引回退。
8. 验收与工程取舍
- 列出查询集合、参数分布和频率,明确等值/范围/排序/返回列。
- 分别证明范围、过滤、顺序和覆盖;对同时间不同状态构造反例,不拿偶然样本证明排序。
- 比较改动前后完整结果,用 14保留估算、实际输出、loops 和等待边界。
- 扩大数据时保持分布并记录缓存热度、统计、并发、写耗时与空间;每次只改一个因素。
- 指定回退方案,正常和失败路径均恢复记录的会话开关及索引可见性;若连接已断开,不能依赖旧会话变量继续恢复,应重新核对配置。清理先结束事务,在隔离库删除 composite_order、composite_digit、composite_basic。
设计完成后,用 14分析查询实际做了多少工作,再用 13理解优化器为何选择或放弃某条路径。
9. 名词解释
| 名词 | 含义 |
|---|---|
| 联合索引 / 字典序 | 多列组成的有序键 / 从第一列开始依次比较,前列相同才比较后列 |
| 等值前缀 | 索引开头连续列都被固定为特定值的部分 |
| 访问范围 / 索引内过滤 | 定位并扫描的键区间 / 在候选中继续排除不符合条件的记录 |
| ICP | 让适用条件在索引侧由引擎先判断,减少部分后续回表 |
| 覆盖 | 查询需要的列可由选中索引提供,不是新建一种独立索引类型 |
| Skip Scan / Index Merge | 枚举缺失前缀后扫描子范围 / 合并同表多个索引扫描,均有适用条件 |
| 不可见索引 | 通常不参与优化器选路但仍维护的索引,不等于删除 |
| filesort | 需要额外排序的计划线索,不能据名称判定磁盘写入 |
参考资料
- MySQL 8.4 Reference Manual:Multiple-Column Indexes、Range Optimization(Skip Scan)、Index Condition Pushdown Optimization、Descending Indexes、Invisible Indexes。
- 适用条件:具体范围、是否避免排序、开关对照与性能应在实际小版本留证。