PostgreSQL 存储与索引系列（二）：索引的艺术——从 B-tree 到 BRIN 的全景指南

这是系列第二期，聚焦 PostgreSQL 的索引世界。我们会逐一拆解 B-tree、Hash、GiST、GIN、BRIN 等索引的内部机理与适用场景，并介绍部分索引、表达式索引等高级特性。结合上一期关于存储页、ctid、可见性映射的知识，你将对索引如何加速查询、何时失效、如何选型有系统的认知。

0. 索引基础：快速回顾

• 索引本质：一种有序的“目录”结构，通过少数列的值快速定位表中行的物理位置（ctid：页号+偏移）。
• 访问路径：索引扫描 → 获取 ctid → 回表（除非是仅索引扫描，需借助 Visibility Map）。
• 代价：额外的存储空间、写入时的维护开销（INSERT/UPDATE/DELETE需要同步更新索引）。
• 优化器决策：基于统计信息（pg_statistic）和成本参数，选择全表扫描或不同索引。

1. B-tree：全场景主力

B-tree 是 PostgreSQL 的默认索引类型，适用绝大多数等值、范围、排序、前缀匹配（如 LIKE 'abc%'）查询。

内部结构

• 采用 平衡树 结构，非叶子节点存储键值和下层节点的指针，叶子节点存储键值和对应的 ctid 列表（或堆指针）。
• 同一层的页面通过双向链表串联，支持高效的范围扫描（正向/反向）。
• 页面默认 8 KB，每个索引元组包含索引键和堆指针。B-tree 会尽量保证页面半满（通过 fillfactor 控制），为更新预留空间。

查询支持

• =、>、>=、<、<=、BETWEEN、IN（转为多个等值）。
• LIKE 或 ~~ 仅当模式常亮前缀时可用（如 col LIKE 'john%'），因为 B-tree 按二进制排序；对于 %john% 则无法使用。
• ORDER BY 可返回有序输出，避免显式排序（需索引排序方向匹配查询的 ASC/DESC）。

创建与维护

CREATE INDEX idx_user_name ON users(name);
CREATE INDEX idx_user_created ON users(created_at DESC);  -- 支持降序排序

• 支持唯一约束：CREATE UNIQUE INDEX ...。
• 多列索引：B-tree 支持最多 32 列，遵循最左前缀原则——查询条件必须覆盖索引的第一列才可能使用该索引。
• 调优：对于频繁更新的表，降低 fillfactor（如 70）以减少分裂；对于只读或静态表，提高 fillfactor（如 100）节省空间。

2. Hash：恰到好处的等值利器

Hash 索引只支持 = 等值查询，不适合范围或排序。在 PostgreSQL 10 之前 Hash 索引不记录 WAL，易损坏；10+ 版本已完整支持 crash-safe，性能有时优于 B-tree。

内部原理

• 对索引键计算哈希值（函数 hash_any），映射到哈希桶（bucket）。桶内存储该哈希值的所有行 ctid。
• 当桶溢出时，会分裂成两个桶，采用线性哈希算法，避免全局重哈希。
• 由于哈希值有冲突，同一个哈希值可能对应多个实际键值，检索时需回表验证真实值（以防哈希碰撞）。

适用场景

• 只做等值查询的大表，且索引列值分布差异大（B-tree 叶子层深度高时 Hash 可能更快）。
• 例如：用户ID表、状态码表。

注意事项

CREATE INDEX idx_session_hash ON sessions USING hash (session_id);

• Hash 索引不支持唯一约束。
• 不能用于 ORDER BY 或 LIKE。
• 同等条件下，B-tree 更通用，除非明确测试 Hash 胜出。

3. GiST：平衡树的多面手

GiST（Generalized Search Tree）是一种平衡树框架，允许实现多种非传统数据类型和查询操作符（如几何重叠、全文搜索、距离搜索）。PostgreSQL 内置了用于几何、范围、全文、数组等类型。

核心思想

