MySQL高级讲座篇之：`pt-query-digest`实战：如何从慢查询日志中挖掘性能黄金点。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众老爷，大家好！我是今天的主讲人，江湖人称“数据库老司机”，今天咱们聊聊MySQL性能优化的葵花宝典之一：pt-query-digest。

开场白：慢查询的那些事儿

话说，各位在MySQL的世界里摸爬滚打，谁还没见过慢查询呢？慢查询就像程序里的Bug，总是在你最不想看到的时候冒出来，让你焦头烂额。尤其是在流量高峰期，一条慢查询就能把你的数据库拖垮，让你的用户体验直线下降。

但是，慢查询也不是一无是处，它就像金矿，蕴藏着巨大的性能提升空间。关键在于，你得会挖！ pt-query-digest就是咱们的矿镐，能帮你从慢查询日志这座矿山里，挖出真正的“性能黄金”。

第一章：工欲善其事，必先利其器：pt-query-digest的安装与配置

磨刀不误砍柴工，咱们先来把pt-query-digest这把利器磨亮。

安装：

pt-query-digest是Percona Toolkit的一部分，所以我们需要先安装Percona Toolkit。
- Debian/Ubuntu:
```
sudo apt-get update
sudo apt-get install percona-toolkit
```
- CentOS/RHEL:
```
sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo yum install percona-toolkit
```
- 其他系统: 请参考Percona Toolkit官方文档：https://www.percona.com/doc/percona-toolkit/index.html
配置慢查询日志：

要让pt-query-digest发挥作用，首先要确保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秒或2秒比较常见。
- log_queries_not_using_indexes：记录没有使用索引的查询，有助于发现潜在的索引缺失问题。
修改配置后，需要重启MySQL服务才能生效。或者，你也可以使用动态SQL修改：
```
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
```
同样，执行完这些SQL后，需要执行 FLUSH LOGS; 来让配置生效。

第二章：pt-query-digest的十八般武艺：常用参数详解

pt-query_digest的参数很多，但常用的也就那么几个。掌握了这些，就能应付大部分场景了。

参数	描述	示例
`--help`	显示帮助信息	`pt-query-digest --help`
`slow-query-log_file`	指定慢查询日志文件	`pt-query-digest /var/log/mysql/mysql-slow.log`
`--since`	指定开始时间，只分析该时间之后的慢查询	`pt-query-digest --since '2023-10-26 00:00:00' /var/log/mysql/mysql-slow.log`
`--until`	指定结束时间，只分析该时间之前的慢查询	`pt-query-digest --until '2023-10-27 00:00:00' /var/log/mysql/mysql-slow.log`
`--limit`	限制输出的查询数量，默认是所有查询	`pt-query-digest --limit 20 /var/log/mysql/mysql-slow.log`
`--report-format`	指定报告格式，可选值为`text` (默认), `json`, `profile`	`pt-query-digest --report-format json /var/log/mysql/mysql-slow.log`
`--group-by`	指定分组方式，常见的有`query` (默认), `hostname`, `user`, `database`等	`pt-query-digest --group-by user /var/log/mysql/mysql-slow.log`
`--order-by`	指定排序方式，常见的有`count`, `time`, `lock`, `rows`, `bytes` 等	`pt-query-digest --order-by time /var/log/mysql/mysql-slow.log`
`--output`	指定输出文件，如果不指定，则输出到标准输出	`pt-query-digest /var/log/mysql/mysql-slow.log > slow_query_report.txt`
`--user`	指定MySQL用户名，用于连接数据库执行EXPLAIN	`pt-query-digest --user root --password 'your_password' /var/log/mysql/mysql-slow.log`
`--password`	指定MySQL密码，用于连接数据库执行EXPLAIN	`pt-query-digest --user root --password 'your_password' /var/log/mysql/mysql-slow.log`
`--database`	指定数据库，用于连接数据库执行EXPLAIN	`pt-query-digest --user root --password 'your_password' --database your_db /var/log/mysql/mysql-slow.log`
`--processlist-user`	只分析来自特定用户的查询	`pt-query-digest --processlist-user 'your_user' /var/log/mysql/mysql-slow.log`
`--processlist-host`	只分析来自特定主机的查询	`pt-query-digest --processlist-host 'your_host' /var/log/mysql/mysql-slow.log`
`--no-report-histogram`	禁用直方图报告，可以加快分析速度	`pt-query-digest --no-report-histogram /var/log/mysql/mysql-slow.log`
`--review h=host,D=db,u=user,p=password`	将分析结果写入到数据库表中，方便后续分析。	`pt-query-digest --review h=localhost,D=test,u=root,p=your_password --output review_slow /var/log/mysql/mysql-slow.log`
`--explain`	对慢查询执行 EXPLAIN，并将其添加到报告中。需要提供数据库连接信息。	`pt-query-digest --explain --user root --password 'your_password' --database your_db /var/log/mysql/mysql-slow.log`
`--sample`	从慢查询日志中随机抽取样本，用于分析。对于非常大的日志文件，可以使用此选项来减少分析时间。例如，`--sample 1%` 将分析日志的 1%。	`pt-query-digest --sample 1% /var/log/mysql/mysql-slow.log`

