本文沿用第 1 篇的订单系统案例，只讨论如何把查询模式转换成可验证的索引设计。

索引必须服务具体查询

索引设计应从查询的过滤、排序、连接和返回列出发，而不是看到某个字段常用就单独建索引。以“按用户倒序查询最近订单”为例，可以建立联合索引：

CREATE INDEX idx_orders_user_created_id
ON orders (user_id, created_at DESC, id DESC)
INCLUDE (status, total_amount);

对应查询为：

SELECT
  id,
  status,
  total_amount,
  created_at
FROM orders
WHERE user_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 50;

user_id 负责等值定位用户范围，created_at DESC, id DESC 与排序一致，其中 id 是唯一且稳定的平局处理键；只需返回的 status 和 total_amount 放入 INCLUDE。索引只能为已写对的查询提供访问路径，参数转换、N+1 与深分页等 SQL 问题留到第 5 篇处理。

联合索引顺序

对 B-tree 来说，前导列的等值条件通常最有利；第一个没有等值约束的列可以通过范围条件限制扫描区域。右侧列的条件仍可在索引内检查，但未必能缩小需要扫描的索引范围。

因此，列顺序不能只套用“等值在前、范围在后”的口诀。还要核对实际谓词、排序方向、字段基数以及同一索引要服务的查询集合。若核心查询长期按 user_id 等值过滤并按时间倒序返回，把高基数用户列放在前面通常比为偶发查询改变顺序更合理。

INCLUDE 与 Index Only Scan

INCLUDE 把仅用于返回的列作为非键列存入索引，可能让上面的查询使用 Index Only Scan，但它不保证完全不访问表。PostgreSQL 仍要借助可见性映射确认数据页上的元组对当前快照可见；更新频繁、Vacuum 尚未处理的数据页可能出现大量 Heap Fetches。

被包含列也会扩大索引，降低缓存密度并增加写入维护成本。只有返回列稳定、查询足够高频，且执行计划确实减少了回表时，覆盖索引的空间交换才有价值。

部分索引与表达式索引

如果系统频繁扫描待支付订单，而绝大部分历史订单已经完成，可以只索引真正关心的子集：

CREATE INDEX idx_orders_pending_created
ON orders (created_at)
WHERE status = 'pending';

查询条件必须与索引谓词匹配：

SELECT id, user_id, created_at
FROM orders
WHERE status = 'pending'
  AND created_at < now() - interval '30 minutes'
ORDER BY created_at
LIMIT 500;

部分索引更小，完成订单也不需要维护它。但 PostgreSQL 必须在规划阶段证明查询条件蕴含索引谓词；WHERE status = $1 无法保证参数永远是 pending，可能不能使用该索引。设计前要检查应用实际发送的预备语句。

如果登录查询始终忽略邮箱大小写，可以建立表达式唯一索引：

CREATE UNIQUE INDEX idx_users_lower_email
ON users (lower(email));

查询必须使用与索引表达式匹配的形式：

SELECT id, email, display_name
FROM users
WHERE lower(email) = lower($1);

表达式索引把计算成本转移到写入阶段，并增加索引维护。如果规范化涉及区域、排序规则或业务语义，应先确认规则能够长期保持稳定。

索引类型由操作符与数据分布决定先写清查询谓词、排序与数据分布，再选择索引类型；索引名称本身不是性能保证。
B-tree 适合等值、范围与排序；GIN 适合多值和包含关系；GiST 适合范围、空间与近邻操作；BRIN 适合超大且值与物理位置高度相关的数据。

B-tree、GIN、GiST 与 BRIN

索引类型常见场景主要边界
B-tree等值、范围、排序、唯一约束多列顺序和选择性很重要
GINJSONB、数组、全文检索中的成员或词项写入和更新成本较高，索引可能很大
GiST范围、几何、距离和可扩展操作符类行为取决于具体 operator class
BRIN超大、追加写且与物理顺序相关的表返回候选块范围，需要二次检查

索引类型必须和查询操作符及数据分布匹配。看到 JSONB 就创建 GIN，或看到时间列就创建 BRIN，都缺少查询证据。BRIN 不为每行保存完整索引项，而是记录物理块范围的摘要，适合按时间持续追加、物理顺序与 created_at 高度相关的超大历史表：

CREATE INDEX idx_orders_created_brin
ON orders USING brin (created_at);

BRIN 会定位可能包含目标值的数据块再检查，不适合高选择性的单行查询，也不适合物理顺序与索引列无关的表。删除、更新和乱序导入会降低相关性，应通过实际块读取量验证效果。

PostgreSQL 18 的 B-tree skip scan

PostgreSQL 18 可以在合适的多列 B-tree 上使用 skip scan。即使查询缺少前导列的等值条件，优化器也可能为该列的不同值生成重复查找，从而跳过无关的索引叶子页。

例如索引 (status, created_at) 的第一列只有少量状态值，而后续时间条件选择性较高时，skip scan 可能比扫描整个索引更便宜。如果前导列是拥有十万种取值的 user_id，重复查找通常没有意义，规划器更可能选择其他路径。

skip scan 是否出现由成本估算决定。它扩大了多列索引的可用场景，却没有消除字段顺序、基数和查询形态之间的权衡。上线前仍要用真实数据执行 EXPLAIN (ANALYZE, BUFFERS)，并观察计划中的索引搜索次数。

生产环境并发创建索引

普通 CREATE INDEX 会阻止并发写入目标表。生产大表通常评估并发创建：

CREATE INDEX CONCURRENTLY idx_payments_order_id
ON payments (order_id);

CONCURRENTLY 允许正常写入继续，但需要更多工作、通常耗时更长，而且不能放在事务块中执行。失败后可能留下 INVALID 索引；它不会用于查询，却仍可能产生更新维护成本，需要检查后删除或重建：

SELECT
  c.relname AS index_name,
  i.indisvalid,
  i.indisready
FROM pg_index AS i
JOIN pg_class AS c ON c.oid = i.indexrelid
WHERE i.indrelid = 'payments'::regclass;

索引创建还可能等待旧事务结束。上线方案应包含执行窗口、锁等待监控、取消条件和失败清理，而不是只把 SQL 交给迁移工具。

删除索引前评估读写价值

可以从统计视图观察索引使用情况：

SELECT
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC;

idx_scan = 0 不能直接证明索引无用。统计信息可能刚被重置，索引也可能只服务月末任务、唯一约束或外键删除检查。删除前还要确认约束依赖、低频关键查询、观察窗口，以及移除后能减少多少写放大和存储占用。

参考资料