PostgreSQL 索引设计完全指南:从一次"加索引锁表 4 分钟业务停摆"看懂为什么 CREATE INDEX 远远不够

2023 年我们做一个 SaaS 数据分析平台 后端用 PostgreSQL 14 主库一台 32C 128G 加两台只读从库。业务初期 100 万订单 1000 万事件 一切都很流畅 查询都在 50ms 内。半年后数据涨到 1 亿订单 8 亿事件 我们陆续踩了一堆坑。第一种最让我傻眼 一个看似普通的 ORDER BY created_at DESC LIMIT 10 查询 没建索引前 5 秒 加了 b-tree 索引秒变 1ms 但加索引那一刻全表锁定 业务停摆 4 分钟 老板和我谈话。第二种最难缠 同样一个 WHERE user_id = 123 AND status = 'paid' 查询 在 staging 100 万行用了我们建的 (user_id, status) 复合索引 完美命中 在生产 1 亿行同样 SQL planner 居然选了全表扫 排查发现是 ANALYZE 没跑 统计信息过期。第三种最离谱 我们有一个 created_at 索引 业务跑了一年 索引 size 已经 30GB 但实际有效数据 5GB 索引膨胀 vacuum 跑不掉因为有长事务卡着 查询性能逐渐下降。第四种最致命 一个隐式类型转换 WHERE user_id = '123' 把 bigint 列与字符串比较 planner 放弃 user_id 索引走全表扫 我们整整两周没发现 直到 CPU 持续 90% 才挖出来。第五种最莫名其妙 一个 JSONB 字段 WHERE data->>'channel' = 'wechat' 查询慢 我们建了 b-tree 索引在 (data->>'channel') 上 staging 飞快 生产仍然慢 排查发现是 channel 取值高度倾斜 90% 是 wechat 索引选择性差 planner 不用。我盯着这一连串问题想了很久才彻底想明白第一版错在一个根本的认知上我以为索引就是 哪个字段查得多就建一个 b-tree 上去 可这个认知是错的真正能扛业务的 Postgres 索引是一个 索引类型选型 加 复合索引列顺序 加 部分索引与表达式索引 加 ANALYZE 与 vacuum 加 索引膨胀监控 加 慢查询分析与执行计划 的整套工程方法论 任何一环没做都可能让查询从毫秒级退化到秒级或者干脆全表扫拖垮 CPU本文从头梳理 PostgreSQL 索引设计的工程要点 b-tree gin gist brin hash 怎么选 复合索引列顺序怎么定 部分索引何时用 ANALYZE vacuum 怎么管 索引膨胀怎么治 EXPLAIN 怎么看 以及一些把 Postgres 索引做扎实要避开的工程坑

问题背景:为什么 CREATE INDEX 不是查得多就建

很多人对索引的认知是 哪个字段 where 用得多就建个索引上去 慢查询就解决 但生产里你会发现 索引建多了写入慢 索引建错了查询照样慢 索引膨胀了性能逐渐衰退 索引选择性差 planner 直接放弃。问题的根源在于:

• 索引类型决定能力:b-tree gin gist brin hash 各擅长不同场景 选错索引等于没建。
• 列顺序决定能不能用:复合索引 (a, b, c) 能服务 WHERE a 或 WHERE a AND b 不能服务 WHERE b 单独查 顺序错了索引白建。
• 统计信息决定 planner 选不选:ANALYZE 没跑或者过期 planner 估算错 行数偏差 100 倍 直接走全表扫。
• 索引会膨胀:update delete 会让索引产生死 tuple 膨胀到几倍大 必须 reindex 或 pg_repack。
• 选择性决定值不值得用:WHERE status='paid' 但 80% 都是 paid 索引选择性差 planner 算下来全表扫更快。
• 覆盖索引能消灭回表:INCLUDE 子句把额外字段塞进索引 不用回表查数据页 大幅降低 IO。

一 索引类型选型:b-tree 不是万能

Postgres 支持 b-tree gin gist brin hash sp-gist 6 种索引 每种擅长场景不同。b-tree 是默认 适合等值与范围查询 gin 适合数组 JSONB 全文检索 brin 适合海量时间序列 hash 适合纯等值。选错索引等于没建 必须按业务匹配。

