简介

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
long_query_time的默认值为10，意思是运行10秒以上的语句。
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件

设置慢查询日志

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启。

设置

查看是否开启

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+---------------------------------------------------------------------+
| Variable_name       | Value                                                               |
+---------------------+---------------------------------------------------------------------+
| slow_query_log      | OFF                                                                 |
| slow_query_log_file | D:\software\mysql\mysql-5.5.54-winx64\data\DESKTOP-ERJBSMO-slow.log |
+---------------------+---------------------------------------------------------------------+
2 rows in set (0.08 sec)

使用命令开启

使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。

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

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+---------------------------------------------------------------------+
| Variable_name       | Value                                                               |
+---------------------+---------------------------------------------------------------------+
| slow_query_log      | ON                                                                  |
| slow_query_log_file | D:\software\mysql\mysql-5.5.54-winx64\data\DESKTOP-ERJBSMO-slow.log |
+---------------------+---------------------------------------------------------------------+
2 rows in set (0.06 sec)

修改慢查询的阀值

使用命令 set global long_query_time=1 修改为阙值到1秒钟的就是慢sql

修改后发现long_query_time并没有改变，需重新打开新连接查看。

mysql> set global long_query_time = 1;
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.08 sec)

打开新连接查看：

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

或者通过set session long_query_time=1来改变当前session变量;

配置永久生效

修改my.cnf文件，然后重启MySQL服务器

#【mysqld】下配置：
slow_query_log=1;
slow_query_log_file=D:\\software\\mysql\\mysql-5.5.54-winx64\\data\\slow-query.log
long_query_time=3
log_output=FILE

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------------------------------+
| Variable_name       | Value                                                     |
+---------------------+-----------------------------------------------------------+
| slow_query_log      | ON                                                        |
| slow_query_log_file | D:\software\mysql\mysql-5.5.54-winx64\data\slow-query.log |
+---------------------+-----------------------------------------------------------+
2 rows in set (2.12 sec)

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

查看

测试一条慢sql：

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

查看文件中是否有慢sql：

D:\software\mysql\mysql-5.5.54-winx64\bin\mysqld, Version: 5.5.54-log (MySQL Community Server (GPL)). started with:
TCP Port: 3307, Named Pipe: (null)
Time                 Id Command    Argument
# Time: 210404 17:42:07
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 5.005466  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use index_db;
SET timestamp=1617529327;
select sleep(5);

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

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

mysqldumpslow（日志分析工具）

查看mysqldumpslow的帮助信息

wushanghuideMacBook-Pro:~ wushanghui$ 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/slow.log
 
# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log
 
# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log
 
# 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log | more

Mysql 慢查询日志

简介