MySQL高级讲座篇之：慢查询诊断与调优：基于`pt-query-digest`的性能瓶颈定位。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众老爷们，大家好！我是你们的老朋友，今天咱们来聊聊MySQL性能优化这档子事儿，特别是慢查询这块，咱们得把它给安排明白了。

今天的主题是：MySQL高级讲座篇之：慢查询诊断与调优：基于pt-query-digest的性能瓶颈定位。

俗话说，工欲善其事，必先利其器。优化MySQL，我们得先找到问题在哪儿。pt-query-digest就是这么个神器，能帮我们快速定位到那些慢得像蜗牛一样的查询语句，然后才能对症下药。

一、慢查询日志是个啥？

首先，得搞清楚慢查询日志是个什么玩意儿。简单来说，它就是MySQL用来记录执行时间超过指定阈值的SQL语句的日志文件。这个阈值由long_query_time参数控制，默认是10秒。超过这个时间，MySQL就会把这条SQL语句记录下来。

为啥要开启慢查询日志？

因为它是我们定位慢查询的唯一线索！没有它，就像大海捞针，根本不知道哪些SQL语句在搞事情。

如何开启慢查询日志？

有两种方式：

修改MySQL配置文件（my.cnf/my.ini）：

在配置文件中添加或修改以下参数：

[mysqld]
slow_query_log = 1  # 开启慢查询日志
slow_query_log_file = /var/log/mysql/mysql-slow.log  # 指定日志文件路径
long_query_time = 1   # 设置慢查询阈值为1秒
log_queries_not_using_indexes = 1  # 记录未使用索引的查询 (可选，建议开启)

然后重启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';

注意： 这种方式修改的参数只在MySQL服务重启之前有效。如果想永久生效，还是得修改配置文件。

一些小建议：

long_query_time的设置要根据实际情况来调整。一般来说，生产环境可以设置成1秒或者更小。
log_queries_not_using_indexes这个参数建议开启，因为没有用到索引的查询，通常都是性能瓶颈。
慢查询日志文件会越来越大，所以要定期清理。可以使用logrotate等工具进行日志切割。

二、 pt-query-digest闪亮登场

有了慢查询日志，接下来就要用pt-query-digest这个神器来分析它了。

pt-query-digest是个什么东西？

它是Percona Toolkit工具集中的一个工具，专门用来分析MySQL慢查询日志、General log、binary log等日志文件。它可以帮我们统计SQL语句的执行频率、平均执行时间、最大执行时间、查询次数、锁等待时间等等，并按照不同的维度进行排序，让我们快速找到性能瓶颈。

如何安装pt-query-digest？

这个取决于你的操作系统和包管理器。

Debian/Ubuntu:

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

CentOS/RHEL:
```
sudo yum install percona-toolkit
```
如果你的系统没有Percona的软件源，需要先添加。
其他系统: 请参考Percona Toolkit的官方文档。

pt-query-digest的基本用法：

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

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

执行完毕后，pt-query-digest会输出一份报告，包含了各种统计信息。

三、 pt-query-digest报告解读

pt-query-digest的报告内容非常丰富，我们挑一些重要的部分来解读一下：

Overall: 总体的统计信息，包括分析的日志文件、时间范围、SQL语句总数、唯一SQL语句数等等。
```
# 2.3s user time, 0.20s system time, 01:13:48 elapsed time
# 500 queries total, 60 unique
# 0 queries using temporary tables
# 0 queries using filesort
# 0 queries using full table scans
```
- queries total: 总共分析了多少条SQL语句。
- unique: 有多少条不同的SQL语句。
- queries using temporary tables: 使用了临时表的查询数量。
- queries using filesort: 使用了文件排序的查询数量。
- queries using full table scans: 使用了全表扫描的查询数量。 注意： 这些数字越高，通常意味着性能越差。
Profile: 按不同维度排序的SQL语句列表。默认是按总执行时间排序。
```
# Profile
# Rank Query ID           Response time   Calls   R/Call   V/M   Item   Pct   Cum Pct
# ==== ================== ============= ======= ======== ===== ====== ===== =======
#    1 0xXXXXXXXXXXXXXXXX 123.4567 1000 0.1234  0.01  select 12.34% 12.34%
#    2 0xYYYYYYYYYYYYYYYY 98.7654  500  0.1975  0.02  update 9.88%  22.22%
#    3 0xZZZZZZZZZZZZZZZZ 76.5432  200  0.3827  0.03  delete 7.65%  29.87%
```
- Rank: 排名，按照总执行时间排序。
- Query ID: SQL语句的哈希值，用于唯一标识一条SQL语句。
- Response time: 总执行时间。
- Calls: 执行次数。
- R/Call: 平均每次执行时间。
- Item: SQL语句的类型 (select, update, delete 等等)。
- Pct: 该SQL语句的执行时间占总执行时间的百分比。
- Cum Pct: 累计百分比。

Detailed Analysis: 每条SQL语句的详细分析，包括SQL语句的文本、执行计划、执行时间分布、锁等待时间等等。

# Query 1: 0.16 QPS, 0.00 concurrency, ID 0xXXXXXXXXXXXXXXXX at byte 12345
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-10-27 10:00:00 to 2023-10-27 11:00:00
# Attribute    total     min     max     avg     95%  stddev  median
# ============ ======= ======= ======= ======= ======= ======= =======
# Count           60       60       60       60       60        0       60
# Exec time      123s     123ms   456ms   2.05s   3.45s   1.23s   1.78s
# Lock time       0s       0s       0s       0s       0s       0s       0s
# Rows sent     123k     1.2k     2.3k     2.1k     2.2k   123.45    2.1k
# Rows examined 123M     1.2M     2.3M     2.1M     2.2M   123.45    2.1M
# Query size     123b     123b     123b     123b     123b       0b     123b
# String:
# Databases    testdb
# Hosts        host1
# Users        user1
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#   1s   ################################################################
#  10s
# Lock time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#   1s
#  10s
# Rows sent distribution
#   1k   ################################################################
#  10k
# 100k
#   1M
#  10M
# Rows examined distribution
#   1k
#  10k
# 100k
#   1M   ################################################################
#  10M
# EXPLAIN /*!50100 PARTITIONS*/
# SELECT * FROM your_table WHERE your_column = 'your_value';

