mysql慢查询分析

一、什么是慢查询
慢查询，顾名思义，执行很慢的查询。有多慢？超过long_query_time参数设定的时间阈值（默认10s），就被认为是慢的，是需要优化的。慢查询被记录在慢查询日志里。慢查询日志默认是不开启的。如果需要优化SQL语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的。

1️⃣show variables like 'slow_query_log';查询是否开启慢查询日志
【开启慢查询sql：set global slow_query_log = 1/on;】
【关闭慢查询sql：set global slow_query_log = 0/off;】
2️⃣show variables like 'log_queries_not_using_indexes';查询未使用索引是否开启记录慢查询日志
【开启记录未使用索引sql：set global log_queries_not_using_indexes=1/on】
【关闭记录未使用索引sql：set global log_queries_not_using_indexes=1/off】
3️⃣show variables like 'long_query_time';查询超过多少秒的记录到慢查询日志中
【设置超1秒就记录慢查询sql：set global long_query_time= 1;设置超1秒就记录】

二、慢查询的配置文件 my.cnf
在MySQL的配置文件my.cnf中写上：

long_query_time = 10
log-slow-queries = /var/lib/mysql/mysql-slow.log
1
2
long_query_time是指执行超过多久的SQL会被日志记录下来，这里是10 秒。
log-slow-queries设置把日志写在哪里。为空的时候，系统会给慢查询日志赋予主机名，并加上slow.log。如果设置了参数log-long-format，那么所有没有使用索引的查询也将被记录。

这是一个非常有用的日志。它对于性能的影响不大（假设所有查询都很快），并且强调了那些最需要注意的查询（丢失了索引或索引没有得到最佳应用）。

三、慢查询解读
第一行：记录时间
第二行：用户名 、用户的IP信息、线程ID号
第三行：执行花费的时间【单位：毫秒】、执行获得锁的时间、获得的结果行数、扫描的数据行数
第四行：这SQL执行的时间戳
第五行：具体的SQL语句

四. 日志分析工具mysqldumpslow，mysql官方自带的，只要安装了mysql就可以使用它，可以用来帮助我们分析慢日志文件

root@kickseed:/home# mysqldumpslow --help
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

 

 

-s 是表示按照何种方式排序，后面可以带如下排序方式

c: 访问次数

l: 锁定时间

r: 返回记录的行数

t: 查询时间

al:平均锁定时间

ar:平均返回记录的行数

at:平均查询时间

-t 是top n的意思，即为返回前面多少条的数据

-g 后边可以写一个正则匹配模式，大小写不敏感的

例子

得到返回记录行数最多的10条SQL语句（行数最多排序需要 -s r ；限制10条需要 -t 10）

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到访问次数最多的10个SQL（访问次数最多排序需要 -s c ；限制10条需要 -t 10）

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照执行时间排序的前10条，且sql语句里面含有左连接的查询语句（执行时间排序需要 -s t；限制10条需要 -t 10）

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | mor