技术小册

主题

索引类型与优化：按查询形态选索引 ​

一句话：索引用额外的写入维护和磁盘空间，换查询时更少的页访问；选型不是背类型，而是先看查询长什么样。

适用 PostgreSQL 16/17。示例中的表名、列名按业务替换。

为什么需要 ​

一张一千万行的订单表，按 user_id 取最近 20 条。没有索引时，数据库只能从头扫到尾，再排序；有了合适的复合索引，可以直接定位到目标区间，读几页就返回。

问题在于「合适」二字。同样的列，WHERE user_id = ?、WHERE status = ?、ORDER BY created_at DESC 对索引的要求不同；建错索引不仅不加速，还会让写入变慢。

核心机制 ​

先分查询形态，再选索引 ​

查询形态首选原因
等值、范围、排序、前缀匹配B-tree（默认）有序结构，能同时服务 =、>、BETWEEN、ORDER BY
多列组合过滤复合 B-tree按「等值列 → 范围/排序列」排列，最左前缀可用
只查一部分行部分索引（partial index）只索引热子集，索引更小、维护更少
对列做函数/表达式后再过滤表达式索引索引表达式必须与查询表达式一致
数组、JSONB、全文包含/存在判断GIN倒排结构，适合多值字段的「包含」查询
地理空间、范围类型、模糊最近邻GiST通用搜索树，支持距离和重叠操作符
超大只追加表，按物理顺序过滤BRIN只存块范围的最小/最大值，体积极小
纯等值、超高高基数Hash只支持 =，通常不是第一选择

B-tree 为什么是默认 ​

B-tree 是有序的平衡树，叶子层按索引键排序。它同时支持等值查找、范围扫描和有序输出，所以大多数 OLTP 查询都落在它上面。

复合索引的顺序很关键。(user_id, status, created_at) 可以服务：

sql
WHERE user_id = 42 AND status = 'paid' ORDER BY created_at DESC

因为前两列是等值条件，第三列天然有序，排序可以省掉。反过来，(created_at, user_id, status) 对同样的查询帮助小得多。

覆盖索引与「只读索引」的代价 ​

CREATE INDEX ... INCLUDE (...)（PostgreSQL 11+）可以把非键列放进索引叶子，减少回表。但 Index Only Scan 仍可能读堆：它依赖可见性映射（visibility map），而可见性映射由 VACUUM 维护。表刚批量更新过、还没 VACUUM 时，计划仍可能显示 Heap Fetches。

GIN 与 GiST 的取舍 ​

GIN 是倒排索引：每个词/键指向包含它的行。读快，但写入要更新多个 posting list，写放大明显；它还有 pending list（fastupdate=on 时的缓冲），查询要先消化 pending list。

GiST 是平衡的通用搜索树，写入更平滑，但检索可能产生「有损」候选，需要回表复核。全文检索、数组包含通常选 GIN；地理空间和范围类型通常选 GiST。

BRIN 只对「物理有序」有效 ​

BRIN 为每个块范围记录最小/最大值。只有当数据在磁盘上的物理顺序与列值高度相关时，它才能有效裁剪；随机写入的时间列，BRIN 的块范围会互相重叠，效果接近全表扫描。建索引前先看 pg_stats.correlation。

为什么优化器会「不用索引」 ​

索引扫描要随机读页。如果查询返回的行占表的大部分，顺序全表扫描反而更便宜。优化器用 seq_page_cost=1.0、random_page_cost=4.0（PostgreSQL 16/17 默认）估算成本；在 SSD 上 random_page_cost 往往应调低到 1.1–1.5，否则优化器会低估索引扫描。

最小上手 ​

sql
-- PostgreSQL 16/17
CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint NOT NULL,
  status     text   NOT NULL,
  created_at timestamptz NOT NULL,
  amount     numeric(12,2) NOT NULL
);

INSERT INTO orders (user_id, status, created_at, amount)
SELECT (random() * 100000)::bigint,
       (ARRAY['paid','pending','cancelled'])[1 + (random() * 2)::int],
       now() - (random() * 365)::int * interval '1 day',
       (random() * 1000)::numeric(12,2)
FROM generate_series(1, 1000000);

ANALYZE orders;

