MySQL

52-MySQL-性能分析-慢查询、PROFILES

2022-10-17  本文已影响0人  紫荆秋雪_文

以下操作有可能在客户端（DataGrip、Navicat、SQLyog）执行无效，需要终端进入

一、优化步骤

1、优化的目标

利用宏观的监控工具和微观的日志分析帮我们快速找到调优的思路和方法

2、优化流程步骤

优化流程步骤.png

2.1、优化过程

我们可以通过使用分析工具来观察数据库整体的运行状态，通过性能分析工具可以让我们了解执行慢的SQL都有哪些，查看具体的SQL执行计划，甚至是 SQL 执行中的每一步的成本代价，这样才能定位问题所在，找到了问题，再采取相应的行动

3、优化效率比较 优化效率比较.png

二、查看系统性能参数

在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的 性能参数 、 执行频率

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

慢查询次数参数可以结合慢查询日志找出慢查询语句，然后针对查询语句进行 表结构优化 或者 查询语句优化

SHOW STATUS LIKE 'Slow_queries';

三、统计 SQL 的查询成本：last_query_cost

一条 SQL 查询语句在执行前需要确定查询执行计划，如果存在多种执行计划的话，MySQL 会计算每个执行计划所需要的成本，从中选择 成本最小 的一个作为最终执行的执行计划。如果我们想要查看某条 SQL 语句的查询成本，可以在执行完这条 SQL 语句之后，通过查看当前会话中的 last_query_cost 变量值来得到当前查询的成本。它通常也是我们 评价一个查询的执行效率 的一个指标。这个查询成本对应的是 SQL 语句所需要读取的页的数量

1、实战1

SELECT student_id, class_id, name, create_time
FROM student_info
WHERE id = 900001;
image.png 
SHOW STATUS LIKE 'last_query_cost';
image.png

2、实战2

SELECT student_id, class_id, name, create_time
FROM student_info
WHERE id BETWEEN 900001 AND 900100;

SHOW STATUS LIKE 'last_query_cost';
image.png

3、小结

通过上面实例的对比发现实战2访问数据页是实战1的20倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然页数量(last_query_cost)增加了不少，但是通过缓冲池的机制，并没有增加多少查询时间

4、使用场景

四、慢查询日志

MySQL的慢查询日志，用来记录在MySQL中 响应时间超过阈值 的语句，具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中。 long_query_time 的默认值为 10 ，意思是运行 10 秒以上的语句，认为是超出了我们的最大忍耐时间值。
它的主要作用是帮助我们发现那些执行时间特别长的 SQL 查询，并且有针对性地进行优化，从而提高系统的整体效率。当数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。
默认情况下，MySQL数据库 没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响

1、开启慢查询日志

1.1、查询是否开启慢查询日志

SHOW VARIABLES LIKE '%slow_query_log%';
检查是否开启慢查询日志.png

1.2、开启慢查询日志

SET GLOBAL SLOW_QUERY_LOG = 'ON';
image.png

1.3、修改long_query_time阈值

设置global的方式对当前session的 long_query_time失效。对新连接的客户端有效。所以需要重新开启一个连接，或者同时修改 GLOBAL，SESSION

SHOW VARIABLES LIKE '%long_query_time%';
默认阈值10秒.png 
#  修改全局 GLOBAL
SET GLOBAL long_query_time = 1;

#修改 SESSION
SET SESSION long_query_time = 1;
image.png

!!! 永久设置—修改 my.cnf 文件

在 [mysqld]下增加或修改参数 long_query_time、 slow_query_log 和 slow_query_log_file 后，然后重启 MySQL 服务器


[mysqld]
slow_query_log=ON  #  开启慢查询日志的开关
slow_query_log_file=/var/lib/mysql/raven-mysql-0-slow.log  #慢查询日志的目录和文件名信息
long_query_time=3  #设置慢查询的阈值为3秒，超出此设定值的  SQL  即被记录到慢查询日志
log_output=FILE

4.2 查看慢查询数目

SHOW GLOBAL STATUS LIKE '%Slow_queries%';
查看慢查询数目.png

4.3 案例演示

步骤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`)
);

步骤2：设置参数 log_bin_trust_function_creators

This function has none of DETERMINISTIC......

# 不加global只是当前窗口有效。
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：调用存储过程

CALL insert_stu1(100001,4000000);

4.4 测试及分析

SELECT * FROM student WHERE name = 'smgWpI';
耗时 1.59s.png 
SHOW STATUS LIKE 'slow_queries';
慢查询数量.png

除了上述变量，控制慢查询日志的还有一个系统变量：min_examined_row_limit。这个变量的意思是，查询 扫描过的最少记录树。这个变量和查询执行时间，共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过 long_query_time 的值，那么，这个查询就被记录到慢查询日志中；反之，则不被记录到慢查询日志中。

show variables like 'min_examined_row_limit%';
或
show variables like 'min%';
image.png

4.5 慢查询日志分析工具：mysqldumpslow

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

1、查看mysqldumpslow的帮助信息

mysqldumpslow --help

2、mysqldumpslow 命令的具体参数如下

3、mysqldumpslow 常用命令

mysqldumpslow -s r -t 10 /var/lib/mysql/raven-mysql-0-slow.log

mysqldumpslow -s c -t 10 /var/lib/mysql/raven-mysql-0-slow.log

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/raven-mysql-0-slow.log

mysqldumpslow -s r -t 10 /var/lib/mysql/raven-mysql-0-slow.log

4.6 关闭慢查询日志

方式1：永久性方式

[mysqld] 
slow_query_log=OFF

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

SHOW VARIABLES LIKE '%long_query_time%'; #查询超时时长

方式2：临时性方式-使用SET语句来设置

SET GLOBAL slow_query_log=off;

SHOW VARIABLES LIKE '%slow%';

SHOW VARIABLES LIKE '%long_query_time%';

4.7 删除慢查询日志

SHOW VARIABLES LIKE 'slow_query_log%';
image.png 
mysqladmin -uroot -p flush-logs slow

五、SHOW PROFILE（查看 SQL 执行成本）

SHOW PROFILE 是MySQL提供的可以用来分析当前会话中 SQL 都做了什么，执行的资源消耗情况的工具，可用于 SQL 调优的测量

5.1、查看 PROFILE 功能是否开启

 show variables like 'profiling';
PROFILE默认关闭.png 
set profiling = 'ON';
开启 PROFILE 功能.png 
SELECT *
FROM student
WHERE name = 'smgWpI';

show profiles;
image.png 
show profile;
查询开销.png 
show profile for query 2;

show profile cpu, block io for query 2;

5.2、show profile的常用查询参数

5.3、需要注意的指标

上一篇 下一篇

猜你喜欢

热点阅读