开发者日记

外观

订单列表回表 200ms 降到 15ms：PG 覆盖索引 INCLUDE 实战 ​

问题背景：订单列表慢，不一定是 SQL 写法问题 ​

订单列表是典型的高频分页查询：按用户过滤，按下单时间倒序，再返回订单号、金额、状态等少量字段。很多团队第一反应是给 user_id, order_time 建复合索引，但执行计划仍然可能出现大量 回表：索引先找到满足条件的记录位置，再反复访问堆表读取 status、amount 等展示字段。

在 PostgreSQL 中，普通 B-Tree 索引保存索引键以及对应的堆元组定位信息。如果查询所需列没有全部出现在索引中，即使过滤和排序已经命中索引，也必须回到表中取剩余列。订单列表一次读取几十上百行时，随机 I/O 被放大，200ms 的耗时往往就耗在这些回表上。

本文使用一个可直接运行的订单表示例，演示如何用 覆盖索引 的 INCLUDE 语法把查询稳定优化到 Index Only Scan，将接口核心查询从约 200ms 降到约 15ms。

方案对比：加宽复合索引，还是使用 INCLUDE ​

先看三种常见方案的差异。

普通单列索引过滤后大量回表宽复合索引列都参与索引键INCLUDE 覆盖索引键列过滤，附带返回列Index Only Scan少回表，随机 I/O 显著下降
  • 普通索引：只解决过滤或排序问题，查询选择列表中的其他字段仍需回表。
  • 宽复合索引：把所有列都放到索引键中，虽然也可能覆盖查询，但会增加 B-Tree 比较成本，影响索引体积和写入性能。
  • INCLUDE 覆盖索引：把用于过滤、排序、连接的列放在键列中，把只用于返回的列放在 INCLUDE 非键列中。非键列不参与索引排序决策，语义更清晰，也更适合列表页这种“少数条件列 + 固定展示列”的场景。

需要强调，覆盖索引不是消除索引访问，而是消除对堆表的二次访问。执行计划出现 Index Only Scan，说明优化器认为索引已经能提供查询所需的全部列。

实操步骤：构造订单表并复现回表问题 ​

下面的 SQL 可直接在 PostgreSQL 12 及以上版本运行。示例使用 generate_series 构造 100 万行订单数据。

sql
-- 1. 创建订单表
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    id           bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id      bigint NOT NULL,
    order_no     varchar(32) NOT NULL,
    status       smallint NOT NULL,
    amount       numeric(12,2) NOT NULL,
    order_time   timestamptz NOT NULL,
    created_at   timestamptz NOT NULL DEFAULT now()
);

-- 2. 写入 100 万行模拟订单：10000 个用户，每人 100 单
INSERT INTO orders (user_id, order_no, status, amount, order_time)
SELECT
    (g % 10000) + 1 AS user_id,
    'NO' || lpad(g::text, 12, '0') AS order_no,
    ((g % 5) + 1)::smallint AS status,
    round((random() * 900 + 100)::numeric, 2) AS amount,
    now() - ((g % 365) || ' days')::interval - ((g % 86400) || ' seconds')::interval AS order_time
FROM generate_series(1, 1000000) AS g;

-- 3. 收集统计信息
ANALYZE orders;

订单列表接口的典型 SQL 如下：

sql
SELECT id, order_no, status, amount, order_time
FROM orders
WHERE user_id = 888
ORDER BY order_time DESC
LIMIT 20;

先创建一个只服务于过滤和排序的普通复合索引：

sql
CREATE INDEX idx_orders_user_time
ON orders (user_id, order_time DESC);

查看执行计划：

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, order_no, status, amount, order_time
FROM orders
WHERE user_id = 888
ORDER BY order_time DESC
LIMIT 20;

典型计划如下：

text
Limit
  -> Index Scan using idx_orders_user_time on orders
        Index Cond: (user_id = 888)

这里虽然利用索引避免了排序，但因为 order_no、status、amount 不在索引中，所以是 Index Scan，不是 Index Only Scan。每返回一行都要根据堆元组地址回表读取数据。生产环境中如果缓存命中率不足，几十行随机堆访问就可能把响应时间推高到百毫秒级。

创建 INCLUDE 覆盖索引，让列表查询命中 Index Only Scan ​

将只用于展示、不参与过滤排序的列放入 INCLUDE：

sql
-- 覆盖订单列表：user_id 过滤，order_time 排序，其余字段随索引返回
CREATE INDEX idx_orders_user_list_covering
ON orders (user_id, order_time DESC)
INCLUDE (order_no, status, amount);

再次查看执行计划：

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, order_no, status, amount, order_time
FROM orders
WHERE user_id = 888
ORDER BY order_time DESC
LIMIT 20;

理想计划如下：

text
Limit
  -> Index Only Scan using idx_orders_user_list_covering on orders
        Index Cond: (user_id = 888)
        Heap Fetches: 0

