用 PostgreSQL 覆盖索引 INCLUDE 减少订单查询回表

举报
yd_250998817 发表于 2026/11/19 13:31:23 2026/11/19
【摘要】 订单查询只取少量列时,普通索引仍需回表。本文用最小示例对比 PostgreSQL 普通索引与 INCLUDE 覆盖索引的 EXPLAIN ANALYZE,并说明 VACUUM 后 Heap Fetches 归零的 index-only scan 可见性条件。

用 PostgreSQL 覆盖索引 INCLUDE 减少订单查询回表

问题场景

订单表 orders 的列表查询通常只取少量列,例如按状态过滤后返回 customer_id 和 total_amount。普通 B-tree 索引能快速定位 status='paid' 的行,但这两个返回列不在索引里,执行器还要访问堆表取列。覆盖索引的思路是:把查询需要的列放进索引,让计划有机会走 Index Only Scan,减少回表。

本文用最小示例对比普通索引与 INCLUDE 索引的 EXPLAIN ANALYZE 输出,并执行 VACUUM 观察 Heap Fetches 归零,最后讲清 index-only scan 的可见性条件。PostgreSQL 11 起 B-tree 支持 INCLUDE 列;示例使用占位连接信息,执行前请替换。

建表与数据

连接测试库,将下面 SQL 按顺序在 psql 中执行:

-- psql -h <host> -U <user> -d <db>

建订单表并插入 5 万行,paid 约占 5%:

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  customer_id bigint NOT NULL,
  status text NOT NULL,
  total_amount numeric(12,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO orders (customer_id, status, total_amount)
SELECT g,
       CASE WHEN g % 20 = 0 THEN 'paid' ELSE 'other' END,
       (g % 1000)::numeric / 10
FROM generate_series(1, 50000) AS g;

ANALYZE orders;

为了让示例计划稳定使用索引,演示中临时关闭顺序扫描:

SET enable_seqscan = off;

生产环境不要长期关闭顺序扫描,这里只是减少小数据量下的计划波动。

普通索引:能定位,仍要回表

CREATE INDEX idx_orders_status ON orders(status);

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, total_amount
FROM orders
WHERE status = 'paid';

关键计划行类似:

Index Scan using idx_orders_status on orders ...
  Buffers: shared hit=...

普通索引只包含 status 键列。执行器通过索引找到候选行后,还要访问堆表读取 customer_id 和 total_amount。EXPLAIN 中看到 Index Scan 而不是 Index Only Scan,说明存在回表取列。

INCLUDE 覆盖索引与 VACUUM

为了稳定对比,先删除普通索引,再创建带 INCLUDE 的索引:

DROP INDEX idx_orders_status;

CREATE INDEX idx_orders_status_cover
  ON orders(status) INCLUDE (customer_id, total_amount);

创建后先不 VACUUM,直接查看计划:

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, total_amount
FROM orders
WHERE status = 'paid';

可能看到:

Index Only Scan using idx_orders_status_cover on orders ...
  Heap Fetches: 2500

节点已经是 Index Only Scan,但 Heap Fetches 非零。原因是新插入的行所在页还没有被标记为全可见,执行器仍需回表检查这些元组对当前快照是否可见。Heap Fetches 记录的就是这种回表检查次数。

执行 VACUUM 后再次查看:

VACUUM orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, total_amount
FROM orders
WHERE status = 'paid';

关键计划行变为:

Index Only Scan using idx_orders_status_cover on orders ...
  Heap Fetches: 0

此时索引覆盖了查询列,并且访问的堆页在 visibility map 中已标记为 all-visible,执行器可以直接从索引返回数据。注意 VACUUM 需要在事务块外执行。

index-only scan 的可见性条件

Heap Fetches 归零不是创建 INCLUDE 索引后自动发生的,它需要同时满足几个条件:

  1. 列覆盖。查询需要从堆中取的列必须都在索引中。对于本文查询,status 是索引键,customer_id 和 total_amount 在 INCLUDE 列中。若查询使用 SELECT *、表达式或未覆盖列,就不能走 index-only scan。INCLUDE 列不参与 B-tree 键排序和唯一性判断,也不能像键列那样用于缩小索引扫描范围;它主要用于覆盖输出列。
  2. 索引访问方法支持。B-tree 的 INCLUDE 是常见覆盖方式,但并非所有索引类型都以相同方式支持覆盖扫描。
  3. 可见性映射。VACUUM 或 autovacuum 会把全可见页记录到 visibility map。Index Only Scan 对候选元组先检查页是否 all-visible;是则直接返回索引值,否则回表检查 MVCC 可见性,并计入 Heap Fetches。
  4. MVCC 快照。即使索引里有列,如果元组对当前快照不可见,也不能直接返回。可见性判断不能跳过。
  5. 计划选择。优化器会比较顺序扫描、普通索引扫描和覆盖索引扫描的成本。小表、选择率高或统计信息不准时,可能不会选 Index Only Scan。前文的 enable_seqscan=off 只是演示手段。

边界与限制

INCLUDE 覆盖索引不是越多越好。它会增加索引体积和写放大,订单表写入频繁时要评估维护成本。它只覆盖固定查询,查询列变化后可能失效;应围绕高频只读查询设计。INCLUDE 列顺序不影响索引键顺序,键列顺序仍要匹配 WHERE 和排序需求。大量更新会让页失去 all-visible 状态,Heap Fetches 可能回升,需要合理配置 autovacuum 并观察表膨胀。不要为了一个查询给宽表所有列建覆盖索引。

小结

普通索引解决“定位行”,INCLUDE 覆盖索引进一步解决“取列回表”。在订单查询这类固定列、读多写少的场景,可以用 CREATE INDEX ... INCLUDE (...) 让计划走 Index Only Scan;再通过 VACUUM 让 visibility map 标记全可见页,观察 Heap Fetches 归零。判断是否生效,重点看 EXPLAIN 的节点类型和 Heap Fetches,而不是只看“用到了索引”。

【版权声明】本文为华为云社区用户原创内容,未经允许不得转载,如需转载请自行联系原作者进行授权。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0)

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。