关于mysql:技术分享-Slow-Query-Log-使用详解

4次阅读

共计 4421 个字符，预计需要花费 12 分钟才能阅读完成。

作者：宓祥康
爱可生交付服务部团队 DBA 善于日志剖析、问题排查等；次要负责解决 MySQL 与我司自研数据库自动化治理平台 DMP 的日常运维问题，对数据库及周边技术有浓重的学习趣味。
本文起源：原创投稿

爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

什么是 Slow Query Log？
该如何应用它？
它的存在对运维数据库有什么帮忙呢？

慢查问日志，开启它咱们能够通过参数来管制其记录执行或查问工夫长的 SQL、全表扫描的 SQL、没有应用索引的 SQL。没错，它的作用就是记录哪些蹩脚的、让数据库变慢的 SQL，把它们揪出来。

咱们个别通过如下参数来管制 slow 的开启与记录：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：管制 slow_query_log 是否开启，参数 ON|OFF
slow_query_log_file：管制文件的写入地位，参数为文件的具体位置，如：/data/slow.log
long_query_time：设置 SQL 执行工夫大于等于多少秒（可准确到微秒）时记录到日志中
min_examined_row_limit：设置查看的行数大于等于多少行时记录到日志中
log_output：设置慢查问记录到哪里，参数 FILE|TABLE
log_queries_not_using_indexes：管制查问过程中未应用索引或全表扫描的 SQL 是否记录到日志中
log_throttle_queries_not_using_indexes：开启 log_queries_not_using_indexes 后，此参数会限度每分钟能够写入慢速查问日志的此类查问的数量，参数设置 0 为不限度

慢日志剖析的形式有两种，因为慢日志文件个别较小，所以一种形式为在慢日志文件中间接应用 less 或 more 命令来查看。第二种形式则是利用 MySQL 官网提供给咱们的程序：mysqldumpslow 来疾速查看 slowlog 日志中记录的慢 SQL。

对于咱们具体来剖析 SQL 的话，个别采纳第一种形式，查找到对应工夫点的对应 SQL 来进行剖析。

show master status;
# Time: 2020-11-16T08:27:16.777259+08:00
# User@Host: root[root] @ [127.0.0.1] Id: 248
# Query_time: 15.293745 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1605486436;

那么如何读懂慢日志外面对这些信息呢？

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 到底执行了多长时间的查问，有没有产生锁期待，此查问理论在数据库中读取了多少行数据了。

在开启 log_queries_not_using_indexes 后，slow log 文件不仅仅会记录慢查问日志，还会把查问过程中未应用索引或全表扫描的 SQL 记录到日志中，长此以往日志的空间便会变得越来越大，那么如何在线且平安的清空这些 slow log 日志，为磁盘开释空间呢？

MySQL 对于慢日志的输入形式反对两种，TABLE 和 FILE，查看办法如下：

mysql> show variables like '%log_output%'; 
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| log_output    | FILE,TABLE |
+---------------+------------+

确认分明输入形式后，能够别离对不同的输入形式抉择不同的清空办法，本次将对两种清空办法独特介绍。

查问 slow query log 开启状态

mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------+
| Variable_name       | Value                               |
+---------------------+-------------------------------------+
| slow_query_log      | ON                                  |
| slow_query_log_file | /opt/mysql/data/3306/mysql-slow.log |
+---------------------+-------------------------------------+

敞开 slow query log

mysql> set global slow_query_log=0;

确认敞开胜利

mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------+
| Variable_name       | Value                               |
+---------------------+-------------------------------------+
| slow_query_log      | OFF                                 |
| slow_query_log_file | /opt/mysql/data/3306/mysql-slow.log |
+---------------------+-------------------------------------+

对日志进行重命名或移除

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=1;

执行 SQL 进行验证

mysql> select sleep(5);
+----------+
| sleep(5) |
+----------+
|        0 |
+----------+

验证新生成文件记录胜利

cat /opt/mysql/data/3306/mysql-slow.log 
[root@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+08:00
# User@Host: root[root] @ localhost []  Id: 81786
# Query_time: 5.000397  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1609824404;
select sleep(5);

清理旧的 slowlog 文件

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

先敞开 slow query log

mysql> set global slow_query_log=0;

确认敞开胜利

mysql> show variables like 'slow_query_log';
+---------------------+-------------------------------------+
| Variable_name       | Value                               |
+---------------------+-------------------------------------+
| slow_query_log      | OFF                                 |
+---------------------+-------------------------------------+

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 = 1;

测试验证

mysql> select sleep(5);
+----------+
| sleep(5) |
+----------+
|        0 |
+----------+
mysql> select * from slow_log \G
*************************** 1. row ***************************
    start_time: 2021-01-05 13:49:13.864002
     user_host: root[root] @ 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