前言

2022/8/3 15:23

暑假学习ing

推荐

【MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！-哔哩哔哩】

尚硅谷MySQL学习笔记

MySQL笔记：第09章_性能分析工具的使用

第09章 性能分析工具的使用

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

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

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


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

详细解释一下这张图:

首先在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和SHOEW　PROFILING

小结：

2.查看系统性能参数

在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 'Connections';
/*
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 44    |
+---------------+-------+
*/

• 若查询服务器工作时间，则可以执行如下语句:

show status like 'uptime';
/*
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Uptime        | 460878|
+---------------+--------+
*/

• 若查询MySQL服务器的慢查询次数，则可以执行如下语句:

SHOW STATUS LIKE 'slow_queries' ;
/*
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
*/

慢查询次数参数可以结合慢查询日志找出慢查询语句，然后针对慢查询语句进行表结构优化或者查询语句优化。再比如，如下的指令可以查看相关的指令情况:

SHOW STATUS LIKE 'Innodb_rows_%';
/*
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_deleted  | 0     |
| Innodb_rows_inserted | 224   |
| Innodb_rows_read     | 214   |
| Innodb_rows_updated  | 0     |
+----------------------+-------+
*/

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

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

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

我们依然使用第8章的student_info表为例:

CREATE TABLE `student_info` (
`id` INT(11) NOT NULL 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 CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

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

SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id = 900001;
#运行结果（1 条记录，运行时间为 0.042s ）

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

SHOW STATUS LIKE 'last_query_cost';
/*
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
*/

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

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

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

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

 SHOW STATUS LIKE 'last_query_cost';
 /*
+-----------------+-----------+
| Variable_name | Value       |
+-----------------+-----------+
| Last_query_cost | 21.134453 |
+-----------------+-----------+
*/

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

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

SQL查询是一个动态的过程，从页加载的角度来看，可以得到以下两点结论:

1.位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。

2.批量决定效率。如果从磁盘中对单一页进行随机读，那么效率是很低的（差不多10ms)，而采用顺序读取的方式，批主对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。

所以说，遇到I/O并不用担心，方法找对了，效率还是很高的。首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中，其次可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。

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

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

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

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

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

4.1 开启慢查询日志参数

1.开启slow_query_log

在使用前，需要先看下慢查询是否已经开启，使用下面这条命令即可:

show variables like 'slow_query_log';
/*
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
*/

我们能看到slow_query_log=OFF，可以把慢查询日志打开，注意设置变量值的时候需要使用global，否则会报错:

set global slow_query_log='ON';

再来查看下慢查询日志是否开启，以及慢查询日志文件的位置：

show variables like '%slow_query_log%';
+---------------------+-----------------------------------------+
| Variable_name       | Value                                   |
+---------------------+-----------------------------------------+
| slow_query_log      | ON                                      |
| slow_query_log_file | /var/lib/mysql/centos7-mysql-1-slow.log |
+---------------------+-----------------------------------------+
2 rows in set (0.00 sec)

能看到这时慢查询分析已经开启，同时文件保存在/var/lib/mysql/LSLNO1-slow.log 文件中

2. 修改long_query_time阈值

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

show variables like '%long_query_time%';
/*
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
*/

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

#测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并执行下述语句
set global long_query_time = 1;
show global variables like '%long_query_time%';
set long_query_time=1;
show variables like '%long_query_time%';
/*
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
*/

补充:配置文件中一并设置参数

如下的方式相较于前面的命令行方式，可以看作是永久设置的方式

修改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/atguigu-slow.log#慢查询日志的目录和文件名信息
long_query_time=3 #设置慢查询的阈值为3秒。超出此设定值的SQL即被记录到慢查询日志
log_output=FTLE

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

4.2 查看慢查询数目

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

SHOW GLOBAL STATUS LIKE '%Slow_queries%';
/*
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
*/