如何用 mysqldumpslow / pt-query-digest 分析慢查询日志？

考察要点

这道题面试官想看你是否具备线上数据库性能问题的定位和分析能力。具体来说，考察以下三点：

1. 工具熟练度：你是否知道并会使用 MySQL 慢查询分析的标准工具，特别是 mysqldumpslow 和 pt-query-digest。
2. 分析方法论：你是否理解分析慢查询的正确思路，即从聚合统计入手，定位“总耗时最长”或“调用次数最多”的查询类别，而不是仅仅关注单次执行最慢的个例。
3. 深度和广度：你是否了解不同工具的优劣和适用场景，并能将慢查询分析与 EXPLAIN、索引优化等后续步骤关联起来，形成一个完整的性能优化闭环。

核心概念

1. 慢查询日志 (Slow Query Log)

慢查询日志是 MySQL 提供的一种日志记录，用于记录执行时间超过指定阈值的 SQL 语句。它是性能优化的重要数据来源。

核心参数：

2. 查询归一化 (Query Normalization / Fingerprinting)

这是慢查询分析的核心技术。它将具体值（如 id = 100，name = 'Alice'）替换为占位符（id = ?，name = ?），使得结构相同但参数不同的 SQL 语句可以被归为一类进行统计。

数据示例： 原始日志中的两条慢查询：

 1SELECT * FROM users WHERE id = 123; -- took 1.5s
 2SELECT * FROM users WHERE id = 456; -- took 1.8s

归一化后的查询模式 (Fingerprint):

 1SELECT * FROM users WHERE id = ?;

这样，分析工具就可以统计出 SELECT * FROM users WHERE id = ? 这类查询总共执行了 2 次，总耗时 3.3s，平均耗时 1.65s。

3. mysqldumpslow

MySQL 官方自带的、轻量级的慢查询日志分析工具。它功能相对简单，主要对查询进行归一化处理并排序。

• 优点：无需额外安装，开箱即用。
• 缺点：功能单一，信息维度少，归一化能力较弱，对于复杂的查询分析能力不足。

4. pt-query-digest

Percona Toolkit (一个强大的开源数据库工具集) 中的明星工具，是业界分析 MySQL 慢查询日志的事实标准。

• 优点：
  • 强大的归一化引擎：能准确识别各类查询模式。
  • 丰富的统计指标：提供总时间、平均时间、最大/最小时间、95% 分位时间、总锁等待时间等。
  • 灵活的输入源：不仅能分析慢查询日志，还能分析 general_log、binlog，甚至实时抓取 PROCESSLIST 或网络包 (tcpdump)。
  • 详细的报告：为每个查询类别生成详细的报告，包含统计信息、执行计划示例 (EXPLAIN) 和原始 SQL 示例。
• 缺点：需要额外安装 Percona Toolkit。

工具对比

常见低分回答

1. 只会手动 grep 和 sort 日志文件：这种原始方法无法对查询进行归一化，导致同一个慢查询模式因为参数不同而被当成无数个独立的查询，完全无法定位核心问题。
2. 只知道 mysqldumpslow，不知道 pt-query-digest：这表明候选人经验可能局限于简单的运维场景，缺乏处理复杂线上性能问题的经验。大厂几乎全部使用 pt-query-digest 或类似的自研系统。
3. 混淆分析指标，只关注单次最慢的查询：在 pt-query-digest 报告中，默认按 Query_time:sum (总耗时) 排序。低分回答会错误地只看 Query_time:max (单次最大耗时)，而忽略了那些虽然单次不慢、但调用频率极高导致总体资源消耗巨大的“千刀万剐”型 SQL。优化后者往往收益更大。
4. 不知道如何开启慢查询日志或相关参数：这是基础知识的缺失，如果连数据源如何获取都不知道，后续分析无从谈起。
5. 分析完后，不知道下一步该做什么：找到慢查询只是第一步。一个完整的回答应该包括后续使用 EXPLAIN 分析执行计划、并提出索引优化或 SQL 改写建议的闭环。

工程实践与优化

在生产环境中，我们遵循一套标准的慢查询分析流程：

Step 1: 开启并采集慢查询日志 确保 slow_query_log=ON，并设置合理的 long_query_time (例如 0.5s-1s)。对于需要全面分析的场景，可以临时将 long_query_time 设为 0，采集一段时间（如 10 分钟）的全量查询，然后立即恢复原值，以避免对磁盘 IO 造成持续压力。

Step 2: 使用工具进行分析

• 使用 mysqldumpslow (快速预览)

  bash

  ⧉⛶

   1# 找出总耗时最长的前 10 个 SQL
   2mysqldumpslow -s t -t 10 /var/lib/mysql/hostname-slow.log
   3
   4# 找出平均查询时间最长的前 10 个 SQL
   5mysqldumpslow -s at -t 10 /var/lib/mysql/hostname-slow.log
   6
   7# 找出包含 "JOIN" 并且按照总耗时排序的前 10 个 SQL
   8mysqldumpslow -s t -t 10 -g "JOIN" /var/lib/mysql/hostname-slow.log

  • -s: 排序方式（t=总时间, at=平均时间, c=次数）
  • -t: 返回前 N 条
  • -g: 按正则表达式匹配

