MySQL slow_log日志解读
一、慢日志slow_log简介
MySQL慢查询日志用来记录在 MySQL 中执行时间超过指定时间(long_query_time 参数控制 )的查询SQL语句。通过慢查询日志,可以查找出哪些查询语句的执行效率低,以便进行优化。
1.1 慢查询开启状态/慢查询日志存放的位置
show variables like "slow_query%";
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
二、慢日志记录格式解读
2.1 MySQL slow_log表结构
+----------------+---------------------+------+-----+----------------------+--------------------------------+ | 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 | | +----------------+---------------------+------+-----+----------------------+--------------------------------+
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
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云平台推荐
随便看看
- 2021-01-26K8S核心概念
- 2021-04-23Linux 预防SSH暴力破解神器—DenyHosts
- 2021-08-22Smokeping+Promethues+Grafana搭建网络质量监控
- 2021-09-27MongoDB数据导入导出工具详解
- 2021-03-20Docker容器网络管理