MySQL慢查询日志的使用
程序员文章站
2022-07-06 11:55:09
一. 设置方法 使用慢查询日志里捕获 启用之前需要先进行一些设置 方法一:全局变量设置 设置慢查询日志的日志文件位置 set global slow_query_log_file = "D:/slow_log/slow_log.log" ; 设置是否对未使用索引的SQL进行记录 set global ......
一. 设置方法
使用慢查询日志里捕获
启用之前需要先进行一些设置
方法一:全局变量设置
设置慢查询日志的日志文件位置
set global slow_query_log_file = "d:/slow_log/slow_log.log" ;
设置是否对未使用索引的sql进行记录
set global log_queries_not_using_indexes = on;
设置只要sql执行时间超过n秒的就记录
set global long_query_time = 0.001 ;
此处设置的0.001秒,便于测试,一般情况比这个大
启用mysql慢查询日志
set global slow_query_log = on;
方法二:配置文件设置
修改配置文件my.cnf,在[mysqld]下的下方加入
[mysqld] slow_query_log = on log_queries_not_using_indexes = on; slow_query_log_file = /usr/local/mysql/data/slow.log long_query_time = 1
查看设置后的参数
show variables like 'slow_query%'; show variables like 'long_query__time';
二. 慢查询日志记录的内容
time id command argument # time: 2019-01-08t04:12:09.269315z # user@host: h5_test[h5_test] @ localhost [::1] id: 12 # query_time: 0.000831 lock_time: 0.000198 rows_sent: 1 rows_examined: 3 use mc_productdb; set timestamp=1546920729; select t.customer_id,t.title,t.content from ( select customer_id from product_comment where product_id =199726 and audit_status = 1 limit 0,15 )a join product_comment t on a.customer_id = t.comment_id;
time:执行查询的日期时间
user@host:执行查询的用户和客户端ip
id:是执行查询的线程id
query_time:sql执行所消耗的时间
lock_time:执行查询对记录锁定的时间
rows_sent:查询返回的行数
rows_examined:为了返回查询的数据所读取的行数
三. 如何分析慢查询日志
usage: mysqldumpslow [ opts... ] [ logs... ] parse and summarize the mysql slow query log. options are --verbose verbose --debug debug --help write this text to standard output -v verbose -d debug -s order what to sort by (al, at, ar, c, l, r, t), 'at' is default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time -r reverse the sort order (largest last instead of first) -t num just show the top n queries -a don't abstract all numbers to n and strings to 's' -n num abstract numbers with at least n digits within names -g pattern grep: only consider stmts that include this string -h hostname hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i name name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time
由于慢查询日志中会含有大量的重复的sql,为了方便,可以通过mysql提供的命令行工具 mysqldumpslow 来分析日志
$ mysqldumpslow.pl slow_log.log reading mysql slow query log from slow_log.log count: 1 time=0.00s (0s) lock=0.00s (0s) rows=0.0 (0), 0users@0hosts c:\program files\mysql\mysql server n.n\bin\mysqld.exe, version: n.n.n-log (mysql community server (gpl)). started with: tcp port: n, named pipe: mysql # time: n-n-08t04:n:n.269315z # user@host: h5_test[h5_test] @ localhost [::n] id: n # query_time: n.n lock_time: n.n rows_sent: n rows_examined: n use mc_productdb; set timestamp=n; select t.customer_id,t.title,t.content from ( select customer_id from product_comment where product_id =n and audit_status = n limit n,n )a join product_comment t on a.customer_id = t.comment_id
与慢查询日志中记录的数据是相似的,只是多出了一行count,这一行记录的是这条sql在记录慢查询日志期间的执行次数,如果一个sql多次被执行,用这个命令分析时,只会出现一个sql日志,count里的数值代表执行次数,其他数字为了合并表示用n代替
上一篇: MySQL查询执行的基础