`pt-digest-query` 对生产环境慢查询日志的聚合分析与建议

好嘞！各位观众老爷，掌声响起来！今天咱们不聊风花雪月，也不谈人生理想，就来唠嗑唠嗑这生产环境里让人头疼的“慢性病”——慢查询！更要请出我们的“御医”—— pt-digest-query，好好给他把把脉，看看它如何妙手回春，让咱们的数据库重焕青春！

开场白：慢查询，数据库的“隐形杀手”

话说这数据库啊，就像咱们的身体，平时吃嘛嘛香，干活倍儿精神。可一旦遇上个慢查询，那就好比身体里长了个小肿瘤，一开始不痛不痒，让你觉得一切正常。可时间一长，这肿瘤越长越大，开始挤压器官，影响血液循环，最后整个身体都垮掉了！

慢查询就是数据库的“隐形杀手”，它会悄无声息地消耗资源，拖慢响应速度，甚至导致整个系统崩溃！想想看，用户兴致勃勃地打开网页，结果转啊转啊转，半天刷不出来，这体验简直糟糕透顶！用户分分钟给你一个差评，然后头也不回地投奔竞争对手的怀抱！

所以啊，治理慢查询，刻不容缓！就像咱们体检一样，定期检查，早发现，早治疗，才能防患于未然。

第一章：认识pt-digest-query——“慢查询克星”登场！

好了，废话不多说，咱们的主角——pt-digest-query 正式登场！它可不是什么江湖郎中，而是Percona Toolkit里的一员猛将，专门用来分析慢查询日志的。

你可以把它想象成一个经验丰富的侦探🕵️‍♂️，它会仔细翻阅你的慢查询日志，把那些“罪魁祸首”揪出来，然后给你一份详细的“犯罪报告”，告诉你谁是主犯，谁是帮凶，以及他们都干了些什么坏事。

pt-digest-query 的主要功能包括：

• 聚合慢查询： 把相似的慢查询归类，避免重复分析，提高效率。
• 分析慢查询： 统计每个慢查询的执行次数、总耗时、平均耗时、最大耗时、最小耗时等关键指标。
• 提供建议： 根据分析结果，给出优化建议，比如添加索引、优化SQL语句等。

第二章：pt-digest-query 的安装与配置——“磨刀不误砍柴工”

想要让 pt-digest-query 发挥威力，首先得把它请到家里来，也就是安装它。

安装 Percona Toolkit：

不同的操作系统，安装方式略有不同。这里以 Debian/Ubuntu 为例：

sudo apt-get update
sudo apt-get install percona-toolkit

安装完成后，就可以在命令行里使用 pt-digest-query 命令了。

配置慢查询日志：

pt-digest-query 需要读取慢查询日志才能进行分析。所以，你需要确保 MySQL 服务器开启了慢查询日志功能，并且设置了合理的阈值。

修改 MySQL 配置文件（通常是 my.cnf 或 my.ini），添加或修改以下参数：

