PostgreSQL 索引设计进阶
PostgreSQL 索引设计实战:类型选型、复合列序与膨胀治理
摘要:索引不是越多越好——每多一个索引,写入就多一份维护成本,膨胀就多一个来源。本文从“索引是负债”这一反直觉认知切入,拆解六大索引类型的选型决策、复合索引的列序原则、部分索引/表达式索引/覆盖索引三种高阶用法,以及索引膨胀的量化诊断与在线重建,适用于 PostgreSQL 14+ 与 TimescaleDB 2.x 环境的索引体系设计与治理。
一、先立一条反直觉的军规:索引是负债
很多人把“加索引”当成性能问题的万能药,但每加一个索引的实际代价是:
- 写入放大:每次 INSERT/UPDATE/DELETE 都要同步维护所有相关索引,一张表挂 8 个索引,写入成本接近翻 8 倍;
- 存储放大:索引体积可到表本身的 50% 以上,磁盘和缓存都在为它买单;
- 膨胀源:B-tree 不合并稀疏页,删多留少的索引会持续膨胀,VACUUM 也收不回来;
- 规划器选择面变宽:索引越多,规划器需要评估的组合越多,统计不准时反而容易选错。
设计索引的正确姿势是反推——从 pg_stat_statements 的 Top 榜倒推高频查询,为它们设计索引,而不是凭直觉给所有 WHERE 列都加。一条军规:没有对应查询支撑的索引,是待清理的负债。索引使用率检查:
SELECT schemaname||'.'||relname AS table,
indexrelname, idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC LIMIT 20;
注意陷阱:唯一索引的 idx_scan 常年接近 0 是正常的——它主要在 INSERT 时做唯一性检查,不一定计入扫描计数,别把它当死索引删了。
二、六种索引类型的选型
PostgreSQL 提供的不只是默认的 B-tree,不同数据形态各有最佳搭档:
| 类型 | 适用场景 | 典型数据 | 不适用 |
|---|---|---|---|
| B-tree | 等值、范围、排序 | 数字、字符串、时间 | 全文、地理 |
| Hash | 仅等值 | 内存较小时可替代 B-tree | 范围、排序 |
| GIN | 包含查询 | JSONB、数组、全文检索 | 写入重的表(更新代价大) |
| GiST | 范围/邻近 | 地理位置、Range 类型 | 普通等值 |
| BRIN | 范围查询于巨型表 | 时序数据、追加写 | 无物理顺序的表 |
| SP-GiST | 非平衡分区 | IP、电话前缀、点数据 | 通用场景 |
| BRIN 是时序场景的宝藏——它只存每个块区间的 min/max,体积比 B-tree 小千倍以上。给你的 timescaledb 那张按时间追加的 metrics 表加一个: |
CREATE INDEX idx_metrics_time_brin
ON metrics USING brin (time);
不过 TimescaleDB 的超表本身按时间分 chunk,查询通过 Chunk Skip 已经过滤了大部分数据,BRIN 主要在 chunk 内部进一步缩小扫描范围——超大 chunk 配合 BRIN 才有显著收益。
GIN 的代价要知道:JSONB 列加 GIN 索引后,UPDATE 那行的任何字段都可能触发整条索引项重写,写入重的表慎用。PostgreSQL 13+ 可加 fastupdate=off 或用部分索引缓解。
三、复合索引的列序原则
复合索引的设计核心是一句话:等值条件在前、范围条件在后、排序列贴着范围列。
以订单表为例:
-- 查询形态
SELECT * FROM orders
WHERE user_id = ? AND status = 'paid' AND created_at > ?
ORDER BY created_at DESC;
-- 索引设计
CREATE INDEX idx_orders_user_status_time
ON orders (user_id, status, created_at DESC);
设计逻辑:
- user_id、status 是等值条件,放前面,索引可以在两层等值内精确定位;
- created_at 是范围条件,放后面——范围列之后的列无法再参与索引过滤,所以范围列必须最后;
- DESC 让排序免去 Sort 节点,与 ORDER BY 一致时执行计划里不会有显式 Sort。
一个高频误区:把范围列放中间((user_id, created_at, status))——user_id 等值匹配后,范围条件 created_at 之后的 status 完全无法用索引过滤,过滤性能打折。区分这俩写法的差异,跑一遍EXPLAIN ANALYZE看Index Cond里出现的是什么就一目了然。
多列选择性的取舍:当某列选择性极高(如 user_id 只有一个用户但有百万订单)而另一列选择性低(status 只有几个值),把选择性高的等值列放前面更优。极端情况下单一列索引 + 位图合并(BitmapAnd)也够用,不必都上复合索引。
四、三种高阶索引用法
用法一:部分索引(Partial Index)——只为高价值子集建
订单表里“未支付”只占 5%,“已支付”占 95%——为已支付订单建索引是浪费,因为大比例数据本来就走全表扫更快。只为小子集建:
CREATE INDEX idx_orders_unpaid
ON orders (user_id, created_at)
WHERE status = 'unpaid';
体积只剩 5%,写入时 95% 的订单根本不用碰这个索引。关键是查询的 WHERE 必须包含与索引谓词一致的条件,规划器才能识别可用——WHERE status='unpaid' AND user_id=? 命中,WHERE user_id=? 不命中。
另一类经典用法是排除 NULL:
CREATE INDEX idx_users_email ON users (email)
WHERE email IS NOT NULL;
NULL 占多数的列,过滤掉后索引体积骤降。
用法二:表达式索引——函数计算的救星
-- 索引失效的反例
SELECT * FROM users WHERE lower(email) = 'a@b.com';
-- 表达式索引与查询写法严格对齐
CREATE INDEX idx_users_email_lower
ON users (lower(email));
规则简单:查询里怎么写,索引就怎么建。lower(email) 上建的索引,对 email = 'A@B.COM' 的查询无效。
用法三:覆盖索引(INCLUDE)——让 Index Only Scan 落地
高频查询只取两三列时,让索引自带答案,避免回表:
-- 频繁执行的查询
SELECT id, email FROM users WHERE username = ?;
-- 覆盖索引:username 是搜索键,id/email 只是载荷
CREATE INDEX idx_users_username_cover
ON users (username) INCLUDE (id, email);
-- EXPLAIN 里看到 Index Only Scan + Heap Fetches: 0 即生效
INCLUDE 列与复合索引键的区别:INCLUDE 列不参与搜索、不参与排序、不要求可比较,只挂在叶子节点提供数据。所以不要把过滤条件放进 INCLUDE,那是搜索键的职责。
一个隐性依赖:Index Only Scan 要求页可见性映射(visibility map)已更新——刚大量 UPDATE 过的表,Heap Fetches 不会立刻归零,autovacuum 跑过一轮后才充分生效。
五、索引膨胀的诊断与在线重建
膨胀的机制:B-tree 删掉大量键后,只剩零星键的页不会自动合并释放——几百个键剩 1 个,那页仍占着。频繁 DELETE/UPDATE 的表,几个月后索引可能比刚建时大 3-5 倍。
量化诊断用 pgstattuple 扩展:
CREATE EXTENSION IF NOT EXISTS pgstattuple;
-- 看 B-tree 叶子页密度,新建索引接近 90(默认 fillfactor)
SELECT avg_leaf_density, leaf_fragmentation
FROM pgstatindex('idx_orders_user_status_time');
avg_leaf_density 远低于 90 就是膨胀的可量化指标,预计可回收空间 ≈ 当前体积 × (密度/90)。
修复用 REINDEX CONCURRENTLY,不要用 VACUUM FULL:
REINDEX INDEX CONCURRENTLY idx_orders_user_status_time;
VACUUM FULL 锁全表(ACCESS EXCLUSIVE),只为修索引就锁表不值——膨胀的根因在索引不在堆,单索引在线重建,峰值额外占用只有这一个索引的体积,磁盘压力可控。表本身膨胀严重时(pgstattuple 报 dead_tuple_percent 超 30%),用 pg_repack 在线重建更稳妥。
六、索引健康度巡检清单
| 检查项 | 方法 | 健康标准 |
|---|---|---|
| 无人使用的索引 | pg_stat_user_indexes 的 idx_scan=0 | 无大体积死索引 |
| 膨胀程度 | pgstatindex 的 avg_leaf_density | ≥ 70 |
| 未生效的 INVALID 索引 | SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid |
0 个 |
| 写入放大评估 | 表的索引数 × 写入 QPS | 索引数 ≤ 5 |
| 冗余索引 | 前缀相同的复合索引重叠检查 | 无 (a) 与 (a,b) 并存 |
| 冗余索引检查一条 SQL 搞定: |
SELECT a.relname AS redundant, b.relname AS covering
FROM pg_index ia
JOIN pg_index ib ON ia.indkey::text LIKE (b.indkey::text || '%')
JOIN pg_class a ON a.oid = ia.indexrelid
JOIN pg_class b ON b.oid = ib.indexrelid
WHERE a.oid <> b.oid AND ia.indisunique = false;
前缀被覆盖的非唯一索引都是冗余的,删掉。
七、总结
索引设计的决策链:
| 步骤 | 动作 | 核心原则 |
|---|---|---|
| 1. 立项 | 从 pg_stat_statements 倒推 | 没有查询支撑不建索引 |
| 2. 选型 | 按数据形态选类型 | B-tree 通用、GIN 查包含、BRIN 查时序 |
| 3. 列序 | 等值→范围→排序 | 范围列放最后 |
| 4. 高阶 | 部分索引/表达式/INCLUDE | 按查询形态反推索引形态 |
| 5. 治理 | pgstatindex 量化膨胀 | REINDEX CONCURRENTLY 在线重建 |
| 6. 巡检 | 死索引、冗余索引、INVALID | 每季度一次 |
| 核心认知一句话:索引和查询是一一对应的设计产物,不是独立于业务的通用优化——脱离查询谈索引就是无的放矢,脱离治理谈索引就是埋雷。每建一个索引都问自己:它服务哪条查询?体积多大?写入放大多少?膨胀了谁来修? |
- 点赞
- 收藏
- 关注作者
评论(0)