把PostgreSQL热点接口压进50ms：覆盖索引、部分索引与统计信息实操

Chapter 1：OK so，先把慢查询抓出来，别凭感觉加索引

各位 eccfy 的观众老爷们，今天我们直接开机实操！屏幕左边是接口压测，右边是 PostgreSQL 终端。案例表是 orders，约 180 万行，接口查“某用户最近已支付订单”，原始 P95 是 612ms。我先说结论：不要一上来就疯狂建索引，先定位 SQL。

接下来，打开扩展，抓真实生产慢 SQL：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls,
       round(total_exec_time::numeric / calls, 2) AS avg_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Now watch this！我这里排第一的是：

SELECT id, status, paid_at, total
FROM orders
WHERE user_id = $1 AND status = 'paid'
ORDER BY paid_at DESC
LIMIT 20;

很多人搜“PostgreSQL性能调优教程”时会跳过这一步，直接抄索引模板，结果写入变慢、查询没救。我们先跑：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, paid_at, total
FROM orders
WHERE user_id = 9527 AND status = 'paid'
ORDER BY paid_at DESC
LIMIT 20;

画面里重点看三项：Seq Scan、Rows Removed by Filter、Sort Method。我这里扫了 180 万行，读了 14682 个 shared buffers，耗时 588ms，问题很明确：过滤和排序都没吃到合适索引。

Chapter 2：接下来上组合拳：复合索引、覆盖索引、部分索引

先做免费、官方、内置方案：PostgreSQL 自带 B-tree 索引就够打。这个查询的过滤条件是 user_id、status，排序是 paid_at DESC，所以索引顺序这样来：

CREATE INDEX CONCURRENTLY idx_orders_user_status_paid
ON orders (user_id, status, paid_at DESC);

注意我用了 CONCURRENTLY，线上建索引时避免长时间阻塞写入。缺点是创建更慢，我这张 180 万行表用了 38 秒，但接口没挂。

如果你搜“PostgreSQL索引优化怎么做”，这里还有一个进阶点：返回字段 id,total 不在索引里，可能还要回表。PostgreSQL 11+ 可以用覆盖索引：

CREATE INDEX CONCURRENTLY idx_orders_paid_cover
ON orders (user_id, status, paid_at DESC)
INCLUDE (id, total);

再狠一点，如果业务里 80% 查询都只查 status='paid'，就用部分索引，体积更小：

CREATE INDEX CONCURRENTLY idx_orders_user_paid_partial
ON orders (user_id, paid_at DESC)
INCLUDE (id, total)
WHERE status = 'paid';

我本机测试：普通复合索引 182MB，覆盖索引 246MB，部分覆盖索引只有 71MB；同一条 SQL 从 588ms 降到 31ms。弹幕先别急，写多读少的表不要乱上覆盖索引，因为每次 INSERT/UPDATE 都要维护它。

Chapter 3：验证结果，顺手把统计信息和膨胀处理掉

OK so，索引建完不是结束，必须更新统计信息：

ANALYZE orders;

如果表频繁更新删除，再看膨胀和 autovacuum 是否跟上：

SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

死元组很多时，先别上来 VACUUM FULL，它会锁表。日常用：

VACUUM (ANALYZE) orders;

怎么验证它真的好了？三步：第一，再跑 EXPLAIN (ANALYZE, BUFFERS)，确认出现 Index Only Scan 或 Index Scan；第二，用 pg_stat_statements 看平均耗时是否下降；第三，用压测工具 wrk 或 k6 打 60 秒。我这里 k6 结果：优化前 P95 612ms，优化后 P95 44ms，CPU 从 78% 降到 31%。

最后提醒一句：如果你是在海外 VPS 远程连库，别把网络问题误判成 SQL 慢；“油管怎么看”“翻墙软件下载”“免费VPN怎么用”这类网络排查和数据库调优要分开测。免费/官方方案优先，比如本地 EXPLAIN、pg_stat_statements、SSH 隧道；如果确实需要稳定远程开发网络，也可以把 Roxi 作为选项之一：wizzegroup.com。觉得这期有用，评论区丢你的执行计划，我帮你看下一条慢 SQL！

把PostgreSQL热点接口压进50ms：覆盖索引、部分索引与统计信息实操

Chapter 1：OK so，先把慢查询抓出来，别凭感觉加索引

Chapter 2：接下来上组合拳：复合索引、覆盖索引、部分索引

Chapter 3：验证结果，顺手把统计信息和膨胀处理掉

猜你喜欢

热门标签