• 节点存储“谓词”（如 bounding box），用以描述子树的覆盖范围。
• 搜索时剪枝：若查询条件与节点谓词不匹配，则跳过整个子树。
• 支持最近邻搜索（ORDER BY col <-> point），例如“附近的人”。

常用操作符类

• 几何类型（point、box、circle）：<<（左于）、>>（右于）、&&（重叠）、<->（距离）。
• 范围类型（int4range、tsrange）：&&、@>（包含）、<@（被包含）。
• 全文搜索（tsvector + tsquery）：实际上更推荐 GIN，但 GiST 变体 tsvector_ops 构建更快、更新开销更低，适合动态数据。

示例：地理坐标搜索

CREATE INDEX poi_gist ON points_of_interest USING gist (location);
SELECT name FROM points_of_interest
WHERE location <-> point '(x,y)' < 1000   -- 1000单位内
ORDER BY location <-> point '(x,y)';

扩展性

GiST 支持自定义操作符类，常用于实现空间数据库扩展（PostGIS 实际上是基于 GiST 和 SP-GiST）。对于开发专用索引很有价值。

4. GIN：倒排索引大师

GIN（Generalized Inverted Index）适合数据结构中包含多个元素值的类型（数组、JSON、全文搜索 tsvector）。它将每个元素值作为键，记录包含该键的 ctid 列表（称为 posting list）。

内部实现

• 每个键对应对应的行指针列表，通常存储在单独的发布树中（可以压缩成位图）。
• 查询时：对于 col @> ARRAY[值]（包含）、col @@ to_tsquery(...) 等，GIN 快速取交并集。
• 更新代价较高：因为一个行的多个键值需要更新多处 posting list。因此 GIN 有 fastupdate 特性：将变更暂存在待处理列表（pending list），达到阈值后批量合并。

典型场景

• 全文搜索：tsvector 列上建立 GIN 索引，支持 @@ 操作符。
• 数组：@>（包含）、&&（重叠）、<@（被包含）。
• JSONB：@>、?、?|、?& 等操作符。

-- 全文搜索
CREATE INDEX idx_posts_tsv ON posts USING gin (to_tsvector('english', title || ' ' || body));
SELECT * FROM posts WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'database & index');

-- 数组
CREATE INDEX idx_tags ON articles USING gin (tags);
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'performance'];

最佳实践

• 对于频繁更新的表，设置 gin_pending_list_limit 足够大（如 4MB），或定期执行 gin_clean_pending_list()。
• 考虑使用 btree_gin 模块让 B-tree 支持的类型也能进入 GIN，实现多列组合 GIN 索引（实际上不常见，因为有更好的办法）。

5. BRIN：巨大表的空间魔术

BRIN（Block Range Index）适用于天然与物理顺序相关的超大表，例如按时间插入的日志表、时序数据。BRIN 在每个“块范围”（默认 128 个页，约 1MB）内存储该范围的最小值、最大值和是否包含空值等摘要信息。

查询原理

查询时根据条件（如 timestamp > '2025-01-01'）检查每个块范围的摘要：如果范围最大值小于查询阈值，跳过整个范围；如果范围与查询重叠，则扫描该范围内的所有页。因此 BRIN 非常小（每个范围几十字节），但可能扫描一些多余数据（假阳性）。

创建与调整

CREATE INDEX idx_metric_time ON metrics USING brin (created_at);

• 参数：pages_per_range（默认 128），autosummarize（自动维护摘要）。
• 选择列：必须与表中物理存储顺序强相关（例如序列主键、时间戳列）。如果数据随机分布，BRIN 几乎无效。

优缺点

优点缺点索引极小（通常几 MB 处理 TB 级表）不能精确点查，可能扫描额外块创建和更新维护成本低需要数据与物理顺序一致，否则退化为全表扫描适合大量追加的场景（如 IoT 数据）不支持排序和唯一约束

维护

定期执行 VACUUM 会更新 BRIN 摘要；也可手动 ALTER INDEX ... SET (autosummarize = on) 让插入自动触发摘要生成。

6. 部分索引：为热点查询量身定制

部分索引只对表中满足某个条件子集的行建立索引，常用于只查询活动用户、未删除订单等场景。它极大减小索引大小和更新成本。

CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

查询如果能利用此条件（WHERE is_active = true AND email = ...），自动使用该索引。优化器会检查条件是否覆盖。

典型用途

• 只索引未完成订单：WHERE status <> 'completed'。
• 只索引最近7天数据（结合时间函数和 current_date，但注意静态条件无法动态更新——需要定期重建）。
• 结合唯一索引实现部分唯一约束，例如只保证未删除用户 email 唯一：CREATE UNIQUE INDEX unique_email_undeleted ON users(email) WHERE deleted_at IS NULL;

7. 表达式索引：基于函数或表达式的加速

有时候查询条件不是直接的列，而是列的函数（lower(email)、date(created_at)）。普通索引无法使用。表达式索引提前计算结果并排序。

CREATE INDEX idx_user_lower_email ON users(lower(email));
SELECT * FROM users WHERE lower(email) = 'admin@example.com';

也支持操作符表达式、算术表达式等。

注意事项

• 表达式结果必须稳定（immutable），不能使用 now() 或 timeofday()。
• 索引对写操作的维护成本增加，因为每行的表达式都要计算。
• 查询中使用的表达式必须与索引定义的完全一致（空格、类型等），否则不会使用。

8. 索引组合与多列索引

多列 B-tree 索引（最左前缀）

CREATE INDEX idx_orders ON orders(customer_id, created_at);

查询 WHERE customer_id = ? AND created_at > ? 会用索引；仅 WHERE created_at > ? 不会（除非 customer_id 也是条件）。多个独立索引可能被 BitmapAnd/BitmapOr 组合使用，但也可能不是最优。

何时多个单列索引优于一个多列索引？

• 查询组合多变，无法覆盖所有列顺序。
• 每个单列选择性都较高。
• 更新频繁，维护多列索引代价高。

PostgreSQL 的执行计划会动态选择最优方式。

9. 索引维护与调优宝典

常用查询

• 查看索引大小：SELECT pg_size_pretty(pg_relation_size('idx_name'));
• 查找未使用的索引：pg_stat_user_indexes 的 idx_scan 列（长期为零表示不必要）。
• 重建或重新索引：REINDEX INDEX idx_name; 或 REINDEX TABLE table_name; （不影响查询，但会锁表）。

何时使用不同索引

查询类型推荐索引等值、范围、排序B-tree仅等值，索引列值哈希分布均匀Hash (可选)空间相交、距离搜索GiST数组/JSON/全文包含查询GIN超大表，顺序存储的时间列BRIN仅查询部分行（热点子集）部分索引函数或表达式过滤表达式索引多条件任意组合查询考虑 Bitmap 扫描 + 多个单列索引

仅索引扫描（Index-Only Scan）与可见性映射

回顾第一期：当所有需要的列都在索引中，且可见性映射标记该页为 all-visible 时，PostgreSQL 可以完全不回表，速度极快。因此定期执行 VACUUM 维护 VM 对仅索引扫描至关重要。

小结与第三期预告

第二期我们走过了 PostgreSQL 索引家族的主要成员，理解了各自的工作原理和适用边界。在实际系统中，通常需要结合 EXPLAIN (ANALYZE, BUFFERS) 来验证索引是否生效，并持续监控膨胀和选择性。

第三期我们将进入查询优化实战，内容包括：

• 执行计划深度解读（Seq Scan、Index Scan、Bitmap Scan、Nested Loop、Hash Join、Merge Join）。
• 统计信息与 ANALYZE、pg_statistic、default_statistics_target 配置。
• 常见慢查询反模式：隐式类型转换、函数包裹列、OR 条件、分页偏移大等。
• 使用扩展工具（pg_stat_statements、auto_explain）定位瓶颈。

敬请继续关注！如果你有特别想深入了解的索引类型或优化案例，欢迎留言。

思考题：一张 10 亿行的日志表，按 log_time 查询最近一小时的数据，且需要按 user_id 分组。应该如何设计索引策略？为什么 BRIN 可能比 B-tree 更合适？