MySQL 慢查询日志

慢查询日志用来记录在 MySQL 中执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率低，以便进行优化。

默认情况下，慢查询日志功能是关闭的。可以通过以下命令查看是否开启慢查询日志功能，开启慢查询日志可能会有一定的性能影响。命令和执行过程如下：

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/c629e6102239-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.01 sec)

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

参数说明：

• slow_query_log：慢查询开启状态
• slow_query_log_file：慢查询日志存放的位置（一般设置为 MySQL 的数据存放目录），默认的文件名为{hostname}-slow.log
• long_query_time：查询超过多少秒才记录，默认为 10 秒，最小为 0，精度可达微秒

设置慢查询日志

可以通过log-slow-queries选项开启慢查询日志。通过long_query_time选项来设置时间值，时间以秒为单位。如果查询时间超过了这个时间值，这个查询语句将被记录到慢查询日志。

[mysqld]
slow_query_log=ON
slow_query_log_file=/var/log/mysql/long_query.log
long_query_time=1

• slow_query_log：设置为ON开启慢查询
• slow_query_log_file：指定慢查询的存放位置
• long_query_time：指定慢查询阈值，单位为秒

还可以使用log_output参数来指定日志的输出方式，默认会输出到文件，当然也可以选择输出到表，需要注意的是，如果选择输出到表，则表中记录的慢查询时间只能精确到秒，而日志文件中可以精确到微秒。

也可以通过命令行临时开启：

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

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

日志的读取

查看慢查询日志

和错误日志、查询日志一样，慢查询日志记录的格式也是纯文本，可以被直接读取。

1. 查询log_query_time设置的值

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

2. 为了方便测试，这里将long_query_time设置为 1s。

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

3. 执行一个“慢”查询

   mysql> select sleep(1);
   +----------+
   | sleep(1) |
   +----------+
   |        0 |
   +----------+
   1 row in set (1.00 sec)
   

   这个查看耗时大于等于 1s ，会出现在慢查询中。

4. 查看慢查询日志

   # more  long_query.log 
   # Time: 2023-04-05T08:47:45.868233-05:00
   # User@Host: root[root] @ devstack [192.168.122.10]  Id:     9
   # Query_time: 1.000723  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
   use sakila;
   SET timestamp=1680698865;
   select sleep(1);
   

   日志说明：

• 慢查询以#作为起始符号。上面的# Time: 2023-04-05T08:47:45.868233-05:00开始记录了一个慢查询
  • User@Host：表示用户和慢查询的连接地址。上述表示 root 用户，devstack 地址，Id为 9。
• Query_time：表示 SQL 查询的耗时，单位为秒
  • Lock_time：表示获取锁的时间，单位为秒
  • Rows_sent：表示发送给客户端的行数
  • Rows_examined：表示服务器层检查的行数
  • use sakila;表示使用sakila数据库
  • SET timestamp：表示慢 SQL 记录时的时间戳
  • 最后一行表示慢查询 SQL 语句。上面的是：select sleep(1);

mysqldumpslow工具

如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL 客户端安装自带）来对慢查询日志进行分类汇总。

下例中对日志文件long_query.log进行了分类汇总，只显示汇总后摘要结果：

# mysqldumpslow long_query.log 

Reading mysql slow query log from long_query.log
Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select sleep(N) as a, N as b

Count: 1  Time=1.00s (1s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@devstack
  select sleep(N)

对于 SQL 文本完全一致，只是变量不同的语句，mysqldumpslow将会自动视为同一个语句进行统计，变量值用N来代替。这个统计结果将大大增加用户阅读慢查询日志的效率，并迅速定位系统的 SQL 瓶颈。

注意事项

在默认情况下，有两类常见语句不会记录到慢查询日志：

• 管理语句
• 不使用索引进行查询的语句。

这里的管理语句包括 ALTER TABLE、ANALYZE TABLE、CHECK TABLE、CREATE INDEX、 DROP INDEX、OPTIMIZE TABLE和 REPAIR TABLE。如果要监控这两类 SQL 语句，可以分别通过参数log_slow_admin_statements和log_queries_not_using_indexes进行控制。