4. MySQL 慢查询日志：配置、解读与分析工具（pt-query-digest）
慢查询日志是 MySQL 数据库性能诊断的“黑匣子”。当 SQL 执行时间超过预设阈值时，MySQL 会将完整的语句、执行时间、锁等待、扫描行数等信息记录到日志文件中。本章将深入讲解如何正确配置慢查询日志、解读日志中的关键指标，以及使用业界标准工具 pt-query-digest 进行高效分析。
4.1 慢查询日志的配置与最佳实践
慢查询日志的配置涉及多个系统变量，错误的配置可能导致日志文件过大、性能下降或关键 SQL 被遗漏。以下为生产环境推荐配置：
| 参数名 | 推荐值 | 说明 |
|---|---|---|
slow_query_log |
ON |
开启慢查询日志 |
slow_query_log_file |
/var/log/mysql/slow-query.log |
日志文件路径，建议与数据文件分离 |
long_query_time |
0.5 (秒) |
执行时间超过 500ms 的 SQL 被记录 |
log_queries_not_using_indexes |
ON |
记录未使用索引的查询（即使执行很快） |
log_slow_admin_statements |
ON |
记录慢管理语句（如 ALTER TABLE） |
log_slow_slave_statements |
ON |
从库上记录慢复制语句 |
min_examined_row_limit |
1000 |
扫描行数超过此值才记录，过滤小查询 |
生产建议： 在低峰期开启
log_queries_not_using_indexes，避免日志写入压力过大。对于高并发业务，建议将 long_query_time 设置为 0.5~1 秒，并配合 pt-query-digest 定期轮转日志。
4.2 慢查询日志格式深度解读
一条完整的慢查询记录包含以下关键字段，理解这些字段是定位问题的前提：
# Time: 2025-03-15T10:23:45.123456Z
# User@Host: go_app[go_app] @ [10.0.1.100] Id: 45231
# Query_time: 2.345678 Lock_time: 0.000123 Rows_sent: 10 Rows_examined: 500000
# Thread_id: 45231 Schema: order_db Last_errno: 0 Killed: 0
# Bytes_sent: 1234 Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# InnoDB_trx_id: 1234567
# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: Yes Tmp_table_on_disk: No
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0
# InnoDB_IO_r_ops: 150 InnoDB_IO_r_bytes: 2457600 InnoDB_IO_r_wait: 0.001234
# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000
# InnoDB_pages_distinct: 350
SET timestamp=1742027025;
SELECT o.id, o.order_no, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 10;| 字段 | 含义 | 诊断价值 |
|---|---|---|
Query_time |
SQL 实际执行时间（秒） | 核心指标，超过阈值即为慢查询 |
Lock_time |
锁等待时间（秒） | 高值表示锁竞争严重 |
Rows_examined |
扫描行数 | 远大于 Rows_sent 时，索引缺失或查询效率低 |
Rows_sent |
返回行数 | 与 Rows_examined 对比，评估过滤效率 |
Full_scan |
是否全表扫描 | Yes 表示未使用索引 |
Filesort |
是否使用文件排序 | Yes 表示排序未使用索引，可能消耗大量内存 |
Tmp_table |
是否使用临时表 | Yes 表示 GROUP BY 或 DISTINCT 效率低 |
InnoDB_IO_r_ops |
InnoDB 读 IO 操作次数 | 高值表示磁盘 IO 压力大 |
常见误区： 不要只看 Query_time。一个 Query_time 为 0.1 秒但 Rows_examined 为 100 万的查询，在并发 1000 时会导致 CPU 和 IO 飙升。必须结合 Rows_examined、Full_scan、Filesort 等字段综合判断。
4.3 慢查询日志分析流程
分析慢查询日志应遵循以下步骤，避免盲目优化：
- 收集原始日志：使用
mysqldumpslow或pt-query-digest聚合分析。 - 识别 TOP N 查询：按总耗时、平均耗时、执行次数排序。
- 提取查询指纹：将参数替换为占位符，聚合相同结构的 SQL。
- 分析执行计划：对 TOP 查询执行
EXPLAIN，检查索引使用情况。 - 定位根因：区分是索引缺失、数据量增长、锁竞争还是配置问题。
- 制定优化方案：添加索引、改写 SQL、调整连接池参数或分库分表。
- 验证效果：再次分析日志，确认 Query_time 和 Rows_examined 下降。
工程案例： 某 Go 项目订单查询接口响应时间从 200ms 飙升到 3s。通过 pt-query-digest 分析发现，一条
SELECT * FROM orders WHERE status = ? ORDER BY created_at DESC LIMIT 10 的 Rows_examined 从 5000 增长到 200 万。原因是 status 字段区分度低，MySQL 选择了全表扫描。解决方案：创建 (status, created_at) 联合索引，查询时间降至 15ms。
4.4 pt-query-digest 使用详解
pt-query-digest 是 Percona Toolkit 中最强大的慢查询分析工具。它能自动聚合、排序、输出报告，并支持多种输出格式。
4.4.1 安装与基本用法
# 安装 Percona Toolkit（Ubuntu）
sudo apt-get install percona-toolkit
# 分析慢查询日志文件
pt-query-digest /var/log/mysql/slow-query.log
# 分析实时慢查询（从 MySQL 进程列表抓取）
pt-query-digest --processlist h=localhost,u=root,p=your_password
# 分析 tcpdump 抓取的 MySQL 流量
pt-query-digest --type tcpdump /tmp/mysql.pcap
# 输出到指定文件
pt-query-digest /var/log/mysql/slow-query.log > /tmp/analysis.txt4.4.2 输出报告解读
pt-query-digest 的输出分为三个主要部分：
| 报告部分 | 内容 | 关注点 |
|---|---|---|
| Overall | 总查询数、唯一查询数、总耗时、平均耗时、时间范围 | 整体负载情况，判断是否需要立即优化 |
| Profile | 按总耗时排序的查询指纹列表，包含响应时间占比、执行次数、平均耗时 | 快速定位 TOP N 问题查询 |
| Query details | 每个查询指纹的详细统计：执行时间分布、锁时间、扫描行数、示例 SQL | 深入分析单个查询的性能特征 |
高效使用技巧： 使用
--limit 10 只输出 TOP 10 查询；使用 --filter '$event->{Rows_examined} > 10000' 过滤扫描行数超过 1 万的查询；使用 --since '2025-03-15 10:00:00' --until '2025-03-15 11:00:00' 分析特定时间窗口。
4.5 慢查询日志分析工具对比
| 工具 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|
mysqldumpslow |
MySQL 自带，无需安装 | 功能简单，不支持复杂过滤和输出格式 | 快速查看 TOP 查询 |
pt-query-digest |
功能强大，支持多种输入源，输出报告详细 | 需要安装 Percona Toolkit | 生产环境深度分析 |
mysqlsla |
轻量级，支持多种日志格式 | 已停止维护，功能有限 | 旧系统兼容 |
Percona Monitoring and Management (PMM) |
可视化界面，历史趋势，告警 | 需要部署服务端，资源消耗较大 | 企业级监控平台 |
4.6 慢查询日志与连接池的关联
慢查询日志不仅帮助优化 SQL，还能间接反映连接池的健康状况：
- 连接池耗尽：当慢查询阻塞连接时，连接池中的连接被长时间占用，导致新请求排队等待。此时慢查询日志中会出现大量
Query_time正常但Lock_time异常的查询。 - 连接泄漏：如果 Go 应用未正确归还连接，慢查询日志中会出现来自同一
Thread_id的连续慢查询，且连接数持续增长。 - 事务超时：长事务导致的慢查询会在日志中显示
InnoDB_trx_id长时间未提交，配合SHOW ENGINE INNODB STATUS可定位事务源头。
排查思路： 当连接池监控显示活跃连接数持续高位时，立即分析慢查询日志。如果发现大量
Rows_examined 极高的查询，优先优化 SQL；如果发现大量 Lock_time 高的查询，优先排查锁竞争和事务隔离级别。
4.7 本章小结
- 慢查询日志是 MySQL 性能诊断的基石，正确配置
long_query_time和log_queries_not_using_indexes至关重要。 - 解读日志时，不能只看
Query_time，必须结合Rows_examined、Full_scan、Filesort等字段综合判断。 pt-query-digest是生产环境最推荐的慢查询分析工具，支持多种输入源和灵活的过滤条件。- 慢查询日志与连接池监控联动，可以快速定位是 SQL 问题还是连接池配置问题。
- 优化流程应遵循“收集 → 分析 → 定位 → 优化 → 验证”的闭环，避免盲目添加索引或调整参数。