用 PostgreSQL 覆盖索引 INCLUDE 减少订单查询回表
用 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 索引后自动发生的,它需要同时满足几个条件:
- 列覆盖。查询需要从堆中取的列必须都在索引中。对于本文查询,status 是索引键,customer_id 和 total_amount 在 INCLUDE 列中。若查询使用 SELECT *、表达式或未覆盖列,就不能走 index-only scan。INCLUDE 列不参与 B-tree 键排序和唯一性判断,也不能像键列那样用于缩小索引扫描范围;它主要用于覆盖输出列。
- 索引访问方法支持。B-tree 的 INCLUDE 是常见覆盖方式,但并非所有索引类型都以相同方式支持覆盖扫描。
- 可见性映射。VACUUM 或 autovacuum 会把全可见页记录到 visibility map。Index Only Scan 对候选元组先检查页是否 all-visible;是则直接返回索引值,否则回表检查 MVCC 可见性,并计入 Heap Fetches。
- MVCC 快照。即使索引里有列,如果元组对当前快照不可见,也不能直接返回。可见性判断不能跳过。
- 计划选择。优化器会比较顺序扫描、普通索引扫描和覆盖索引扫描的成本。小表、选择率高或统计信息不准时,可能不会选 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,而不是只看“用到了索引”。
- 点赞
- 收藏
- 关注作者
评论(0)