[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  # 记录未使用索引的查询

解释一下：

• slow_query_log = 1： 开启慢查询日志功能。
• slow_query_log_file：指定慢查询日志文件的路径。你可以根据自己的需求修改路径。
• long_query_time：设置慢查询的阈值。这里设置为 1 秒，表示执行时间超过 1 秒的查询会被记录到慢查询日志中。你可以根据实际情况调整这个值。一般来说，生产环境建议设置为 1-5 秒。
• log_queries_not_using_indexes = 1： 记录未使用索引的查询。这个参数非常重要，它可以帮助你发现那些没有使用索引的查询，从而进行优化。

修改完配置文件后，需要重启 MySQL 服务器才能生效：

sudo systemctl restart mysql

第三章：pt-digest-query 的使用方法——“侦探开始破案啦！”

配置完毕，就可以让 pt-digest-query 开始工作了！

基本用法：

最简单的用法就是直接指定慢查询日志文件：

pt-digest-query /var/log/mysql/mysql-slow.log

这条命令会读取指定的慢查询日志文件，然后把分析结果输出到屏幕上。

常用选项：

pt-digest-query 提供了很多选项，可以让你更灵活地控制分析过程。

• --since 和 --until： 指定分析的时间范围。比如，只分析最近一天的数据：

  pt-digest-query --since 1d /var/log/mysql/mysql-slow.log

• --limit： 限制输出结果的数量。比如，只显示最慢的 10 个查询：

  pt-digest-query --limit 10 /var/log/mysql/mysql-slow.log

• --group-by： 指定分组方式。默认情况下，pt-digest-query 会根据 SQL 语句的相似度进行分组。你也可以根据其他字段进行分组，比如用户、数据库等。

• --order-by： 指定排序方式。默认情况下，pt-digest-query 会按照总耗时进行排序。你也可以根据其他字段进行排序，比如执行次数、平均耗时等。

• --output： 指定输出格式。pt-digest-query 支持多种输出格式，比如 text、json、xml 等。

• --filter： 指定过滤条件。你可以根据 SQL 语句、执行时间等条件进行过滤。

一个完整的例子：

pt-digest-query --since 1d --limit 20 --order-by Query_time_sum --output text /var/log/mysql/mysql-slow.log > slow_queries_report.txt

这条命令会分析最近一天的数据，显示最慢的 20 个查询，按照总耗时排序，并将结果以文本格式输出到 slow_queries_report.txt 文件中。

第四章：pt-digest-query 的输出结果解读——“侦探的报告，字字珠玑！”

pt-digest-query 的输出结果非常详细，包含了大量的统计信息。下面我们来解读一下这些信息，看看它们都代表什么意思。

一个典型的 pt-digest-query 输出结果如下：

# Overall: 11 unique queries, 39 total
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Query time (s):   230.68    0.00   49.21    5.91   18.62   11.76    1.61
# Lock time (s):      0.02    0.00    0.01    0.00    0.00    0.00    0.00
# Rows sent:       11.18k    0.00   4.41k  286.66  950.00  735.27   43.00
# Rows examined:    35.08M    0.00  15.69M 923.10k   3.57M   4.75M   33.00
# Rows affected:      0.00    0.00    0.00    0.00    0.00    0.00    0.00
# Bytes sent:        1.80M    0.00  395.55k   47.24k  112.18k  77.39k   2.99k
# Tmp tables:           0   0.00%   0.00%   0.00%
# Tmp disk tables:      0   0.00%   0.00%   0.00%
# Tmp row size:         0    0.00    0.00    0.00    0.00    0.00    0.00
# Query size:      13.21k   43.00  2.42k 348.72   945.00  564.11  149.00

# Profile
# Rank Query ID           Response time   Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ========
#    1 0xAAAAAAAAAAAAAAAA 150.6397 22 6.85 1.54  SELECT xxx FROM table1 WHERE ...
#    2 0xBBBBBBBBBBBBBBBB 50.0000 10 5.00 0.75  SELECT yyy FROM table2 WHERE ...
#    3 0xCCCCCCCCCCCCCCCC 30.0000 7 4.29 0.61  UPDATE zzz SET ... WHERE ...

# Query 1: 0xAAAAAAAAAAAAAAAA
# Database: your_database
# Users: your_user
# Hosts: your_host
# Query_time: 150.64s total, 6.85s avg, 6.85s max, 6.85s min
# Lock_time: 0.01s total, 0.00s avg, 0.00s max, 0.00s min
# Rows_sent: 5000 total, 227 avg, 227 max, 227 min
# Rows_examined: 1000000 total, 45454 avg, 45454 max, 45454 min
# Query: SELECT xxx FROM table1 WHERE ...

解释一下各个指标的含义：

• Overall： 总体的统计信息。

  • unique queries： 唯一查询的数量。
  • total： 查询的总次数。

• Attribute： 各种属性的统计信息。

  • Query time (s)： 查询时间，包括总时间、最小值、最大值、平均值、95% 占比、标准差、中位数。
  • Lock time (s)： 锁等待时间。
  • Rows sent： 发送的行数。
  • Rows examined： 扫描的行数。这个指标非常重要，扫描的行数越多，查询效率越低。
  • Rows affected： 影响的行数。
  • Bytes sent： 发送的字节数。
  • Tmp tables： 使用临时表的比例。
  • Tmp disk tables： 使用磁盘临时表的比例。使用临时表会降低查询效率，使用磁盘临时表则会更慢。
  • Tmp row size： 临时表的行大小。
  • Query size： 查询语句的大小。

• Profile： 慢查询的概要信息。

  • Rank： 排名，按照总耗时排序。
  • Query ID： 查询 ID，用于唯一标识一个查询。
  • Response time： 响应时间，也就是查询的总耗时。
  • Calls： 调用次数。
  • R/Call： 每次调用的平均耗时。
  • V/M： 方差/平均值，用于衡量查询的稳定性。
  • Item： 查询语句的摘要。

• Query N： 每个慢查询的详细信息。

  • Database： 数据库名。
  • Users： 用户名。
  • Hosts： 主机名。
  • Query_time： 查询时间，包括总时间、平均时间、最大时间、最小时间。
  • Lock_time： 锁等待时间。
  • Rows_sent： 发送的行数。
  • Rows_examined： 扫描的行数。
  • Query： 完整的查询语句。

第五章：根据pt-digest-query 的分析结果进行优化——“对症下药，药到病除！”

拿到 pt-digest-query 的报告，接下来就是根据报告进行优化了。这就像医生拿到体检报告，然后根据报告给出治疗方案一样。

常见的优化手段：

1. 添加索引： 这是最常见的优化手段。如果 Rows_examined 远大于 Rows_sent，说明查询扫描了大量的无用数据，这时候就可以考虑添加索引来减少扫描的行数。

   • 定位需要添加索引的字段： 观察 WHERE 子句、JOIN 子句、ORDER BY 子句和 GROUP BY 子句中使用的字段，这些字段通常是添加索引的最佳选择。
   • 选择合适的索引类型： MySQL 支持多种索引类型，比如 B-Tree 索引、Hash 索引、Fulltext 索引等。选择合适的索引类型可以提高查询效率。一般来说，B-Tree 索引适用于范围查询和排序，Hash 索引适用于等值查询，Fulltext 索引适用于全文搜索。
   • 避免过度索引： 索引并不是越多越好。过多的索引会增加数据库的维护成本，降低写入性能。

2. 优化 SQL 语句： 有些 SQL 语句写得不够优化，也会导致慢查询。

   • *避免使用 `SELECT `：** 只选择需要的字段，减少数据传输量。
   • 避免在 WHERE 子句中使用函数或表达式： 这会导致索引失效。
   • *使用 EXISTS 代替 `COUNT()：** 如果只需要判断是否存在满足条件的记录，使用EXISTS` 效率更高。
   • 优化 JOIN 语句： 尽量使用索引字段进行 JOIN，避免全表扫描。
   • 使用 LIMIT 限制返回结果的数量： 如果只需要少量数据，使用 LIMIT 可以减少数据传输量。
   • 分批处理： 对于大批量的数据操作，可以分批处理，避免一次性操作导致数据库压力过大。

3. 优化数据库配置： 有些数据库配置不合理，也会导致慢查询。

   • 调整缓冲区大小： 增加 innodb_buffer_pool_size 可以提高 InnoDB 存储引擎的性能。
   • 调整连接数： 增加 max_connections 可以提高数据库的并发能力。
   • 开启查询缓存： 如果查询结果不经常变化，可以开启查询缓存，提高查询效率。

4. 升级硬件： 如果以上方法都无法解决问题，那可能就是硬件瓶颈了。可以考虑升级 CPU、内存、硬盘等硬件设备。

一个实战案例：

假设 pt-digest-query 报告显示，以下 SQL 语句执行时间过长：

SELECT * FROM orders WHERE customer_id = 123 AND order_date BETWEEN '2023-01-01' AND '2023-12-31';

分析发现，orders 表上没有 customer_id 和 order_date 的联合索引。

解决方案：

ALTER TABLE orders ADD INDEX idx_customer_id_order_date (customer_id, order_date);

添加联合索引后，再次执行 pt-digest-query，发现该 SQL 语句的执行时间明显缩短。

第六章：自动化慢查询分析——“让机器替你操心！”

手动执行 pt-digest-query 和分析结果，虽然可以解决问题，但比较繁琐。如果能把这个过程自动化，那就更好了！

使用脚本定时执行 pt-digest-query：

可以编写一个脚本，每天定时执行 pt-digest-query，并将结果保存到文件中。

#!/bin/bash

DATE=$(date +%Y%m%d)
LOG_FILE=/var/log/mysql/mysql-slow.log
REPORT_FILE=/var/log/mysql/slow_query_report_$DATE.txt

pt-digest-query --since 1d --limit 20 --order-by Query_time_sum --output text $LOG_FILE > $REPORT_FILE

# 可以添加邮件通知，将报告发送给相关人员
# mail -s "Slow Query Report" [email protected] < $REPORT_FILE

然后使用 crontab 定时执行该脚本：

crontab -e

添加以下内容：

0 0 * * * /path/to/your/script.sh

这条命令表示每天凌晨 0 点 0 分执行该脚本。

集成到监控系统：

可以将 pt-digest-query 集成到监控系统中，比如 Prometheus + Grafana。通过监控慢查询的数量、执行时间等指标，可以及时发现问题并进行处理。

第七章：总结——“工欲善其事，必先利其器！”

好了，各位观众老爷，今天的讲座就到这里了。

总结一下，pt-digest-query 是一个非常强大的慢查询分析工具，它可以帮助你快速定位和解决慢查询问题，提高数据库的性能。

记住，工欲善其事，必先利其器！掌握 pt-digest-query，就等于拥有了一把锋利的宝剑，可以斩断数据库的“慢性病”，让你的系统更加健康、稳定！

希望今天的讲座对大家有所帮助！如果有什么问题，欢迎在评论区留言交流！下次再见！ 👋