有时候我们需要排查执行缓慢的SQL语句,这就用到了mysql慢查询日志。
开启慢查询日志的方式有两种:临时开启和永久开启。
开启慢查询日志功能可能需要mysql的版本达到5.7,可以通过select VERSION();查看版本号。
看一下当前mysql数据库是否开启了慢查询
show variables like 'slow_query%';
show variables like 'long_query%';
slow_query_log 慢查询开启状态,ON开启,OFF关闭
slow_query_log_file 慢查询日志存放的位置(这个目录需要MySQL的运行帐号的可写权限,一般设置为MySQL的数据存放目录)
long_query_time 查询超过多少秒才记录(才算是慢查询)
一、临时开启(数据库服务重启后失效)
set global slow_query_log_file='/var/lib/mysql/tmp_slow.log';
set global long_query_time=1;
set global log_output='FILE,TABLE'; //默认是FILE。如果也有TABLE,则同时输出到mysql库的slow_log表中。
set global slow_query_log='ON';
long_query_time设置后需要打开一个新的查询窗口(会话)才能看到新设置的值。老的查询窗口还是显示之前的值,其实已经改了。
二、永久开启(数据库服务重启后不失效)
修改配置文件my.cnf,在[mysqld]下的下方加入
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/tmp_slow.log //linux
long_query_time = 1
然后重启mysql服务。
=========================================
至于选择哪种开启方式,需要根据实际情况决定。比如我要查看生产环境上面执行缓慢的SQL查询,就不方便重启mysql服务,这时候临时开启就是不错的选择。
接下来可以执行个慢查询语句select SLEEP(2);
cat -n /var/lib/mysql/tmp_slow.log看一下慢查询日志里面,已经有了这个慢查询的语句。(n替换为数字)
当前直接查看慢查询日志不太好看,有些眼花缭乱,可以通过mysql自带的工具查看
mysqldumpslow -t 10 /var/lib/mysql/tmp_slow.log #显示出慢查询日志中最慢的10条sql
如果mysql服务在docker容器里,可以这样执行查看
docker exec -i 79d3204efc6b mysqldumpslow -t 10 /var/lib/mysql/tmp_slow.log
79d3204efc6b是mysql的容器ID,用你自己的,这个不用说了吧。
mysqldumpslow还有一些其他参数,可以通过mysqldumpslow --help查看
慢查询用法
mysqldumpslow能将相同的慢SQL归类,并统计出相同的SQL执行的次数,每次执行耗时多久、总耗时,每次返回的行数、总行数,以及客户端连接信息等。
mysqldumpslow语法说明:
-s ,按照什么方式起来排序。默认at,也就是按照平均查询时间来排序。都是按照倒序排列。
al: average lock time 平均锁定时间
ar: average rows sent 平均返回行数
at: average query time 平均查询时间
c: count 总执行次数
l: lock time 总锁定时间
r: rows sent 总返回行数
t: query time 总查询时间
-t ,show the top n queries,显示前多少名的记录
-a ,默认不开启这个选项。mysqldumpslow将相似的SQL的值(字符串或者数字)替换为N,开启该选项,则显示真实值。不开启该选项,有点类似于Oracle的绑定变量的记录。
-g ,类似于grep命令,过滤出需要的信息。如,只查询A表的慢查询记录。
-l ,总时间中包含锁定时间
如果要看到详细的sql情况,可以执行下面命令
docker exec -i ab4b71771201 mysqldumpslow -a -s t -t 10 /var/lib/mysql/tmp_slow8.log