-- 1 b-tree 默认 等值 范围 排序 都行
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_created_at ON orders (created_at DESC);
-- 复合索引 (user_id, created_at) 服务 WHERE user_id=? ORDER BY created_at
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

-- 2 gin 数组 JSONB 全文检索
-- tags text[] 数组列 找包含某标签的订单
CREATE INDEX idx_orders_tags ON orders USING gin (tags);
-- 例 SELECT * FROM orders WHERE tags @> ARRAY['vip'];

-- JSONB 列 找特定 channel 的事件
CREATE INDEX idx_events_data ON events USING gin (data jsonb_path_ops);
-- 例 SELECT * FROM events WHERE data @> '{"channel":"wechat"}';

-- 中文全文检索 加 trigger 维护 tsvector
ALTER TABLE articles ADD COLUMN tsv tsvector;
CREATE INDEX idx_articles_tsv ON articles USING gin (tsv);

-- 3 brin 时间序列 海量数据 索引体积小
-- pages_per_range 16 是 brin 的颗粒度 越小越精确 索引越大
CREATE INDEX idx_events_ts_brin ON events USING brin (ts)
WITH (pages_per_range = 16);
-- brin 适合 100 亿行级别的时间序列 索引只占 b-tree 的 1/100

-- 4 hash 纯等值 比 b-tree 略快但功能少
-- 一般用不到 b-tree 已经够好
CREATE INDEX idx_orders_token ON orders USING hash (token);

前面是按数据类型选基础索引 接下来这三种 表达式索引 部分索引 覆盖索引 是 Postgres 的高阶能力 被严重低估 实际用好能直接消灭一类性能问题 比如 lower(email) 的运行时计算 status 倾斜带来的索引膨胀 回表带来的 IO 放大。

-- 5 表达式索引 函数结果建索引
CREATE INDEX idx_users_lower_email ON users (lower(email));
-- 例 SELECT * FROM users WHERE lower(email)='alice@example.com';

-- 6 部分索引 只索引满足条件的行 减小体积
CREATE INDEX idx_orders_pending ON orders (user_id, created_at)
WHERE status = 'pending';
-- 适合 status 高度倾斜的场景 pending 只占 5% 索引只建 5%

-- 7 覆盖索引 INCLUDE 把额外字段塞进索引 不用回表
CREATE INDEX idx_orders_user_id_covering ON orders (user_id)
INCLUDE (status, amount, created_at);
-- 例 SELECT status, amount FROM orders WHERE user_id=? Index-Only Scan

索引类型选型的工程经验 90% 的场景 b-tree 够用 数组与 JSONB 用 gin 时序数据用 brin 中文全文检索用 gin+tsvector hash 几乎不用 因为 b-tree 已经足够好。表达式索引和部分索引是被严重低估的工具 我们公司一个高频查询用 (lower(email)) 表达式索引把 100 倍 lower() 计算开销消除了 部分索引让 pending 订单的索引只占总订单的 5% size 也少 5%。

二 复合索引列顺序:最左前缀

复合索引 (a, b, c) 的能用与不能用 完全取决于查询是否命中最左前缀。这是 Postgres b-tree 索引的核心机制 但生产里被 90% 的人误用 经常建出永远用不上的索引。

-- 复合索引 (user_id, status, created_at)
CREATE INDEX idx_orders_uid_status_created
ON orders (user_id, status, created_at DESC);

-- 能用上索引的查询 最左前缀连续
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123;
-- Index Scan using idx_orders_uid_status_created 用上

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';
-- Index Scan 完美用上前两列

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123 AND status = 'paid'
ORDER BY created_at DESC LIMIT 10;
-- Index Scan 前两列 + 第三列排序 一次到位

-- 不能用上索引的查询 跳过了中间列
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123 AND created_at > now() - interval '7 days';
-- 只能用 user_id 部分 第三列 created_at 因为跳过了 status 没法用

-- 完全用不上 没有最左
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'paid';
-- Seq Scan 全表扫 因为没有 user_id 这个最左

-- 列顺序原则 高选择性的列放前面
-- 但如果业务总是固定查 WHERE A 偶尔加 B 就 (A, B) 不是 (B, A)
-- 实际要看典型 query 模式 不是只看 cardinality