• 使用 pt-query-digest (专业分析) - 强烈推荐

  bash

  ⧉⛶

   1# 对慢查询日志进行完整分析，并输出到 report.txt
   2pt-query-digest /var/lib/mysql/hostname-slow.log > report.txt
   3
   4# 分析最近 1 小时的慢查询日志
   5pt-query-digest --since '1h' /var/lib/mysql/hostname-slow.log > report_last_1h.txt
   6
   7# 从 processlist 实时分析，持续 60 秒
   8pt-query-digest --processlist u=root,p=password --run-time 60s > report_processlist.txt

Step 3: 解读 pt-query-digest 报告 报告主要分为两部分：

1. 总体统计 (Overall summary)：服务器版本、运行时间、总查询数、QPS 等。
2. 查询组排名 (Ranked query groups)：这是核心。默认按总耗时 (Query_time:sum) 降序排列。

报告片段示例：

 1# Rank 1: 99.9% (3555) of query time, 99.8% (2002) of all queries
 2# Query ID: 0xDEADBEEFCAFE
 3# Query_time distribution
 4#   count | 2002
 5#   ...
 6#   sum   | 3555
 7#   min   | 1
 8#   max   | 5
 9#   avg   | 1.7
10#   95%   | 3.2
11# Tables:
12#   SHOW TABLE STATUS LIKE 'users'\G
13#   EXPLAIN /*!50100 PARTITIONS*/
14SELECT email, name FROM users WHERE id = ?\G

解读要点：

• Rank 1: 这是最消耗资源的查询类型。
• Query_time: sum: 该类型查询的总耗时，是首要关注点。
• count: 执行总次数。
• avg: 平均耗时。
• 95%: 95% 的查询都低于这个耗时，有助于判断是否存在偶发的性能抖动。
• EXPLAIN ...: 工具会自动抓取一条该类型查询的执行计划，这是优化的直接依据。
• SELECT ...: 归一化后的 SQL 语句。

Step 4: 制定并执行优化方案 根据 pt-query-digest 报告中排名靠前的 SQL 及其 EXPLAIN 结果，进行针对性优化：

• Extra 字段出现 Using filesort, Using temporary：考虑添加或修改索引，避免排序和临时表。
• type 字段为 ALL 或 index：全表扫描或全索引扫描，检查 WHERE 条件列是否有索引。
• rows 字段过大：扫描行数过多，检查索引选择性 (Cardinality) 是否过低。
• SQL 改写：避免在 WHERE 子句中使用函数、优化 JOIN 顺序、用 UNION ALL 代替 OR 等。

常见追问与延伸题目

1. 追问：如果开启慢查询日志导致服务器 IO 压力过大，有什么解决方案？

   • 回答要点 1 (调整阈值)：适当调高 long_query_time，比如从 0.1s 调整到 1s，可以显著减少日志写入量，但这会丢失部分优化线索。
   • 回答要点 2 (采样记录)：在支持的 MySQL 分支（如 MariaDB 的 log_slow_rate_limit 或 Percona Server 的 log_slow_verbosity）中，可以配置采样率，比如只记录 10% 的慢查询。
   • 回答要点 3 (切换分析源)：使用 pt-query-digest 直接分析 tcpdump 抓取的网络包，或者实时分析 SHOW FULL PROCESSLIST。这种方式不产生磁盘 IO，但 tcpdump 会消耗 CPU，PROCESSLIST 采样可能丢失执行很快的查询。
   • 回答要点 4 (利用 Performance Schema)：对于 MySQL 5.7+，可以利用 Performance Schema。它在内存中记录查询统计信息，IO 开销远小于慢查询日志。pt-query-digest 也支持从 Performance Schema 的 events_statements_summary_by_digest 表中读取数据进行分析，这是现代 MySQL 中更推荐的方式。

2. 追问：你刚才提到 pt-query-digest 报告里的 95% 分位时间，这个指标有什么用？

   • 回答要点 1 (识别抖动)：平均值 (avg) 容易被极端值（极大或极小）影响，无法真实反映普遍情况。95% 分位时间（95th percentile）表示 95% 的查询执行时间都低于此值。如果 95% 分位时间远小于最大值 (max)，但与平均值 (avg) 接近，说明大部分查询性能稳定，只有少数请求是“毛刺”，可能由缓存失效、锁竞争等偶发因素导致。
   • 回答要点 2 (定义 SLA/SLO)：在定义服务等级协议 (SLA) 时，95% 或 99% 分位时间是比平均值更可靠的指标。例如，我们可以承诺“95% 的用户查询在 200ms 内返回”，这比承诺“平均响应时间 50ms” 对用户体验的保障更强。

3. 追问：除了 pt-query-digest，你还知道哪些其他的数据库性能监控或诊断工具？

   • 回答要点 1 (官方工具)：MySQL Enterprise Monitor 提供了图形化的性能监控和诊断界面。Performance Schema 和 Sys Schema 提供了丰富的内置视图，可以直接查询锁等待、文件 IO、内存使用等详细信息。
   • 回答要点 2 (开源社区)：Prometheus + Grafana 组合是目前主流的监控方案，通过 mysqld_exporter 采集 MySQL 的状态变量和性能指标，实现可视化监控和告警。go-mysql-transfer、Maxwell 等工具可以订阅 binlog 进行实时分析。
   • 回答要点 3 (云厂商)：如果是使用云数据库（如 AWS RDS, 阿里云 PolarDB），它们通常都内置了强大的性能洞察 (Performance Insights) 功能，可以图形化地展示 Top SQL、等待事件和资源消耗，是云环境下的首选。