温故MySQL - 10 性能分析工具：定位执行慢的SQL~慢查询日志

文章目录

• • • 1. 开启慢查询日志参数
    • 2. 修改long_query_time阈值
    • 3. 查询慢查询的条目数
    • 4. 慢查询日志分析工具：mysqldumpslow
    • 5. 关闭慢查询日志

定位执行慢的SQL ：慢查询日志

MySQl的慢查询日志，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上（不含10秒）的语句，认为是超出了我们的最大忍耐时间。

它的主要作用是，帮助我们发现那些执行时间特别长的SQL查询，并且有针对性的进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找出那些慢查询，对解决问题很有帮助。比如一条SQL执行超过5秒中，我们就算慢SQL。希望能收集超过5秒的SQl，结合explain进行全面根系。

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

慢查询日志支持将日志记录写入文件。

1. 开启慢查询日志参数

查询慢查询日志是否开启：

show variables like 'slow_query_log';

将慢查询日志打开：

set global slow_query_log='on';

查看慢查询日志是否开启，慢查询日志文件的位置：

show variables like 'slow_query_log%';

2. 修改long_query_time阈值

查看慢查询的时间阈值设置，使用如下命令：

show variables like '%long_query_time%';

这里如果我们想把时间缩短，比如设置为 1 秒，可以这样设置：

// 全局会话
set global long_query_time = 1;
// 当前会话
set long_query_time=1;

3. 查询慢查询的条目数

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

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

用于测试的数据准备：

// 1、创建表
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

// 2、设置参数 log_bin_trust_function_creators允许创建函数
set global log_bin_trust_function_creators=1;

// 3、创建函数
// 产生随机字符串
DELIMITER //
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;

// 产生随机数值
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;

// 4、创建存储过程
DELIMITER //
CREATE PROCEDURE insert_stu1( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student (stuno, NAME ,age ,classId ) VALUES
((START+i),rand_string(6),rand_num(10,100),rand_num(10,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;

// 5、调用存储过程
// 调用刚刚写好的函数, 4000000条记录,从100001号开始
CALL insert_stu1(100001,4000000);

执行两条查询语句：

select * from student where stuno=3455655;
select * from student where name='RMkRAc';

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

show global status like 'slow_queries';

可以看到共有2条慢查询记录。

4. 慢查询日志分析工具：mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow 。 查看mysqldumpslow的帮助信息：

perl mysqldumpslow.pl --help

mysqldumpslow命令的具体参数如下：

-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序：
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间 （默认方式）
ac:平均查询次数
-t: 即为返回前面多少条的数据；

举例：

查看慢查询日志的位置：

show variables like 'slow_query_log%'

按照查询时间排序，查看前五条 SQL 语句，可以这样写：

perl mysqldumpslow.pl -s t -t 5 F:installbagmysql-8.0.27-winx64dataLAPTOP-6TF38M5O-slow.log

因为没有加-a参数，因此会将数字抽象成N，字符串抽象成S，假如加入-a参数：

举例：

// 得到返回记录集最多的10个SQL
perl mysqldumpslow.pl -a -s r -t 5 F:installbagmysql-8.0.27-winx64dataLAPTOP-6TF38M5O-slow.log

// 得到访问次数最多的10个SQL 
perl mysqldumpslow.pl -a -s c -t 5 F:installbagmysql-8.0.27-winx64dataLAPTOP-6TF38M5O-slow.log  
    
// 得到按照时间排序的前10条里面含有左连接的查询语句 
perl mysqldumpslow.pl -a -s t -t 10 -g "left join" F:installbagmysql-8.0.27-winx64dataLAPTOP-6TF38M5O-slow.log      
   
// 建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
perl mysqldumpslow.pl -a -s r -t 10 -g "left join" F:installbagmysql-8.0.27-winx64dataLAPTOP-6TF38M5O-slow.log |more     

5. 关闭慢查询日志

方式1：永久性方式

[mysqld]
slow_query_log=OFF

重启MySQL服务，执行如下语句查询慢日志功能：

SHOW VARIABLES LIKE '%slow%'; #查询慢查询日志所在目录
SHOW VARIABLES LIKE '%long_query_time%'; #查询超时时长

方式2：临时性方式

（1）停止MySQL慢查询日志功能：

set global slow_query_log=off;

（2）重启MySQL服务，使用show语句查询慢查询日志功能信息：

show variables like '%slow%';
show variables like '%long_query_time%';