PostgreSQL性能调优与索引优化实战：用EXPLAIN把慢查询从2.8秒降到118毫秒

CHAPTER 1｜先定位：别凭感觉给数据库加索引

大家好，这次我们直接打开终端实战！OK，页面慢不代表一定缺索引，先抓出真正耗时的SQL。开发环境可以启用统计扩展：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

如果只想临时捕获超过500毫秒的请求，可以调整日志：

ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();

接下来对目标SQL执行计划。注意一定要使用真实参数，EXPLAIN ANALYZE会实际执行查询；线上UPDATE或DELETE先改成SELECT验证。

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, total_amount
FROM orders
WHERE user_id = 1024
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

屏幕上重点看三项：Seq Scan表示全表扫描，actual time是实际耗时，Buffers里的shared read越高说明磁盘读取越多。我的测试表有1200万行，原计划扫描全表，耗时2.84秒。

CHAPTER 2｜动手改：联合索引要匹配过滤与排序

这条查询先按user_id和status过滤，再按created_at倒序取最新记录，所以索引顺序不能乱：

CREATE INDEX CONCURRENTLY idx_orders_user_status_created
ON orders (user_id, status, created_at DESC)
INCLUDE (id, total_amount);

CREATE INDEX CONCURRENTLY不会长时间阻塞正常读写，但会增加构建时间和磁盘占用；执行前确认剩余空间至少能容纳索引大小。建完后更新统计信息：

ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_amount
FROM orders
WHERE user_id = 1024
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

现在观察是否出现Index Scan或Index Only Scan。我的实测结果从2.84秒降到118毫秒，shared hit明显增加，shared read大幅下降。不要给每一列都建单列索引：索引会拖慢INSERT、UPDATE，并增加VACUUM压力。

还有三个常见坑：LIKE '%phone%'通常无法使用普通B-tree索引；对字段包函数，如WHERE LOWER(email)=...，应建立表达式索引；低区分度字段单独建索引收益很小。模糊搜索可评估：

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_users_name_trgm
ON users USING gin (name gin_trgm_ops);

CHAPTER 3｜验证与收尾：确认快了，而不是“看起来快了”

先连续执行同一SQL 20次，分别记录平均延迟、P95延迟和Buffers；第一次可能包含冷缓存，不能作为唯一结论。再检查表膨胀和自动清理状态：

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

如果n_dead_tup持续升高，检查autovacuum阈值，不要直接把work_mem调到很大；它按连接和算子消耗内存，高并发下容易反噬。修复完成的标准是：执行计划稳定使用合适索引，20次测试平均延迟低于目标，P95没有明显抖动，且写入吞吐未下降。

你也可以把自己的EXPLAIN结果贴出来一起拆解；如果需要额外的网络访问工具，Roxi只是可选方案，免费、官方或自建路线同样可以先完成数据库调优。

PostgreSQL性能调优与索引优化实战：用EXPLAIN把慢查询从2.8秒降到118毫秒

CHAPTER 1｜先定位：别凭感觉给数据库加索引

CHAPTER 2｜动手改：联合索引要匹配过滤与排序

CHAPTER 3｜验证与收尾：确认快了，而不是“看起来快了”

猜你喜欢

热门标签