MySQL性能分析工具的使用_innodbrowsinserted-优快云博客

本文链接：https://blog.youkuaiyun.com/main_Scanner01/article/details/123721306

性能分析工具的使用

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

数据库服务器的优化步骤

当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。

整个流程划分成了观察（Show status） 和 行动（Action） 两个部分。字母 S 的部分代表观察（会使
用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

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

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

小结：

查看系统性能参数

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

SHOW STATUS语句语法如下：

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

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

Connections：连接MySQL服务器的次数。

show status like 'Connections';

Uptime：MySQL服务器的上线时间。

show status like 'Uptime';

Slow_queries：慢查询的次数。

show status like 'Slow_queries';

Innodb_rows_read：Select查询返回的行数

show status like 'Innodb_rows_read';

Innodb_rows_inserted：执行INSERT操作插入的行数

show status like 'Innodb_rows_inserted';

Innodb_rows_updated：执行UPDATE操作更新的行数

show status like 'Innodb_rows_updated';

Innodb_rows_deleted：执行DELETE操作删除的行数

show status like 'Innodb_rows_deleted';

Com_select：查询操作的次数。

show status like 'Com_select';

Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。

show status like 'Com_insert';

Com_update：更新操作的次数。

show status like 'Com_update';

Com_delete：删除操作的次数。

show status like 'Com_delete';

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

SHOW STATUS LIKE 'Innodb_rows_%';

统计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;

查看最后一条语句花费的成本

show status like 'last_query_cost';

下面的意思就是最后一次的查询大概需要做一页的随即查找。

然后再看下查询优化器的成本，这时我们大概需要进行 2249个页的查询。(4999 rows in set (0.04 sec))

然后再看下查询优化器的成本，这时我们大概需要进行218个页的查询。(484 rows in set (0.00 sec))

上面两个查询之间页的数量相差十倍，查询效率并没有明显的变化，两条SQL语句的时间基本上一样，就是因为采取了顺序读取的方式一次性加载到缓冲池中，然后再进行查找。虽然也数量增加了不少，但是通过缓冲池的机制，并没有增加多少时间。（回忆第07章_InnoDB数据存储结构，区、段、页）

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

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

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

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

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

开启慢查询日志参数

开启slow_query_log

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

show variables like '%slow_query_log' ;

开启慢查询日志

set global slow_query_log='ON';

查看慢查询日志以及慢查询日志文件所在位置

show variables like '%slow_query_log%' ;

修改long_query_time阈值

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

show variables like '%long_query_time%';

这里如果我们想把时间缩短，比如设置为 1 秒，可以这样设置： long_query_time既是global参数，也是session参数

#测试发现：设置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%';

永久修改，设置慢查询日志时间

修改my.cnf(默认在/etc/my.cnf)，在[mysqld]下增加或修改参数long_query_time、slow_query_log.file后重启服务器。

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

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

补充说明：

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