PostgreSQL 索引原理与查询优化实战

数据库

一次慢查询把下单接口拖垮的事故

去年双十一预热，下单接口 P99 从 80ms 飙到 2.3s。监控里 CPU 打满，连接池被吃光。我们一开始以为是代码问题，翻了半天业务逻辑，最后 EXPLAIN ANALYZE 一跑：一条按 user_id + created_at 范围查订单的 SQL，在做全表顺序扫描（Seq Scan），扫描了 800 万行。

加了一个组合索引，P99 回到 60ms。那次之后我养成一个习惯：任何慢查询，先问它走没走索引，再谈别的。

下面把 PostgreSQL 索引这件事讲透，重点是"什么时候用哪种、为什么"。

一、PostgreSQL 有哪些索引

不是所有索引都长一个样。PostgreSQL 内置好几种访问方法：

类型 适用场景 底层
B-tree 等值、范围、排序、前缀 平衡树（默认）
GIN 多值/反向索引：数组、jsonb、全文检索 倒排
GiST 几何、范围、相似度、全文检索 通用搜索树
BRIN 物理上按某种顺序存储的大表（如时间序） 块范围摘要
Hash 纯等值（基本被 B-tree 取代） 哈希桶

绝大多数业务用 B-tree 就够了。下面重点讲 B-tree，再补 GIN 和部分/表达式索引这两个常被忽略的利器。

二、B-tree 是怎么工作的

B-tree 把索引列排成有序的平衡树。查找一个值时走树降到叶子，复杂度 O(log n)。它支持：

  • 等值：WHERE status = 'paid'
  • 范围：WHERE created_at > '2026-01-01'
  • 排序：ORDER BY id DESC
  • 前缀模糊：WHERE email LIKE 'ada%'（注意前模糊 '%ada' 用不上）

组合索引与最左前缀

组合索引 (a, b, c) 其实是按 a、然后 b、然后 c 排序的。所以它只在查询命中最左前缀时有效：

-- 能用到 (user_id, created_at)
WHERE user_id = 5 AND created_at > '2026-01-01';
WHERE user_id = 5;
WHERE user_id = 5 AND created_at = '2026-01-01';

-- 用不到（跳过了 user_id）
WHERE created_at > '2026-01-01';

这就是为什么那次事故里，单独在 created_at 上建索引没用——查询是先按 user_id 过滤的。

三、读懂 EXPLAIN ANALYZE

光会建索引不够，得会看执行计划。EXPLAIN 是预估，EXPLAIN ANALYZE 是真实执行（会真的跑，写操作请用 EXPLAIN (ANALYZE, BUFFERS) 谨慎对待）。

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 5 AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

关注三件事：

  1. Seq Scan vs Index Scan：前者是全表扫，慢查询的头号嫌疑人。
  2. cost 与 actual time：(cost=0.00..123.45 rows=10) 里的 actual time=0.02..1.20，单位是毫秒。看实际耗时，别被预估带偏。
  3. Buffers：Buffers: shared hit=3 read=120 表示读了 120 个磁盘块、只命中 3 个缓存。命中率低说明数据不在内存，可能是索引没覆盖、或 shared_buffers 太小。

一个典型的坏计划：

Seq Scan on orders  (cost=0.00..182345.00 rows=812 width=64)
  Filter: ((user_id = 5) AND (created_at > '2026-01-01'::date))
  Rows Removed by Filter: 7999999

Rows Removed by Filter 接近全表行数，说明在逐行过滤——这正是缺索引的信号。

四、组合索引设计三原则

  1. 等值列在前，范围列在后。因为 B-tree 在第一个范围条件之后就无法继续利用有序性了。上面 (user_id, created_at) 就是正确顺序（user_id 等值在前）。
  2. 选择性高的列优先放前面（在等值条件里）。区分度高，过滤得快。
  3. 把 ORDER BY 的列并进索引，避免额外的排序节点（Sort）。查询 WHERE user_id=5 ORDER BY created_at DESC 用 (user_id, created_at) 就能直接按序取，省掉 Sort。

覆盖索引（INCLUDE）

如果查询只取索引里已有的列，PostgreSQL 连表都不用回，这叫"索引覆盖 / Index Only Scan"。用 INCLUDE 把不需要参与查找、但SELECT要用的列附加上去：

CREATE INDEX idx_orders_user_time
  ON orders (user_id, created_at)
  INCLUDE (status, total);

这样 SELECT status, total FROM orders WHERE user_id=5 AND created_at > ... 可以纯索引返回，不再回表。

五、部分索引与表达式索引

部分索引（Partial Index）

如果业务只频繁查"未支付"的订单，给全表建索引是浪费。只对关心的行建索引：

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

索引体积更小、维护更便宜，且对 WHERE status = 'pending' 的查询精准命中。

表达式索引（Expression Index）

查询里对列做了函数运算，普通索引就用不上。比如按小写邮箱查：

-- 这样建索引
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- 这样查才命中
SELECT * FROM users WHERE lower(email) = 'ada@example.com';

再比如按"月份"聚合，常写 date_trunc('month', created_at)，就建 CREATE INDEX ... ON orders (date_trunc('month', created_at))。索引表达式和查询表达式必须逐字一致才会被用上。

六、GIN：数组、jsonb 与全文检索

当你要索引"一个值对应多个元素"时，GIN 是首选。

jsonb 里的字段

