慢查询识别与优化实战
从生产数据库的监控告警到定位根因、实施修复——这一节讲系统性的慢查询处理流程，以及最常见的慢查询模式和修复方案。
第一步：找到慢查询
方法一：pg_stat_statements（推荐）
pg_stat_statements 记录所有执行过的查询的统计信息，是生产环境发现慢查询的首选工具。
-- 启用扩展（需要在 postgresql.conf 中配置 shared_preload_libraries）
-- 在 postgresql.conf 中添加：
-- shared_preload_libraries = 'pg_stat_statements'
-- 然后重启 PostgreSQL
-- 创建扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 查找执行时间最长的 TOP 10 查询
SELECT
LEFT(query, 100) AS query_sample,
calls,
round(total_exec_time::numeric / calls, 2) AS avg_ms,
round(total_exec_time::numeric, 0) AS total_ms,
rows,
round(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY avg_ms DESC
LIMIT 10;
-- 重置统计数据（发布新代码后重置，观察新版本的查询性能）
SELECT pg_stat_statements_reset();
方法二：慢查询日志
# postgresql.conf 中开启慢查询日志
log_min_duration_statement = 1000 # 记录超过 1000ms 的查询（单位毫秒）
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
然后 grep 慢查询日志：
# 查找超过 5 秒的查询
grep "duration:" /var/log/postgresql/postgresql-2024-01-15.log | \
awk '$3 > 5000' | sort -t' ' -k3 -rn | head -20
最常见的慢查询模式和修复
模式 1：缺少索引（最常见）
-- 症状：EXPLAIN 显示 Seq Scan on 大表
-- 诊断：
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';
-- → Seq Scan on orders (actual time=0.017..892.234 rows=12 ...)
-- 修复：添加复合索引
CREATE INDEX CONCURRENTLY idx_orders_user_status
ON orders (user_id, status);
-- 验证：再次运行 EXPLAIN ANALYZE
-- → Index Scan using idx_orders_user_status on orders
-- (actual time=0.087..0.312 rows=12 ...)
模式 2：WHERE 子句中函数导致索引失效
-- ❌ 慢：函数调用导致索引失效
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-15';
SELECT * FROM users WHERE EXTRACT(year FROM created_at) = 2024;
-- ✅ 修复方法一：重写查询，避免对列使用函数
SELECT * FROM users WHERE email = 'alice@example.com'; -- 应用层统一转小写
SELECT * FROM orders
WHERE created_at >= '2024-01-15' AND created_at < '2024-01-16';
-- ✅ 修复方法二：函数索引（当无法修改查询时）
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
-- 查询必须写成：WHERE LOWER(email) = LOWER('Alice@Example.com')
模式 3：N+1 查询问题
-- ❌ N+1：在应用层循环查询，每次取一条相关数据
-- Python/Node 代码：
-- for order in orders:
-- user = db.query("SELECT * FROM users WHERE id = ?", order.user_id)
-- ✅ 修复：一次性 JOIN 获取所有数据
SELECT o.*, u.email, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending';
-- 或批量 IN 查询
SELECT * FROM users WHERE id IN (1, 2, 3, ..., 100);
模式 4：ORDER BY 导致的 filesort
-- 症状：EXPLAIN 中出现 "Sort Method: external merge Disk"（磁盘排序）
EXPLAIN ANALYZE SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;
-- 修复：为 ORDER BY 列添加索引（降序排序）
CREATE INDEX idx_orders_created_desc ON orders (created_at DESC);
-- 对于分页查询，还要避免 OFFSET 大数值
-- ❌ 慢：OFFSET 100000 要跳过 10 万行
SELECT * FROM orders ORDER BY id DESC LIMIT 20 OFFSET 100000;
-- ✅ 键集分页（Keyset Pagination）
SELECT * FROM orders
WHERE id < :last_seen_id -- 上一页最后一条的 id
ORDER BY id DESC
LIMIT 20;
模式 5：JOIN 效率低（统计信息不准导致错误执行计划）
-- 症状：EXPLAIN 显示 Hash Join 但实际是小数据集
-- 或者：使用了 Nested Loop 但内表没有索引
-- 更新统计信息
ANALYZE orders;
ANALYZE users;
-- 如果统计信息准确但计划仍然不佳，可以提示优化器
SET enable_seqscan = off; -- 临时禁用 Seq Scan（调试用）
SET enable_nestloop = off; -- 临时禁用 Nested Loop
-- 增加统计信息采样率（高选择性列）
ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 500; -- 默认 100
ANALYZE orders;
完整的慢查询处理流程
graph TD
A["收到慢查询告警\n或用户报告变慢"] --> B["在 pg_stat_statements\n找到 TOP 慢查询"]
B --> C["在测试环境复现\n用 EXPLAIN ANALYZE"]
C --> D{"识别慢的节点"}
D --> |Seq Scan 大表| E["添加索引\nCREATE INDEX CONCURRENTLY"]
D --> |函数导致索引失效| F["重写 WHERE 条件\n或创建函数索引"]
D --> |Sort / Disk sort| G["添加排序列索引\n或增加 work_mem"]
D --> |估计行数偏差大| H["ANALYZE 表\n或调整 statistics 参数"]
D --> |N+1 查询| I["改为 JOIN 或批量 IN\n（应用层修改）"]
E & F & G & H & I --> J["验证：再次运行 EXPLAIN ANALYZE\n确认改进"]
J --> K["在生产环境使用 CONCURRENTLY\n索引不锁表"]
K --> L["监控 pg_stat_statements\n观察改进效果"]
work_mem 调优技巧
work_mem 控制每个排序操作或哈希操作可用的内存，太小会导致磁盘排序：
-- 查看当前 work_mem
SHOW work_mem; -- 默认 4MB，通常偏小
-- 临时提高 work_mem 解决 Disk Sort（只对当前会话生效）
SET work_mem = '64MB';
EXPLAIN ANALYZE SELECT * FROM big_table ORDER BY sort_column LIMIT 1000;
-- 如果 "Sort Method" 变为 "quicksort Memory" 则说明内存够了
-- 在 postgresql.conf 中全局设置（谨慎：max_connections × work_mem 是内存上限）
work_mem = 16MB -- 通常 16MB-64MB 是合理范围
本节记录清单
- [ ] 在 postgresql.conf 中配置
pg_stat_statements和慢查询日志 - [ ] 用
pg_stat_statements找到你的 TOP 5 慢查询 - [ ] 对每个慢查询运行 EXPLAIN ANALYZE，识别慢的模式
- [ ] 修复至少一个慢查询，验证性能改善
下一章：复杂查询：CTE、窗口函数与子查询——索引优化搞定了基础性能，接下来学习 PostgreSQL 的高级查询功能，用一条 SQL 解决以前需要多次查询或应用层处理的复杂问题。