PostgreSQL 索引与 配置中心 的正确姿势

前阵子把公司配置中心的存储从 MySQL 迁到了 PostgreSQL，刚切完那会儿一切正常，跑了两三个月之后，同事突然跟我说，发布配置的页面转圈越来越久，翻一下配置变更历史要三四秒才出来。我寻思配置中心能有多大数据，进库里一看，好家伙，变更历史表已经攒了八百多万行了... 行吧，当初偷的懒，迟早是要还的。

先搞清楚慢在哪，别急着加索引

加索引之前先把慢查询抓出来，不然就是瞎建。PG 自带 pg_stat_statements，没开的话先开一下：

# postgresql.conf 里加一行，然后重启
shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION pg_stat_statements;

SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

老版本的 PG 这个字段叫 mean_time，报错说列不存在的话换一下就行。跑完基本一眼就能看到罪魁祸首，抓出来的 SQL 大概长这样：

SELECT * FROM config_history
WHERE app_id = 'order-service' AND env = 'prod'
ORDER BY modified_time DESC
LIMIT 20;

拿去 EXPLAIN ANALYZE 一下，果然是 Seq Scan，八百万行全扫一遍再排序截断，不慢才怪。

组合索引的列顺序，真的有讲究

这里有个老生常谈但真的容易忘的点：组合索引走最左前缀，列的顺序不是随便排的。原则很简单，等值条件的列放前面，范围和排序的列放最后：

CREATE INDEX CONCURRENTLY idx_history_query
ON config_history (app_id, env, modified_time);

app_id 和 env 都是等值匹配，modified_time 用来排序，这个顺序就是对的，ORDER BY ... DESC LIMIT 20 可以直接沿着索引倒着走，连额外的排序步骤都省了。

要是你随手建成了 (modified_time, app_id, env)，那按应用查历史基本就用不上它，没准儿还和全表扫描一个水平，白搭。

另外如果历史数据是从老库批量导进来的，建完索引记得 ANALYZE config_history; 一下，不然统计信息是旧的，优化器没准儿还是不走索引，到时候别冤枉索引不好使。

这几种写法，索引直接摆烂

排查的时候顺手总结了一下，下面这三种情况，建了索引也基本白建：

• 对索引列套函数：比如 WHERE lower(app_key) = ...，普通 B-tree 索引直接不认。真有这需求就建表达式索引：CREATE INDEX ON config (lower(app_key));
• 隐式类型转换：列是 varchar，查询的时候传了个数字进去，索引说不用就不用，老实把类型传对。
• 前置模糊 LIKE '%关键字'：B-tree 帮不了你，但配置中心按关键字搜配置这种场景又挺常见，那就上 pg_trgm：

CREATE EXTENSION pg_trgm;
CREATE INDEX CONCURRENTLY idx_config_key_trgm
ON config USING gin (config_key gin_trgm_ops);

软删除的数据，用部分索引绕开

配置中心一般都有软删除，is_deleted = true 的历史数据躺在表里，查询每次还得在索引里陪着它们溜一圈。部分索引就是干这个的：

CREATE INDEX CONCURRENTLY idx_history_alive
ON config_history (app_id, env, modified_time)
WHERE is_deleted = false; -- <--- 只索引活着的行

索引体积小了一半不止，查询快了，每次发布配置时的写入维护开销也跟着小了。这招在日志表、审计表上一样通用。

对了，索引也不是越多越好，每多一个索引，写入就多一份维护成本。可以用 pg_stat_user_indexes 看看 idx_scan 一直是 0 的索引，八成是以前随手建的，该删就删。

大表建索引，记得 CONCURRENTLY

生产上给大表建索引，别直接 CREATE INDEX，会锁写，表大的话能锁到你怀疑人生。用 CONCURRENTLY 就不锁写，代价是慢一点。两个小坑提前说一下：一是它不能放在事务块里跑，用 Flyway 之类的迁移工具时注意把自动事务关掉；二是万一中途失败，会留下一个无效索引，DROP INDEX 掉重跑就行，第一次遇到的时候我还愣了一下。

最后

建完索引再跑一遍 EXPLAIN ANALYZE，Seq Scan 变成了 Index Scan，耗时从 3400ms 掉到 3ms，页面秒开。同事问我干了啥，我说没干啥，就加了俩索引。

一句话总结：先量化再动手（pg_stat_statements + EXPLAIN ANALYZE）；等值列在前、排序列在后；套函数、隐式转换、前置模糊是索引三大杀手；软删除用部分索引；大表建索引用 CONCURRENTLY。配置中心这种读多写少、历史表疯狂膨胀的场景，把这几条记住，基本就够用了。