PostgreSQL 索引与查询优化实战

线上告警响了,某个接口 P99 飙到 8 秒。你打开慢查询日志,发现一条 SELECT 在百万行的表上跑了几秒。加个索引,8 秒变 30 毫秒。这个故事每天都在重演。

PostgreSQL 的查询优化器很强,但它只能在"已有的索引"里挑路走。索引没建对,再聪明的优化器也只能硬扫。这篇文章不讲虚的,只讲一个核心问题:怎么让索引真正为你工作。

一、为什么索引是数据库性能的第一杠杆

先说一个反直觉的事实:数据库慢,九成不是 CPU 不够、内存不够、磁盘不够,而是没有用上索引。

数据在磁盘上是按页(block,默认 8KB)存的。一个全表扫描(Sequential Scan)意味着 PostgreSQL 要把这表的每一页都读一遍。一千万行的表,可能就是几十 GB 的磁盘 I/O。而一次索引扫描(Index Scan)或位图扫描(Bitmap Index Scan),通常只需要读几十页。

两者之间的差距,不是"快一点",而是数量级。

Seq Scan on orders    rows=10,000,000   actual time=8234.521..8234.521
Index Scan on orders  rows=42           actual time=0.038..0.041

更关键的是:索引是少数能让你"零成本换百倍性能"的手段。升级硬件最多给你线性提升,而把 Seq Scan 变成 Index Scan,往往是 100 倍以上的跨越。

所以,每次遇到慢查询,第一反应永远不该是"加机器",而是先打开 EXPLAIN ANALYZE,看它是不是在硬扫。

二、索引类型怎么选:不是所有索引都叫 B-tree

PostgreSQL 的索引不是"一个东西",而是 6 种类型,每种服务于不同的查询模式。选错类型,等于没加。

1. B-tree(默认)

创建索引时不指定类型,就是它。适合等值查询、范围查询(>、<、BETWEEN)和排序(ORDER BY)。这是 95% 场景的正确选择。

CREATE INDEX idx_orders_created_at ON orders(created_at);
-- 等值、范围、ORDER BY created_at 都能用上

2. Hash

只能做等值匹配(=),不支持范围、不支持排序。理论上比 B-tree 略快、略小,但收益非常有限。再加上 PG 10 之前 Hash 索引不写 WAL、崩溃后不安全,导致它在历史上几乎没人用。实践建议:别用 Hash,直接 B-tree。

3. GIN(倒排索引)