复合索引列顺序的工程经验 第一列必须是几乎所有查询都会带的列 第二列是大部分查询会带的列 第三列服务排序或者范围查询 不要为了一两个边角查询硬塞 那是另起一个索引的事。我们公司的规范是 任何新加复合索引必须列出至少 3 个会命中它的真实 query 不能命中就不加 否则索引只会拖慢写入。

三 ANALYZE 与统计信息:planner 的依据

Postgres 的 query planner 依赖统计信息选执行计划 行数估计错 一切都错。ANALYZE 命令更新统计信息 autovacuum 会自动跑 但批量导入或者数据分布巨变时必须手动 ANALYZE。

-- 看一个表的统计信息是否过期
SELECT relname, n_live_tup, n_dead_tup,
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- 手动 ANALYZE 一个表 几秒钟
ANALYZE orders;

-- 看 planner 估算与实际差异
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';
-- rows 估算 1000 实际 50000 差 50 倍 说明统计严重偏差
-- 解决 ANALYZE 或者增加统计采样深度

-- 增加单列采样深度 默认 100 关键列调到 1000
ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 1000;
ANALYZE orders (user_id);

-- 看一个列的高频值与分布
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
-- n_distinct=4 most_common_vals={paid,pending,refund,canceled}
-- most_common_freqs={0.85, 0.10, 0.03, 0.02}
-- planner 知道 status='paid' 占 85% 全表扫更快

单列统计够用大多数场景 但当两个列有强相关性时 planner 默认假设独立会严重高估或低估行数 比如 city 与 zipcode 一旦确定 city 大部分 zipcode 也定了 planner 不知道这层相关性 估算 city=X AND zipcode=Y 的命中行数会偏差几十倍。Postgres 10+ 支持多列统计弥补这个缺陷。

-- 多列统计 解决相关性问题
-- 比如 city 与 zipcode 高度相关 planner 默认假设独立
-- 估算会错很多 必须建 multivariate statistics
CREATE STATISTICS stat_city_zip (dependencies, ndistinct)
ON city, zipcode FROM users;
ANALYZE users;

统计信息的工程经验 大批量 INSERT UPDATE DELETE 后必须 ANALYZE 不要指望 autovacuum 触发够及时 关键列 SET STATISTICS 1000 让 planner 估算更准 多列相关性用 CREATE STATISTICS 弥补独立假设的偏差。我们公司每周一凌晨跑一次全库 ANALYZE 加几个关键表的 STATISTICS 1000 慢查询数量下降 60%。

四 EXPLAIN ANALYZE:执行计划解读

EXPLAIN 是看 planner 选了什么计划 EXPLAIN ANALYZE 真正执行一次并打印实际耗时与行数。看不懂执行计划就没法优化索引 必须会读。

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.amount, u.name
FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid' AND o.created_at > now() - interval '7 days'
ORDER BY o.created_at DESC LIMIT 100;

/* 典型输出
Limit  (cost=0.86..123.45 rows=100 width=64) (actual time=0.5..12.3 rows=100 loops=1)
  Buffers: shared hit=523 read=12
  ->  Nested Loop  (cost=0.86..6234.56 rows=5040 width=64) (actual time=0.5..12.2 rows=100 loops=1)
        Buffers: shared hit=523 read=12
        ->  Index Scan Backward using idx_orders_status_created on orders o
              (cost=0.43..3456.78 rows=5040 width=20) (actual time=0.3..3.4 rows=100 loops=1)
              Index Cond: ((status = 'paid') AND (created_at > (now() - '7 days'::interval)))
              Buffers: shared hit=312 read=8
        ->  Index Scan using users_pkey on users u
              (cost=0.43..0.55 rows=1 width=48) (actual time=0.08..0.08 rows=1 loops=100)
              Index Cond: (id = o.user_id)
              Buffers: shared hit=211 read=4
Planning Time: 0.234 ms
Execution Time: 12.456 ms
*/

-- 几个关键看点
-- 1 Scan 类型 Index Scan 好 Seq Scan 一般坏 但小表 Seq Scan 反而快
-- 2 cost 估算 vs actual time 实际 差距大说明统计有问题
-- 3 rows 估算 vs rows 实际 差 10 倍以上必须查统计
-- 4 Buffers shared hit 内存命中 read 磁盘读 read 多说明 cache 不够
-- 5 loops 嵌套循环的内层执行次数 loops 太多说明 join 顺序不好

