先说结论:覆盖索引(Covering Index,覆盖索引)不是一个新索引类型,而是「你这条查询要用的列,刚好都在同一个索引里」。做到了,数据库翻完索引就把结果给你,不用再回表取整行;没做到,它得拿着主键再跑一趟。
上一篇《覆盖索引怎么设计:从回表代价到 MySQL 与 PostgreSQL 的落地写法》是从执行计划的角度拆的,这篇换个更好入口的顺序重讲一遍,尽量少用术语。
目录
- 一、回表:先去目录柜,再去书架(What Is a Lookup)
- 二、覆盖索引:把答案写在卡片背面(Covering Index in Plain Words)
- 三、一张订单表,三个判断条件(Three Checks)
- 四、MySQL 跑一遍(Runnable SQL)
- 五、EXPLAIN 里的几种值,只看一个(Reading Extra)
- 六、PostgreSQL:名字对了,还要看 Heap Fetches(Index Only Scan)
- 七、代价:索引是拿写入换读取(The Cost)
- 八、什么情况下先别急着加(When to Skip)
- 九、症状、原因、解法(Symptom → Cause → Fix)
一、回表:先去目录柜,再去书架(What Is a Lookup)
图书馆找书分两步。先在目录柜翻卡片,卡片上写着书名和索书号;再拿着索书号去书架,把书取下来。
数据库走二级索引(Secondary Index,二级索引)的过程一模一样。索引像目录柜,翻得又快又连续;真正的数据行在别处,得按索引里记的线索去取。
InnoDB 的线索有点特别:二级索引不存物理地址,存的是主键值。所以取整行的动作就变成「拿主键,再去聚簇索引(Clustered Index,聚簇索引)里查一遍」。这一步有个专有名字,叫回表(Lookup,回表)。
回表贵在它是随机跳的。翻索引时你在连续读,一次能读进一页;回表时每行都可能躺在磁盘另一头,一千行就是一千次跳。慢查询日志里那种「只扫了几十行,却花了几百毫秒」的语句,多半栽在这里,跟索引有没有建关系不大。
flowchart TD
A[一条查询] --> B[翻二级索引]
B --> C{要的列都在索引里吗}
C -->|在| D[直接拿结果走]
C -->|不在| E[取出叶子里的主键]
E --> F[(按主键回表)]
F --> G[拿到整行]
D --> H[返回结果]
G --> H
classDef start fill:#DBEAFE,stroke:#3B82F6,stroke-width:2px,color:#111827
classDef step fill:#DCFCE7,stroke:#22C55E,stroke-width:2px,color:#111827
classDef ask fill:#FEF3C7,stroke:#F59E0B,stroke-width:2px,color:#111827
classDef store fill:#CFFAFE,stroke:#06B6D4,stroke-width:2px,color:#111827
classDef done fill:#EDE9FE,stroke:#8B5CF6,stroke-width:2px,color:#111827
class A start
class B,E,G step
class C ask
class F store
class H done
二、覆盖索引:把答案写在卡片背面(Covering Index in Plain Words)
接着用图书馆的比方。有些问题不用翻开书就能答,比如「这本书多少页」「哪家出版社」。那就把这两项写在卡片背面。查这类问题时,你压根不用去书架。
覆盖索引干的就是这件事:把查询要用的列,一起塞进同一个索引。叶子节点里既然已经有这些值,数据库就不必回表了。
要提醒一句,「覆盖」是一对关系,不是一个物体的属性。同一条索引,对 A 查询覆盖,对 B 查询可能就不覆盖。MySQL 里也没有 CREATE COVERING INDEX 这种写法,所谓覆盖索引,就是在设计普通索引时多想半步。
判断覆盖看三件事:
| 看什么 | 具体要求 |
|---|---|
| 过滤 | WHERE 里的列在索引里,且满足最左前缀 |
| 排序 | ORDER BY 的列跟在等值条件后面,顺序一致 |
| 返回 | SELECT 的每一列都在索引里(主键不用写,索引自带) |
三条全中才算覆盖,缺一条就还是老路。
三、一张订单表,三个判断条件(Three Checks)
拿订单表举例,字段是 id、user_id、created_at、amount、status、remark(备注)。最热的查询是「查某个用户最近三笔订单的金额和状态」。
SELECT id, amount, status
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 3;
想让它覆盖,索引建成 (user_id, created_at, amount, status):
user_id是等值条件,放最左边,先把范围缩到一个人created_at紧跟其后,顺序都排好了,连排序都省掉amount、status放尾部,只为「能读到」,不参与查找id不用写,二级索引叶子自带主键
这时候如果有人把 SQL 改成 SELECT id, amount, status, remark,覆盖立刻破功。备注不在索引里,只能回表。这也是 SELECT * 最坑的地方:今天刚好覆盖,明天运维加一列,查询就悄悄退化了,EXPLAIN 还不会报错。
排序这条容易被忽略。user_id 是等值、created_at 是排序,这个组合能顺着索引往下走。可要是把排序改成 ORDER BY amount,哪怕所有列都在索引里,数据库还是得把结果捞出来重排一遍。覆盖只管「读多少」,不管「怎么排」。
四、MySQL 跑一遍(Runnable SQL)
下面这段在 MySQL 8.0 上可以整段粘贴执行。数据只造六行,小表上优化器很可能直接全表扫,所以用 FORCE INDEX 把路固定住,先看清「覆盖」和「不覆盖」在 EXPLAIN 上的差别。真实项目里记得把 FORCE INDEX 去掉。
DROP TABLE IF EXISTS orders; -- 可重复执行,先清掉旧表
CREATE TABLE orders ( -- 订单表
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 主键,二级索引叶子自带
user_id BIGINT UNSIGNED NOT NULL, -- 用户 id,过滤列
created_at DATETIME NOT NULL, -- 下单时间,排序列
amount DECIMAL(10,2) NOT NULL, -- 金额,返回列
status TINYINT NOT NULL DEFAULT 0, -- 状态,返回列
remark VARCHAR(200) DEFAULT NULL -- 备注,故意不放索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 字符集统一,避免隐式转换
INSERT INTO orders (user_id, created_at, amount, status, remark) VALUES
(42, '2026-11-01 10:00:00', 199.00, 1, '首单'), -- 用户 42 第一笔
(42, '2026-11-05 14:30:00', 88.50, 1, '复购'), -- 用户 42 第二笔
(42, '2026-11-09 09:15:00', 320.00, 2, '退款中'), -- 用户 42 第三笔
(42, '2026-11-12 20:40:00', 45.90, 1, '加购'), -- 用户 42 第四笔
(7, '2026-11-03 11:20:00', 120.00, 1, '新客'), -- 另一个用户
(9, '2026-11-08 16:05:00', 66.60, 3, '已关闭'); -- 第三个用户
CREATE INDEX idx_user_created ON orders (user_id, created_at); -- 窄索引,只管过滤排序
CREATE INDEX idx_user_cover ON orders (user_id, created_at, amount, status); -- 覆盖索引,多带返回列
EXPLAIN
SELECT id, amount, status FROM orders FORCE INDEX (idx_user_cover) -- 要的列全在索引里
WHERE user_id = 42 -- 等值命中最左列
ORDER BY created_at DESC -- 顺索引顺序,免排序
LIMIT 3; -- 期望 Extra 出现 Using index
EXPLAIN
SELECT id, amount, remark FROM orders FORCE INDEX (idx_user_cover) -- 多要 remark 一列
WHERE user_id = 42 -- 过滤仍走索引
ORDER BY created_at DESC -- 排序仍可用索引
LIMIT 3; -- 期望 Extra 里没有 Using index
第一条你应该在 Extra 里看到 Using index,第二条看不到。这两行的差别,就是回表有没有发生。
我这边没有可用的 MySQL 服务端,没法贴真实输出,所以上面写的是「期望看到」。你本地跑完最好自己确认一眼,别人的机器不代表你的数据分布。
五、EXPLAIN 里的几种值,只看一个(Reading Extra)
Extra 这一列有几种值长得挺像,很容易看错。
| Extra | 实际意思 | 算覆盖吗 |
|---|---|---|
Using index | 只读了索引 | 是 |
Using index condition | 条件下推到引擎了,但整行还是取了 | 否 |
Using where | 取完行再筛,说明已经回表 | 否 |
| 空 | 没有额外信息,通常就是回表了 | 否 |
Using index condition 是个陷阱。名字里带 index,看着像那么回事,其实说的是索引下推(Index Condition Pushdown,索引下推),省的是回表的次数,不是回表本身。记住一句话:Extra 里没有单独出现 Using index,就当没覆盖。
另外两种也认得一下:Using index; Using filesort 表示读是覆盖了,排序另做了一遍;Using index for group-by 是分组场景的优化,跟这里讲的覆盖不是一回事。
六、PostgreSQL:名字对了,还要看 Heap Fetches(Index Only Scan)
PostgreSQL 里对应的执行计划节点叫 Index Only Scan,字面意思和 Using index 一样。但这里有个 MySQL 没有的坑。
PG 的索引里不记「这一行对当前事务可不可见」。判断可见性,理论上还得去堆表看一眼。为了少看这一眼,它维护了一张 visibility map(可见性映射),按页给数据贴小绿标:这页所有行都干净,谁都能用。
只有贴了绿标的页,Index Only Scan 才真的不碰堆表。没贴的页,节点名字再好看,它照样去取。
所以判断 PG 有没有真省掉回表,别只看节点名,要看 Heap Fetches:
CREATE INDEX idx_orders_cover ON orders (user_id, created_at) INCLUDE (amount, status, id); -- 11+ 才有 INCLUDE
ANALYZE orders; -- 先让优化器知道数据分布
VACUUM orders; -- 贴绿标,否则依然回表
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount, status FROM orders -- 要的列都在 INCLUDE 里
WHERE user_id = 42 -- 等值条件走索引
ORDER BY created_at DESC -- 排序由索引顺序提供
LIMIT 3; -- Heap Fetches 为 0 才算真覆盖
第三行是关键。刚批量导入、刚大批量更新过的表,绿标是残缺的,计划里写着 Index Only Scan,Heap Fetches 却可能等于返回行数——看着很美,一行都没省。
INCLUDE 是 PG 11 加的语法:括号里的列是键,参与查找和排序;INCLUDE 里的列只是捎带存着,不能用来 WHERE,也不能用来 ORDER BY,作用就一个——返回时不用回表。
还有个结构性差异:PG 的二级索引不带主键。上面那句 SELECT id,如果索引里没有 id,别的列覆盖得再好也得回表。所以我把 id 也写进了 INCLUDE。从 MySQL 迁过来的人,几乎都会在这里踩一次。
七、代价:索引是拿写入换读取(The Cost)
覆盖索引不是白给的,三笔账要算。
第一笔是空间。索引越宽,一页装得越少。按每页可用 16KB、每条索引项 100 字节粗算,一页放得下一百六十来条;把索引项撑到 300 字节,一页只剩五十来条。同样的数据要更多页,缓存里能容下的热数据也就更少。InnoDB 单列索引键上限是 3072 字节,VARCHAR(200) 的备注基本没资格进来。
第二笔是写入。每多一个索引,INSERT、UPDATE、DELETE 都要多维护一棵树。读多写少的报表库可以大方一点,订单、日志这种写密集的表得克制。
第三笔是冗余。(user_id, created_at) 是 (user_id, created_at, amount, status) 的最左前缀,后者已经完整包含前者,两个都留着只是让写入更慢。判断方法很土但有效:看短的那个是不是长的开头一段。
还有个容易漏的:主键太长会把所有二级索引一起拖胖。InnoDB 的二级索引叶子要带主键值,主键用 UUID 的话,每个索引都跟着变宽。这也是很多团队宁可要自增 BIGINT 的原因。
八、什么情况下先别急着加(When to Skip)
三种情况,碰上一种就该收手。
要返回的列特别多,别硬塞。一张宽表返回十几个字段,全塞进索引等于把表复制了一份,读是快了,写和磁盘都疼。这时候更该做的是少取几列,或者把大字段拆到另一张表。
过滤条件本身没选择性,覆盖也救不了。比如 status 只有三个值,每次要捞全表的三分之一,索引扫的页数已经接近全表,省下的那点回表抵不过多读的索引页。这类查询该考虑分区、预聚合,或者干脆换个存法。
读的模式还在变,先别急着覆盖。今天给 A 查询建的索引,明天可能就变成负担。等慢查询榜单稳定下来再动手,收益才算得清。
九、症状、原因、解法(Symptom → Cause → Fix)
| 症状 | 原因 | 解法 |
|---|---|---|
| 只扫几十行,却要几百毫秒 | 每行都在回表,随机读叠加 | 把返回列并进索引,再看 Extra 有没有 Using index |
索引建了,Extra 里却没有 Using index | 返回列里有索引外的字段,常见于 SELECT * | 收窄 SELECT,只取真需要的列 |
看到 Using index condition 就以为成了 | 把索引下推当成覆盖读 | 只认单独的 Using index |
覆盖了还是慢,Extra 带 Using filesort | 排序列和索引顺序不一致 | 调索引列顺序,让排序列跟在等值列后 |
PG 是 Index Only Scan,Heap Fetches 却很大 | 页没贴绿标 | 跑 VACUUM,再看 autovacuum 跟没跟上 |
| 加完索引写入变慢、磁盘涨得快 | 索引太宽,或和已有索引最左前缀重复 | 删冗余索引,长文本别进索引 |
一句话说清楚这件事:覆盖索引是拿一点额外存储和写入,换掉随机读的交易。划不划算看你读多还是写多,不取决于它听起来高不高级。
本专栏《SQL 数据库技术专栏》从基础查询、多表关联一路讲到索引、执行计划与数据库选型,每篇都配一段可直接执行的 SQL,每周更新一篇。想按顺序读完,可在专栏页订阅。