MySQL_慢查询日志

• MySQL的慢查询日志是 MySQL提供的一种日志记录，它用来记录在 MySQL中响应时间超过阀值的语句，具体指运行时间超过long_ query_time值的SQL，则会被记录到慢查询日志中。
• 具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。
• 由他来査看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合explain进行全面分析。

默认情况下， MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

查看是否开启及如何开启

• 查看默认：SHOW VARIABLES LIKE '%slow_query_log%';

  mysql> show variables like '%slow_query_log%';
  +---------------------+-----------------------------------+
  | Variable_name       | Value                             |
  +---------------------+-----------------------------------+
  | slow_query_log      | OFF                               |
  | slow_query_log_file | /var/lib/mysql/localhost-slow.log |
  +---------------------+-----------------------------------+
  2 rows in set (0.00 sec)
  

• 开启：SET GLOBAL slow_query_log = 1;

  mysql> SET GLOBAL slow_query_log = 1;
  Query OK, 0 rows affected (0.12 sec)
  
  mysql> show variables like '%slow_query_log%';
  +---------------------+-----------------------------------+
  | Variable_name       | Value                             |
  +---------------------+-----------------------------------+
  | slow_query_log      | ON                                |
  | slow_query_log_file | /var/lib/mysql/localhost-slow.log |
  +---------------------+-----------------------------------+
  2 rows in set (0.00 sec)
  

当前这样设置只是临时的，MySql重启后，参数便会被重置，如果要永久有效，需要修改my.cnf文件，在[mysqld]下面添加slow_query_log=1和slow_query_log_file=/var/lib/mysql/localhost-slow.log，然后重启MySql。

[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log
long_query_time=1
log_output=FILE

开启慢查询日志后，怎么样的SQL才会记录到慢查询日志里面呢？

命令：show variables like '%long_query_time%';

mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

这个是由 long_query_time 控制，默认情况下 long_query_time 的值为10秒

可以使用命令修改，也可以在my.cnf配置文件中修改。

假如 一个SQL运行的时间刚好等于 long_query_time 的情况，并不会被记录下来。也就是说，在mysql源码是判断大于 long_query_time 时间的才记录，而不是大于等于。

设置阙值时间：

命令：set global long_query_time=1 (为了测试，设置成1秒钟以上的为慢查询)

mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

查看阙值时间

命令：show global variables like 'long_query_time'

(注意这里使用的是show global variables，如果使用show variables无法看到，因为需要重新开启一个会话才能看到。)

mysql> show global variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> show variables like 'long_query_time'; #在修改阙值时间的本窗口中查询，依旧是10秒
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

设置好后，测试一下是否可行

sql语句：

mysql> SELECT COUNT(*) FROM `goods`;
+----------+
| COUNT(*) |
+----------+
|  1520000 |
+----------+
1 row in set (1.62 sec)

/var/lib/mysql/localhost-slow.log中的记录

# Time: 200110 13:58:46
# User@Host: root[root] @ localhost []  Id:    19
# Query_time: 1.613741  Lock_time: 0.000082 Rows_sent: 1  Rows_examined: 1520000
SET timestamp=1578664726;
SELECT COUNT(*) FROM `goods`;

查看慢sql条数

mysql> show global status like '%slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 14    |
+---------------+-------+
1 row in set (0.00 sec)

日志分析工具 mysqldumpslow

语法：mysqldumpslow [ OPTS... ] [ LOGS... ]

root@localhost:/var/lib/mysql# 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	返回前面多少条的数据
-g	后面搭配一个正则匹配模式，大小写不敏感

常用命令

得到返回记录集最多的10条sql
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log

得到访问次数最多的10个sql
mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log

另外建议在使用这些命令时结合 | 和 more 使用，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log | more