MySQL slow_log日志解读

作者：UStarGao 日期：2021-03-02 21:03:30 人气：栏目：MySQL

一、慢日志slow_log简介

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

1.1 慢查询开启状态/慢查询日志存放的位置

show variables like "slow_query%";

MySQL slow_log日志解读(图1)

SET GLOBAL slow_query_log=ON/OFF;      # 开启或关闭
SET GLOBAL long_query_time=n;                 # 设置慢查询记录阀值时间

1.2 查询超过多少秒才记录

show variables like "long_query_time";

1.3 开启记录没有使用索引查询语句

show variables like "log_queries%";

【注】开启该参数后，所有没有走索引的语句都会被记录到slow_log里面，包括update等操作，会直接导致slow_log文件或slow_log表大小暴增。

1.4 慢日志存储格式

show variables like 'log_output';    # 默认存储在表里，即TABLE

set global log_output='FILE';       # 也可以把慢日志存储到文件里，即FILE

MySQL slow_log日志解读(图6)

二、慢日志记录格式解读

2.1 MySQL slow_log表结构

MySQL slow_log日志解读(图3)

+----------------+---------------------+------+-----+----------------------+--------------------------------+
| Field          | Type                | Null | Key | Default              | Extra                          |
+----------------+---------------------+------+-----+----------------------+--------------------------------+
| start_time     | timestamp(6)        | NO   |     | CURRENT_TIMESTAMP(6) | on update CURRENT_TIMESTAMP(6) |
| user_host      | mediumtext          | NO   |     | NULL                 |                                |
| query_time     | time(6)             | NO   |     | NULL                 |                                |
| lock_time      | time(6)             | NO   |     | NULL                 |                                |
| rows_sent      | int(11)             | NO   |     | NULL                 |                                |
| rows_examined  | int(11)             | NO   |     | NULL                 |                                |
| db             | varchar(512)        | NO   |     | NULL                 |                                |
| last_insert_id | int(11)             | NO   |     | NULL                 |                                |
| insert_id      | int(11)             | NO   |     | NULL                 |                                |
| server_id      | int(10) unsigned    | NO   |     | NULL                 |                                |
| sql_text       | mediumblob          | NO   |     | NULL                 |                                |
| thread_id      | bigint(21) unsigned | NO   |     | NULL                 |                                |
+----------------+---------------------+------+-----+----------------------+--------------------------------+

MySQL slow_log日志解读(图4)

log_slow_admin_statements

【注】开启该参数后，主从同步进程binlog dump进程会记录到慢日志里面。

2.2 slow_log字段解读

# Time: 2020-12-15T10:04:48.000000 CST                              # log记录的时间
# User@Host: dbasy9kuser[dbasy9kuser] @  [10.19.67.56]              # SQL的执行主机
# Query_time: 3  Lock_time: 0  Rows_sent: 5  Rows_examined: 545599  # SQL 的执行信息（执行时间(单位：s)，锁时间，返回结果行数，查询总行数）
SET timestamp=1607997888                                            # SQL 执行发生的时间（时间戳格式）
SELECT * FROM `jz_server` WHERE sysid = 2 AND `nature` <> 4   ORDER BY `addtime`  DESC   LIMIT 0, 5; # SQL 的执行内容

三、慢日志获取方式

3.1 查询某个时间段排名前十的慢日志记录

SELECT  
  db, query_time, lock_time, start_time, sql_text  
FROM  
  mysql.slow_log  
WHERE  
  start_time  between  '2019-08-15 10:00:37' AND  '2019-08-15 10:25:37' ORDER BY query_time desc LIMIT 10 \G

MySQL slow_log日志解读(图5)

3.2 查询，慢查询开始时间、查询时间、返回结果的行数、扫描行数，并按照扫描行数字段倒序排列，输出前20行

