mysqlslowlog中querytime分析_技术分享|SlowQueryLog使用详解

作者&＃xff1a;宓祥康

爱可生交付服务部团队 DBA 擅长日志分析、问题排查等&＃xff1b;主要负责处理 MySQL 与我司自研数据库自动化管理平台 DMP 的日常运维问题&＃xff0c;对数据库及周边技术有浓厚的学习兴趣。

本文来源&＃xff1a;原创投稿

爱可生开源社区出品&＃xff0c;原创内容未经授权不得随意使用&＃xff0c;转载请联系小编并注明来源。

引言

什么是 Slow Query Log&＃xff1f;

该如何使用它&＃xff1f;

它的存在对运维数据库有什么帮助呢&＃xff1f;

一、 简介

慢查询日志&＃xff0c;开启它我们可以通过参数来控制其记录执行或查询时间长的 SQL、全表扫描的 SQL、没有使用索引的 SQL。没错&＃xff0c;它的作用就是记录哪些糟糕的、让数据库变慢的 SQL&＃xff0c;把它们揪出来。

我们一般通过如下参数来控制 slow 的开启与记录&＃xff1a;slow_query_log、slow_query_log_file、long_query_time、min_examined_row_limit、log_output、log_queries_not_using_indexes、log_throttle_queries_not_using_indexes。

slow_query_log&＃xff1a;控制 slow_query_log 是否开启&＃xff0c;参数 ON|OFF

slow_query_log_file&＃xff1a;控制文件的写入位置&＃xff0c;参数为文件的具体位置&＃xff0c;如&＃xff1a;/data/slow.log

long_query_time&＃xff1a;设置 SQL 执行时间大于等于多少秒(可精确到微秒)时记录到日志中

min_examined_row_limit&＃xff1a;设置检查的行数大于等于多少行时记录到日志中

log_output&＃xff1a;设置慢查询记录到哪里&＃xff0c;参数 FILE|TABLE

log_queries_not_using_indexes&＃xff1a;控制查询过程中未使用索引或全表扫描的 SQL 是否记录到日志中

log_throttle_queries_not_using_indexes&＃xff1a;开启 log_queries_not_using_indexes 后&＃xff0c;此参数会限制每分钟可以写入慢速查询日志的此类查询的数量&＃xff0c;参数设置 0 为不限制

二、查看方式与内容分析

慢日志分析的方式有两种&＃xff0c;因为慢日志文件一般较小&＃xff0c;所以一种方式为在慢日志文件中直接使用 less 或 more 命令来查看。第二种方式则是利用 MySQL 官方提供给我们的程序&＃xff1a;mysqldumpslow 来快速查看 slowlog 日志中记录的慢 SQL。

对于我们详细来分析 SQL 的话&＃xff0c;一般采用第一种方式&＃xff0c;查找到对应时间点的对应 SQL 来进行分析。

show master status;

# Time: 2020-11-16T08:27:16.777259&＃43;08:00

# User&＃64;Host: root[root] &＃64; [127.0.0.1] Id: 248

# Query_time: 15.293745 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0

SET timestamp&＃61;1605486436;

那么如何读懂慢日志里面对这些信息呢&＃xff1f;

show master status #慢 SQL

Time #出现该慢 SQL 的时间

query_time # SQL 语句的查询时间(在 MySQL 中所有类型的 SQL 语句执行的时间都叫做 query_time,而在 Oracle 中则仅指 select)

lock_time: #锁的时间

rows_sent: #返回了多少行,如果做了聚合就不准确了

rows_examined: #执行这条 SQL 处理了多少行数据

SET timestamp #时间戳

通过这些我们就可以来明确的知道一条 SQL 究竟执行了多长时间的查询&＃xff0c;有没有发生锁等待&＃xff0c;此查询实际在数据库中读取了多少行数据了。

三、如何在线安全清空 slow.log 文件

在开启 log_queries_not_using_indexes 后&＃xff0c;slow log 文件不仅仅会记录慢查询日志&＃xff0c;还会把查询过程中未使用索引或全表扫描的 SQL 记录到日志中&＃xff0c;久而久之日志的空间便会变得越来越大&＃xff0c;那么如何在线且安全的清空这些 slow log 日志&＃xff0c;为磁盘释放空间呢&＃xff1f;

MySQL 对于慢日志的输出方式支持两种&＃xff0c;TABLE 和 FILE&＃xff0c;查看方法如下&＃xff1a;

