MySQL慢查询日志slowlog

0 慢查询日志定义

慢速查询日志记录的是执行时间超过long_query_time秒和检查的行数超过min_examined_row_limit的SQL语句，这些语句通常是需要进行优化的。

官方参考文档：https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html

1 慢查询日志的配置参数

服务器使用以下顺序的控制参数来决定是否将查询语句写入慢查询日志：

1. 查询必须不是管理语句（如alter、optimize table等），或者必须启用log_slow_admin_statements参数记录管理类语句；
2. 查询必须至少花费了long_query_time秒，或者必须启用log_queries_not_using_indexes，并且查询的索引没有行限制（如全表扫描、索引全扫描等）；
3. 查询必须至少检索了min_examined_row_limit行；
4. 不被参数log_throttle_queries_not_using_indexes设置阈值限制写入慢sql日志。

下面介绍这些参数：

一、long_query_time

规定了查询时间超过此参数值被定义为慢SQL，状态变量Slow_queries记录了慢查询SQL的数量。long_query_time的单位为秒，可以设置成小数，精确到微妙。最小值为0，最大值为31536000，即365天，默认值为10。

查看当前设置：

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)

查看慢sql数量：

mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 5     |
+---------------+-------+
1 row in set (0.01 sec)

将此参数设置为5：

mysql> set global long_query_time=5;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

二、slow_query_log

此参数决定是否激活慢sql日志，默认值是off，即关闭。

启用慢查询日志：

mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)

三、slow_query_log_file

此参数指定慢sql日志的文件路径和文件名，默认位置在数据目录datadir中，默认文件名是hostname-slow.log。

mysql> show variables like 'slow_query_log_file';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log_file | /disk1/data/mysql001-slow.log |
+---------------------+-------------------------------+
1 row in set (0.00 sec)

查看慢sql日志文件：

[mysql@mysql001 log]$ tailf /disk1/data/mysql001-slow.log
/usr/sbin/mysqld, Version: 8.0.34 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2023-12-20T22:44:21.890879+08:00
# User@Host: root[root] @ localhost []  Id:     9
# Query_time: 0.009038  Lock_time: 0.000008 Rows_sent: 0  Rows_examined: 0 Thread_id: 9 Errno: 0 Killed: 0 Bytes_received: 286 Bytes_sent: 92 Read_first: 0 Read_last: 0 Read_key: 12 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 1 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 1 Created_tmp_disk_tables: 0 Created_tmp_tables: 1 Start: 2023-12-20T22:44:21.881841+08:00 End: 2023-12-20T22:44:21.890879+08:00
SET timestamp=1703083461;
select f.title, count(*) as cnt
  from sakila.rental r
  join sakila.inventory i
    on r.inventory_id = i.inventory_id
  join sakila.film f
    on i.film_id = f.film_id
 where r.rental_date between '2005-03-01' and '2005-03-31'
 group by f.film_id
 order by cnt desc
 limit 10;

四、log_queries_not_using_indexes

启用该变量，会记录期望检索所有行的查询语句，也就是说做表全扫描。使用索引的查询也会被记录。例如，使用完整索引扫描的查询使用索引，但会记录日志，因为索引不会限制行数。默认值是false。

五、min_examined_row_limit

参数规定了只有当检索的行数超过了参数值的sql语句才会被记录到慢sql日志文件中，默认值是0，没有限制。可以和上一个参数log_queries_not_using_indexes搭配使用，可以避免记录一些访问小表的查询。

六、log_throttle_queries_not_using_indexes

该参数限制每分钟记录到慢查询日志中的查询语句数量，默认值是0，不限制。

七、log_slow_extra

参数log_slow_extra从MySQL 8.0.14开始可用，当启用时，将记录与慢sql相关的额外信息，如状态参数Handler_%。参数默认值为off，建议打开，将参数设置为on。

mysql> set global log_slow_extra=on;
Query OK, 0 rows affected (0.00 sec)

2 使用mysqldumpslow解释慢查询日志