mysql> select start_time,query_time,rows_sent,rows_examined from mysql.slow_log order by rows_examined desc limit 20;
+---------------------+------------+-----------+---------------+
| start_time          | query_time | rows_sent | rows_examined |
+---------------------+------------+-----------+---------------+
| 2021-05-27 05:48:01 | 04:48:01   |         0 |    1361366160 |
| 2021-05-26 05:34:27 | 04:34:27   |         0 |    1322974929 |
| 2021-05-25 05:24:22 | 04:24:22   |         0 |    1285056267 |
| 2021-05-24 05:19:23 | 04:19:23   |         0 |    1248321802 |
| 2021-05-23 05:08:33 | 04:08:33   |         0 |    1212539510 |
| 2021-05-22 05:09:01 | 04:09:01   |         0 |    1176998457 |
| 2021-05-21 04:53:06 | 03:53:06   |         0 |    1142168217 |
| 2021-05-20 04:42:48 | 03:42:48   |         0 |    1108856321 |
| 2021-05-19 04:36:32 | 03:36:32   |         0 |    1075943736 |
| 2021-05-18 04:27:37 | 03:27:37   |         0 |    1043378594 |
| 2021-05-17 04:20:57 | 03:20:57   |         0 |    1011539900 |
| 2021-05-16 04:13:28 | 03:13:28   |         0 |     979909261 |
| 2021-05-15 04:12:55 | 03:12:55   |         0 |     948554448 |
| 2021-05-14 03:59:42 | 02:59:42   |         0 |     917398775 |
| 2021-05-13 03:53:08 | 02:53:08   |         0 |     887432340 |
| 2021-05-12 03:47:03 | 02:47:03   |         0 |     857910049 |
| 2021-05-11 03:40:29 | 02:40:29   |         0 |     828897399 |
| 2021-05-10 03:32:04 | 02:32:04   |         0 |     800768561 |
| 2021-05-09 03:27:46 | 02:27:46   |         0 |     773313128 |
| 2020-12-24 01:09:07 | 00:09:07   |         0 |     764586293 |
+---------------------+------------+-----------+---------------+

3.3 统计某段时间内慢查询个数

select count(1) from mysql.slow_log where start_time>='2022-03-01 09:37:00' and start_time<='2022-03-01 09:42:00';

3.4 导出慢日志记录到本地

#（1）按查询开始时间降序排列，导出前100条慢SQL
mysql -uroot -h$IP -P$Port -p$Password -e "select * from mysql.slow_log order by start_time desc limit 100\G" >> slowlog.txt

#（2）导出某段时间范围内的慢日志信息
 mysql -uroot -h$IP -P$Port -p$Password -e \
 "select * from mysql.slow_log where start_time>='2022-03-01 09:37:00' and start_time<='2022-03-01 09:42:00'" >> slowlog.txt

四、清理慢日志slow_log

慢日志有两种存储格式：（1）TABLE格式（2）FILE格式。所以清理慢日志也有两种方法，详情参考以下内容~

4.1 清理慢日志slow_log表

set global slow_query_log=OFF;   # 关闭慢日志
set sql_log_bin=0;               # 临时关闭binlog（session级别）
truncate table mysql.slow_log;   # 清理慢日志
set global slow_query_log=ON;    # 再次开启慢日志

4.2 清理慢日志slow_log文件

> SET global slow_query_log=0;        # 关闭慢日志

> SHOW VARIABLES LIKE '%query_log%';  # 查看慢日志文件存放位置
+------------------------------+-------------------------------------------------------------------------------------+
| Variable_name                | Value                                                                               |
+------------------------------+-------------------------------------------------------------------------------------+
| binlog_rows_query_log_events | ON                                                                                  |
| slow_query_log               | ON                                                                                  |
| slow_query_log_file          | /opt/udb/instance/mysql-5.7/895b2228-a872-41a0-a0fc-913b6e4fd035/log/mysql-slow.log |
+------------------------------+-------------------------------------------------------------------------------------+

> SET global slow_query_log_file='/opt/udb/instance/mysql-5.7/895b2228-a872-41a0-a0fc-913b6e4fd035/log/mysql-slow-new.log';  # 设置一个新的慢查询文件

> SHOW VARIABLES LIKE '%query_log%';
+------------------------------+-----------------------------------------------------------------------------------------+
| Variable_name                | Value                                                                                   |
+------------------------------+-----------------------------------------------------------------------------------------+
| binlog_rows_query_log_events | ON                                                                                      |
| slow_query_log               | OFF                                                                                     |
| slow_query_log_file          | /opt/udb/instance/mysql-5.7/895b2228-a872-41a0-a0fc-913b6e4fd035/log/mysql-slow-new.log |
+------------------------------+-----------------------------------------------------------------------------------------+

> SET global slow_query_log=1; # 开启慢日志

# 最后删除slow_log物理文件即可。

作者：UStarGao
链接：https://www.starcto.com/mysql/96.html
来源：STARCTO
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

UCloud云平台推荐

UCloud新用户专属注册连接

UCloud CDN超值特惠专场

UCloud全球云主机（UHost/VPS）大促页面

UCloud快杰云主机大促页面

加载中~