-- 慢查询日志开启 抓所有慢于阈值的 SQL
ALTER SYSTEM SET log_min_duration_statement = '1000ms';
ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();

慢查询日志只能记录单次慢 SQL 看不到整体频次 真正的金矿是 pg_stat_statements 它把所有 SQL 按规范化形式聚合 统计调用次数 总耗时 平均耗时 命中行数 让你一眼看出哪些 SQL 是真正的资源大户。上线一定要装这个扩展 它是排查慢查询的第一手工具。

-- pg_stat_statements 全库慢查询统计
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

EXPLAIN ANALYZE 的工程经验 看执行计划三步走 第一看是不是用了正确的索引 没用就 EXPLAIN VERBOSE 看 planner 为什么放弃 第二看估算行数与实际差距 差 10 倍说明 ANALYZE 该跑了 第三看 Buffers 命中率 read 多说明 cache 不够或者 IO 是瓶颈。pg_stat_statements 是排查慢查询的金矿 上线就要装 哪些 SQL 慢哪些频繁全在这里一目了然。

[mermaid]flowchart TD
A[新 SQL 上线] --> B[EXPLAIN ANALYZE]
B --> C{是否走索引}
C -->|否| D[检查列顺序与类型]
C -->|是| E{rows 估算误差}
E -->|大| F[ANALYZE 更新统计]
E -->|小| G{Buffer 命中}
G -->|低| H[加内存或调 shared_buffers]
G -->|高| I[计划通过]
D --> J[改索引或改 SQL]
F --> B
H --> I
J --> B
I --> K[上线生产]
K --> L[pg_stat_statements 持续监控]

五 索引膨胀与 vacuum:维护是性能

Postgres 的 MVCC 机制让 UPDATE DELETE 后留下死 tuple 不会立刻回收 索引同样有死 tuple 会膨胀。autovacuum 自动回收但有局限 长事务会卡住 vacuum 表 大表的索引可能膨胀几倍 必须监控并定期 reindex。

-- 看索引膨胀情况 用 pgstattuple 扩展
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT schemaname, relname AS table, indexrelname AS index,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- 看单个索引的膨胀率
SELECT * FROM pgstatindex('idx_orders_user_created');
-- avg_leaf_density < 50 说明膨胀严重 应该 REINDEX

-- 看 autovacuum 是否在跑
SELECT relname, last_vacuum, last_autovacuum,
       autovacuum_count, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- 长事务可能卡住 vacuum 必须监控
SELECT pid, now() - xact_start AS duration, state, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
  AND xact_start < now() - interval '5 minutes';
-- idle in transaction 超过 5 分钟必须报警 它会卡 vacuum

-- REINDEX CONCURRENTLY 不锁表 推荐 13+ 用
REINDEX INDEX CONCURRENTLY idx_orders_user_created;

-- 整张表的索引一起 reindex
REINDEX TABLE CONCURRENTLY orders;

-- pg_repack 更激进 重建表与索引 但需要扩展
-- pg_repack -d mydb -t orders -j 4

索引膨胀的工程经验 高频 UPDATE 的表索引膨胀最严重 比如 status 字段经常更新的订单表 我们公司每月跑一次 REINDEX CONCURRENTLY 索引 size 从 30GB 降回 5GB 查询速度提升 3-5 倍 这是被很多 DBA 忽视的免费优化。监控长事务是关键 idle in transaction 超过 5 分钟必须告警并 kill 否则 vacuum 形同虚设 整个数据库性能逐步下降。

六 索引设计的工程坑:那些文档里学不到的

讲完原理来说几个真实生产里踩过的坑。第一个坑是 CREATE INDEX 默认锁表 大表加索引会停业务几分钟到几十分钟 生产必须用 CREATE INDEX CONCURRENTLY 不锁表 代价是建索引慢一倍但业务无感知 这是上线索引的硬规范。第二个坑是 隐式类型转换让索引失效 WHERE user_id = '123' bigint 列与字符串比较 planner 放弃索引走全表扫 必须严格类型匹配 应用层和 ORM 一定要类型对齐。第三个坑是 LIKE 'foo%' 能用索引 LIKE '%foo' 用不上索引 后者要做全文检索或者 trigram 索引 pg_trgm 扩展支持模糊匹配。第四个坑是 ORDER BY 不带 LIMIT 即使有索引也可能选全表排 因为没有 LIMIT planner 觉得索引扫不划算 加 LIMIT 让 planner 倾向用索引扫顺序输出。第五个坑是 索引过多拖慢写入 每个索引在 INSERT UPDATE 时都要维护 我们曾经一个表 12 个索引 写入 QPS 从 5000 降到 800 删掉 5 个不常用索引后回到 4000 索引精简也是优化。

