pgsql-29 慢查询分析

2025-09-25 约 1693 字预计阅读 4 分钟

1. 概述

慢查询分析是数据库性能优化最实用的技能。完整流程分三步：发现慢查询 → 定位原因 → 优化验证。本篇覆盖 PG 的各类分析工具与实战套路。

对比 MySQL：MySQL 用 slow_query_log + mysqldumpslow/pt-query-digest 分析慢日志。PostgreSQL 的对应能力更强——pg_stat_statements 扩展直接在内存里聚合每条 SQL 的统计（无需解析日志），是 PG 慢查询分析的核心工具。

2. 发现慢查询

2.1 慢查询日志

记录执行时间超过阈值的 SQL 到日志文件。

# postgresql.conf
log_min_duration_statement = 1000     # 记录超过 1000ms 的查询（-1关闭，0记录全部）
log_line_prefix = '%m [%p] %u@%d '    # 时间 进程 用户@库
log_checkpoints = on
log_lock_waits = on                   # 记录锁等待（排查阻塞）
log_temp_files = 0                    # 记录使用临时文件的查询（work_mem不足信号）

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

2.2 pg_stat_statements（核心工具）

在内存中聚合每条规范化 SQL 的执行统计，是找出"最该优化的 SQL"的首选。

shared_preload_libraries = 'pg_stat_statements'   # 需重启

CREATE EXTENSION pg_stat_statements;

-- 按总耗时排序找热点SQL（最该优化的）
SELECT
    substring(query, 1, 80)          AS query,
    calls                            AS 调用次数,
    round(total_exec_time::numeric,1) AS 总耗时ms,
    round(mean_exec_time::numeric,2)  AS 平均ms,
    round(stddev_exec_time::numeric,2) AS 抖动,
    rows                             AS 返回行
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

按 total_exec_time 排序：找出"累计最耗资源"的（可能单次不慢但调用极频繁）。
按 mean_exec_time 排序：找出"单次最慢"的。
优化重点应是"总耗时"高的——那才是系统真正的负担。

-- 命中率相关（缓存命中差的查询 IO 重）
SELECT query, shared_blks_hit, shared_blks_read
FROM pg_stat_statements ORDER BY shared_blks_read DESC LIMIT 10;

SELECT pg_stat_statements_reset();   -- 清零重新统计

2.3 auto_explain（自动抓执行计划）

自动把慢查询的执行计划写入日志，省去手动复现。

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '2s'
auto_explain.log_analyze = on
auto_explain.log_buffers = on

3. 定位当前正在运行的慢查询/阻塞

-- 当前运行超过5秒的查询
SELECT pid, now()-query_start AS 已运行, state, wait_event_type, wait_event,
       substring(query,1,80) AS query
FROM pg_stat_activity
WHERE state = 'active' AND now()-query_start > interval '5 seconds'
ORDER BY 已运行 DESC;

-- 找阻塞源头（谁把别人堵住了）
SELECT pid, pg_blocking_pids(pid) AS 被谁阻塞, query
FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- 终止失控查询
SELECT pg_cancel_backend(pid);      -- 温和取消
SELECT pg_terminate_backend(pid);   -- 强制断开

4. 分析单条查询：EXPLAIN

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC LIMIT 20;

重点看（详见查询优化与执行计划）：

Seq Scan 大表 → 缺索引或索引失效。
估算行数 vs 实际行数差距大 → 统计信息过期，需 ANALYZE。
Rows Removed by Filter 很大 → 索引过滤不精准。
出现 external merge Disk → work_mem 不足，排序落盘。

5. 典型慢查询模式与优化

症状	常见原因	优化手段
大表 Seq Scan	缺索引/索引失效	建索引、修正 SQL（见索引优化策略）
估算严重偏离实际	统计过期	`ANALYZE`，或提高统计目标
排序落盘（Disk）	work_mem 不足	会话级调大 work_mem，或加索引消除排序
`SELECT *` 拉太多列	未走覆盖索引、传输大	只查需要的列
深分页 `OFFSET 100000`	扫描并丢弃大量行	改用游标/键集分页（WHERE id > 上次最大id）
N+1 查询	应用循环里逐条查	改批量 IN 查询或 JOIN
隐式类型转换	索引失效	匹配参数类型

5.1 深分页优化示例

-- ❌ 慢：OFFSET 越大越慢
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;

-- ✅ 键集分页（Keyset Pagination）：记住上一页最后一个id
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

6. 优化流程总结

pg_stat_statements 找出 total_exec_time 最高的 SQL。
对该 SQL 跑 EXPLAIN (ANALYZE, BUFFERS) 看执行计划。
判断瓶颈：缺索引？统计过期？排序落盘？SQL 写法差？
针对性优化（建索引、改 SQL、ANALYZE、调 work_mem）。
再次 EXPLAIN ANALYZE 验证，对比前后耗时。
上线后持续用 pg_stat_statements 监控。

7. 面试高频问答

Q1：怎么找出系统里最该优化的 SQL？ 用 pg_stat_statements 按 total_exec_time 排序——累计耗时最高的最值得优化（可能单次不慢但调用极频繁），而不是只看单次最慢的。

Q2：pg_stat_statements 和慢查询日志的区别？ 慢日志按阈值记录到文件、需事后解析；pg_stat_statements 在内存实时聚合规范化 SQL 的调用次数、总/平均耗时、IO 等，更适合找热点。两者互补。

Q3：一条查询突然变慢，怎么排查？ 先 EXPLAIN ANALYZE 看是否走了索引、估算与实际行数是否偏离（统计过期）、是否排序落盘；再看 pg_stat_activity 是否被锁阻塞。

Q4：EXPLAIN 里估算行数和实际差很多说明什么？ 统计信息过期或数据分布特殊，导致优化器选错计划。执行 ANALYZE 更新统计，必要时 ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 提高采样精度。

Q5：深分页（大 OFFSET）为什么慢？怎么优化？ OFFSET 需扫描并丢弃前面所有行，越翻越慢。改用键集分页：记住上一页最后一条的排序键，用 WHERE key > last_key 定位，配合索引可稳定高效。

8. 下一步

学习分区表技术，用分区应对超大表。

目录