mysql 5.7 慢日志主要参数解析

原创谢辉元 2020-09-13

3991

当对mysql进行优化或者性能诊断时，大家一定首先会想到去查看mysql的慢查询日志（slow query log）,不过要使用好该功能还得先设置好相应的几个参数，本文就总结下关于慢查询日志的几个主要参数及其含义。
slow_query_log
slow_query_log为慢查询开关参数：参数类型：布尔值，1（on）表示开启， 0(off) 表示关闭，默认为关闭，全局参数，不支持会话级别修改。

参数文件设置：
slow_query_log = 1
动态修改：
[root@mysql.sock][xhy]> set slow_query_log=off;
ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL
修改后本会话也立即生效
[root@mysql.sock][xhy]> set global slow_query_log=off;  
Query OK, 0 rows affected (0.03 sec)

slow_query_log_file
slow_query_log_file 参数为指定慢日志文件路径和名称的参数，如不设置则默认值为路径：datadir+ 文件名：主机名-slow.log，全局参数

未设置该参数值为：
[root@mysql.sock][(none)]> show variables like '%datadir%';
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| datadir       | /data/mysql/mysql3307/data/ |
+---------------+-----------------------------+
1 row in set (0.00 sec)
[root@mysql.sock][(none)]> system hostname;
mysql1
[root@mysql.sock][(none)]> show variables like 'slow_query_log_file';
+---------------------+--------------------------------------------+
| Variable_name       | Value                                      |
+---------------------+--------------------------------------------+
| slow_query_log_file | /data/mysql/mysql3307/data/mysql1-slow.log |
+---------------------+--------------------------------------------+
1 row in set (0.00 sec)
参数文件设置：
slow_query_log_file = /data/mysql/mysql3306/logs/slow.log
动态修改：
[root@mysql.sock][xhy]> show variables like 'slow_query_log_file';
+---------------------+-------------------------------------+
| Variable_name       | Value                               |
+---------------------+-------------------------------------+
| slow_query_log_file | /data/mysql/mysql3306/logs/slow.log |
+---------------------+-------------------------------------+
1 row in set (0.00 sec)
[root@mysql.sock][xhy]> set slow_query_log_file='/data/mysql/mysql3306/logs/slow1.log';
ERROR 1229 (HY000): Variable 'slow_query_log_file' is a GLOBAL variable and should be set with SET GLOBAL
修改后本会话也立即生效
[root@mysql.sock][xhy]> set global slow_query_log_file='/data/mysql/mysql3306/logs/slow1.log';
Query OK, 0 rows affected (0.00 sec)
[root@mysql.sock][xhy]>  show variables like 'slow_query_log_file';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log_file | /data/mysql/mysql3306/logs/slow1.log |
+---------------------+--------------------------------------+
1 row in set (0.01 sec)

long_query_time
long_query_time为查询时间阈值设置参数，参数类型：数值型，默认值为10秒，精度为微妙（0.000001秒）支持全局级别和会话级别，可动态修改。

参数文件设置：
long_query_time = 1
动态修改：
--修改当前会话，当前会话立即生效
[root@mysql.sock][xhy]> set long_query_time=0.001;
Query OK, 0 rows affected (0.00 sec)
--修改全局会话，当前已登录的会话不生效，需退出重新登录生效
[root@mysql.sock][xhy]> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

log_queries_not_using_indexes

log_queries_not_using_indexes 为设置是否记录没有使用索引查询的SQL的参数，参数类型：布尔值，1（on）表示开启， 0(off) 表示关闭，默认值为关闭（0 or off）,全局动态参数。当开启该参数时，即使查询时间小于long_query_time设置的值只要是没有使用索引也会记录。

参数文件设置：
log_queries_not_using_indexes = 1
动态修改：
[root@mysql.sock][xhy]> set log_queries_not_using_indexes=0;
ERROR 1229 (HY000): Variable 'log_queries_not_using_indexes' is a GLOBAL variable and should be set with SET GLOBAL
[root@mysql.sock][xhy]> set global log_queries_not_using_indexes=0;
Query OK, 0 rows affected (0.00 sec)
如下测试打开log_queries_not_using_indexes参数效果
会话一：
[root@mysql.sock][xhy]>  show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

[root@mysql.sock][xhy]> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

