MySQL日志之Slow Query Log(慢查询日志)

06 Database 评论

1. 什么是慢查询日志

Mysql的慢查询日志是用来记录在mysql中响应时间超过阈值的语句，具体指运行时间超过long_query_time(默认10s)值的sql（不包含10s）。

它的主要作用是：帮助我们发现哪些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统整体效率。当我们的数据库发生阻塞、运行变慢的时候，检查以下慢查询日志，找到那些慢查询，对解决问题很有帮助。比如一条sql执行超过5秒钟，我们就算慢SQL，结合explain进行全面分析。

默认情况下，mysql数据库没有开启慢查询日志，如果不需要调优的化，一般不建议启动该参数，因为开启慢查询日志会带来一些性能影响。

2. 慢查询日志相关操作

2.1 开启slow_query_log

1
2
3
4
5
6
7
8
9
10
11
12
13

mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------------+
| Variable_name       | Value                                     |
+---------------------+-------------------------------------------+
| slow_query_log      | OFF                                       |
| slow_query_log_file | /usr/local/mysql/data/xiaoyuge-2-slow.log |
+---------------------+-------------------------------------------+
2 rows in set (0.00 sec)

#开启慢查询
mysql> set global slow_query_log = 'ON';
Query OK, 0 rows affected (0.00 sec)

2.2 修改long_query_time阈值

使用如下命令查看慢查询的时间阈值设置：

1
2
3
4
5
6
7
mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

意思是：超过10秒的SQL语句就会被记录慢查询日志中。

1
2
3
4
5
6
7
8
9
10
mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

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

或者修改my.cnf文件，在[mysqld]下增加或修改参数long_query_time、slow_query_log和slow_query_log_file后，重启Mysql服务器

1
2
3
4
5
[mysqld]
slow_query_log=ON   #开启慢查询日志的开关
slow_query_log_file=/usr/local/mysql/data/xiaoyuge-2-slow.log   #慢查询日志的目录和文件名信息
long_query_time=5   #设置慢查询的阈值为5秒，超出此设定值的SQL即被记录到慢查询日志
log_output=FILE     # 一般有两种形式，一种是输出到文件FILE中，一种是写入数据表格table中，会保存到mysql库的slow_log表中

如果不指定存储路径，慢船日志将默认存储到Mysql数据库的数据文件夹下，如果不指定文件名，默认文件名为hostname-slow.log

2.3 其他常用参数

2.3.1 min_examined_row_limit

除了上述变量，控制慢查询日志的还有一个系统变量:min_examined_row_limit（最小记录数）。这个变量和查询执行时间共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过long_query_time值，那么这个查询会被记录到慢查询日志中，反之则不记录。

1
2
3
4
5
6
7
mysql> show variables like 'min_examined_row_limit';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+
1 row in set (0.01 sec)

也可以通过修改my.cnf，来修改min_examined_row_limit的值。

2.3.2 log-queries-not-using-indexes

除了记录普通的慢查询之外，Mysql还提供了两个参数来让我们记录未使用索引的查询，它们分别是：log-queries-not-using-indexes和log_throttle_queries_not_using_indexes。

系统变量log_queries_not_using_indexes的作用是未使用索引的查询也被记录到慢查询日志中

1
2
3
4
5
6
7
mysql> show variables like 'log_queries_not_using_indexes%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

2.3.3 log_throttle_queries_not_using_indexes

可以通过设置log_throttle_queries_not_using_indexes来限制每分钟写入慢查询日志中不走索引的SQL语句个数，默认为0，表示不开启，也就是说不对写入SQL语句条数进行控制。

1
2
3
4
5
6
7
mysql> show variables like 'log_throttle%';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_throttle_queries_not_using_indexes | 0     |
+----------------------------------------+-------+
1 row in set (0.00 sec)

在生产环境下，如果没有使用索引，那么此类SQL语句会频繁被记录到slow log，从而导致slow log文件大小不断增加。

2.3.4 log_slow_extra

如果启用 log_slow_extra 系统变量（从 MySQL 8.0.14 开始提供），服务器会在日志写入几个额外字段。若要记录bytes_received 与 bytes_sent这两个字段则需要开启

3. 查看慢查询数目

查询当前系统中有多少条慢查询记录：

1
2
3
4
5
6
7
mysql> show global status like '%slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

4. 慢查询分析工具

在生产环境中，如果需要手工分析日志，查找、分析SQL，显然是个体力活。mysql提供了日志分析工具mysqldumpslow，或则使用pt-query-digest。它可以从logs、processlit 和tcpdump来分析Mysql的状况。
logs包括：slow log、general log、binlog。 可以把分析结果输出到文件中，或者把文件写到表中。分析过程是先对查询局的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。

5. 关闭慢查询

Mysql 服务器停止慢查询日志功能有如下两种方式：

  • 修改my.cnf

    1
    2
    [mysqld]
    slow_query_log=OFF

  • 执行SQL

    1
    SET GLOBAL slow_query_log=off;

6. 删除慢查询日志

1
2
3
4
5
6
7
8
mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------------+
| Variable_name       | Value                                     |
+---------------------+-------------------------------------------+
| slow_query_log      | OFF                                       |
| slow_query_log_file | /usr/local/mysql/data/xiaoyuge-2-slow.log |
+---------------------+-------------------------------------------+
2 rows in set (0.00 sec)

通过以上查询可以看到慢查询日志的目录，在该目录下手动删除慢查询日志文件即可。或者使用命令mysqladmin来删除，语法如下：mysqladmin -uroot - p flush logs。
新的慢查询日志会直接覆盖旧的慢查询日志，不需要手动删除。

慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意，一旦执行了这个命令，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，就必须事先备份

小余哥xiaoyouge

分享一些技术教程与实践，以及日常踩坑记录。