参考网址:
MySQL 官方5.7手册:https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html
1. 慢查询日志
慢查询日志主要用来记录 执行时间超过设定的某个时长的 SQL 语句 。
慢查询日志由 long_query_time
执行时间超过几秒并且至少 min_examined_row_limit
需要检查行的 SQL 语句组成。慢查询日志可用于查找需要很长时间执行的查询。
默认情况下,有两类常见语句不会记录到慢查询日志中:管理语句和不使用索引进行查询的语句 。
这里的管理语句包括 alter table
、analyze table
、check table
、create index
、drop index
、optimize table
、repair table
。如果要监控这两类语句,可以通过参数 log-slow-admin-statements 和 log_queries_not_using_indexes 进行控制。
1.1 开启慢查询日志
可以在 my.cnf
或 my.ini
文件中的 [mysqld]
项下配置,来开启慢查询日志。
[mysqld]
slow_query_log = 1
slow_query_log_file = D:\Tools\phpstudy_pro\Extensions\MySQL5.7.26\data\DESKTOP-TF6324F.log
long_query_time = 1
min_examined_row_limit = 100
log_output = FILE
配置项说明:
slow_query_log
:指定是否开启慢查询日志。指定的值为 1 或者 不指定值 都会开启慢查询日志;指定值为 0 或者 不配置此项 就不会开启慢查询日志。slow_query_log_file
:慢查询日志的文件位置,默认在data
目录下,文件名为host_name-slow.log
。long_queyr_time
:指定 SQL 语句执行时间超过多少秒时记录慢查询日志。log_output
:与查询日志的log_output
选项相同。min_examined_row_limit
:查询语句的执行检查行数(Rows_examined)少于该参数指定行的SQL不会被记录到慢查询日志中。
注意:log_output
能够配置将日志记录到数据表中还是记录到文件中,当记录到数据表中时,则数据表中记录的慢查询时间只能精确到 秒;如果是记录到日志文本中,则日志文件中记录的慢查询时间能够精确到 微秒 。建议在实际工作中,将慢查询日志记录到文件中 。
在文件中配置完成后,重启 MySQL 服务器配置就可以生效了。
除了在配置文件中开启慢查询日志外,也可以通过在 MySQL 命令行中执行如下命令开启慢查询日志:
mysql> SET GLOBAL slow_query_log = 1;
Query OK, 0 rows affected (0.01 sec)
mysql> SET GLOBAL slow_query_log_file = D:/Tools/phpstudy_pro/Extensions/MySQL5.7.26/log/slow_log.log;
Query OK, 0 rows affected (0.01 sec)
mysql> SET GLOBAL long_query_time = 1;
Query OK, 0 rows affected (0.01 sec)
mysql> SET GLOBAL log_output = 'file';
Query OK, 0 rows affected (0.01 sec)
开启慢查询日志后,会在 slow_query_log_file
项指定的路径下生成对应的日志文件。
1.2 查看慢查询日志
慢查询日志如果配置的是输出到文件,则会保存到纯文本文件中,直接查看日志文本的内容即可。
D:\Tools\phpstudy_pro\COM\..\Extensions\MySQL5.7.26\\bin\mysqld.exe, Version: 5.7.26-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument
# Time: 2021-08-24T13:44:08.510454Z
# User@Host: root[root] @ localhost [::1] Id: 2
# Query_time: 0.022057 Lock_time: 0.002959 Rows_sent: 39971 Rows_examined: 39971
use demo;
SET timestamp=1629812648;
select * from tp_users;
构造一个耗时的SQL查询语句
mysql> select benchmark(10000000, md5('mysql'));
+-----------------------------------+
| benchmark(10000000, md5('mysql')) |
+-----------------------------------+
| 0 |
+-----------------------------------+
1 row in set (1.38 sec)
然后查看慢查询日志文件
# Time: 2021-08-24T14:23:18.086324Z
# User@Host: root[root] @ localhost [::1] Id: 4
# Query_time: 1.363166 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1629814998;
select benchmark(10000000, md5('mysql'));
慢查询记录说明:
- 第一行:代表记录慢查询日志的时间。注意:这是 MySQL 服务器的时间,可能和当地时间不同。
- 第二行:记录的 MySQL 的链接信息。
- 第三行:是语句的执行情况,分别为
- Query_time :语句的执行时间,以秒为单位;
- Lock_time :获取锁的时间,以秒为单位;
- Rows_sent :发送到客户端的行数,也可以理解为查询出来的结果行数;
- Rows_examined :服务器曾检查的行数,不包括存储引擎内部的任何处理;
- 第四行:是语句真正发生的时间
- 第五行:具体执行的 SQL 语句。
1.3 删除慢查询日志
慢查询日志和查询日志一样,以纯文本文件的形式存储在服务器磁盘中,可以直接删除。
注意,如果直接删除文件,需要执行刷新命令后才会重新生成日志文件。如果只是删除了日志中的内容,则不需要执行刷新命令。
MySQL 命令中刷新日志
mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.02 sec)
或者在服务器命令行中执行如下命令来刷新日志
[root@root ~]# mysqladmin -uroot -p flush-logs
Enter password:
日志刷新成功后,再次查看日志目录下的文件,就会发现一级重新创建了查询日志文件了。
1.4 关闭慢查询日志
关闭慢查询日志,只需要在 my.cnf
或 my.ini
文件的 [mysqld]
配置项中,将 slow_query_log
项的值修改为 0
或者直接删除此项即可。
也可以在 MySQL 命令行中执行如下命令来关闭慢查询日志
mysql> SET GLOBAL slow_query_log = 0;
Query OK, 0 rows affected (0.02 sec)
当关闭慢查询日志后,删除慢查询日志文件,在执行刷新日志的操作,MySQL 将不再重新创建慢查询日志文件。
2. 慢查询日志分析工具
如果慢查询日志中记录内容很多,可以使用 mysqldumpslow
工具来对慢查询日志进行分类汇总
mysqldumpslow
工具是一个 perl 脚本,需要在 perl 环境下才能运行
一般 MySQL 完整版内置了 mysqldumpslow
工具,一般在 MySQL 的安装目录 bin 目录下 会有一个 mydumpslow.pl
文件。
而 cmder
工具则是一个便携式的控制台模拟器,它内置的 perl 环境
然后打开在 mysql/bin 目录下打开cmder ,运行如下代码就可以了
perl mysqldumpslow.pl slow_log.log
也可以使用 --help
命令查看帮助说明
perl mysqldumpslow.pl --help