联合索引与覆盖索引
联合索引是按多列建立的索引,索引项先按第一列排序,第一列相同再按第二列排序,依此类推。覆盖索引不是一种单独的索引,而是指某个索引已经包含一次查询要用到的全部列,查询不必再去读完整的行。
这两个概念决定了一条查询走索引时到底读了多少数据:联合索引的列顺序决定能圈出多小的扫描范围,覆盖与否决定圈出来的每条记录还要不要再回聚簇索引取一次行。具体到一次查询,需要看索引项怎样逐级排序、二级索引缺列时怎样回表,以及排序和 LIMIT 怎样借用索引顺序。下文以 MySQL 8.0 InnoDB 为准。
1. 面试核心
先记住这几条,再读正文- 联合索引
(a,b,c)先按 a 排序,a 相同再按 b,a、b 都相同才按 c,所以a=1 AND b>10能圈出一段连续的扫描范围。卡 · 联合索引的键顺序 - 范围条件之后的索引列不再缩小扫描区间,但仍能参与 ICP 过滤或覆盖取列,例如
a=1 AND b>10 AND c=5中的 c。卡 · 范围定位、下推过滤与覆盖 - 二级索引缺少查询要的列时,要拿主键回聚簇索引读完整行,这一步叫回表;索引已包含所需列时,查询可以直接从索引取值。卡 · 二级索引与回表
- 前导列被等值条件固定后,剩下的索引列顺序可以直接满足排序:
(tenant_id,status,created_at,id)固定前两列后,可按后两列满足ORDER BY created_at DESC,id DESC。卡 · 排序、LIMIT 与列顺序 - 为了覆盖而往索引里加列,会让索引变宽,写入、存储和缓存成本都跟着上升。
2. 联合索引的键顺序
联合索引里的记录像电话簿一样逐级排序。以索引 (tenant_id, status, created_at) 为例:索引项先按 tenant_id 排;tenant_id 相同的,再按 status 排;前两列都相同的,才按 created_at 排。
所以下面这个条件可以在索引里找到一段连续的区间:先定位到 tenant_id = 10,在其中定位 status = 'PAID',再从 2026-08-01 开始往后读。
WHERE tenant_id = 10
AND status = 'PAID'
AND created_at >= '2026-08-01'
反过来,如果只给出 status,同一个状态的记录分散在每个 tenant 的分组里,在整棵索引中并不连续,没法直接定位成一个区间。这就是最左前缀:只有从第一列开始、连续给出的那几列,才能用来圈定扫描范围。
3. 等值、范围与排序列
决定列顺序时,三类列各有分工:等值列固定前缀,范围列划出扫描区间,排序列要和剩下的键顺序对上。完整的设计流程见 索引设计。
关键在于范围列之后的列不再有序可用。比如 (tenant_id, created_at, status) 能按 tenant 和时间扫描,但条件是 created_at > '2026-08-01' 时,区间里不同时间点的记录交错排列,status 只能在扫描过程中逐条过滤,没法再缩小区间。如果主要查询是按 tenant、status 等值过滤再按时间翻页,就应该把 status 放到时间前面,用 (tenant_id, status, created_at)。
跳跃扫描、索引条件下推和索引合并能让一些不满足最左前缀的查询也用上索引,但它们各有适用条件,实际走哪条路径以执行计划为准。
4. 二级索引与回表
InnoDB 的二级索引不直接保存整行,而是保存索引列加上聚簇键(通常就是主键),靠聚簇键找到完整的行。存储组织见 聚簇索引。
所以用二级索引查询一个不在索引里的列时,要分两步:
- 在二级索引里找到符合条件的记录,拿到它们的主键。
- 用主键去聚簇索引读完整的行,取出需要的列。
第二步就是回表。命中的记录少时,回表开销不明显;命中成千上万条时,每条都要按主键到聚簇索引里单独查一次,这些访问往往分散在不同的页上,回表就会成为主要成本。
5. 覆盖索引
如果一个索引同时包含查询的过滤列和返回列,它就覆盖了这次查询,第 2 步回表可以省掉:
SELECT created_at
FROM orders
WHERE tenant_id = 10 AND status = 'PAID';
这条查询只用到 tenant_id、status 和 created_at,索引 (tenant_id, status, created_at) 里都有,所以可以直接从索引取值。EXPLAIN 的 Extra 列会显示 Using index。
覆盖查询也不是完全不碰聚簇索引。二级索引记录本身不带版本信息,MVCC 判断可见性时有时要回到聚簇记录:二级索引记录带有删除标记时会这样;修改过该索引页的事务对当前读视图来说较新时,也会这样。必要时,InnoDB 还会通过 undo 重建可见的旧版本。
覆盖是一个索引和一条具体查询之间的关系,换一条要多返回一列的查询,同一个索引就不再覆盖。把所有返回列都塞进索引,会增加空间、缓存压力和写放大,所以只为高频、性能敏感、返回列少的查询考虑覆盖。
6. 主键宽度与索引成本
每条二级索引记录都带着聚簇键,主键越宽,所有二级索引的空间和缓存开销都越大。机制和选择条件见 聚簇索引。
7. 索引访问的执行证据
用上了索引不等于查询就快,还要看扫描了多少行、回表多少次、有没有额外排序。判断时看哪些证据字段见 数据库索引,实测审查流程见索引设计。
8. 范围定位、下推过滤与覆盖
范围之后的索引列虽然不能缩小区间,但还能在回表之前帮忙过滤。下面用一张独立的示意表说明,优化器最终选不选这条索引,仍取决于数据量和统计信息:
CREATE TABLE index_demo (
id BIGINT PRIMARY KEY,
tenant_id BIGINT NOT NULL,
status VARCHAR(16) NOT NULL,
created_at DATETIME NOT NULL,
payload VARCHAR(200),
KEY idx_tenant_time_status (tenant_id, created_at, status)
) ENGINE=InnoDB;
EXPLAIN SELECT payload FROM index_demo
WHERE tenant_id = 10 AND created_at > '2026-08-01'
AND status = 'PAID';
tenant_id 等值加 created_at 范围,定位出一段连续的索引区间。status 不能再缩小这段区间,但它就在索引项里。索引条件下推(Index Condition Pushdown,ICP)是指存储引擎在扫描索引时,先用索引里已有的列检查条件,不满足的记录直接跳过,不去回表。这里引擎先用 status = 'PAID' 过滤,只为剩下的记录回表读 payload。EXPLAIN 显示的 Using index condition 就是这个机制。
如果把返回列改成 id, created_at, status,过滤和返回要的列都在索引项里(id 是聚簇键,二级索引本来就带着),查询就被覆盖了,Extra 显示 Using index。两者回答的是不同问题:Using index 说的是索引里的数据够不够用,Using index condition 说的是过滤在回表前还是回表后做。
前缀索引是个例外。name(10) 这样的索引只保存前 10 个字符,查询要返回完整的 name 时,索引里的内容不够,仍然要回表。
9. 排序、LIMIT 与列顺序
索引本身就是有序的,排序列和索引顺序对上时,数据库可以按索引顺序读,读够 LIMIT 条就停,不必先把所有结果取出来再排序。假设主要查询是某租户已支付订单的最新列表:
SELECT id, created_at
FROM orders
WHERE tenant_id = 10 AND status = 'PAID'
ORDER BY created_at DESC, id DESC
LIMIT 20;
在索引 (tenant_id, status, created_at, id) 上,前两列被等值条件固定后,剩下的记录正好按 created_at, id 排列,从末尾反向扫描就是 DESC 顺序,读到 20 条即可停止。这里显式写出 id,是为了让排序稳定、便于分析;实际设计时要记得 InnoDB 二级索引已经带着主键,避免定义出没有意义的冗余列。
如果查询去掉 status 条件,情况就变了。索引里的记录先按状态分组,每个状态组内部按时间有序,但把几组直接拼起来并不是全局的时间顺序。执行计划沿这条索引取出所有状态的结果后,就需要额外排序。要避免排序,可以针对这条查询另建 (tenant_id, created_at, id)。排序方向混合(一列升序、一列降序)时,还要核对索引各列定义的升降序。
LIMIT 20 限制的是返回行数,不是扫描行数。如果扫描过程中大量记录被其他条件过滤掉,就要读更多索引项才能凑够 20 条;大 OFFSET 也要先读过前面被跳过的那些记录。所以实际成本要看执行计划里的扫描量。
10. 常见问题
10.1 字符串前缀匹配
不会。对可以直接比较的字符串索引列,LIKE 'abc%' 相当于「大于等于 abc、小于下一个前缀」的范围,可以在索引上定位。它之后的列能不能继续用,和普通范围条件一样,取决于生成的访问区间。LIKE '%abc' 没有固定前缀,以 abc 结尾的值分散在整个索引里,只能扫描后逐条过滤。
10.2 联合索引与单列访问
不能一概而论。联合索引能用于和它有序前缀匹配的范围定位,也能用于全索引扫描、覆盖查询和满足条件的跳跃扫描。某一列经常被单独查询时,先看现有联合索引给出的扫描范围;范围过宽,再评估单列索引或另一组联合索引。最终要结合读写成本和真实查询频率取舍。
11. 面试题卡片
12. 参考资料
- MySQL 8.0:Multiple-Column Indexes
- MySQL 8.0:Clustered and Secondary Indexes
- MySQL 8.0:EXPLAIN Output Format
- MySQL 8.0:Range Optimization
- MySQL 8.0:Index Condition Pushdown
- MySQL 8.0:ORDER BY Optimization
- MySQL 8.0:InnoDB Multi-Versioning
来源:Coding 101 原文 · 免费阅读,引用请保留出处。内容使用说明