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

作者: 紫荆秋雪_文 | 来源:发表于2022-10-17 18:13 被阅读0次

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

一、优化步骤

1、优化的目标

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

1、响应时间更快
2、吞吐量更大

2、优化流程步骤

优化流程步骤.png

S（Show status）：代表观察，使用相应的分析工具
A（Action）：代表行动，对应分析可以采取优化

2.1、优化过程

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

1、首先在 S1部分，我们需要观察服务器的状态是否存在周期性的波动。如果存在周期性波动，有可能是周期性节点的原因，如：11、促销活动等。这样的话，我们可以通过A1这一步骤解决，也就是加缓存或者更改缓存失效策略
2、如果缓存策略没有解决，或者不是周期性波动的原因，就需要进一步分析查询延时和卡顿的原因。接下来进入S2这步，需要开启慢查询。慢查询可以帮我们定位执行慢的SQL语句。我们可以通过设置long_query_time参数定义慢的阈值，如果SQL执行时间超过了long_query_time，则会认为是慢查询。当收集上来这些慢查询之后，就可以通过分析工具对慢查询日志进行分析
3、在S3这步中，就知道了执行慢的SQL，这样就可以针对性地用EXPLAIN 或 DESCRIBE查看对应 SQL 语句的执行计划或者利用 show_profile 查看 SQL 中每一个步骤的时间成本。这样我们就可以了解 SQL 查询慢是因为执行时间长，还是等待时间长
4、如果是 SQL 等待时间长，进入A2步骤。在这一步骤中，可以 调优服务器的参数，如适当增加数据库缓冲池
5、如果是 SQL 执行时间长，进入A3步骤，这一步中需要考虑
- 5.1、是索引设计的问题?
- 5.2、是查询关联的数据表过多？
- 5.3、是数据表的字段设计问题导致?
6、如果A2和A3都不能解决问题，需要考虑数据库自身的 SQL 查询性能是否已经达到了瓶颈，如果确认没有达到性能瓶颈，就需要重新检查，重复以上的步骤
7、如果A2和A3都不能解决问题，如果已经达到了性能瓶颈，进入 A4 阶段，需要考虑增加服务器，采用读写分离的架构，或者考虑对数据库进行分库分表，如垂直分库、垂直分表、水平分表

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

二、查看系统性能参数

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

SHOW STATUS语法

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

常用的性能参数
- Connections：连接MySQL服务器的次数。
- Uptime：MySQL服务器的上线时间。
- Slow_queries：慢查询的次数。
- Innodb_rows_read：Select查询返回的行数
- Innodb_rows_inserted：执行INSERT操作插入的行数
- Innodb_rows_updated：执行UPDATE操作更新的行数
- Innodb_rows_deleted：执行DELETE操作删除的行数
- Com_select：查询操作的次数。
- Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
- Com_update：更新操作的次数。
- Com_delete：删除操作的次数。
查询 MySQL 服务器的慢查询次数

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

SHOW STATUS LIKE 'Slow_queries';

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

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

1、实战1

查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找

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

查询 id 在 900001 到 9000100 之间的学生记录

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

查询优化器的成本，这时我们大概需要进行 20 个页的查询

SHOW STATUS LIKE 'last_query_cost';

image.png

3、小结

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

4、使用场景

last_query_cost：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候
SQL 查询是一个动态的过程，从 页 加载的角度来看，可以得到以下两点结论
- 1、位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多
- 2、批量决定效率。如果我们从磁盘中对单一页进行随机读，那么效率是很低的（10ms），而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取
- 3、所以说，遇到 I/O 并不用担心，方法找对了，效率也是很高的。我们首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到 缓冲池 中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升

四、慢查询日志

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

查看 long_query_time 值

SHOW VARIABLES LIKE '%long_query_time%';

默认阈值10秒.png

修改 long_query_time 值

#  修改全局 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

