MYSQL进阶-msql日志-慢查询日志

2 慢查询日志

慢查询日志主要用来记录执行时间超过设置的某个时长的SQL语句，能够帮助数据库维护人员找出执行时间比较长、执行效率比较低的SQL语句，并对这些SQL语句进行针对性优化。

2.1 开启慢查询日志

可以在my.cnf文件或者my.ini文件中配置开启慢查询日志。

[mysqld]
slow_query_log = 1
slow_query_log_file = /data/mysql/log/query_log/slow_statement.log
long_query_time = 10
log_output = FILE

各配置项说明如下：

·slow_query_log：指定是否开启慢查询日志。指定的值为1或者不指定值都会开启慢查询日志；指定的值为0或者不配置此选项就不会开启慢查询日志。

·slow_query_log_file：慢查询日志的文件位置。

·long_query_time：指定SQL语句执行时间超过多少秒时记录慢查询日志。

·log_output：与查询日志的log_output选项相同，此处不再赘述。

注意：log_output能够配置将日志记录到数据表中还是记录到文件中，当记录到数据表中时，则数据表中记录的慢查询时间只能精确到秒；如果是记录到日志文件中，则日志文件中记录的慢查询时间能够精确到微秒。建议在实际工作中，将慢查询日志记录到文件中。

配置完成后，重启MySQL服务器配置才能生效。

除了在文件中配置开启慢查询日志外，也可以在MySQL命令行中执行如下命令开启慢查询日志。

mysql> SET GLOBAL slow_query_log = 1;
 Query OK, 0 rows affected (0.00 sec)
 mysql> SET GLOBAL slow_query_log_file = '/data/mysql/log/query_log/slow_statement.log'; 
Query OK, 0 rows affected (0.00 sec) 
mysql> SET GLOBAL long_query_time = 10;
 Query OK, 0 rows affected (0.00 sec)
 mysql> SET GLOBAL log_output = 'FILE';
  Query OK, 0 rows affected (0.00 sec)

成功开启慢查询日志后，会在/data/mysql/log/query_log目录下生成slow_statement.log文件。

[root@binghe150 ~]# ll /data/mysql/log/query_log total 4 -rw-r----- 1 mysql mysql 177 Jan 17 14:01 slow_statement.log

2.2 查看慢查询日志

慢查询日志如果配置的是输出到文件，则会保存到纯文本文件中，直接查看纯文本文件的内容即可。接下来简单介绍查看慢查询日志的步骤。

（1）查询t_goods_category数据表中的数据。

mysql> SELECT * FROM t_goods_category;
+----+---------------------+
| id | t_category          |
+----+---------------------+
|  1 | 女装/女士精品       |
|  2 | 户外运动            |
|  3 | 男装                |
|  4 | 童装                |
|  5 | 运动                |
|  6 | 食品                |
+----+---------------------+
6 rows in set (0.00 sec)

（2）查看slow_statement.log文件中的内容。

[root@binghe150 ~]# cat /data/mysql/log/query_log/slow_statement.log 
/usr/local/mysql/bin/mysqld, Version: 8.0.18 (binghe edition). started with:
Tcp port: 3306  Unix socket: /data/mysql/run/mysql.sock
Time                 Id Command    Argument

发现查询t_goods_category数据表中数据的SQL语句并没有记录到slow_statement.log文件中，这是因为查询t_goods_category数据表中数据的SQL语句的执行时间并未超过long_query_time选项配置的10s。

（3）构造一个查询时间超过10s的SQL语句。

mysql> SELECT BENCHMARK(99999999, MD5('mysql'));
+-----------------------------------+
| BENCHMARK(99999999, MD5('mysql')) |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (21.60 sec)

结果显示，SQL语句的执行耗费了21.6s。

（4）再次查看slow_statement.log文件中的内容。

[root@binghe150 ~]# cat /data/mysql/log/query_log/slow_statement.log 
/usr/local/mysql/bin/mysqld, Version: 8.0.18 (binghe edition). started with:
Tcp port: 3306  Unix socket: /data/mysql/run/mysql.sock
Time                 Id Command    Argument

Time: 2020-01-17T06:11:57.064272Z # User@Host: root[root] @ localhost [] Id: 8 # Query_time: 21.600894 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
use goods;
SET timestamp=1579241495;
SELECT BENCHMARK(99999999, MD5('mysql'));

slow_statement.log文件中记录了SQL语句SELECT BENCHMARK(99999999,MD5(‘mysql’))。

2.3 删除慢查询日志

慢查询日志和查询日志一样以纯文本文件的形式存储在服务器磁盘中，可以直接删除。如果需要重新生成慢查询日志，可以在MySQL命令行中运行FLUSH LOGS命令，或者在服务器命令行中执行mysqladmin flush-logs命令。

（1）删除慢查询日志。

rm -rf /data/mysql/log/query_log/slow_statement.log

删除后，查看/data/mysql/log/query_log目录下的文件。

[root@binghe150 ~]# ll /data/mysql/log/query_log
total 0

结果显示，slow_statement.log文件已经被成功删除。

（2）在MySQL命令行中刷新日志。

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.01 sec)

或者在服务器命令行中执行如下命令刷新日志。

[root@binghe150 ~]# mysqladmin -uroot -p flush-logs
Enter password:

日志刷新成功后，再次查看/data/mysql/log/query_log目录下的文件。

[root@binghe150 ~]# ll /data/mysql/log/query_log   
total 4
-rw-r----- 1 mysql mysql 354 Jan 17 14:22 slow_statement.log

MySQL重新创建了slow_statement.log文件。

2.4 关闭慢查询日志

关闭慢查询日志，只需要在my.cnf文件或者my.ini文件中配置slow_query_log=0或者直接删除此选项即可。

[mysqld]
slow_query_log = 0

也可以在MySQL命令行中执行如下命令关闭慢查询日志。

mysql> SET GLOBAL slow_query_log = 0;
Query OK, 0 rows affected (0.00 sec)

当关闭慢查询日志后，删除慢查询日志文件，再执行刷新日志的操作，MySQL将不再重新创建慢查询日志文件。