PostgreSQL 覆盖索引

光蛋
6 阅读7分钟

覆盖索引

PostgreSQL 中的所有索引均为二级索引，其存储与表的主数据区（PostgreSQL 术语称为表的堆）相互独立。这意味着在普通索引扫描中，每行数据的检索都需要同时从索引和堆中读取数据。

此外，满足 WHERE 条件的索引条目通常会集中存储，但它们所引用的表行在堆中可能分布在任意位置。因此，普通索引扫描的堆访问阶段会涉及大量的随机访问，在传统机械硬盘等存储介质上，该操作的执行效率极低。尽管位图扫描（见第 11.5 节）会通过排序堆访问顺序来降低开销，但优化效果仍然有限。

一、只用索引的扫描

为解决普通索引扫描的性能瓶颈，PostgreSQL 支持只用索引的扫描，这类扫描无需访问堆数据，仅通过索引即可完成查询。其核心原理是直接从索引项中提取查询所需的值，而非通过索引项去关联堆中的数据行。

1.1 核心限制条件

要使用只用索引的扫描，必须同时满足以下两个根本条件：

  1. 索引类型支持

  1. 索引的物理结构必须能够存储或重构出索引项对应的原始数据值。

    • 支持的索引类型：B-Tree、部分 GiST、部分 SP-GiST（具体取决于操作符类）。
    • 不支持的索引类型：GIN 索引（索引项通常仅存储原始数据的部分信息）。
  1. 查询列全覆盖
  1. 查询语句中引用的所有列，必须全部包含在该索引中。

示例

假设表 tab 包含列 x、y、z，且存在索引 idx_tab_x_y (x, y)。

  • 可使用只用索引扫描的查询：

sql -- 查询列 x、y 均在索引中 SELECT x, y FROM tab WHERE x = 'key'; -- 查询列 x 在索引中，过滤条件列 y 也在索引中 SELECT x FROM tab WHERE x = 'key' AND y < 42;

  • 不可使用只用索引扫描的查询：

sql -- 查询列 z 不在索引中，需访问堆 SELECT x, z FROM tab WHERE x = 'key'; -- 过滤条件列 z 不在索引中，需访问堆 SELECT x FROM tab WHERE x = 'key' AND z < 42;

1.2 MVCC 可见性验证机制

PostgreSQL 中所有表扫描都需验证行数据对当前事务的 MVCC 快照是否可见，而可见性信息仅存储在堆数据中，索引项不包含该信息。这一特性看似与只用索引扫描的无堆访问原则矛盾，其解决方案如下：

  1. 可见性映射（Visibility Map）
  1. PostgreSQL 为表堆的每个页面维护一个可见性映射位，用于标记该页面内的所有行数据是否对所有当前及未来事务可见。

  1. 扫描流程中的可见性判断

    1. 当通过索引找到候选行时，只用索引扫描会先检查对应堆页面的可见性映射位。
    2. 若映射位被设置：该行数据可见，直接从索引返回结果，无需访问堆。
    3. 若映射位未被设置：必须访问堆数据以判断行可见性，此时与普通索引扫描性能无异。

  2. 性能优势来源

  1. 可见性映射的体积远小于表堆（约为堆体积的 1/10000），且通常常驻内存缓冲区，因此访问可见性映射的物理 I/O 开销极低。

1.3 适用场景

只有当表堆中大部分页面的可见性映射位被设置时，只用索引扫描才能体现出显著的性能优势。该场景常见于很少被修改的静态数据表，此类表的可见性映射位一旦被设置，便不会频繁变更。

二、覆盖索引

为充分发挥只用索引扫描的性能优势，可针对性创建覆盖索引—— 一种包含查询所需全部列的特殊索引。

PostgreSQL 允许在索引中添加非搜索键的负载列，这些列仅用于存储数据，不参与索引的搜索逻辑，实现方式为 INCLUDE 子句。

2.1 基本语法与示例

语法

CREATE [UNIQUE] INDEX 索引名 ON 表名(搜索键列) INCLUDE (负载列);

