PostgreSQL / 系统课时
评估覆盖索引与 INCLUDE
评估覆盖索引与 INCLUDE
当查询只需要少量筛选列和返回列时,可以让索引同时保存返回所需的数据,从而获得索引覆盖能力。本课通过 INCLUDE 区分“搜索键”和“载荷列”,并用执行计划与索引大小判断收益是否值得付出额外成本。
学习目标
- 能区分普通索引键与
INCLUDE载荷列的职责。 - 能创建适合常见查询的覆盖索引,并验证是否可能使用 Index Only Scan。
- 能从索引宽度、更新成本和表页可见性判断覆盖索引是否值得。
先明确覆盖索引解决什么问题
考虑订单列表查询:条件只按 customer_id 过滤,但结果还需要 created_at 和 amount。普通索引可以快速找到对应行,却通常还要回表读取其他列;覆盖索引则尝试让查询需要的数据直接来自索引。
核心区别是:真正参与搜索和排序的列放在索引键中,而只是为了返回结果而携带的列放进 INCLUDE。后者不是搜索键,也不参与普通索引条件匹配。
用 INCLUDE 创建覆盖索引
下面建立一个最小可验证示例:
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL,
created_at timestamptz NOT NULL,
amount numeric(12,2) NOT NULL
);
CREATE INDEX orders_customer_cover_idx
ON orders (customer_id)
INCLUDE (created_at, amount);
INSERT INTO orders (customer_id, created_at, amount)
VALUES
(1001, '2026-09-01 10:00:00+00', 19.90),
(1001, '2026-09-02 10:00:00+00', 49.00),
(2001, '2026-09-02 11:00:00+00', 88.00);
EXPLAIN (COSTS OFF)
SELECT created_at, amount
FROM orders
WHERE customer_id = 1001;预期结果
Index Only Scan using orders_customer_cover_idx on orders
Index Cond: (customer_id = 1001)执行计划是否最终选择 Index Only Scan 仍取决于表状态和优化器成本判断。尤其要注意,Index Only Scan 并不意味着永远不访问堆表:如果相关数据页的 visibility map 没有标记为 all-visible,PostgreSQL 仍可能访问堆表确认行可见性。
为什么 INCLUDE 不等于“把所有列都塞进索引”
INCLUDE 的价值建立在一个明确前提上:查询经常需要这些返回列,而且从索引直接拿到它们能够减少堆访问。把大量宽字段无差别放进去,会增加索引体积、写入维护成本和缓存压力。
因此应优先加入真正出现在高频查询结果中的窄列。对于 text、jsonb 等可能很宽的列,更要谨慎评估,因为索引元组过大可能直接带来插入或更新问题。
另外,INCLUDE 列不是搜索键。例如:
CREATE INDEX orders_customer_cover_idx2
ON orders (customer_id)
INCLUDE (created_at, amount);
EXPLAIN (COSTS OFF)
SELECT amount
FROM orders
WHERE amount > 100;预期结果
Seq Scan on orders
Filter: (amount > 100)这里不能把 amount 当成索引搜索键使用,因为它只是 INCLUDE 载荷列。若业务真正需要按 amount 过滤,就应重新评估索引键设计,而不是简单继续扩大 INCLUDE。
覆盖效果还受表更新特征影响
覆盖索引最容易被误解的一点,是“查询列都在索引里,所以一定更快”。实际上,Index Only Scan 能否真正减少堆访问,还与 visibility map 的 all-visible 状态有关。更新频繁的表通常更难长期保持大量页面可直接跳过堆访问。
可以先用 EXPLAIN (ANALYZE, BUFFERS) 对真实查询做基线,再观察是否减少了明显的 heap fetch 或缓冲区访问:
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT created_at, amount
FROM orders
WHERE customer_id = 1001;预期结果
Index Only Scan using orders_customer_cover_idx on orders
Index Cond: (customer_id = 1001)
Heap Fetches: <VALUE>其中 <VALUE> 会随表数据和可见性状态变化。不能把某个固定的 Heap Fetches 数字当成通用标准;真正需要观察的是查询是否稳定减少了堆访问,以及整体延迟是否下降。
用索引大小判断代价
设计完成后,不要只看执行计划,还应检查索引增长是否合理。可以使用 pg_relation_size 观察具体索引的物理大小:
SELECT pg_size_pretty(pg_relation_size('orders_customer_cover_idx')) AS index_size;预期结果
index_size
------------
<SIZE>实际大小取决于行数、数据类型和版本等因素。覆盖索引应服务于明确的高频查询模式,而不是以“可能以后会用到”为理由持续增加载荷列。
最终的设计原则很简单:索引键负责找到数据,INCLUDE 负责让常见查询少回表。当节省的堆访问能够稳定抵消额外的索引体积和写入成本时,覆盖索引才真正有价值。