PostgreSQL / 系统课时

评估覆盖索引与 INCLUDE

评估覆盖索引与 INCLUDE

当查询只需要少量筛选列和返回列时,可以让索引同时保存返回所需的数据,从而获得索引覆盖能力。本课通过 INCLUDE 区分“搜索键”和“载荷列”,并用执行计划与索引大小判断收益是否值得付出额外成本。

学习目标

  • 能区分普通索引键与 INCLUDE 载荷列的职责。
  • 能创建适合常见查询的覆盖索引,并验证是否可能使用 Index Only Scan。
  • 能从索引宽度、更新成本和表页可见性判断覆盖索引是否值得。

先明确覆盖索引解决什么问题

考虑订单列表查询:条件只按 customer_id 过滤,但结果还需要 created_at 和 amount。普通索引可以快速找到对应行,却通常还要回表读取其他列;覆盖索引则尝试让查询需要的数据直接来自索引。

核心区别是:真正参与搜索和排序的列放在索引键中,而只是为了返回结果而携带的列放进 INCLUDE。后者不是搜索键,也不参与普通索引条件匹配。

用 INCLUDE 创建覆盖索引

下面建立一个最小可验证示例:

sql
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;

预期结果

text
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 列不是搜索键。例如:

sql
CREATE INDEX orders_customer_cover_idx2
    ON orders (customer_id)
    INCLUDE (created_at, amount);

EXPLAIN (COSTS OFF)
SELECT amount
FROM orders
WHERE amount > 100;

预期结果

text
Seq Scan on orders
  Filter: (amount > 100)

这里不能把 amount 当成索引搜索键使用,因为它只是 INCLUDE 载荷列。若业务真正需要按 amount 过滤,就应重新评估索引键设计,而不是简单继续扩大 INCLUDE。

覆盖效果还受表更新特征影响

覆盖索引最容易被误解的一点,是“查询列都在索引里,所以一定更快”。实际上,Index Only Scan 能否真正减少堆访问,还与 visibility map 的 all-visible 状态有关。更新频繁的表通常更难长期保持大量页面可直接跳过堆访问。

可以先用 EXPLAIN (ANALYZE, BUFFERS) 对真实查询做基线,再观察是否减少了明显的 heap fetch 或缓冲区访问:

sql
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT created_at, amount
FROM orders
WHERE customer_id = 1001;

预期结果

text
Index Only Scan using orders_customer_cover_idx on orders
  Index Cond: (customer_id = 1001)
  Heap Fetches: <VALUE>

其中 <VALUE> 会随表数据和可见性状态变化。不能把某个固定的 Heap Fetches 数字当成通用标准;真正需要观察的是查询是否稳定减少了堆访问,以及整体延迟是否下降。

用索引大小判断代价

设计完成后,不要只看执行计划,还应检查索引增长是否合理。可以使用 pg_relation_size 观察具体索引的物理大小:

sql
SELECT pg_size_pretty(pg_relation_size('orders_customer_cover_idx')) AS index_size;

预期结果

text
 index_size
------------
 <SIZE>

实际大小取决于行数、数据类型和版本等因素。覆盖索引应服务于明确的高频查询模式,而不是以“可能以后会用到”为理由持续增加载荷列。

最终的设计原则很简单:索引键负责找到数据,INCLUDE 负责让常见查询少回表。当节省的堆访问能够稳定抵消额外的索引体积和写入成本时,覆盖索引才真正有价值。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

评估覆盖索引与 INCLUDE

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通