CREATE INDEX idx_events_payload ON events USING gin (payload jsonb_path_ops);

SELECT * FROM events WHERE payload @> '{"type": "click"}';

@> 是 jsonb 包含运算符，GIN 能高效处理。

数组包含

CREATE INDEX idx_posts_tags ON posts USING gin (tags);

SELECT * FROM posts WHERE tags @> ARRAY['go','postgres'];

全文检索

CREATE INDEX idx_articles_body ON articles USING gin (to_tsvector('zhcfg', body));

SELECT * FROM articles
WHERE to_tsvector('zhcfg', body) @@ to_tsquery('zhcfg', '数据库 索引');

注意 to_tsvector('zhcfg', ...) 里的配置名要和索引里完全一致，否则走不上索引。

七、统计信息与 autovacuum：索引为什么会"突然失效"

我们踩过另一个坑：一个跑了半年的查询某天突然变慢，执行计划从 Index Scan 退回 Seq Scan。原因不是 SQL 变了，是统计信息过期了。

PostgreSQL 的优化器靠 pg_statistic 里的数据分布估算行数。如果表大量写入后没及时 ANALYZE，估算失真，优化器可能"算错"而放弃索引。

  • autovacuum 会顺带做 ANALYZE，但大批量导入后它可能跟不上。
  • 手动触发：ANALYZE orders; 或 VACUUM ANALYZE orders;
  • 查统计：SELECT * FROM pg_stats WHERE tablename = 'orders';

经验：大批量 ETL / 删数据之后，务必显式 ANALYZE。另外，如果某列值极度倾斜（比如 99% 都是 status='done'），优化器知道查 'done' 几乎等于全表，自然不走索引——这时部分索引（只索引那 1% 的非 done）才是正解。

八、慢查询优化 checklist

我排障时基本按这个顺序：

  1. EXPLAIN ANALYZE 看是不是 Seq Scan。
  2. 看 Rows Removed by Filter，估算是不是严重失真（对比实际行数）。
  3. 检查 WHERE / ORDER BY / JOIN 列是否有索引，组合索引顺序对不对。
  4. 函数包列？lower()、date_trunc() 之类需要表达式索引。
  5. 是否命中最左前缀。
  6. 统计信息是否新鲜（ANALYZE）。
  7. 该列是否选择性太低，考虑部分索引。
  8. 能不能用覆盖索引免去回表。

九、实战：优化一个订单查询

原 SQL（慢，2.1s）：

SELECT id, status, total, created_at
FROM orders
WHERE user_id = 42
  AND status IN ('paid', 'shipped')
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 50;

表有 800 万行，原执行计划是 Seq Scan。

第一步：建组合索引，等值列 user_id 和 status 在前，范围 created_at 在后。但 status 是 IN（多值），把它放在中间，B-tree 仍能利用 user_id 的有序前缀：

CREATE INDEX idx_orders_user_status_time
  ON orders (user_id, status, created_at)
  INCLUDE (total);

第二步：ANALYZE orders; 刷新统计。

第三步：复查执行计划，确认变成 Index Scan，且 Buffers: shared hit 很小、actual time 降到 1ms 级别。

为什么 IN 能放中间：B-tree 对 (user_id, status, created_at) 来说，user_id=42 把范围缩到该用户，status IN (...) 在这个范围内继续缩小，仍保留 created_at 的有序性，于是 ORDER BY created_at DESC LIMIT 50 可以直接沿索引倒序取前 50 行，不必排序、不必扫全表。这一条索引把 2.1s 干到了个位数毫秒。

常见问题

Q1：索引是不是越多越好？

恰恰相反。索引拖慢写入（INSERT/UPDATE/DELETE 都要维护），占磁盘，还可能让优化器选错。一张频繁写的表，索引超过 5~6 个就要警惕。每个索引都要能回答"它加速了哪个真实查询"。

Q2：为什么我建了索引，查询还是不走？

最常见：函数包列、没命中最左前缀、统计信息失真、列选择性太低、或查询返回了表里大部分行（此时 Seq Scan 反而更便宜）。用 EXPLAIN ANALYZE 验证，别靠猜。

Q3：LIKE 能走索引吗？

'abc%' 这种前导模糊能走 B-tree（因为有序）。'%abc' 后导模糊不行。如果需要后导/包含匹配，考虑 pg_trgm 的 GIN trigram 索引或全文检索。

Q4：UNIQUE 约束会自动建索引吗？

会。PRIMARY KEY 和 UNIQUE 都会自动创建 B-tree 索引，不用你再手动建。

Q5：怎么看一个表有哪些索引、分别多大？

SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE tablename = 'orders';

工具推荐

写 SQL、把查询结果和 JSON 互相倒腾时，这几个 ToolsKu 工具顺手：

  • SQL 格式化 — 把又长又乱的慢查询 SQL 排整齐，便于 EXPLAIN 分析
  • JSON → SQL — 拿到的 JSON 结果快速生成 INSERT 做压测数据
  • JSON 结构对比 — 对比优化前后两次查询返回的字段差异

索引优化的本质，是帮优化器"少读点数据"。所有的技巧——组合顺序、部分索引、覆盖索引——都指向同一件事：让一次查询尽可能只在索引和很少的块里完成。

本站提供浏览器本地工具，免注册即可试用 →

#PostgreSQL#索引#查询优化#数据库#EXPLAIN