处理"一个字段里有多个值"的场景:**数组、全文检索、jsonb、tsvector**。比如一个 tags text[]` 字段,你想查"包含 'python' 标签的所有文章":

CREATE INDEX idx_articles_tags ON articles USING GIN(tags);
SELECT * FROM articles WHERE tags @> ARRAY['python'];

没有 GIN,这种查询只能全表扫。有了它,毫秒级返回。jsonb 字段同理,用 GIN (data jsonb_path_ops) 是 PG 处理 JSON 的标准姿势。

4. GiST

适合几何数据(PostGIS)、范围类型(tsrange、int4range)、以及一些"相交/包含"语义的查询。比如会议室预订,你要查"某时间段有没有冲突":

CREATE INDEX idx_booking_range ON bookings USING GIST(during);
SELECT * FROM bookings WHERE during && '[2026-07-17 14:00, 2026-07-17 15:00)';

5. BRIN(块范围索引)

专门为大表、且物理顺序与索引列高度相关的场景设计。比如日志表,数据按 created_at 顺序写入,几亿行。BRIN 只记录"每个数据块的范围区间",体积比 B-tree 小几个数量级,对于"按时间扫一段"的查询非常高效。

CREATE INDEX idx_logs_created_brin ON logs USING BRIN(created_at);

选型口诀:等值范围用 B-tree;数组全文 jsonb 用 GIN;几何范围用 GiST;超大时序表用 BRIN;Hash 基本别碰。

三、复合索引与最左前缀原则

单列索引好理解,真出问题的是复合索引。新手最容易踩的坑:索引列顺序无所谓——错,顺序至关重要。

PostgreSQL 复合索引遵循最左前缀原则(Leftmost Prefix):一个 (a, b, c) 的索引,可以服务 WHERE a = ?、WHERE a = ? AND b = ?、WHERE a = ? AND b = ? AND c = ?,但不能直接服务 WHERE b = ? 或 WHERE c = ?(因为前导列缺失,索引树没法导航)。

CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- 能用上索引:
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';
SELECT * FROM orders WHERE user_id = 42;

-- 用不上(或只能用到部分):
SELECT * FROM orders WHERE status = 'paid';           -- 没有最左前缀
SELECT * FROM orders WHERE user_id = 42 AND created_at > NOW();  -- 只用到 user_id

实战排序原则:

1. 等值条件(=)的列放最前面。
2. 范围条件(>、BETWEEN)的列放最后——范围条件之后的列,索引基本无法继续缩小范围。
3. 高选择性的列优先(但优先级低于"等值 vs 范围"这个规则)。
4. 如果某列同时用于 ORDER BY,要权衡:排序列和过滤列怎么排能让数据库直接走 Index Scan,避免额外 Sort。

一个反例:很多人习惯 CREATE INDEX (created_at, user_id),理由是 created_at 选择性高。但如果你绝大多数查询是 WHERE user_id = ? ORDER BY created_at DESC,正确的索引是 (user_id, created_at),而不是反过来。索引是为查询服务的,不是为"看起来更合理"服务的。

四、用 EXPLAIN ANALYZE 看真相

EXPLAIN 显示执行计划(估算),EXPLAIN ANALYZE 真正执行 SQL 并给出实际数据。永远用 EXPLAIN (ANALYZE, BUFFERS),看真数据。

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';

输出大概长这样:

Index Scan using idx_user_status_created on orders  (cost=0.42..28.71 rows=8 width=240) (actual time=0.038..0.041 rows=7 loops=1)
  Index Cond: ((user_id = 42) AND (status = 'paid'::text))
  Buffers: shared hit=5
Planning Time: 0.245 ms
Execution Time: 0.062 ms

看执行计划的四个关键点:

1. 节点类型:Seq Scan 是全表扫描(预警!);Index Scan 是直接走索引;Bitmap Index Scan + Bitmap Heap Scan 是索引先拿到一批 row pointer,再去堆里取数据(适合匹配行数较多的情况);Index Only Scan 是覆盖索引,直接从索引返回,连堆都不碰(最快)。

2. rows(估算 vs 实际):rows=8 是优化器估算值,(actual ... rows=7) 是实际值。两者差很多说明统计信息过期——跑一次 ANALYZE orders; 通常就修好了。

3. actual time:.. 前面是首行耗时,后面是末行耗时。看 Execution Time 而不是 Planning Time。

4. Buffers:shared hit 是命中缓存,shared read 是走磁盘。hit=5 说明只读了几页,这是索引扫描的标志。如果看到 shared read=125000,基本就是硬扫在磨磁盘。

一个常见误解:Seq Scan 不一定是坏事。小表全表扫描比走索引还快(因为索引多一次随机 I/O)。PostgreSQL 在表小于 seq_page_cost 阈值时会主动选择 Seq Scan。所以看到小表 Seq Scan 别紧张。

五、索引为什么没生效:四大常见杀手

明明加了索引,执行计划还是 Seq Scan?90% 是下面四个原因。

1. 函数包裹了列

-- 索引在 email 上
CREATE INDEX idx_users_email ON users(email);

-- 用不上!
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

对列施加任何函数,都会让普通索引失效,因为 B-tree 索引存的是"原值",而你在用"变换后的值"去查。

解决办法:表达式索引——直接对 LOWER(email) 建索引(见第七节),或者改成 WHERE email = '[email protected]' 这种能命中原值的形式。

2. 隐式类型转换

-- user_id 是 bigint,但代码传了字符串
SELECT * FROM orders WHERE user_id = '42';

PostgreSQL 通常比 MySQL 友好,但跨类型比较仍可能让索引失效。特别是 varchar 列用 integer 比较时。原则:参数类型必须和列类型严格一致。 用参数化查询(Prepared Statement)而不是字符串拼接,能从根上避免这类问题。

3. 前导通配符的 LIKE

-- 用不上 B-tree 索引
SELECT * FROM users WHERE name LIKE '%smith';
SELECT * FROM users WHERE name ILIKE '%smith';

B-tree 索引是按字典序排的,前导通配符意味着"任意前缀",索引没法二分。

• 后缀匹配 LIKE 'smith%' 能走索引(默认 text_pattern_ops 操作符类支持)。
• 真的需要前/中缀模糊搜索,上 pg_trgm 扩展 + GIN 索引,或者直接用全文检索(tsvector + GIN)。

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING GIN(name gin_trgm_ops);
SELECT * FROM users WHERE name % 'smith';  -- 模糊匹配,走索引

4. OR 条件两侧没都建索引

SELECT * FROM users WHERE email = '[email protected]' OR phone = '13800000000';

要让这个查询走 BitmapOr,email 和 phone 都必须有索引。任意一边没索引,优化器就只能退化成 Seq Scan。OR 多的时候,优先考虑用 UNION ALL 重写,语义更清晰也更容易优化。

六、什么时候别加索引:索引是有代价的

加索引看起来"无害",其实每一次 INSERT / UPDATE / DELETE 都要同步维护所有相关索引。索引越多,写越慢,表越大,VACUUM 越慢,内存压力越大。

以下几种场景,不要加索引:

1. 写多读少的表。比如日志表、埋点表,写入是主要负载。你加 5 个索引,写入可能慢 3 倍。
2. 小表。几千行的字典表、配置表,Seq Scan 比走索引还快(索引多一次随机 I/O)。加索引纯属浪费。
3. 低选择性的列。比如 gender(只有"男/女/未知")、is_deleted(只有 true/false)。索引这种列,优化器大概率也不会用,因为命中后还是要扫一大堆数据。
4. 几乎不被查询的列。索引是"为热点查询买单"的,冷字段别加。

判断标准:加索引前问自己三个问题——这个查询频率高吗?这个列选择性高吗?这张表写多还是读多?三个都成立才加。

定期清理也是必要的。pg_stat_user_indexes 视图会告诉你每个索引的实际使用情况:

SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0;  -- 从来没被用过的索引

idx_scan = 0 的索引是纯负债,直接 DROP。

七、进阶:部分索引、表达式索引、覆盖索引

理解了前面六节,这三个进阶技巧能让你的索引工程从"够用"变成"精致"。

1. 部分索引(Partial Index)

只对满足条件的行建索引。索引体积更小、维护更便宜、命中更精准。

-- 只给"未完成订单"建索引,完成订单直接走 Seq Scan 也无所谓
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

如果业务上 99% 的订单是"已完成",1% 是"待处理",而这个索引专门服务那 1% 的高频查询,体积能从几 GB 缩到几十 MB。

2. 表达式索引(Expression Index)

对列的某种变换直接建索引,解决第五节"函数包裹列"的问题。

CREATE INDEX idx_users_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = '[email protected]';  -- 现在能用上索引

经典场景:大小写不敏感的邮箱/用户名查询、按月份聚合查询等。注意查询条件必须和索引表达式完全一致,否则白搭。

3. 覆盖索引(Covering Index,INCLUDE)

当你只需要索引列加上几个额外的字段时,用 INCLUDE 把这些字段"塞进"索引,就能走 Index Only Scan,完全跳过堆访问,性能再上一个台阶。

CREATE INDEX idx_orders_user ON orders(user_id) INCLUDE (total_amount, created_at);

-- 这个查询能走 Index Only Scan,不需要回表
SELECT user_id, total_amount, created_at
FROM orders
WHERE user_id = 42;

注意 INCLUDE 后的列不参与索引排序,所以它们只对"覆盖"有用,对 WHERE / ORDER BY 无效。这是它和把列加到索引主键里的关键区别——主键里的列要被"导航",INCLUDE 的列只是"顺手带上"。

判断是否需要覆盖索引,看 EXPLAIN ANALYZE 里是不是 Index Scan 后还跟着一次堆访问(命中行数多的话,堆访问很贵)。如果是,且这几个字段是高频查询的固定返回列,就上 INCLUDE。

小结

PostgreSQL 的索引优化,本质是"先看清真相,再对症下药"。

1. 第一反应永远是 EXPLAIN (ANALYZE, BUFFERS)。不要凭直觉加索引,看执行计划再说。
2. 索引类型要匹配查询模式。等值范围用 B-tree,数组/全文/jsonb 用 GIN,大时序表用 BRIN,Hash 基本别用。
3. 复合索引列顺序决定生死。等值列在前、范围列在后,遵守最左前缀。
4. 索引失效四大杀手:函数包裹、类型不匹配、前导通配符 like、OR 单边缺索引。
5. 索引有代价,不是越多越好。写多读少、小表、低选择性列别加;定期清理 idx_scan = 0 的死索引。

金句:索引不是越多越好,而是越准越好——让索引命中查询,让查询命中性能。