第三章：实战演练：从慢查询日志中挖掘性能黄金

光说不练假把式，咱们来几个实战案例。

案例1：找出最耗时的查询

这是最常见的需求，我们需要找出哪些查询占用了最多的数据库资源。

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

运行上面的命令，pt-query-digest会分析慢查询日志，并按照总耗时（Time）排序，输出最耗时的查询。输出结果类似如下：

# Overall: 31.36k total, 10 unique, 0.00 QPS, 0.00x concurrency __________
# Time range: 2023-10-26 00:00:01 to 2023-10-26 23:59:59
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          1727s      4ms   112s    55ms    94ms   1.46s     8ms
# Lock time             0s       0s       0s       0s       0s       0s       0s
# Rows sent        17.00k       0       1k    0.55   1.00k    5.63   0.00
# Rows examined    22.23M       1   1.17M  727.16   1.17M   1.64k   2.00
# Query size         3.48k      60     658   113.02  195.63   74.39  103.00

# Profile
# Rank Query ID           Response time   Calls R/Call V/M   Ite  Qry ID
# ==== ================== ============= ===== ====== ===== === =======
#    1 0x04E34C489E237E2C 1693.8639 97.9%   308   5.49   0.04  0.0  SELECT product
#    2 0x65B9960744E26215   16.8803  1.0%     1  16.88  16.88  0.0  SELECT order
#    3 0x504571888631B02B   12.1079  0.7%    10   1.21   0.00  0.0  SELECT user
# MISC < 1%
# Query 1: 0.01 QPS, 0.01x concurrency, ID 0x04E34C489E237E2C at byte 307
# This query is executing a SELECT statement
# Scores: V/M = 0.04
# Time range: 2023-10-26 00:00:01 to 2023-10-26 23:59:59
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count       97%     308
# Exec time   97%   1694s      4ms   112s    5.49s   94ms   1.46s     8ms
# Lock time    0%       0s       0s       0s       0s       0s       0s       0s
# Rows sent    1%     191       0      1k    0.62   1.00k    5.69   0.00
# Rows examined 3%  584.02k       1   1.17M   1.89k   1.17M   6.63k   2.00
# Query size  100%   3.41k      60     658   11.06   19.56   74.33  103.00
# String:
# Databases    your_db
# Hosts        localhost
# Users        your_user
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  ################################################################
#   1s
#  10s
# 100s  #

# EXPLAIN /*!50100 PARTITIONS*/
SELECT
  p.product_id,
  p.product_name,
  p.product_description,
  p.price,
  p.category_id,
  c.category_name,
  SUM(oi.quantity) AS total_quantity_sold
FROM products p
JOIN categories c ON p.category_id = c.category_id
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY
  p.product_id,
  p.product_name,
  p.product_description,
  p.price,
  p.category_id,
  c.category_name
ORDER BY
  total_quantity_sold DESC
LIMIT 10;

Overall: 报告的总体信息，包括总查询数、唯一查询数、QPS（每秒查询数）、并发数和时间范围。
Attribute: 查询属性的统计信息，如执行时间（Exec time）、锁定时间（Lock time）、发送的行数（Rows sent）、检查的行数（Rows examined）和查询大小（Query size）。
Profile: 按查询 ID 对查询进行排名，显示每个查询的响应时间、调用次数、每次调用的平均响应时间（R/Call）和方差/分钟（V/M）。
Query 1: 有关最慢查询的详细信息，包括查询执行的百分比（pct）、总执行时间、最小执行时间、最大执行时间、平均执行时间、95% 的执行时间、标准差和中位数。
String: 有关查询的其他信息，如数据库、主机和用户。
Query_time distribution: 查询时间分布的直方图。这有助于了解查询时间的范围和频率。
EXPLAIN: 用于查看查询执行计划的 EXPLAIN 语句。这有助于识别查询中的潜在问题，如缺少索引或全表扫描。

