20.慢查询日志分析

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

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

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

查看是否开启

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | OFF                             |
| slow_query_log_file | /var/lib/mysql/shinley-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.01 sec)

开启

只对当前数据库生效,重启后失效

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

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | ON                              |
| slow_query_log_file | /var/lib/mysql/shinley-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)

如果要永久生效,就必须修改配置文件my.cnf

修改my.cnf文件,[mysqld]下增加或修改参数slow_query_log和slow_query_log_file后,然后重启MySQL服务器

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

开启了慢查询日志后,什么样的SQL才会记录到慢查询日志里面呢?

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

命令:

mysql> SHOW VARIABLES LIKE'%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)

修改时间: 修改后看不出变化,需要重新打开终端,再次查看,才能看到效果。

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

mysql> SHOW VARIABLES LIKE'%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

重新打开终端查看

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

或者直接加 global查看

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

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

可以使用命令修改,也可以在my.cnf参数里面修改。

如果运行时间正好等于设置的时间,并不会记录下来。只有大于设置的时间才会记录。

【mysqld】下配置

slow_query_log=1;
slow_query_log_file=/var/lib/mysql/shinley-slow.log
long_query_time=3;
log_out_put=FILE;

模拟慢查询,延迟4秒

mysql> select sleep(4);
+----------+
| sleep(4) |
+----------+
|        0 |
+----------+
1 row in set (4.00 sec)

查询当前系统中有多少条慢查询记录

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

日志分析工具 mysqldumpslow

查看帮助

shinley@shinley:~$ mysqldumpslow -h;
Option h requires an argument
ERROR: bad option

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/shinley-slow.log

得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /var/lib/mysql/shinley-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/shinley-slow.log

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

mysqldumpslow -s r -t 10 /var/lib/mysql/shinley-slow.log