min_examined_row_limit默认是 0。与 long_query_time=10 合在一起，表示只要查询的执行时间超过 10 秒钟，哪怕一个记录也没有扫描过，都要被记录到慢查询日志中。你也可以根据需要，通过修改 my.ini 文件，来修改查询时长，或者通过 SET 指令，用 SQL 语句修改 min_examined_row_limit的值

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

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

1、查看mysqldumpslow的帮助信息

mysqldumpslow --help

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

-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序
- c: 访问次数
- l: 锁定时间
- r: 返回记录
- t: 查询时间
- al:平均锁定时间
- ar:平均返回记录数
- at:平均查询时间（默认方式）
- ac:平均查询次数
-t: 即为返回前面多少条的数据
-g: 后边搭配一个正则匹配模式，大小写不敏感的

3、mysqldumpslow 常用命令

得到返回记录集最多的10个SQL

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

得到访问次数最多的10个SQL

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

得到按照时间排序的前10条里面含有左连接的查询语句

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

另外建议在使用这些命令时结合 | 和more 使用，否则有可能出现爆屏情况

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

4.6 关闭慢查询日志

方式1：永久性方式

[mysqld] 
slow_query_log=OFF

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

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

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

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

停止MySQL慢查询日志功能

SET GLOBAL slow_query_log=off;

重启MySQL服务，使用SHOW语句查询慢查询日志功能信息

SHOW VARIABLES LIKE '%slow%';

SHOW VARIABLES LIKE '%long_query_time%';

4.7 删除慢查询日志

使用 SHOW 语句显示慢查询日志信息

SHOW VARIABLES LIKE 'slow_query_log%';

image.png

把 /var/lib/mysql/raven-mysql-0-slow.log目录下文件手动删除
使用命令来重新生成查询日志文件

mysqladmin -uroot -p flush-logs slow

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

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

5.1、查看 PROFILE 功能是否开启

 show variables like 'profiling';

PROFILE默认关闭.png

开启 PROFILE 功能

set profiling = 'ON';

开启 PROFILE 功能.png

执行 SQL

SELECT *
FROM student
WHERE name = 'smgWpI';

查看执行的语句

show profiles;

image.png

要查看最近一次查询的开销

show profile;

查询开销.png

查询指定的 Query ID 的开销

show profile for query 2;

查询不同部分的开销，如CPU、block.io

show profile cpu, block io for query 2;

5.2、show profile的常用查询参数

① ALL：显示所有的开销信息。
② BLOCK IO：显示块IO开销。
③ CONTEXT SWITCHES：上下文切换开销。
④ CPU：显示CPU开销信息。
⑤ IPC：显示发送和接收开销信息。
⑥ MEMORY：显示内存开销信息。
⑦ PAGE FAULTS：显示页面错误开销信息。
⑧ SOURCE：显示和Source_function，Source_file， Source_line相关的开销信息。
⑨ SWAPS：显示交换次数开销信息

5.3、需要注意的指标

① converting HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了
② Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表
③ Copying to tmp table on disk：把内存中临时表复制到磁盘上，警惕！！！
④ locked
如果在 show profile 诊断结果中出现了以上 4 条结果中的任何一条，则 SQL 语句需要优化
不过show profile命令将被弃用，我们可以从 information_schema 中的 profiling 数据表进行查看

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

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

一、优化步骤

1、优化的目标

2、优化流程步骤

2.1、优化过程

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

二、查看系统性能参数

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

1、实战1

2、实战2

3、小结

4、使用场景

四、慢查询日志

1、开启慢查询日志

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

1.2、开启慢查询日志

1.3、修改long_query_time阈值

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

4.2 查看慢查询数目

4.3 案例演示

步骤1. 建表

步骤2：设置参数 log_bin_trust_function_creators

步骤3：创建函数

步骤4：创建存储过程

步骤5：调用存储过程

4.4 测试及分析

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

1、查看mysqldumpslow的帮助信息

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

3、mysqldumpslow 常用命令

4.6 关闭慢查询日志

方式1：永久性方式

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

4.7 删除慢查询日志

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

5.1、查看 PROFILE 功能是否开启

5.2、show profile的常用查询参数

5.3、需要注意的指标

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

MySQL

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

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

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