从结果中，我们可以看到最耗时的查询是 SELECT product，占用了97.9%的总执行时间。接下来，我们需要分析这个查询，看看能不能优化。 pt-query-digest已经帮我们输出了EXPLAIN语句，我们可以直接拿来用。

案例2：找出未使用索引的查询

如果开启了 log_queries_not_using_indexes，我们可以找出哪些查询没有使用索引，从而找出需要添加索引的字段。

pt-query-digest /var/log/mysql/mysql-slow.log --filter '$event->{rows_examined} > 1000'

这个命令会找出 rows_examined 大于 1000 的查询，这些查询很可能没有使用索引，导致全表扫描。 --filter 参数允许我们使用 Perl 代码过滤查询。

案例3：按用户分组统计慢查询

有时候，我们需要知道哪些用户发起的慢查询最多。

pt-query-digest /var/log/mysql/mysql-slow.log --group-by user

这个命令会按照用户（user）分组，统计每个用户的慢查询数量和总耗时。

案例4：分析指定时间段内的慢查询

有时候，我们只想分析某个时间段内的慢查询，比如某个流量高峰期。

pt-query-digest /var/log/mysql/mysql-slow.log --since '2023-10-26 10:00:00' --until '2023-10-26 11:00:00'

这个命令会分析 2023-10-26 10:00:00 到 2023-10-26 11:00:00 之间的慢查询。

案例5：将分析结果写入数据库

pt-query-digest可以将分析结果写入到数据库表中，方便后续分析和可视化。

pt-query-digest --review h=localhost,D=test,u=root,p=your_password --output review_slow /var/log/mysql/mysql-slow.log

这个命令会将分析结果写入到 test 数据库中的 review_slow 表中。你需要提前创建好这个表，表结构可以参考 pt-query-digest --review-create 命令的输出。

第四章：性能优化建议：从pt-query-digest报告中提取信息

pt-query-digest的报告非常详细，里面包含了大量的性能优化信息。下面是一些常见的优化建议：

添加索引：

如果EXPLAIN结果显示 type 为 ALL (全表扫描) 或者 rows 很大，那么很可能需要添加索引。根据EXPLAIN结果中的 possible_keys 和 key 字段，选择合适的索引。

比如，在上面的案例1中，如果EXPLAIN显示 products 表需要全表扫描，那么可以考虑在 product_id、category_id 等字段上添加索引。
优化SQL语句：
- *避免使用 `SELECT `：** 只查询需要的字段，减少IO开销。
- 使用 JOIN 代替子查询： JOIN 通常比子查询效率更高。
- 优化 WHERE 子句： 尽量使用索引字段，避免使用 OR 和 NOT IN 等操作。
- 避免在 WHERE 子句中使用函数： 这会导致索引失效。
调整MySQL配置：
- innodb_buffer_pool_size： 增大InnoDB缓冲池大小，减少磁盘IO。
- key_buffer_size： 增大Key Buffer大小，提高MyISAM表的索引访问速度。
- query_cache_size： 开启查询缓存，缓存查询结果。但是，在高并发场景下，查询缓存可能会成为瓶颈，需要谨慎使用。
硬件升级：

如果以上优化都无效，那么可能需要考虑升级硬件，比如增加内存、更换SSD硬盘等。

第五章：高级技巧：自定义pt-query-digest

pt-query-digest还可以进行自定义，以满足更复杂的需求。

使用 --filter 参数进行高级过滤：

--filter 参数允许我们使用 Perl 代码过滤查询。比如，只分析包含特定关键词的查询：
```
pt-query-digest /var/log/mysql/mysql-slow.log --filter '$event->{query} =~ /your_keyword/'
```
使用 --no-report-histogram 加速分析：

如果慢查询日志非常大，可以使用 --no-report-histogram 禁用直方图报告，从而加快分析速度。
使用 --sample 参数进行抽样分析：

对于非常大的慢查询日志，可以使用 --sample 参数进行抽样分析，减少分析时间。例如，--sample 1% 将分析日志的 1%。

第六章：总结与展望

pt-query-digest是MySQL性能优化的利器，掌握了它，就能从慢查询日志中挖掘出大量的性能提升空间。但是，pt-query-digest只是一个工具，真正的性能优化还需要结合具体的业务场景和数据库结构，进行深入分析和调整。

希望今天的讲座能帮助大家更好地理解和使用pt-query-digest，祝大家早日成为MySQL性能优化的大师！

感谢各位的观看，下次再见！

发表回复 取消回复

发表回复取消回复