慢查询日志文件

开启慢查询日志记录

在MySQL中，慢查询日志的记录需要通过手动开启，可以在控制台通过set命令来设置，如下

#开启慢查询日志
set global slow_query_log='ON';

#设置慢查询日志存放的位置
set global slow_query_log_file='/user/local/mysql/data/slow.log';         //linux
set global slow_query_log_file='D:\\mysql\data\show.log';				 //windows

#设置慢查询语句时间(当查询语句执行时间超过指定时间(等于不会记录)，就会被记录，单位：s)
#从5.1版本之后，该时间设置可以精确到毫秒
set global long_query_time=1;

通过控制台设置后，如果服务重启就会导致之前的设置失效，我们可以通过MySQL的配置文件进行配置，如下所示

[mysqld]
#开启慢查询日志
slow_query_log = ON
#指定慢查询日志的文件路径
slow_query_log_file = E:\mysql\mysql-5.7.30-winx64\data\WIN-RLSMADHPTDP-slow.log
#也可以通过参数log_output指定日志输出形式为表slow_log
#log_output= TABLE
long_query_time = 2

在MySQL实例的mysql数据库中，存在一张slow_log表，可以用用作记录慢查询日志，相比于文件日志，表记录的查询时间只能精确到秒级。

开启慢查询功能后，会根据配置产生慢查询日志，一条慢查询日志的数据组成如下：

“Time: 2021-04-05T07:50:53.243703Z”：查询执行时间
“User@Host: root[root] @ localhost []  Id:     3”：用户名 、用户的IP信息、线程ID号
“Query_time: 0.000495”：执行花费的时长【单位：秒】
“Lock_time: 0.000170”：执行获得锁的时长
“Rows_sent”：获得的结果行数
“Rows_examined”：扫描的数据行数
“SET timestamp”：这SQL执行的具体时间
最后一行：执行的SQL语句

慢查询参数其他参数配置
如果SQL语句没有使用索引查询，则可以通过设置log_queries_not_using_indexes将该查询日志记录到慢查询日志里面进行分析，开启SQL语句的索引检验

#控制台设置
set global log_queries_not_using_indexes='ON';

#配置文件设置
[mysqld]
log_queries_not_using_indexes=ON

从MySQL5.6.5版本开始，新增了一个参数log_throttle_queries_not_using_indexes，表示每分钟允许记录到slow log的且未使用索引的SQL语句次数。该值默认为0，表示没有限制，为了防止慢查询日志过多的记录导致文件过于庞大，可以根据需求手动设置

#控制台设置
set global log_throttle_queries_not_using_indexes=10;

#配置文件设置
[mysqld]
log_throttle_queries_not_using_indexes=10;

MySQL提供了慢查询日志分析工具mysqldumpslow，汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所指定的顺序输出。

例如用户希望的到执行时间最长的10条SQL语句，运行命令如下：

mysqldumpslow -s r -t 10 slow-mysql.log

相关参数说明：

-s order (c,t,l,r,at,al,ar) 
         c:总次数
         t:总时间
         l:锁的时间
         r:获得的结果行数
         at,al,ar :指t,l,r平均数  【例如：at = 总时间/总次数】
-s 对结果进行排序，怎么排，根据后面所带的 (c,t,l,r,at,al,ar)，缺省为at
-t  NUM       just show the top n queries：仅显示前n条查询
-g PATTERN   grep: only consider stmts that include this string：通过grep来筛选语句。

slow_log表
在数据库实例下的mysql库中，可以查看到slow_log表的模型，show create table slow_log

CREATE TABLE slow_log (
 start_time timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE 				CURRENT_TIMESTAMP(6),
 user_host mediumtext NOT NULL,
 query_time time(6) NOT NULL,
 lock_time time(6) NOT NULL,
 rows_sent int(11) NOT NULL,
 rows_examined int(11) NOT NULL,
 db varchar(512) NOT NULL,
 last_insert_id int(11) NOT NULL,
 insert_id int(11) NOT NULL,
 server_id int(10) unsigned NOT NULL,
 sql_text mediumblob NOT NULL,
 thread_id bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'

从表模型中可以发现，slow_log的存储引擎用的是CSV，对大数据量下的查询效率可能不高，可以将slow_log表的引擎转换为MyISAM，并根据需求在指定的字段加上索引以进一步提高查询效率。需要注意的是，将slow_log表的存储引擎更改为MyISAM后，还是会对数据库造成额外的开销

slow log对SQL语句的捕获

InnoSQL版本加强了对SQL语句的捕获方式。在原版MySQL的基础上在slow log中添加了逻辑读取(logical reads)和物理读取(physical reads)。物理读取是指从磁盘进行IO读取的次数，逻辑读取包含所有的读取，不管是磁盘还是缓冲池。

从上面的例子中可看出该子查询的的逻辑读次数为91584次，而物理读取次数为19次。从逻辑读与物理读的比例上来看，该SQL语句可进行优化。
在《MySQL技术内部 InnoDB存储引擎》一书中，还介绍了long_query_io和slow_quert_type参数，用户可以通过额外的参数long_query_io将超过指定逻辑IO次数的SQL语句记录到slow log中。该值默认为100，即表示对于逻辑读取次数大于100的SQL语句，记录到slow log中，为了兼容原MySQL数据库的运行方式，还添加了参数slow_quert_type，用来表示启用slow log的方式，可选值
0 表示不将SQL语句记录到slow log
1 表示根据运行时间将SQL语句记录到slow log
2 表示根据逻辑IO次数将SQL语句记录到slow log
3 表示根据运行时间及逻辑IO次数将SQL语句记录到slow log
注：关于long_query_io和slow_query_type这两个参数，在MySQL的官方文档中，均未找到

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/153702.html

慢查询日志文件

相关推荐

发表回复