遇到慢查询第一反应“加索引”这个习惯我太熟了。我早期带团队的时候每次线上 PostgreSQL 慢查询报警总有同事马上提一个“给 XX 表加索引”的变更单。索引加完查询确实快两天可过几周又冒出新的慢查询于是继续加索引。最后一张表上堆了二三十个索引写入越来越慢磁盘越来越胖云账单越来越难看。后来我把这里面几个“反常识”的点彻底想通了陆续治好了好几套线上系统的性能问题。这篇文章就聊其中最实用的 3 个方向数据页留白、删除冗余索引、修正字段类型。它们都不属于“继续堆索引”的套路而是反过来帮你判断哪些优化才是真正值得投入的性能和成本才能一起降下来。下面每一条都会给出可落地的判断 SQL 和变更步骤不管是维护订单库的后端同学还是专职 DBA都可以直接参考。1. 反常识一数据页“留白”反而更快Fillfactor 比加索引更值得调1.1 UPDATE 变慢的真正原因索引带来了“连锁写入”很多人以为 PostgreSQL 的 UPDATE 就是找到那行数据然后改掉其实不是。PostgreSQL 用的是 MVCC 机制一条 UPDATE 在底层会被拆成“把旧行标记为已删除 插入一条新行版本”。也就是说你不是改了原来的数据而是重新写了一条。这还没完。普通表上的每一条索引都会存储一个指向行版本的指针。当一行发生 UPDATE 时如果更新的字段恰好属于某个索引列那么这个索引也必须新增一个指针条目。你更新的列越多、表上的索引越多一次 UPDATE 引发的“连锁写入”就越严重。我见过一个真实案例一张订单表上有 12 个索引业务每次更新订单状态明明只改一个字段但底层要同时维护十几个索引结构。结果就是 CPU 持续高位磁盘 IOPS 经常被打满WAL 日志体积也涨得飞快。这种场景下你再看那些“给查询加索引”的优化方案方向就完全反了。1.2 Fillfactor 如何配合 HOT 更新绕过索引写入PostgreSQL 里有一个机制叫 HOT全称是 Heap-Only Tuple。它解决的核心问题是如果一个 UPDATE 不涉及任何索引列并且更新后的新行版本能放到旧行所在的同一个数据页里那这几十个索引就都不用动了只需要在页内建立一条“指针链”把查询引导到新行版本上。HOT 触发有两个关键条件第一更新不涉及索引列第二旧行所在的数据页有空余空间放新行版本。很多人忽略的是第二个条件。默认情况下PostgreSQL 建表的 fillfactor 是 100意思是数据页尽量填满。页面一旦填满后续 UPDATE 产生的新版本即使不涉及索引列也放不进当前页只能跑到别的页面HOT 就没法生效所有索引的维护成本照付。这时候你给表设一个合理的 fillfactor比如 80让页面预留 20% 空间反而能给 HOT 创造存活空间大量 UPDATE 就能走“页内指针链”的廉价路径。提示fillfactor 不是越低越好。太低会让数据页变多全表扫描和范围扫描的效率反而下降。一般从 85 到 80 开始试UPDATE 特别频繁的表可以用到 70。1.3 先看 HOT 更新率再决定要不要调并不是所有表都适合调 fillfactor。一张纯插入、很少更新的日志表fillfactor 设低纯粹是浪费磁盘空间。所以我建议先查统计信息用数据说话。SELECT relname, n_tup_upd, n_tup_hot_upd, round(100 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS hot_update_pct FROM pg_stat_user_tables WHERE n_tup_upd 10000 ORDER BY n_tup_upd DESC LIMIT 20;判断逻辑很直接找那些 UPDATE 次数很多、但 hot_update_pct 偏低的表。如果更新次数高而 HOT 命中率不到 60%说明这表大概率在“无效维护索引”上浪费了大量资源值得调整 fillfactor或者删掉一些冗余索引。设定 fillfactor 本身很简单ALTER TABLE orders SET (fillfactor 80);需要特别注意这个命令只对后续新写入和重排的数据页生效存量数据不会立刻重写。如果想让已有数据页也按 80 的密度重新组织得在低峰期执行 VACUUM FULL 或者用 pg_repack 重建表。VACUUM FULL 会申请排他锁大表务必做好维护窗口和备份。1.4 调 Fillfactor 后可能踩的坑我调试过一张 2 亿行的订单流水表刚把 fillfactor 从 100 调到 80 的时候热更新率确实从 30% 涨到了 80% 以上但磁盘占用涨了 10% 左右因为每个数据页都留了 20% 的空位。这就是留白策略的成本用少量空间换 CPU、IOPS 和 WAL 开销本质上是空间换稳定性。如果你的实例磁盘水位长期在 90% 以上那先别动 fillfactor优先清索引和清理膨胀。等磁盘水位降下来再考虑这个方案。另外一个常见误区是全局统一调低 fillfactor。系统表、配置表、字典表这些基本不更新千万别跟着调。只针对写多读少、更新频繁的业务表做个性化设置就够了。2. 反常识二很多“索引优化”其实在帮倒忙删掉反而更快2.1 索引维护成本到底有多高咱们做个粗算一张表有 10 个索引每次写入一条数据除了把数据行写入堆表还要在这 10 个索引里各加一条索引项。也就是说一次 INSERT 的实际写入量从 1 份变成了 11 份。UPDATE 如果涉及字段带索引情况更糟。这还没算内存成本。索引项会被读入 Buffer Pool索引越大占用的共享缓存越多留给热点数据的缓存就越少。缓存命中率一降磁盘 IO 就上来了性能自然下降。所以我一直说索引不是只对查询友好它更像一笔负债——查询时是资产写入时是成本索引还占存储空间。对那些长期没被使用、或者使用价值远低于维护成本的索引删掉才是真正的性能优化。2.2 三类“该删”的索引我大致把这类“垃圾索引”分成三类你可以对照自己的库看看。第一类是完全没有使用记录的索引。PostgreSQL 的统计信息表里记录着每个索引被用过的次数直接查SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size FROM pg_stat_user_indexes WHERE idx_scan 0 ORDER BY pg_relation_size(indexrelid) DESC;如果一个索引的 idx_scan 长期是 0同时占用空间又大基本可以判断它没有存在的必要。注意要看长期趋势不能只凭一两天的数据就动手可能有些索引只在月末跑批时使用。第二类是冗余索引典型场景表上已经有 (a, b, c) 组合索引又单独建了一个 (a) 索引。因为查询优化器完全可以用 (a, b, c) 的索引前缀来加速基于 a 的查询单独建 (a) 等于重复建设。判断这类索引人工看定义比较稳妥也可以跑一个基础的“首列重复”检测缩小排查范围SELECT a.indexrelid::regclass AS index_a, b.indexrelid::regclass AS index_b, a.indkey[0] AS first_col FROM pg_index a JOIN pg_index b ON a.indrelid b.indrelid AND a.indexrelid b.indexrelid AND a.indkey[0] b.indkey[0] ORDER BY 3, 1, 2;注意首列相同不代表一定冗余比如 (a, b) 和 (a, c) 查询场景不同可能都需要。这张表只是帮你缩小排查范围最后还得结合业务查询确认。第三类是过滤性太差的索引比如在一个只有 0 和 1 两个值的字段上建普通索引。查询优化器大概率觉得全表扫描比走索引更划算这个索引平时基本派不上用场但写入维护成本照付。这类索引应该直接删掉如果确实需要覆盖某种高频查询请继续往下看用部分索引替代。2.3 用部分索引替代全表索引省下真金白银部分索引就是在创建时用 WHERE 条件把索引范围缩小只索引需要的行。举一个订单表常见的例子orders 表有 1 亿行其中 99% 的订单已经完成只有 1% 是待处理状态。业务又高频查询“状态为待处理且在某个时间之前的订单”这时候你要是建一个 (status, created_at) 的常规索引等于把 1 亿行全建进去了大量已完成订单在这里面其实永远不会被新查询用到。更好的写法是CREATE INDEX CONCURRENTLY idx_orders_pending_time ON orders (created_at) WHERE status pending;这样索引里只包含待处理订单的行索引体积可能从 1GB 级别直接降到几十 MB。体积小了写入维护成本低了查询扫描的索引页也少了Buffer Pool 占用也降了。而且因为索引条目数少统计信息更准确优化器也更愿意走这个索引。部分索引很适合“字段值分布极不均匀、但高频过滤值很稳定”的场景。除了状态枚举还可以用于软删除标记、租户 ID、分类目录等。前提是查询条件里必须带上同样的常量表达式优化器才会用这个索引。2.4 安全删除索引的操作顺序删除索引比创建索引的风险更隐蔽因为删错了查询会瞬间退化。我的建议是分三步走。第一步先查使用率。除了看 idx_scan还要结合 pg_stat_statements 看哪些慢 SQL 的查询计划里出现了这个索引确认它确实支撑着关键路径。第二步用 DROP INDEX CONCURRENTLY 删除不要直接 DROP INDEX。原因是普通 DROP INDEX 会持有 ACCESS EXCLUSIVE 锁阻塞对这个表的所有读写。CONCURRENTLY 模式一边做标记一边清理不会长时间阻塞业务代价是耗时更长必须在事务块外执行。DROP INDEX CONCURRENTLY idx_orders_old_status;第三步删完别马上清理观察 1 到 2 周重点看业务有没有新的慢查询告警。如果确实出问题重新创建索引的成本比恢复被删数据低得多。这个“可以回滚”的节奏能让你在优化时更大胆。3. 反常识三类型错了索引建了等于白建3.1 你建了索引但 PostgreSQL 不配合的原因有一次我排查一个“明明有索引却走全表扫描”的问题看到表结构时愣住了订单时间字段 create_time 用的是 varchar里面存的是“2024-01-01 10:20:30”这种字符串。业务 SQL 是这样写的SELECT * FROM orders WHERE create_time::date 2024-01-01;问题就出在这个 ::date。因为 create_time 是字符串类型要拿它跟日期比较就得先做类型转换转换完之后普通 btree 索引里存的是原始字符串不是转换后的日期值优化器没法直接使用这个索引干脆全表扫描。更隐蔽的情况是隐式转换。比如你给字符串类型的 phone 字段建了索引查询参数传的是数字PostgreSQL 可能因为参数类型和字段类型不匹配而放弃索引。这就是第三类反常识优化类型选不对索引建了也白搭。与其费尽心思建表达式索引不如直接把基础数据类型改对。3.2 对性能和成本的影响账要算清楚类型问题不只是索引失效还直接决定你的表和索引有多大。咱们算一笔账。假设有一列存布尔值用 boolean 类型只占 1 字节很多人习惯用 text 或 varchar 存“true”“false”每个值光字符串内容就是 4 到 5 个字节再加 varlena 头部开销单行多出 4 到 5 倍。再看时间。timestamptz 固定 8 字节用字符串存“2024-01-01 10:20:30”是 19 字符加上头部开销约 20 字节。1 亿行的表光这一列就多占 1.2GB 左右的磁盘空间连带对应索引也要多占几 GB。空间膨胀了查询扫描的页数变多Buffer Pool 命中率下降所有读操作的延迟都会受影响。所以修正类型不仅是为了让索引能生效更是在给整个缓存体系减负。数据量和索引体积降下来同样的内存能缓存更多行这条链路几乎不需要额外成本就能提速。3.3 大表改字段类型的正确姿势很多人的第一反应是直接执行ALTER TABLE orders ALTER COLUMN create_time TYPE timestamptz USING create_time::timestamptz;小表可以这么干但大表千万不要。ALTER COLUMN TYPE 需要全表扫描并重写表期间持有排他锁业务会直接停摆。哪怕是最新版 PostgreSQL也只是把这个过程并行化了一些锁的代价依然很大。我习惯用一套“新列回填 切读切写”的流程虽然听起来繁琐但胜在安全可控第一步新增对应字段ALTER TABLE orders ADD COLUMN create_time_new timestamptz;第二步分批回填数据。用订单 ID 做范围条件一批一批来避免长事务和大量锁UPDATE orders SET create_time_new create_time::timestamptz WHERE id BETWEEN 1 AND 100000 AND create_time_new IS NULL;循环执行直到没有新数据需要更新为止。每批之间可以 sleep 几秒给业务和 WAL 留缓冲。第三步校验数据一致性。对比新旧两列确认没有解析异常或者空值丢失再继续下一步。第四步改应用写入逻辑让代码同时写新列等一个完整业务周期确保新列的数据完整。第五步删除旧列保留新列ALTER TABLE orders DROP COLUMN create_time; ALTER TABLE orders RENAME COLUMN create_time_new TO create_time;最后在新列上重建索引重建尽量用 CONCURRENTLY避免阻塞业务。这套流程的要点是永远不要让数据库在无人值守的情况下做“一次性大手术”。分批执行虽然慢但每一条都能随时暂停风险完全在掌控范围内。3.4 实在不能改表结构时表达式索引做过渡有些系统是多个部门共用的改字段类型需要走很长的审批流程。等不及的时候可以用表达式索引做一块“创可贴”。CREATE INDEX CONCURRENTLY idx_orders_create_time_date ON orders ((create_time::date));这样查询里写 create_time::date 条件时就能命中这个索引至少把全表扫描问题解决掉。但表达式索引也有代价它的键值是计算出来的写入时要额外做一次类型转换索引体积通常比正规列索引大维护成本也更高。所以它只适合过渡别想着“反正有表达式索引就不改表结构了”那是把成本从一个坑挪到另一个坑。4. 把三招串起来一套可持续使用的排查流程4.1 我从“乱加索引”到“先查后改”的转变以前我在优化一个系统的时候上来就盯着慢 SQL 加索引结果改完这边慢 SQL那边写入又开始报警整个下午都在救火。后来我强迫自己按固定流程走效率明显高了很多。第一步挑出 TOP 慢 SQL用 EXPLAIN ANALYZE 看执行计划确认瓶颈到底在顺序扫描、排序落盘、嵌套循环还是索引失效。第二步查索引使用率把零使用、冗余、过滤性差的索引全标出来汇总成“待删清单”。第三步对 UPDATE 和 DELETE 频繁的表查 HOT 更新率结合 autovacuum 运行频率判断是否有优化空间。第四步抽查主要业务表的字段类型重点看时间、布尔、数字、状态枚举有没有用字符串存。第五步根据优化收益算优先级索引删除和新增通常成本最低fillfactor 调整次之字段类型改造成本最高需要排进版本计划。这样排下来第一步解决的是“当前正在痛”的问题后面四步解决的是“接下来为什么会痛”的问题。治标和治本兼顾才不会永远在救火。4.2 一份可以直接抄的典型问题速查表我把最常见的现象、原因和解决手段整理成了一张表方便你直接对照排查。现象常见原因排查方向解决手段写入慢autovacuum 频繁触发索引冗余过多或 fillfactor 过高查 pg_stat_user_indexes 的 idx_scan查 pg_stat_user_tables 的 n_tup_hot_upd删冗余索引热点更新表调 fillfactor查询走了全表扫描明明有索引字段类型导致隐式转换或表达式计算EXPLAIN ANALYZE 查看 Filter 条件改成正确类型或临时用表达式索引索引体积巨大磁盘占用高大量索引长期未使用重复索引多按索引大小排序结合 idx_scan 统计DROP INDEX CONCURRENTLY 删除热更新率极低UPDATE 开销高表上索引列太多或页面没有留白空间计算 n_tup_hot_upd / n_tup_upd 比例减少索引调低 fillfactor删除数据后磁盘没降下来表和索引膨胀VACUUM 来不及回收查 pg_stat_user_tables 的 n_dead_tup低峰期 VACUUM FULL 或 pg_repack同样功能的 SQL时快时慢缓存命中率低或执行计划不稳定查缓存命中率固定参数化查询用 pg_hint_plan 或调整 shared_buffers 与工作参数这张表里的每一项我在实际项目中都遇到过不止一次。很多时候一次慢查询的根因能同时命中两三个格子所以排查时最好按流程走不要只看一个现象就下结论。4.3 优化效果怎么量化一次订单系统的改造复盘之前我维护过一个订单系统订单表 8000 万行左右。当时的状况是订单状态字段 status 用 varchar(20) 存“pending”“paid”这类字符串表上既有 (status) 单列索引又有 (status, created_at) 组合索引还有两个基本没被用过的历史索引。业务 UPDATE 频繁HOT 更新率只有 35%autovacuum 几乎时刻在跑。我们做的改造是一status 改成 smallint用 0/1/2/3 代表订单状态二删除两个零使用索引把组合索引改成部分索引只索引 pending 和 paid 等少量高频状态三把 fillfactor 从默认 100 调到 80。改造完成后的效果非常直接表空间加索引整体占用下降了大约 30%因为字符串类型换成 smallint加上删掉了没用索引数据页也按 80 重新整理了一遍。更明显的是写入 RT高峰期订单创建和状态更新的 p99 延迟从几百毫秒降到了几十毫秒。autovacuum 从“几乎不停”变成“一天几次”CPU 使用率也降了三分之一左右。这个案例里的数字不用照搬不同业务差异很大。核心是你要理解优化不是玄学每一项改动在改之前和改之后都能用统计信息、执行计划、资源水位去验证。这才是“性能和成本一起打下来”的真正含义。5. 写在最后几个只有踩过坑才知道的细节整篇文章讲了三类反常识优化最后我再补几个实操层面的小经验都是踩过坑换来的。索引增删一定要用 CONCURRENTLY。普通建索引和删索引会锁表线上系统稍有不慎就是故障。CONCURRENTLY 虽然慢一点但能最大限度减少对业务的影响。缺点是不能在事务块里执行而且如果建索引过程中出现了异常会留下一个失效索引需要手动清理。调 fillfactor 前先确认表有足够余量空间。留白意味着同样的行数需要更多的数据页磁盘不足的实例会因此陷入更艰难的境地。建议先清理膨胀和冗余索引腾出空间后再调顺序别搞反。改字段类型尤其是大表一定要评估依赖这个字段的所有 SQL 和索引。最稳妥的做法是先从应用层找到所有读写该字段的地方列个清单改完一处处验证而不是只靠数据库层的迁移脚本。用统计信息做决策但不要把统计表里的数据当唯一真相。pg_stat_user_indexes 的记录如果从没有清零过它反映的是“从收集统计以来”的全量使用情况跟最近三个月的真实使用趋势可能不一致。可以选一个业务低谷期执行 pg_stat_reset()让后面的统计更接近当前的业务状态但要注意这会影响所有统计数据的连续性。我个人在实际操作中最深的体会是PostgreSQL 的优化很多时候比的不是谁懂得多而是谁更沉得住气。加索引是一分钟就能完成的修改删索引和改类型却需要反复验证。但后者的收益往往是长期的它不是在解决某一条慢 SQL而是在修正整个数据库的底层效率。把这三件事做扎实以后你在面对慢查询时就不会只想用“加索引”这一个锤子了。