[root@mysql.sock][xhy]> desc c;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| b     | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

[root@mysql.sock][xhy]> explain select count(*) from c where b<=1000;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 33120 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

[root@mysql.sock][xhy]> select count(*) from c where b<=1000;        
+----------+
| count(*) |
+----------+
|      562 |
+----------+
1 row in set (0.01 sec)

[root@mysql.sock][xhy]> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)

[root@mysql.sock][xhy]> select count(*) from c where b<=1001;
+----------+
| count(*) |
+----------+
|      563 |
+----------+
1 row in set (0.01 sec)

[root@mysql.sock][xhy]> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
1 row in set (0.00 sec)
slow log输出 select count(*) from c where b<=1001查询时间小于1秒也被记录到了慢日志：
# Time: 2020-09-14T01:26:49.949178+08:00
# User@Host: root[root] @ localhost []  Id:     6
# Query_time: 0.009820  Lock_time: 0.000064 Rows_sent: 1  Rows_examined: 32768
SET timestamp=1600018009;
select count(*) from c where b<=1001;

log_throttle_queries_not_using_indexes

log_throttle_queries_not_using_indexes 参数是配合log_queries_not_using_indexes 参数使用的，由于打开 log_queries_not_using_indexes参数可能会导致慢日志记录的数量极具增加（如果你的系统大部分的查询都是没有使用到索引的情况），log_throttle_queries_not_using_indexes参数就是为了限制记录每分钟没有使用索引的查询语句的数量，参数类型：整数型，默认为0 （不限制）该参数也为全局动态参数。

参数文件设置（如下设置每分钟记录10次）
log_throttle_queries_not_using_indexes = 10
动态修改：
[root@mysql.sock][xhy]> set log_throttle_queries_not_using_indexes=3;       
ERROR 1229 (HY000): Variable 'log_throttle_queries_not_using_indexes' is a GLOBAL variable and should be set with SET GLOBAL
[root@mysql.sock][xhy]> set global log_throttle_queries_not_using_indexes=3;  
Query OK, 0 rows affected (0.00 sec)
如下测试：
会话一：
[root@mysql.sock][xhy]> select count(*) from c where b<=1002;
+----------+
| count(*) |
+----------+
|      564 |
+----------+
1 row in set (0.01 sec)

[root@mysql.sock][xhy]> select count(*) from c where b<=1003;
+----------+
| count(*) |
+----------+
|      565 |
+----------+
1 row in set (0.01 sec)

[root@mysql.sock][xhy]> select count(*) from c where b<=1004;
+----------+
| count(*) |
+----------+
|      566 |
+----------+
1 row in set (0.02 sec)

[root@mysql.sock][xhy]> select count(*) from c where b<=1005;
+----------+
| count(*) |
+----------+
|      567 |
+----------+
1 row in set (0.01 sec)

[root@mysql.sock][xhy]> select count(*) from c where b<=1006;
+----------+
| count(*) |
+----------+
|      568 |
+----------+
1 row in set (0.01 sec)

slow log输出 只有到b<=1004到3条语句被记录下来，后面可以看到throttle:          3 'index not used' warning(s) suppressed.的警告：
# Query_time: 0.009333  Lock_time: 0.000102 Rows_sent: 1  Rows_examined: 32768
SET timestamp=1600018779;
select count(*) from c where b<=1002;
# Time: 2020-09-14T01:39:43.190728+08:00
# User@Host: root[root] @ localhost []  Id:     6
# Query_time: 0.009566  Lock_time: 0.000097 Rows_sent: 1  Rows_examined: 32768
SET timestamp=1600018783;
select count(*) from c where b<=1003;
# Time: 2020-09-14T01:39:45.391600+08:00
# User@Host: root[root] @ localhost []  Id:     6
# Query_time: 0.021960  Lock_time: 0.000103 Rows_sent: 1  Rows_examined: 32768
SET timestamp=1600018785;
select count(*) from c where b<=1004;
# Time: 2020-09-14T01:41:22.382265+08:00
# User@Host: [] @  []  Id:     6
# Query_time: 0.038686  Lock_time: 0.000257 Rows_sent: 1  Rows_examined: 32768
SET timestamp=1600018882;
throttle:          3 'index not used' warning(s) suppressed.;

mysql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

mysql 5.7 慢日志主要参数解析

评论