MySql之慢查询日志

1、慢查询日志

（1）默认mysql数据库没有开启慢查询日志，需要手动设置这个参数
（2）如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志或多或少的会带来一定的性能影响。慢查询日志支持将日志记录写入文件

2、查看是否开启：SHOW VARIABLES LIKE '%slow_query_log%'

（1）默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启
（2）开启 set global slow_query_log=1;使用该命令只对当前数据库生效，如果数据库重启则会失效。若要永久生效需要配置my.cnf。修改[mysqld]下增加slow_query_log和slow_query_log_file

slow_query_log=1
slow_query_log_file=/var/lib/mysql/mysql-slow.log

3、mysql慢查询时间

（1）默认情况下10s为慢查询sql，这个参数是由long_query_time控制。查看时间：SHOW VARIABLES LIKE '%long_query_time%'，设置慢的阙值时间可执行set global long_query_time=3 (设置3秒)
（2）也可以修改配置my.cnf.
（3）当执行时间大于long_query_time才会记录，即被认为是慢sql

4、慢查询分析：

（1）已经设置慢查询阈值为3s；
（2）执行sql select sleep(4)，查看日志

TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
# Time: 171204 19:51:18
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 4.000023(实际查询时间)  Lock_time: 0.000000 （锁时间）Rows_sent: 1  Rows_examined: 0
use account;
SET timestamp=1512388278; #事件戳
SELECT SLEEP(4); #导致超时的sql

5、查询系统慢查询的sql数量：执行show global status like '%slow_queries%'。

6、最终实现配置需要在[mysqld]下添加

slow_query_log=1 #开启慢查询日志
slow_query_log_file=/var/lib/mysql/mysql-slow.log #日志写入文件配置
long_query_time=3 #设置sql执行超时阈值单位:秒
log_output=FILE

7、日志查询分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析sql，显然是个体力活，mysql提供了日志分析工具mysqldumpslow。可以执行mysqldumpslow --help

[root@node-3 ~]# 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