一、开启慢查询日志
1.永久开启
要开启MySQL慢查询日志,需要修改MySQL服务器的配置文件my.cnf
或者my.ini
。
在配置文件中加上如下配置:
# 开启慢查询日志 1:开启|0:关闭
slow_query_log = 1
# 指定慢查询的阈值,默认为10秒
long_query_time = 10
# 指定慢查询日志的保存位置 (如果路径不存在一定要手动创建,否者可能会因为权限不够,无法记录慢查询日志)
slow_query_log_file = /var/log/mysql/mysql-slow.log
完成配置后,需要重启MySQL服务器才能使配置生效。
2.临时开启
SET GLOBAL slow_query_log = 1;
# 目录必须存在,且有读写权限,不然会报错
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 10;
# 日志输出格式 TABLE|FILE
SET GLOBAL log_output = 'FILE';
临时开启的方法下,不需要重启MySQL就可以生效
,但是MySQL重启后会失效
。
注意: 设置完可能需要重新打开会话窗口查询
才能发现配置生效了!
二、常用命令
# 查看慢查询日志的完整路径和文件名
show variables like 'slow_query_log_file';
# 查看当前MySQL服务器的慢查询日志是否开启
show variables like 'slow_query_log';
# 查询慢查询的阈值
show variables like '%long_query_time%';
# 查询慢查询日志输出方式
show variables like '%log_output%';
三、分析慢查询日志
mysqldumpslow 工具是慢查询自带的分析慢查询工具,一般只要安装了mysql,就会有该工具。
完整语法如下所示:
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] -- 后跟参数以及log文件的绝对地址;
-s 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
mysqldumpslow
命令常见的分析和优化查询日志文件语句如下:
# 取出使用最多的10条慢查询
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log
# 取出查询时间最慢的3条慢查询
mysqldumpslow -s t -t 3 /var/log/mysql/mysql-slow.log
# 取出查询时间最慢的前10条里面含有左连接的慢查询
mysqldumpslow -s t -t 10 -g 'left join' /var/log/mysql/mysql-slow.log
# 取出按照扫描行数最多的前10条慢查询
mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log
其中,-s t
表示按查询总时间
排序,-s c
表示按查询次数
排序,-t 10
表示显示前10条
查询信息。