MySQL慢速查询日志包含执行时间较长的查询信息，且包含的记录较多时，看起来比较困难。可以使用mysqldumpslow解析MySQL慢速查询日志文件，并总结日志内容。

一、摘要分析

mysqldumpslow会对查询进行摘要分析，8.0版本新添的两个分析摘要函数如下：

1. statement_digest_text()：返回摘要文本；
2. statement_digest()：返回摘要hashvalue。

用法如下：

mysql> select statement_digest_text("select user(),host from mysql.user where user = 'lu9up'");
+----------------------------------------------------------------------------------+
| statement_digest_text("select user(),host from mysql.user where user = 'lu9up'") |
+----------------------------------------------------------------------------------+
| SELECT SYSTEM_USER ( ) , HOST FROM `mysql` . `user` WHERE SYSTEM_USER = ?        |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select statement_digest("select user(),host from mysql.user where user = 'lu9up'");
+-----------------------------------------------------------------------------+
| statement_digest("select user(),host from mysql.user where user = 'lu9up'") |
+-----------------------------------------------------------------------------+
| 12984e6ff7cbdbd28e2a377375af873fcd606891f82c670a74c04db83f7ac09c            |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

二、mysqldumpslow操作

调用语法：

mysqldumpslow [options] [log_file ...]

options：

-s指定排序方式，默认是at，根据平均时间排序，共有七种排序方式：

mysqldumpslow操作示例：

使用mysqldumpslow对慢查询日志文件进行分析，输出平均执行时间最久的两条查询：

[mysql@mysql001 ~]$ mysqldumpslow -s at -t 2 /disk1/data/mysql001-slow.log

Reading mysql slow query log from /disk1/data/mysql001-slow.log
Count: 1  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  select f.title, count(*) as cnt
  from sakila.rental r
  join sakila.inventory i
  on r.inventory_id = i.inventory_id
  join sakila.film f
  on i.film_id = f.film_id
  where r.rental_date between 'S' and 'S'
  group by f.film_id
  order by cnt desc
  limit N

Count: 8  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=4.9 (39), root[root]@localhost
  show variables like 'S'

3 使用pt-query-digest解析慢查询日志

pt-query-digest是Percona Toolkit的一个工具，用于分析MySQL的慢查询日志文件、通用查询日志文件和二进制日志文件中的查询，也可以分析SHOW PROCESSLIST命令输出的结果和tcpdump抓取的MySQL协议数据（如：网络流量包）。默认情况下，对所有分析的查询按摘要分组，分析结果按查询时间降序输出。

官方参考文档：https://docs.percona.com/percona-toolkit/pt-query-digest.html

3.1 安装pt-query-digest

一、下载Percona Toolkit：

[mysql@mysql001 ~]$ wget percona.com/get/pt-query-digest

二、赋权

[mysql@mysql001 ~]$ chmod +775 pt-query-digest

完成赋权后就可以正常使用了。

3.2 语法和选项

语法：

pt-query-digest [OPTIONS] [FILES] [DSN]

选项：

选项的具体使用细则参考官方文档：https://docs.percona.com/percona-toolkit/pt-query-digest.html#options

3.3 用法示例

1）直接分析慢查询文件

[mysql@mysql001 output]$ pt-query-digest /disk1/data/mysql001-slow.log > slow`date +"%Y%m%d"`.log
[mysql@mysql001 output]$ ll
total 20
-rw-rw-r-- 1 mysql mysql 17819 Dec 20 22:51 slow20231220.log

2）分析网络流量包

从3306端口抓取1000个流量包输出到文件mysql.tcp.txt：

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt

分析抓取的网路流量包：

pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log

3）分析pocesslist的输出

pt-query-digest --processlist h = host1

4）保存分析过的sql语句到表中

pt-query-digest --review h=192.168.131.99 --no-report mysql001-slow.log

默认保存的表是percona_schema.query_review。

5）保存分析结果到表中

pt-query-digest --history h=192.168.131.99 --no-report mysql001-slow.log

默认保存的表是percona_schema.query_history。