覆盖索引是什么:用借书和外卖看懂回表这件事

0 阅读11分钟

先说结论:覆盖索引(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,每周更新一篇。想按顺序读完,可在专栏页订阅。