为什么 id 没有放进 INCLUDE 也能被覆盖？因为在 PostgreSQL 的普通 B-Tree 索引中，堆元组的标识信息会随索引项保存。对于本例这种默认规划器场景，主键列 id 可由索引条目的堆定位信息推导，因此无需重复保存。但不要把这个特性泛化到所有查询或所有索引类型，最可靠的判断方式仍然是 EXPLAIN。

如果旧索引已经没有价值，可以删除：

sql
DROP INDEX idx_orders_user_time;

也可以先保留两个索引，在生产低峰期通过 EXPLAIN (ANALYZE, BUFFERS) 对比真实执行计划，确认新索引稳定命中后再清理旧索引。

进一步结合部分索引，只覆盖活跃订单 ​

如果订单列表默认只展示未删除、未关闭的订单，可以使用 部分索引 缩小索引体积。注意：查询条件必须与部分索引谓词保持足够一致，优化器才会选择它。

sql
-- 只覆盖状态为 1、2 的活跃订单列表
CREATE INDEX idx_orders_active_user_list
ON orders (user_id, order_time DESC)
INCLUDE (order_no, status, amount)
WHERE status IN (1, 2);

-- 查询必须显式带上相同谓词
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, order_no, status, amount, order_time
FROM orders
WHERE user_id = 888
  AND status IN (1, 2)
ORDER BY order_time DESC
LIMIT 20;

部分索引适合固定业务条件，例如未删除、租户有效、待支付订单等。但如果页面需要展示全部状态，就不能只建带 WHERE 的部分索引，否则“全部订单”查询无法使用它。

如何验证优化确实生效 ​

不要只看接口平均耗时，建议按以下顺序验证：

  1. EXPLAIN (ANALYZE, BUFFERS) 中节点应为 Index Only Scan。
  2. 关注 Heap Fetches，稳态下应接近 0；数值很高说明仍在访问堆表。
  3. 对比 Buffers: shared hit/read，优化后堆块读取应明显减少。
  4. 压测时区分热缓存与冷缓存，避免因为所有数据都在 shared buffers 中而误判收益。
  5. 使用生产相近的数据分布测试，不要只查只有少量订单的用户。

覆盖索引的收益来自减少随机堆访问。因此，在数据量越大、回表越分散、缓存命中率越低的场景，收益越明显。示例中从 200ms 降到 15ms，并不是 INCLUDE 有魔法，而是扫描路径从“索引定位 + 大量回表”变成了“只扫描索引叶子页”。

避坑总结：覆盖索引最容易踩的 4 个坑 ​

坑 1：建了 INCLUDE 索引，但查询仍然回表 ​

常见现象是执行计划仍显示 Index Scan 或 Bitmap Heap Scan。重点检查 SELECT 列表是否包含未覆盖列，例如 created_at、更新时间、版本号，或者 ORM 自动追加的审计字段。

修正方法是精简查询字段，只查列表页真正需要的列；如果字段确实必须返回，就把它加入 INCLUDE。不要无脑中大型字段，索引膨胀会反过来拖慢写入和范围扫描。

坑 2：把过滤列误放进 INCLUDE，导致索引无法用于条件过滤 ​

错误写法类似：

sql
-- 错误示例：status 只用于非键附带列，不能高效参与索引过滤
CREATE INDEX idx_orders_bad_include
ON orders (user_id, order_time DESC)
INCLUDE (status);

如果查询经常写 WHERE user_id = ? AND status = 1，而索引键中没有 status，数据库无法像使用键列一样沿 B-Tree 直接定位该状态。修正原则是：参与 WHERE、JOIN、ORDER BY 的列优先考虑键列；只出现在 SELECT 中、不参与查找和排序的列才放 INCLUDE。

坑 3：看到 Index Only Scan 却仍然很慢 ​

Index Only Scan 不等于完全不读表。PostgreSQL 必须确认元组对当前事务可见；当表刚被大量更新、autovacuum 没来得及生成可见性映射时，会出现大量 Heap Fetches。

常见错误现象是刚批量导入或更新后执行计划正确但耗时偏高。修正方法是对大事务后的表执行 VACUUM orders;，并确保 autovacuum 正常运行。日常不要长期依赖手动 VACUUM FULL，它会重写表并持有强锁，应在明确维护窗口中使用。

坑 4：索引越建越宽，写入延迟和存储成本上升 ​

覆盖索引本质上复制了一份列数据。订单表每次更新 status、amount 等被包含列时，相关索引也可能需要维护。如果为每个接口都建一个宽索引，最终会造成表和索引膨胀，INSERT、UPDATE 延迟升高。

修正方法是合并相似查询的索引，按访问模式设计 1 到几个核心索引；对固定条件使用部分索引减少体积；定期用 pg_stat_user_indexes 观察索引使用率，用 pg_relation_size 监控索引大小，长期未使用的索引要评估下线。

覆盖索引特别适合“条件简单、排序固定、返回字段少、访问频率高”的查询。订单列表、用户消息列表、交易流水摘要页都属于高收益场景。真正上线前，请用真实数据量执行 EXPLAIN (ANALYZE, BUFFERS)，确认扫描节点、堆访问次数和缓存读取得益，再决定索引是否保留。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。