MySQL慢查询日志的配置和使用 - MySQL 慢查询的定位以及分析工具的使用 - 《SQL》

开启慢查询
- 修改配置文件
- 设置全局变量
慢查询日志文件分析工具
- mysqldumpslow
- pt-query-digest
show processlist

MySQL 慢查询
在平时工作中都有过 SQL 优化经历，那么在优化前就必须找到慢 SQL 方可进行分析。
慢查询日志是 MySQL 内置的一项功能，可以记录执行超过指定时间的 SQL 语句。
以下是慢查询的相关参数：

参数	含义
slow_query_log	是否启用慢查询日志， ON为启用，OFF为没有启用，默认为OFF
log_output	日志输出位置，默认为 FILE，即保存为文件，若设置为 TABLE，则将日志记录到 mysql.show_log 表中，支持设置多种格式
slow_query_log_file	指定慢查询日志文件的路径和名字，可使用绝对路径指定，默认值是主机名-slow.log，位于配置的 datadir 目录
long_query_time	执行时间超过该值才记录到慢查询日志，单位为秒，默认为 10
min_examined_row_limit	对于查询扫描行数小于此参数的SQL，将不会记录到慢查询日志中，默认为 0
log_queries_not_using_indexes	是否将未使用索引的 SQL 记录到慢查询日志中，开启此配置后会无视 long_query_time 参数，默认为 OFF
log_throttle_queries_not_using_indexes	设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间，默认为 0
log-slow-admin-statements	记录执行缓慢的管理 SQL，如 ALTER TABLE、ANALYZE TABLE、CHECK TABLE、CREATE INDEX、DROP INDEX、OPTIMIZE TABLE 和 REPAIR TABLE，默认为 OFF
log_slow_slave_statements	记录从库上执行的慢查询语句，如果 binlog 的值为 row，则失效，默认为 OFF

执行如下语句看是否启用慢查询日志，ON为启用，OFF为没有启用

show variables like "%slow_query_log%"

开启慢查询

有两种方式可以开启慢查询

修改配置文件

设置全局变量

修改配置文件

方式一需要修改配置文件 my.ini，在[mysqld]段落中加入如下参数：

[mysqld]
log_output='FILE,TABLE'
slow_query_log='ON'
long_query_time=0.001

:::tips 然后需要重启 MySQL 才可以生效，命令为 service mysqld restart :::

设置全局变量

方式二无需重启即可生效，但是重启会导致设置失效，设置的命令如下所示：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL log_output = 'FILE,TABLE';
SET GLOBAL long_query_time = 0.001;

这样就可以将慢查询日志同时记录在文件以及 mysql.slow_log 表中。
通过第二种方式开启慢查询日志，然后使用全表查询语句 SELECT * FROM user
然后再查询慢查询日志：SELECT * FROM mysql.slow_log，可以发现其中有这样一条记录：

其中，start_time 为执行时间，user_host 为用户的主机名，query_time 为查询所花费的时间，lock_time 为该查询使用锁的时间，rows_sent 为这条查询返回了多少数据给客户端，rows_examined 表示这条语句扫描了多少行，db 为数据库，sql_text 为这条 SQL，thread_id 为执行这条查询的线程 id。
这样就可以通过 slow_log 表的数据进行分析，然后对 SQL 进行调优了。
以上是通过 Table 来进行分析的，下面来通过文件的慢查询是怎么样的。
如果不知道文件保存在哪里，可以使用 SHOW VARIABLES LIKE '%slow_query_log_file%' 来查看文件保存位置，打开慢查询日志文件，可以看出每五行表示一个慢 SQL，这样查看比较费事，可以使用一些工具来查看。
MySQL 慢查询的定位以及分析工具的使用 - 图3

慢查询日志文件分析工具

mysqldumpslow

MySQL 内置了 mysqldumpslow 这个工具来分析慢查询日志文件，Windows 环境下使用该工具需要安装 Perl 环境。
可以通过 -help 来查看它的命令参数：
MySQL 慢查询的定位以及分析工具的使用 - 图4

常见用法

# 取出使用最多的10条慢查询
mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log
# 取出查询时间最慢的3条慢查询
mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log 
# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log

比如可以通过 mysqldumpslow -s t 10 LAPTOP-8817LKVE-slow.log 命令得到按照查询时间排序的 10 条 SQL 。

pt-query-digest