mysql> show variables like &＃39;%log_output%&＃39;;

&＃43;---------------&＃43;------------&＃43;

| Variable_name | Value |

&＃43;---------------&＃43;------------&＃43;

| log_output | FILE,TABLE |

&＃43;---------------&＃43;------------&＃43;

确认清楚输出方式后&＃xff0c;可以分别对不同的输出方式选择不同的清空方法&＃xff0c;本次将对两种清空方法共同介绍。

3.1 FILE 类型清空方法

查询 slow query log 开启状态

mysql> show variables like &＃39;%slow_query_log%&＃39;;

&＃43;---------------------&＃43;-------------------------------------&＃43;

| Variable_name | Value |

&＃43;---------------------&＃43;-------------------------------------&＃43;

| slow_query_log | ON |

| slow_query_log_file | /opt/mysql/data/3306/mysql-slow.log |

&＃43;---------------------&＃43;-------------------------------------&＃43;

关闭 slow query log

mysql> set global slow_query_log&＃61;0;

确认关闭成功

mysql> show variables like &＃39;%slow_query_log%&＃39;;

&＃43;---------------------&＃43;-------------------------------------&＃43;

| Variable_name | Value |

&＃43;---------------------&＃43;-------------------------------------&＃43;

| slow_query_log | OFF |

| slow_query_log_file | /opt/mysql/data/3306/mysql-slow.log |

&＃43;---------------------&＃43;-------------------------------------&＃43;

对日志进行重命名或移除

mv /opt/mysql/data/3306/mysql-slow.log /opt/mysql/data/3306/mysql-old-slow.log

重新开启 slow query log

mysql> set global slow_query_log&＃61;1;

执行 SQL 进行验证

mysql> select sleep(5);

&＃43;----------&＃43;

| sleep(5) |

&＃43;----------&＃43;

| 0 |

&＃43;----------&＃43;

验证新生成文件记录成功

cat /opt/mysql/data/3306/mysql-slow.log

[root&＃64;DMP1 3306]# cat mysql-slow.log

/opt/mysql/base/5.7.31/bin/mysqld, Version: 5.7.31-log (MySQL Community Server (GPL)). started with:

Tcp port: 3306 Unix socket: /opt/mysql/data/3306/mysqld.sock

Time Id Command Argument

# Time: 2021-01-05T13:26:44.001647&＃43;08:00

# User&＃64;Host: root[root] &＃64; localhost [] Id: 81786

# Query_time: 5.000397 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0

SET timestamp&＃61;1609824404;

select sleep(5);

清理旧的 slowlog 文件

mv /opt/mysql/data/3306/mysql-old-slow.log /mysqlback

3.2 TABLE 类型清空方法

先关闭 slow query log

mysql> set global slow_query_log&＃61;0;

确认关闭成功

mysql> show variables like &＃39;slow_query_log&＃39;;

&＃43;---------------------&＃43;-------------------------------------&＃43;

| Variable_name | Value |

&＃43;---------------------&＃43;-------------------------------------&＃43;

| slow_query_log | OFF |

&＃43;---------------------&＃43;-------------------------------------&＃43;

TABLE 类型的 slowlog 存放在 mysql.slow_log 表中,对 slow_log 进行重命名为 old_slow_log

mysql> use mysql

mysql> ALTER TABLE slow_log RENAME old_slow_log;

创建全新的 slow_log 文件拷贝原来 slow_log 文件的表结构

mysql> CREATE TABLE slow_log LIKE old_slow_log;

启动 slow query log

mysql> SET GLOBAL slow_query_log &＃61; 1;

测试验证

mysql> select sleep(5);

&＃43;----------&＃43;

| sleep(5) |

&＃43;----------&＃43;

| 0 |

&＃43;----------&＃43;

mysql> select * from slow_log \G

*************************** 1. row ***************************

start_time: 2021-01-05 13:49:13.864002

user_host: root[root] &＃64; localhost []

query_time: 00:00:05.000322

lock_time: 00:00:00.000000

rows_sent: 1

rows_examined: 0

db: mysql

last_insert_id: 0

insert_id: 0

server_id: 874143039

sql_text: select sleep(5)

thread_id: 339487

删除旧的 slow_log 表

mysql> drop table old_slow_log;

总结

该文章主要讲述了 slow log 的开启方式、分析方法与清空操作&＃xff0c;熟练使用分析 slow log 文件可以实时观察数据库 SQL 的执行情况&＃xff0c;并为 SQL 优化奠定基础。