示例

假设有频繁执行的查询：

SELECT y FROM tab WHERE x = 'key';

  • 传统方案：创建索引 idx_tab_x (x)，但查询需访问堆获取 y 的值。

  • 优化方案：创建覆盖索引

    • CREATE INDEX idx_tab_x_include_y ON tab(x) INCLUDE (y);
    •   该索引包含查询所需的 x（搜索键）和 y（负载列），可直接通过只用索引扫描完成查询，无需访问堆。

2.2 关键特性

  1. 负载列的限制

    1. 负载列无需满足索引的操作符类要求，仅需支持存储。
    2. 负载列不参与唯一性约束：若创建唯一覆盖索引 CREATE UNIQUE INDEX ... (x) INCLUDE (y)，唯一性仅针对 x 列，与 y 列无关。
    3. 不支持表达式作为负载列。
    4. 仅 B-Tree、GiST、SP-GiST 索引支持 INCLUDE 子句。

  2. 使用建议

    1. 谨慎添加宽负载列：若索引元组超过索引类型允许的最大尺寸，会导致数据插入失败；同时，负载列会增加索引体积，可能降低搜索效率。
    2. 仅在需要启用只用索引扫描时添加负载列：若查询仍需访问堆数据，从堆中读取列值的开销更低，无需在索引中冗余存储。

2.3 传统覆盖索引实现方式

在 PostgreSQL 引入 INCLUDE 子句之前，用户通常通过将负载列作为普通索引列的方式创建覆盖索引，语法如下：

CREATE INDEX idx_tab_x_y ON tab(x, y);
  • 适用条件：负载列需作为索引的尾列，避免影响索引的搜索效率（原因见第 11.3 节）。
  • 局限性：无法在搜索键列上实施唯一性约束（若设置唯一索引，约束会作用于 x 和 y 的组合，而非仅 x 列）。

2.4 索引后缀截断（B-Tree 特有）

B-Tree 索引的上层节点会自动截断非键列（负载列），仅在叶子节点存储完整的索引数据：

  • 负载列从不参与索引的上层搜索逻辑，仅用于叶子节点的数据读取。
  • 若搜索键列的前缀已能唯一标识叶子节点的元组，B-Tree 会自动截断尾部的搜索键列。
  • 使用 INCLUDE 子句定义的负载列，可确保索引上层节点的体积更小，提升索引的整体性能。

三、特殊场景下的只用索引扫描

3.1 表达式索引

对于表达式索引，理论上可以通过只用索引扫描优化查询，但 PostgreSQL 查询优化器的支持存在局限性。

示例

假设存在表达式索引 idx_tab_fx (f(x))（x 为表列，f() 为计算开销较高的函数），查询语句：

SELECT f(x) FROM tab WHERE f(x) < 1;
  • 理想情况：可通过只用索引扫描直接返回 f(x) 的值，避免重复计算。
  • 实际问题：优化器无法识别“查询列可由表达式索引直接提供”，会因认为缺少 x 列而拒绝使用只用索引扫描。
  • 解决方案：创建索引 idx_tab_fx_x (f(x), x)，冗余添加 x 列以“说服”优化器启用只用索引扫描（即使 x 列实际未被使用）。

注意事项：优化器仅在简单查询中能匹配表达式索引，涉及多表连接的复杂查询可能无法生效。

3.2 部分索引

部分索引（仅包含满足特定条件的行）与只用索引扫描的结合存在特殊逻辑。

示例

假设有部分索引：

CREATE UNIQUE INDEX idx_tests_success ON tests (subject, target) WHERE success;

针对查询：

SELECT target FROM tests WHERE subject = 'some-subject' AND success;
  • 关键逻辑：索引的过滤条件 success = true 已保证索引项均满足查询的 WHERE 条件，因此无需在运行时重新检查 success 列，即便该列不在索引中。
  • 版本支持：PostgreSQL 9.6 及以上版本可识别该场景并启用只用索引扫描，旧版本不支持。
avatar
文章
阅读
粉丝