MySQL 慢查询日志

1、MySQL慢查询的配置属性

A.查看慢查询的开启状态及慢查询的日志存储目录

  1. show variables like '%slow_query%';

image.png
慢查询日志的存储未指定文件名会默认生成”主机名-slow.log”的文件。

B.查询慢查询的日志记录时间

  1. show variables like 'long_query_time';

image.png

2、慢查询日志的开启

A.通过全局命令开启

  1. mysql> set global slow_query_log='ON'; 
  2. mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';
  3. mysql> set global long_query_time=1;

B.通过配置文件配置并重启MySQL服务

①.修改配置my.cnf或者my.ini文件并保存

在配置文件的[mysqld]下面添加一行内容 slow_query_log=1 即可开启慢查询，其他配置内容如下

  1. [mysqld]
  2. slow_query_log = ON
  3. slow_query_log_file = /usr/local/mysql/data/slow.log
  4. long_query_time = 1

image.png

②.重启MySQL服务

  1. Microsoft Windows [版本 10.0.18363.900]
  2. (c) 2019 Microsoft Corporation。保留所有权利。
  4. C:\WINDOWS\system32>net stop mysql
  5. MySQL 服务正在停止.
  6. MySQL 服务已成功停止。
  9. C:\WINDOWS\system32>net start mysql
  10. MySQL 服务正在启动 ..
  11. MySQL 服务已经启动成功。

image.png

③.打开MySQL客户端通过 show variables like '%slow_query%'; 查询慢查询开启状态

image.png

3、测试慢查询的日志记录

  1. mysql> select sleep(2);
  2. +----------+
  3. | sleep(2) |
  4. +----------+
  5. |        0 |
  6. +----------+
  7. 1 row in set (2.01 sec)
  9. mysql> select sleep(11);
  10. +-----------+
  11. | sleep(11) |
  12. +-----------+
  13. |         0 |
  14. +-----------+
  15. 1 row in set (11.00 sec)
  17. mysql>

image.png
查看慢查询日志保存
image.png
日志中保存了查询超过10s的SQL语句

4、记录慢查询日志到数据库表

A.在配置文件添加如下命令行

mysql库下面有一个默认的slow_log表，可以直接将slow_query_log_file = slow_log，即可将慢查询日志记录到表中。

  1. ##配置慢查询
  2. slow_query_log=1
  3. log_output = table

image.png

B.重启MySQL服务

  1. Microsoft Windows [版本 10.0.18363.900]
  2. (c) 2019 Microsoft Corporation。保留所有权利。
  4. C:\WINDOWS\system32>net stop mysql
  5. MySQL 服务正在停止..
  6. MySQL 服务已成功停止。
  9. C:\WINDOWS\system32>net start mysql
  10. MySQL 服务正在启动 ..
  11. MySQL 服务已经启动成功。
  14. C:\WINDOWS\system32>

image.png

C.通过命令查看慢查询日志输出到表是否成功

  1. mysql> show variables like '%log_output%';
  2. +---------------+-------+
  3. | Variable_name | Value |
  4. +---------------+-------+
  5. | log_output    | TABLE |
  6. +---------------+-------+
  7. 1 row in set, 1 warning (0.01 sec)
  9. mysql>

image.png

D.在MySQL表中查看慢查询的日志记录

  1. mysql> select * from mysql.slow_log;
  2. +----------------------------+------------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+------------------+-----------+
  3. | start_time                 | user_host                    | query_time      | lock_time       | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text         | thread_id |
  4. +----------------------------+------------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+------------------+-----------+
  5. | 2020-07-10 23:43:40.290020 | root[root] @ localhost [::1] | 00:00:11.000904 | 00:00:00.000000 |         1 |             0 |    |              0 |         0 |         1 | select sleep(11) |         8 |
  6. | 2020-07-10 23:44:10.887578 | root[root] @ localhost [::1] | 00:00:11.000009 | 00:00:00.000000 |         1 |             0 |    |              0 |         0 |         1 | select sleep(11) |         8 |
  7. +----------------------------+------------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+------------------+-----------+
  8. 2 rows in set (0.00 sec)
  10. mysql>

image.png
如果查询出的sql_text列中为二进制文本需要通过convert函数进行转换

  1. select convert(sql_text using utf8) sql_text from mysql.slow_log;

再查看慢查询日志记录文件中未持续更新保存，所有新记录全在MySQL表中
image.png

5、MySQL慢查询记录到日志文件和表中的区别

  • 慢查询记录到日志文件和表中，记录本身差别不大，如果是记录在表中，慢查询的执行时间信息无法精确到微妙，
  • 如果将慢查询信息记录在表中，方便查询，但因为是结构化的数据，可能会比记录在慢查询日志文件中（平面文本文件）要慢一点，如果是记录到文件，需要mysqldumpslow工具解析。
  • 慢查询不记录执行失败的查询，比如long_query_time设置为10（10秒钟），一个查询超过了10秒钟，但是因为其他原因执行失败，MySQL的慢查询将无法记录此查询信息。