MySQL高级篇知识点——性能分析工具的使用

本文笔记整理来自尚硅谷视频https://www.bilibili.com/video/BV1iq4y1u7vj?p=134，相关资料可在视频评论区进行获取。

数据库调优的目标就是响应时间更快，吞吐量更大。利用宏观的监控工具和微观的日志分析可以帮我们快速找到调优的思路和方式。

1.数据库服务器的优化步骤

（1）当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。整个流程划分成了观察 (Show status) 和行动 (Action) 两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

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

（2）详细解释以下上面的图： ① 首先在 S1 部分，我们需要观察服务器的状态是否存在周期性的波动。如果存在周期性波动有可能是周期性节点的原因，比如双十一、促销活动等。这样的话，我们可以通过 A1 这一步骤解决，也就是加缓存，或者更改缓存失效策略。

② 如果缓存策略没有解决，或者不是周期性波动的原因，我们就需要进一步分析查询延迟和卡顿的原因。接下来进入 S2 这一步，我们需要开启慢查询。慢查询可以帮我们定位执行慢的 SQL 语句。我们可以通过设置long_query_time 参数定义“慢”的阈值，如果 SQL 执行时间超过了 long_query_time，则会认为是慢查询。当收集上来这些慢查询之后，我们就可以通过分析工具对慢查询日志进行分析。

③ 在 S3 这一步骤中，我们就知道了执行慢的 SQL，这样就可以针对性地用 EXPLAIN 查看对应 SQL 语句的执行计划，或者使用 show profile 查看 SQL 中每一个步骤的时间成本。这样我们就可以了解 SQL 查询慢是因为执行时间长，还是等待时间长。

④ 如果是 SQL 等待时间长，我们进入 A2 步骤。在这一步骤中，我们可以调优服务器的参数，比如适当增加数据库缓冲池等。如果是 SQL 执行时间长，就进入 A3 步骤，这一步中我们需要考虑是索引设计的问题？还是查询关联的数据表过多？还是因为数据表的字段设计问题导致了这一现象。然后在这些维度上进行对应的调整。

⑤ 如果 A2 和 A3 都不能解决问题，我们需要考虑数据库自身的 SQL 查询性能是否已经达到了瓶颈，如果确认没有达到性能瓶颈，就需要重新检查，重复以上的步骤。如果已经达到了性能瓶颈，进入 A4 阶段，需要考虑增加服务器，采用读写分离的架构，或者考虑对数据库进行分库分表，比如垂直分库、垂直分表和水平分表等。

⑥ 以上就是数据库调优的流程思路。如果我们发现执行 SQL 时存在不规则延迟或卡顿的时候就可以采用分析工具帮我们定位有问题的 SQL，这三种分析工具你可以理解是 SQL 调优的三个步骤：慢查询、EXPLAIN 和 SHOW PROFILING。

（3）小结

2.查看系统性能参数

（1）在 MySQL 中，可以使用 SHOW STATUS 语句查询一些数据库服务器的性能参数、 执行频率。 SHOW STATUS 语句语法如下：

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

（2）一些常用的性能参数如下：

参数含义Connections连接 MySQL 服务器的次数UptimeMySQL 服务器的上线时间Slow_queries慢查询的次数Innodb_rows_readSelect 查询返回的行数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 'Connections';

3.统计SQL的查询成本：last_query_cost

（1）一条 SQL 查询语句在执行前需要确定查询执行计划，如果存在多种执行计划的话，MySQL 会计算每个执行计划所需要的成本，从中选择成本最小的一个作为最终执行的执行计划。

（2）如果我们想要查看某条 SQL 语句的查询成本，可以在执行完这条 SQL 语句之后，通过查看当前会话中的 last_query_cost 变量值来得到当前查询的成本。它通常也是我们评价一个查询的执行效率的一个常用指标。这个查询成本对应的是 SQL 语句所需要读取的页的数量。

（3）我们依然使用索引的创建与设计原则中的 student_info 表为例：

# 创建学生表和课程表
CREATE TABLE `student_info` (
 `id` INT(11) AUTO_INCREMENT,
 `student_id` INT NOT NULL ,
 `name` VARCHAR(20) DEFAULT NULL,
 `course_id` INT NOT NULL ,
 `class_id` INT(11) DEFAULT NULL,
 `create_time` DATETIME DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

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

SELECT * FROM student_info WHERE id = 900001;

然后再看下查询优化器的成本，实际上我们只需要检索一个页即可：

mysql> SHOW STATUS LIKE 'last_query_cost'; 
+-----------------+----------+
| Variable_name	  | Value    | 
+-----------------+----------+
| Last_query_cost | 1.000000 | 
+-----------------+----------+

② 如果我们想要查询 id 在 900001 到 9000100 之间的学生记录呢？

SELECT * FROM student_info WHERE id BETWEEN 900001 AND 900100;

运行结果（100 条记录，运行时间为 0.006s ）：

mysql> SHOW STATUS LIKE 'last_query_cost'; 
+-----------------+-----------+
| Variable_name   | Value     | 
+-----------------+-----------+
| Last_query_cost | 21.134453 | 
+-----------------+-----------+

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

（4）使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。

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

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

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

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

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

4.1.开启慢查询日志参数

（1）查看慢查询开启情况，默认是不开启的。

SHOW VARIABLES LIKE '%slow_query_log%';

（2）开启慢查询之后，再次查看。

# slow_query_log 是全部变量
SET GLOBAL slow_query_log='ON';

SHOW VARIABLES LIKE '%slow_query_log%';

你能看到这时慢查询分析已经开启，同时文件保存在 slow_query_log_file 文件中。

（3）查看并修改 long_query_time 阈值。

# 默认是 10s
show variables like '%long_query_time%';

# 修改为 1s
# 测试发现：设置 global 的方式对当前 session 的 long_query_time 失效，对新连接的客户端有效。所以可以一并 执行下述语句
set global long_query_time = 1;
SET long_query_time = 1;

（4）上面的方法只是临时修改，一但 MySQL 重启，上述设置又会恢复到默认值，永久修改的方法如下： ① 如果是在 windows下：在配置文件 my.ini [mysqld] 中修改或加上： ② 如果是在 linux下：则在配置文件 /etc/my.cnf [mysqld] 中修改或加上：

slow_query_log=ON
long_query_time=1
slow_query_log_file='具体文件路径名'
log_output=FILE

如果不指定存储路径，慢查询日志将默认存储到 MySQL 数据库的数据文件夹下；如果不指定文件名，默认文件名为 hostname-slow.log。

4.2.查看慢查询数目

查询当前系统中有多少条慢查询记录，如果是刚开启慢查询且还没有执行时间超过 long_query_time 的语句，那么慢查询数目肯定为 0

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

4.3.创建表以及模拟数据 4.3.1.建表

USE atguigudb1;

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;

4.3.2.创建相关函数

创建的MySQL高级篇知识点——索引的创建与设计原则这篇文章中的 3.1 节一样。 （1）产生随机字符串函数

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 0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc;

9.2.4.I/O 相关

# 1.查看消耗磁盘 I/O 的文件 
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;

9.2.5.Innodb 相关

#1. 行锁阻塞情况 
select * from sys.innodb_lock_waits;

风险提示：通过 sys 库去查询时，MySQL 会消耗大量资源去收集相关信息，严重的可能会导致业务请求被阻塞，从而引起故障。建议生产上不要频繁的去查询 sys 或者 performance_schema、information_schema 来完成监控、巡检等工作。