关键概念速查

避坑清单

1. 生产建索引必须 CREATE INDEX CONCURRENTLY 不锁表 不要图省事用默认 CREATE INDEX 大表停业务几十分钟。
2. 复合索引列顺序按最左前缀 第一列必须是几乎所有查询都带的列 不要按 cardinality 拍脑袋。
3. 类型必须严格匹配 应用层避免 user_id = '123' 这种字符串比较 bigint 列 直接走全表扫。
4. JSONB 字段查询用 gin jsonb_path_ops 不要在表达式上建 b-tree 一是性能差二是不支持包含查询。
5. 时序数据用 brin 索引 100 亿行的事件表 brin 索引只占 b-tree 的 1/100 写入也快。
6. 大批量 INSERT UPDATE DELETE 后必须 ANALYZE 不要等 autovacuum 关键列 SET STATISTICS 1000。
7. idle in transaction 超过 5 分钟必须告警 它会卡 autovacuum 让全库性能逐渐下降。
8. 高频 UPDATE 的表每月跑 REINDEX CONCURRENTLY 索引膨胀回收掉性能提升 3-5 倍。
9. 慢查询日志 log_min_duration_statement=1000ms pg_stat_statements 必装 慢查询排查的金矿。
10. 索引精简也是优化 一个表索引超过 8 个就该 review 不常用的删掉写入性能能翻倍。

总结

Postgres 索引这事 很多人的直觉是 哪个字段查得多就 CREATE INDEX 上去 慢查询就好了 这其实是把 我会写 CREATE INDEX 和 我能在生产用 Postgres 扛住 1 亿行 1 万 QPS 业务 混为一谈。前者是会用 DDL 后者是懂数据库工程。中间隔着的是 索引类型选型 列顺序 统计信息 执行计划 索引维护 整整一套工程方法论。

从原型到生产 你需要做的事远不止 CREATE INDEX。你要懂 b-tree gin brin 各自的适用场景 要会按最左前缀设计复合索引 要保持统计信息新鲜 要看懂 EXPLAIN ANALYZE 要监控索引膨胀 要识别 idle in transaction 要会用 CONCURRENTLY 不停服建索引。每一项单独看都不复杂 但它们组合在一起 才是一个能扛业务规模的 Postgres 索引体系。少任何一项 都可能让某个慢查询拖垮整个数据库。

我经常用一个比喻来理解索引 它有点像图书馆的目录系统。表是书架 数据行是书 索引是目录卡片 b-tree 是按书名首字母排的卡片 gin 是按关键词倒排的卡片 brin 是按书架位置分块的卡片 复合索引是按 类别+作者+年份 多级排序的卡片 ANALYZE 是图书管理员定期清点书架知道每个区域有多少书 EXPLAIN 是你查找时管理员告诉你 我建议你先看哪个目录再翻哪个书架。你不能因为有了卡片就觉得查得快 还要管卡片有没有按你的查找方式组织 卡片是否需要重新整理 管理员的清点是否最新 这才是一整套图书馆检索。少了任何一项 卡片再多也救不了你找不到书。

这套架构最难的地方在于 它的复杂度在小数据时几乎完全暴露不了。你 100 万行的表 没索引也是 100ms 全表扫 觉得 Postgres 真好用。但真正上规模 1 亿行 8 亿事件 复杂 join 高并发写入 你才发现 99% 的复杂度都在 那 1% 的索引细节里 列顺序错了 统计过期了 索引膨胀了 隐式转换了 长事务卡死了。建议任何想用 Postgres 扛严肃业务的团队 上线前一定要做 真实数据压测 灌 1 亿行数据 跑真实业务 SQL 看每条 SQL 的 EXPLAIN 是否都走索引 千万别等真实业务流量来教你 那时候改索引可能要停服几小时。

—— 别看了 · 2026