📚 课程目录第3章 MySQL慢查询日志分析

3. MySQL 慢查询日志分析：开启慢查询日志、使用 mysqldumpslow 工具、pt-query-digest 实战

慢查询日志是 MySQL 提供的核心诊断工具，用于记录执行时间超过指定阈值的 SQL 语句。在报表导出场景中，慢查询日志能精准定位导致页面超时或数据库 CPU 飙升的“罪魁祸首”。本章将系统讲解慢查询日志的开启配置、原生分析工具 mysqldumpslow 的使用方法，以及业界最专业的分析利器 pt-query-digest 的实战技巧。

3.1 开启慢查询日志

慢查询日志默认关闭，需要手动启用。推荐同时开启 log_queries_not_using_indexes，以捕获未走索引的全表扫描查询。

3.1.1 动态开启（无需重启）

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;

-- 设置慢查询阈值（单位：秒，建议报表系统设为 1 秒）
SET GLOBAL long_query_time = 1;

-- 指定日志文件路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;

3.1.2 配置文件持久化（my.cnf）

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1   # 记录 ALTER TABLE 等管理语句
min_examined_row_limit = 100    # 仅记录扫描行数超过 100 的查询

生产建议：报表导出系统建议将 long_query_time 设为 0.5~1 秒，避免漏掉大量聚合查询。同时监控日志文件大小，避免磁盘写满。

3.2 使用 mysqldumpslow 工具

mysqldumpslow 是 MySQL 自带的慢查询日志汇总工具，能够对日志中的 SQL 进行分组、排序和统计，快速找出“最慢”或“出现次数最多”的查询模式。

3.2.1 常用参数

参数	说明	示例
`-s c`	按查询次数排序	`mysqldumpslow -s c`
`-s t`	按查询总耗时排序	`mysqldumpslow -s t`
`-s at`	按平均耗时排序	`mysqldumpslow -s at`
`-t N`	只显示前 N 条	`mysqldumpslow -t 10`
`-g pattern`	正则过滤 SQL 内容	`mysqldumpslow -g "SELECT"`

3.2.2 实战命令

# 查看总耗时最高的前 5 条查询
mysqldumpslow -s t -t 5 /var/log/mysql/mysql-slow.log

# 查看出现次数最多的前 10 条查询，并过滤包含 "report_export" 的表
mysqldumpslow -s c -t 10 -g "report_export" /var/log/mysql/mysql-slow.log

# 输出平均查询时间最长的查询，并显示完整 SQL
mysqldumpslow -s at -t 3 --verbose /var/log/mysql/mysql-slow.log

  输出示例解读：

  Count: 123  Time=2.34s (287s)  Lock=0.01s (1s)  Rows=5000.0 (615000)

  表示该 SQL 出现了 123 次，平均耗时 2.34 秒，总耗时 287 秒，平均扫描 5000 行。报表导出中若出现此类记录，说明该查询需要优先优化。

3.3 pt-query-digest 实战

pt-query-digest 是 Percona Toolkit 中的明星工具，比 mysqldumpslow 更强大：支持多维度分析、输出 HTML/JSON 报告、自动识别查询指纹、提供优化建议。它是报表慢查询调优的“标配”。

3.3.1 安装与基本用法

# 安装 Percona Toolkit（以 CentOS 为例）
yum install percona-toolkit

# 分析慢查询日志，输出到终端
pt-query-digest /var/log/mysql/mysql-slow.log

# 输出 HTML 报告，便于可视化分析
pt-query-digest --output html /var/log/mysql/mysql-slow.log > slow_report.html

# 分析最近 1 小时的慢查询（通过 tcpdump 抓包）
tcpdump -s 65535 -x -nn -q -tttt -i any port 3306 > mysql_tcp.log
pt-query-digest --type tcpdump mysql_tcp.log

3.3.2 核心输出解读

pt-query-digest 的报告分为三个主要部分：

Overall Statistics（全局统计）：总查询数、总耗时、唯一查询指纹数、时间范围。
Profile（排名列表）：按总耗时或平均耗时排序的查询指纹列表，包含响应时间占比、调用次数、平均行数等。
Query Detail（查询详情）：每个查询指纹的完整 SQL 样例、执行计划建议、表结构信息。

指标	含义	报表导出关注点
Response time (s)	该查询的总响应时间	占比超过 30% 的查询必须优化
Calls	该查询被调用的次数	高频低耗时查询也可能造成累积压力
R/Call (s)	每次调用的平均耗时	超过 1 秒即需关注
Rows/Call	每次调用扫描/返回的行数	超过 1000 行需检查索引
% of total	占总响应时间的百分比	前 5 条通常占 80% 以上资源

3.3.3 高级过滤与定制分析

# 只分析涉及特定表的查询
pt-query-digest --filter '$event->{db} eq "report_db" && $event->{arg} =~ m/report_export/i' /var/log/mysql/mysql-slow.log

# 忽略小于 0.5 秒的查询，聚焦严重慢查询
pt-query-digest --min-time 0.5 /var/log/mysql/mysql-slow.log

# 输出 JSON 格式，便于导入监控系统
pt-query-digest --output json /var/log/mysql/mysql-slow.log > slow_analysis.json

避坑提示：在生产环境直接分析大日志文件（超过 1GB）时，建议先使用 head -n 10000 截取样本，或使用 --limit 100 限制输出行数，避免工具长时间阻塞磁盘 I/O。

3.4 慢查询分析流程对比

3.5 实战案例：报表导出慢查询定位

某报表系统导出功能响应时间从 3 秒飙升到 30 秒，通过 pt-query-digest 分析发现以下 TOP 1 查询：

# Query 1: 0.00% 占比，但总耗时 85.2%
# Response time: 256.7s  Calls: 12  R/Call: 21.39s
SELECT
  o.order_id, o.order_date, o.total_amount,
  c.customer_name, c.region,
  p.product_name, od.quantity, od.unit_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-12-31'
  AND c.region = '华东'
ORDER BY o.order_date DESC;

  分析结论：

  • 该查询扫描了 120 万行，返回 8 万行，缺少 order_date 和 region 的联合索引。

  • 优化方案：创建索引 idx_order_region (order_date, region)，并将查询改为分页导出。

  • 优化后单次查询耗时从 21 秒降至 0.8 秒，导出总时间从 30 秒降至 5 秒。

3.6 本章小结

开启慢查询日志是调优的第一步，建议设置 long_query_time=1 并开启 log_queries_not_using_indexes。
mysqldumpslow 适合快速查看 TOP N 慢查询，轻量无依赖。
pt-query-digest 是深度分析的利器，支持多维排名、HTML 报告、tcpdump 抓包分析。
报表导出场景中，重点关注 总耗时占比高 和 扫描行数大 的查询，优先优化联合索引和分页逻辑。