MySQL慢查询日志的使用

一. 设置方法

使用慢查询日志里捕获

启用之前需要先进行一些设置

方法一：全局变量设置

设置慢查询日志的日志文件位置

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代替