简介
- 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
评论