-- 复合索引：等值列在前，排序列在后
CREATE INDEX CONCURRENTLY idx_orders_user_status_created
  ON orders (user_id, status, created_at DESC);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

期望看到 Limit -> Index Scan using idx_orders_user_status_created，Buffers: shared hit=... 很小。如果仍是 Seq Scan，先确认 ANALYZE 跑过、条件类型没有隐式转换。

其他形态：

sql
-- 只索引未完成订单，索引更小
CREATE INDEX CONCURRENTLY idx_orders_pending
  ON orders (created_at) WHERE status = 'pending';

-- 表达式索引：查询必须写同样的表达式
CREATE INDEX CONCURRENTLY idx_orders_month
  ON orders ((date_trunc('month', created_at)));

-- JSONB 包含查询
CREATE INDEX CONCURRENTLY idx_orders_meta
  ON orders USING GIN (meta jsonb_path_ops);

-- 时序表：先确认物理相关性
CREATE INDEX CONCURRENTLY idx_orders_created_brin
  ON orders USING BRIN (created_at) WITH (pages_per_range = 128);

生产动作 ​

  • 大表建索引：用 CREATE INDEX CONCURRENTLY，它拿 SHARE UPDATE EXCLUSIVE，不阻塞 DML，但不能放在事务块里。失败会留下 indisvalid=false 的无效索引，先 DROP INDEX 再重试。
  • 确认索引真的被用：pg_stat_user_indexes.idx_scan 长期为 0 的索引是候选删除项；删之前确认没有报表或低频查询依赖。
  • 索引膨胀：pg_relation_size('idx_name') 持续上涨、查询变慢时，用 REINDEX INDEX CONCURRENTLY（PostgreSQL 12+）。
  • 统计信息：批量导入或大批量更新后执行 ANALYZE；多列相关性用 CREATE STATISTICS ... (dependencies, ndistinct)。
  • SSD 成本参数：random_page_cost=4.0（默认）在 SSD 上偏高，常见调到 1.1–1.5；改完用 EXPLAIN 对比计划，不要一次改多个参数。
  • 排障路径：现象「加了索引仍慢」→ EXPLAIN (ANALYZE, BUFFERS) 看 Index Cond / Filter / Rows Removed by Filter → 条件没进 Index Cond 说明索引列或类型不匹配；返回行太多说明选择性不够，考虑部分索引或复合索引。

边界 ​

  • 低选择性列不值得单独建索引：status 只有三个值时，单列索引通常不如复合索引或部分索引。
  • 小表不需要索引：几页的数据全表扫描更快，索引反而增加写入成本。
  • 写多读少的表要控制索引数量：每个索引都要在 INSERT/UPDATE/DELETE 时维护；更新索引列还会产生新的索引项。
  • LIKE '%xxx' 用不上普通 B-tree：前缀不确定的模糊匹配需要 pg_trgm 的 GIN/GiST 索引，或转向全文检索。
  • 函数/类型不匹配会让索引失效：WHERE created_at::date = '2026-01-01' 用不上 created_at 的普通索引，应改成范围条件或建表达式索引。
  • BRIN 不是「大表万能索引」：物理相关性低时，它裁不掉多少块；先用 pg_stats.correlation 验证。
  • Bitmap Scan 不是 PostgreSQL 独有：Oracle 有 bitmap index，其他库也有类似的位图合并；这里说的是 PostgreSQL 的 Bitmap Index Scan + Bitmap Heap Scan 计划节点，作用是把多个索引结果按物理页顺序回表。

讲清楚 ​

索引的本质是用空间和写入成本换读取时更少的页访问。B-tree 之所以是默认，是因为它同时支持等值、范围和排序；复合索引的顺序要按「等值列在前、范围或排序列在后」安排。GIN 适合数组、JSONB、全文这类多值字段的包含查询，但写放大明显；BRIN 只在数据物理有序时有效。真正容易踩的坑不是「不知道有几种索引」，而是建了索引却没用上：条件被函数包住、类型不匹配、返回行太多、或者可见性映射没更新导致 Index Only Scan 仍然回表。判断方法只有一个：看 EXPLAIN (ANALYZE, BUFFERS) 里的 Index Cond、Rows Removed by Filter 和 Buffers，不要凭感觉。

把一门技术讲透到既懂也会用

技术小册 · 持续更新