QPS: 每秒查询次数。
concurrency: 并发数。
Exec time: 执行时间统计，包括总时间、最小时间、最大时间、平均时间、95%时间、标准差、中位数。
Lock time: 锁等待时间统计。
Rows sent: 发送的行数。
Rows examined: 扫描的行数。 注意： Rows examined远大于Rows sent通常意味着存在性能问题，需要优化索引。
EXPLAIN: 该SQL语句的执行计划，可以用来分析查询过程。

四、案例分析：实战演练

假设我们通过pt-query-digest分析慢查询日志后，发现以下SQL语句的执行时间最长：

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

这条语句看起来很简单，但是执行时间却很长，我们该如何分析和优化呢？

查看执行计划：
```
EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date BETWEEN '2023-01-01' AND '2023-12-31';
```
通过执行计划，我们可以看到MySQL是如何执行这条SQL语句的。如果type列是ALL，说明进行了全表扫描，这肯定是很慢的。
分析索引：

我们需要检查orders表上是否有customer_id和order_date的索引。如果没有，我们需要创建索引。
```
ALTER TABLE orders ADD INDEX idx_customer_id_order_date (customer_id, order_date);
```
创建联合索引可以提高查询效率。
检查数据类型：

确保customer_id和order_date的数据类型正确。如果customer_id是字符串类型，而查询条件中使用的是数字，MySQL可能会进行隐式类型转换，导致索引失效。
优化SQL语句：

如果order_date字段包含了时间部分，BETWEEN可能会导致索引失效。可以尝试将BETWEEN改成>=和<=：
```
SELECT * FROM orders WHERE customer_id = 123 AND order_date >= '2023-01-01 00:00:00' AND order_date <= '2023-12-31 23:59:59';
```
分析数据量：

如果orders表的数据量非常大，即使使用了索引，查询速度也可能很慢。可以考虑分表、分区等方案来减少单表的数据量。
硬件资源：

如果以上优化都无效，可能需要考虑硬件资源是否足够。例如，内存不足会导致频繁的磁盘IO，从而影响查询速度。

五、 pt-query-digest的常用选项

pt-query-digest有很多选项，可以根据不同的需求进行配置。

选项	描述
`--limit N`	限制报告中显示的SQL语句数量，默认显示前20条。
`--group-by`	指定分组方式，例如`--group-by query`表示按SQL语句分组，`--group-by user`表示按用户分组。
`--since` 和 `--until`	指定分析的时间范围。例如`--since '2023-10-27 00:00:00' --until '2023-10-27 23:59:59'`。
`--order-by`	指定排序方式，例如`--order-by Query_time:sum`表示按总执行时间排序，`--order-by Lock_time:sum`表示按总锁等待时间排序。
`--report-format`	指定报告格式，例如`--report-format json`表示输出JSON格式的报告。
`--output`	指定输出文件，例如`--output report.txt`表示将报告输出到report.txt文件。
`--no-report-truncate`	默认情况下，`pt-query-digest`会截断SQL语句的长度。使用`--no-report-truncate`可以禁用截断，显示完整的SQL语句。
`--filter`	使用正则表达式过滤SQL语句。例如`--filter 'SELECT .* FROM users'`只分析包含`SELECT .* FROM users`的SQL语句。
`--user` `--password` `--host` `--port`	用于连接MySQL服务器，获取执行计划等信息。

一些使用技巧：

分析指定时间段的慢查询：

pt-query-digest --since '2023-10-27 00:00:00' --until '2023-10-27 23:59:59' /var/log/mysql/mysql-slow.log

按用户分组分析：

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

输出JSON格式的报告：

pt-query-digest --report-format json /var/log/mysql/mysql-slow.log > report.json

分析执行时间超过1秒的SQL语句，并只显示前10条：

pt-query-digest --limit 10 --filter 'Query_time > 1' /var/log/mysql/mysql-slow.log

六、总结与建议

pt-query-digest是一个非常强大的慢查询分析工具，可以帮助我们快速定位MySQL的性能瓶颈。但是，它只是一个工具，最终的优化还需要我们根据实际情况进行分析和调整。

一些建议：

定期分析慢查询日志： 不要等到系统出现性能问题才去分析慢查询日志，应该定期进行分析，及时发现潜在的问题。
关注Rows examined和Rows sent： 这两个指标的差异越大，通常意味着性能问题越严重。
使用EXPLAIN分析执行计划： 执行计划可以帮助我们了解MySQL是如何执行SQL语句的，从而找到优化点。
多尝试不同的优化方案： 没有一种万能的优化方案，需要根据实际情况进行尝试和调整。
持续学习和积累经验： MySQL性能优化是一个持续学习的过程，需要不断积累经验，才能更好地解决问题。

好了，今天的讲座就到这里了。希望大家能够掌握pt-query-digest的使用方法，并将其应用到实际工作中，让MySQL跑得更快，更稳！

最后，祝大家工作顺利，身体健康，早日成为MySQL优化大师！下次有机会再和大家分享其他的MySQL优化技巧。再见！

发表回复 取消回复

发表回复取消回复