除此之外还有 pt-query-digest，这个是 Percona Toolkit 中的工具之一，下载地址：[https://www.percona.com/downloads/percona-toolkit/LATEST/](https://www.percona.com/downloads/percona-toolkit/LATEST/)，如果是 Windows 系统，可以在安装 Perl 的环境下，把脚本下载下来：

[https://raw.githubusercontent.com/percona/percona-toolkit/3.x/bin/pt-query-digest](https://raw.githubusercontent.com/percona/percona-toolkit/3.x/bin/pt-query-digest)

下面先对 pt-query-digest 进行简单的介绍：
pt-query-digest 是用于分析 MySQL 慢查询的一个第三方工具，可以分析 binlog、General log 和 slowlog，也可以通过 showprocesslist 或者通过 tcpdump 抓取的 MySQL 协议数据来进行分析，可以把分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。

下载和赋权

wget www.percona.com/get/pt-query-digest
chmod u+x pt-query-digest
ln -s /opt/soft/pt-query-digest /usr/bin/pt-query-digest

用法介绍

// 查看具体使用方法 
pt-query-digest --help
// 使用格式
pt-query-digest [OPTIONS] [FILES] [DSN]

「常用OPTIONS」

--create-review-table  当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。
--create-history-table  当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。
--filter  对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
--host  mysql服务器地址
--user  mysql用户名
--password  mysql用户密码
--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。
--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。
--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
--until 截止时间，配合—since可以分析一段时间内的慢查询。

「常用DSN」

A    指定字符集
D    指定连接的数据库
P    连接数据库端口
S    连接Socket file
h    连接数据库主机名
p    连接数据库的密码
t    使用--review或--history时把数据存储到哪张表里
u    连接数据库用户名

DSN使用key=value的形式配置；多个DSN使用,分隔
「使用示例」

# 展示slow.log中最慢的查询的报表
pt-query-digest slow.log
# 分析最近12小时内的查询
pt-query-digest --since=12h slow.log
# 分析指定范围内的查询
pt-query-digest slow.log --since '2020-06-20 00:00:00' --until '2020-06-25 00:00:00'
# 把slow.log中查询保存到query_history表
pt-query-digest --user=root --password=root123 --review h=localhost,D=test,t=query_history --create-review-table slow.log
# 连上localhost，并读取processlist，输出到slowlog
pt-query-digest --processlist h=localhost --user=root --password=root123 --interval=0.01 --output slowlog
# 利用tcpdump获取MySQL协议数据，然后产生最慢查询的报表
# tcpdump使用说明：https://blog.csdn.net/chinaltx/article/details/87469933
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt
# 分析binlog
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest  --type=binlog mysql-bin000093.sql
# 分析general log
pt-query-digest  --type=genlog  localhost.log

用法实战

「编写存储过程批量造数据」
在实际工作中没有测试性能，经常需要改造大批量的数据，手动插入是不太可能的，这时候就得用到存储过程了

CREATE TABLE `kf_user_info` (
`id` int(11) NOT NULL COMMENT '用户id',
`gid` int(11) NOT NULL COMMENT '客服组id',
`name` varchar(25) NOT NULL COMMENT '客服名字'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户信息表';

如何定义一个存储过程呢？

CREATE PROCEDURE 存储过程名称 ([参数列表])
BEGIN
 需要执行的语句
END

举个例子，插入id为1-100000的100000条数据
用Navicat执行

-- 删除之前定义的
DROP PROCEDURE IF EXISTS create_kf;
-- 开始定义
CREATE PROCEDURE create_kf(IN loop_times INT) 
BEGIN
DECLARE var INT;
SET var = 1;
WHILE var < loop_times DO    
INSERT INTO kf_user_info (`id`,`gid`,`name`) 
VALUES (var, 1000, var);
SET var = var + 1;
END WHILE; 
END;
-- 调用
call create_kf(100000);

存储过程的三种参数类型

参数类型	是否返回	作用
IN	否	向存储过程传入参数，存储过程中修改该参数的值，不能被返回
OUT	是	把存储过程计算的结果放到该参数中，调用者可以得到返回值
INOUT	是	IN和OUT的结合，即用于存储过程的传入参数，同时又可以把计算结构放到参数中，调用者可以得到返回值

用MySQL执行
得用DELIMITER 定义新的结束符，因为默认情况下SQL采用（;）作为结束符，这样当存储过程中的每一句SQL结束之后，采用（;）作为结束符，就相当于告诉MySQL可以执行这一句了。但是存储过程是一个整体，不希望SQL逐条执行，而是采用存储过程整段执行的方式，因此就需要定义新的DELIMITER，新的结束符可以用（//）或者（$$）
因为上面的代码应该就改为如下这种方式

DELIMITER //
CREATE PROCEDURE create_kf_kfGroup(IN loop_times INT)  
BEGIN  
 DECLARE var INT;
 SET var = 1;
 WHILE var <= loop_times DO    
  INSERT INTO kf_user_info (`id`,`gid`,`name`) 
  VALUES (var, 1000, var);
  SET var = var + 1;
 END WHILE;  
END //
DELIMITER ;

查询已经定义的存储过程

show procedure status;

「开始执行慢sql」

select * from kf_user_info where id = 9999;
select * from kf_user_info where id = 99999;
update kf_user_info set gid = 2000 where id = 8888;
update kf_user_info set gid = 2000 where id = 88888;

可以执行如下sql查看慢sql的相关信息。

SELECT * FROM mysql.slow_log order by start_time desc;

查看一下慢日志存储位置

show variables like "slow_query_log_file"

pt-query-digest /var/lib/mysql/VM-0-14-centos-slow.log

执行后的文件如下 MySQL 慢查询的定位以及分析工具的使用 - 图6

# Profile
# Rank Query ID                            Response time Calls R/Call V/M 
# ==== =================================== ============= ===== ====== ====
#    1 0xE2566F6154AFF41948FE497E53631B43   0.1480 56.1%     4 0.0370  0.00 UPDATE kf_user_info
#    2 0x2DFBC6DBF0D68EF2EC2AE954DC37A1A4   0.1109 42.1%     4 0.0277  0.00 SELECT kf_user_info
# MISC 0xMISC                               0.0047  1.8%     2 0.0024   0.0 <2 ITEMS>

从最上面的统计sql中就可以看到执行慢的sql
可以看到响应时间，执行次数，每次执行耗时（单位秒），执行的sql
下面就是各个慢sql的详细分析，比如，执行时间，获取锁的时间，执行时间分布，所在的表等信息。**

show processlist

还有种情况是慢查询还在执行中，慢查询日志里是找不到慢 SQL 的，这个时候可以用 show processlist 命令来寻找慢查询，该命令可以显示正在运行的线程，执行结果如下图所示，可以根据 Time 的大小来判断是否为慢查询。