如何获取PostgreSQL慢查询？从小白到高手的实战指南
数据库优化是性能调优的核心，而慢查询则是性能瓶颈的罪魁祸首。如何找到慢查询并优化它们，是每个开发者和DBA都必须掌握的技能。
今天，我们就来聊聊如何在PostgreSQL中快速获取慢查询日志，并结合不同场景进行分析优化。本文风格参考阮一峰老师，简洁明了，人人都能看懂。
一、什么是慢查询？
慢查询就是那些执行时间超出预期，可能导致数据库响应变慢的SQL语句。找到它们，就像在找系统性能问题的“黑洞”。
PostgreSQL提供了多种方法来捕捉慢查询，从日志分析到系统自带的性能视图，一应俱全。
二、启用慢查询日志
1. 修改postgresql.conf
要想让PostgreSQL记录慢查询日志，首先要启用相关配置。你需要修改postgresql.conf文件中的以下几项：
# 启用日志记录
log_min_duration_statement = 1000 # 单位为毫秒，记录超过1秒的查询
log_statement = 'all' # 可选项：none、ddl、mod、all
这段配置的含义是：记录所有超过1秒的SQL语句。你可以根据实际情况调整log_min_duration_statement的阈值，比如500ms甚至100ms。
2. 重启PostgreSQL
修改配置后，需要重启服务以使配置生效：
sudo systemctl restart postgresql
3. 验证日志输出
执行一条故意耗时的查询：
SELECT pg_sleep(2); -- 模拟2秒查询
然后查看PostgreSQL日志文件，应该能看到类似的输出：
2024-11-14 10:00:00.123 CST [12345] LOG: duration: 2003.123 ms statement: SELECT pg_sleep(2);
日志中清楚地记录了查询耗时和SQL语句。
三、场景1：查询优化，手动排查慢SQL
1. 使用EXPLAIN分析查询计划
发现慢查询后，第一步是分析它的执行计划：
EXPLAIN ANALYZE SELECT * FROM large_table WHERE column = 'value';
输出示例：
Seq Scan on large_table (cost=0.00..431.00 rows=10000 width=12) (actual time=0.123..10.456 rows=100 loops=1)
从结果中可以看到，顺序扫描(Seq Scan) 是导致查询慢的原因。
2. 添加索引
优化方案之一是给large_table的column列添加索引：
CREATE INDEX idx_large_table_column ON large_table(column);
再执行查询，性能将显著提升。
四、场景2：动态分析，使用pg_stat_statements
对于实时监控，我们可以使用PostgreSQL自带的扩展：pg_stat_statements。
1. 安装和启用扩展
在postgresql.conf中启用：
shared_preload_libraries = 'pg_stat_statements'
然后执行以下SQL命令来创建扩展：
CREATE EXTENSION pg_stat_statements;
2. 查询慢SQL统计信息
通过以下SQL获取最耗时的查询：
SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;
输出示例：
| Query | Total Time | Calls | Mean Time |
|---|---|---|---|
| SELECT * FROM large_table WHERE column… | 5000 ms | 10 | 500 ms |
| INSERT INTO orders … | 3000 ms | 5 | 600 ms |
这个方法可以快速定位执行最频繁、耗时最高的SQL。
五、场景3：自动化分析，结合开源工具
在大型系统中，手动分析慢查询往往效率低下。推荐使用pgBadger，一个开源的日志分析工具。
1. 安装pgBadger
使用以下命令安装pgBadger：
sudo apt install pgbadger
2. 分析日志文件
假设PostgreSQL日志文件存储在/var/log/postgresql/postgresql.log：
pgbadger /var/log/postgresql/postgresql.log -o report.html
pgBadger会生成一个HTML报告，包含详细的慢查询统计和性能分析。
六、总结
从日志捕捉到实时监控，再到自动化分析，PostgreSQL为我们提供了多种获取慢查询的方法。希望本文能帮助大家快速定位并优化慢SQL。
优化性能，从慢查询开始！