性能分析工具的使用

文章目录

1. 数据库服务器调优的步骤
2. 查看系统性能参数
3. 统计SQL的查询成本：last_query_cost
4. 定位执行慢的SQL：慢查询日志
- 4.1 开启slow_query_log
- 4.2 修改long_query_time阈值
- 4.3 查看慢查询数目
- 4.4 慢查询日志分析工具：mysqldumpslow
5. 查看 SQL 执行成本：SHOW PROFILE
6. 分析查询语句：EXPLAIN
- 6.1 概述
- 6.2 各字段详解

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

在这里插入图片描述

我们可以通过设置long_query_time参数定义"慢"的阈值，如果SQL执行时间超过了long_query_time，则会认为是慢查询。当收集上来这些慢查询之后，我们就可以通过分析工具对慢查询日志进行分析。
我们就知道了执行慢的SQL，这样就可以针对性地用 EXPLAIN查看对应SQL语句的执行计划，或者使用show profile查看SQL中每一个步骤的时间成本。这样我们就可以了解SQL查询慢是因为执行时间长，还是等待时间长。

2. 查看系统性能参数

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

SHOW STATUS LIKE 'Slow_queries';

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

SHOW STATUS LIKE 'Innodb_rows_% ’;

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

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

SELECT student_id,class_id,name,create_time from student_info WHERE id >= 900001 and id <= 9000100;

mysql> show status like 'last_query_cost';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| Last_query_cost | 21.134486 |
+-----------------+-----------+
1 row in set (0.00 sec)

SELECT student_id,class_id,NAME,create_time FROM student_info WHERE id = 988881;

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

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

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

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

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

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

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

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10秒

4.1 开启slow_query_log

SHOW VARIABLES LIKE '%slow_query_log';

在这里插入图片描述

如果不指定存储路径，慢查询日志将默认存储到MySQL数据库的数据文件夹下。
如果不指定文件名，默认文件名为hostname-slow.log
慢查询分析已经开启，同时文件保存在/var/lib/mysql/hostname-slow.log.log文件中。

4.2 修改long_query_time阈值

mysql> show variables like '%long_query_time%';

mysql> set global long_query_time = 1;   # 全局

4.3 查看慢查询数目

mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 5     |
+---------------+-------+
1 row in set (0.01 sec)

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

在这里插入图片描述
工作中常用参考：

#得到返回结果集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop103-slow.log

#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/hadoop103-slow.log

#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hadoop103-slow.log

#另外建议在使用这些命令时结合|和more使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop103-slow.log | more

5. 查看 SQL 执行成本：SHOW PROFILE

mysql> show profiles;
+----------+------------+--------------------------------------------------+
| Query_ID | Duration   | Query                                            |
+----------+------------+--------------------------------------------------+
|       18 | 0.00115275 | show variables like '%slow_query_log%'           |
|       19 | 0.00005100 | show variables like '%long_query_time%'          |
|       20 | 0.00137050 | show variables like '%long_query_time%'          |
|       21 | 0.00117325 | show variables like '%long_query_time%'          |
|       22 | 0.00119925 | show variables like 'long_query_time%'           |
|       23 | 0.00252550 | show variables like 'long_query_time'            |
|       24 | 0.00218475 | SHOW GLOBAL STATUS LIKE '%Slow_queries%'         |
|       25 | 0.00041325 | SELECT * FROM student_info WHERE stuno = 3455655 |
|       26 | 1.58461100 | SELECT * FROM student WHERE stuno = 3455655      |
|       27 | 1.27094075 | SELECT * FROM student WHERE name = 'oQmLUr'      |
|       28 | 0.00149875 | show status like 'slow_queries'                  |
|       29 | 0.00195000 | show variables like 'min%'                       |
|       30 | 0.00147475 | SHOW VARIABLES LIKE 'slow_query_log% '           |
|       31 | 0.00130400 | SHOW VARIABLES LIKE 'slow_query_log%'            |
|       32 | 0.00141200 | show variables like 'profiling'                  |

show profile cpu,block io for query 5;

在这里插入图片描述

6. 分析查询语句：EXPLAIN

6.1 概述

定位了查询慢的SQL之后，我们就可以使用EXFLAIN或DESCRBE工具做针对性的分析查询语句。
MysQL中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划

这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。MySQL为我们提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划。

6.2 各字段详解

EXPLAIN语句输出的各个列的作用如下：
在这里插入图片描述

table：有多少张表EXPLAIN后就有几条记录，temp表也算，比如查询中包含两个表，则分别说明对s1表和s2表的访问方法是什么。

id：

id如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行

select_type:

SIMPLE:
查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型，连接查询也算是 SIMPLE 类型
PRIMARY
对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type值就是PRIMARY
SUBQUERY:
如果包含子查询的查询语句不能够转为对应的semi-join(多表连接)的形式，并且该子查询是不相关子查询，该子查询中的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY
DEPENDENT SUBQUERY
如果包含子查询的查询语句不能够转为对应的semi-join(多表连接)的形式，并且该子查询是相关子查询，则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY

type：
执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，又称"访问类型"，其中的type列就表明了这个访问方法是啥，是较为重要的一个指标。比如，看到type列的值是ref，表明MySQL即将使用ref访问方法来执行对s1表的查询。

访问方法有几种： system, const, eq_ref，ref，fulltext, ref_or_null，index_merge,unique_subquery , index_subquery ,range, index , ALL。

const：当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const，比如:

mysql> EXPLAIN SELECT * FROM s1 WHERE id = 10005;

eq_ref：在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的，则对该被驱动表的访问方法就是eq_ref

EXPLAIN SELECT * FROM s1 inner join s2 on s1.id = s2.id;

在这里插入图片描述

从执行计划的结果中可以看出，MySQL打算将s1作为驱动表，s2作为被驱动表，重点关注s2的访问方法是 eq_ref，表明在访问s2表的时候可以通过主键的等值匹配来进行访问。

ref：当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref。

ref_or_null：当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;

index_merge：一般情况下对于某个表的查询只能使用到一个索引，但单表访问方法时在某些场景下可以使用Intersection(交集)、Union(并集)、Sort-Union这三种索引合并的方式来执行查询。我们看一下执行计划中是怎么体现MySQL使用索引合并的方式来对某个表执行查询的:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

unique_subquery：类似于两表连接中被驱动表的eq_ref访问方法， unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery，比如下边的这个查询语句:

mysql> EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 =
s2.key1) OR key3 = 'a';  # 子查询查id

index_subquery：index_subquery与unique_subquery类似，只不过访问子查询中的表时使用的是普通的索引。

range：如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法。

EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');

EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';

index：当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index

mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

ALL:最熟悉的全表扫描

 EXPLAIN SELECT * FROM s1;

一般来说，这些访问方法中除了All这个访问方法外，其余的访问方法都能用到索引，除了index_merge访问方法外，其余的访问方法都最多只能用到一个索引。
在这里插入图片描述
key_len：实际使用到的索引长度
帮你检查是否充分的利用上了索引，值越大越好,主要针对于联合索引，有一定的参考意义。

ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息。比如只是一个常数或者是某个列。
在这里插入图片描述

rows：预估的需要读取的记录条数值越小越好，使用索引查到的行数。
filtered：某个表经过搜索条件过滤后剩余记录条数的百分比
说明：rows是使用索引初步估算的行数，这些行中的全部或者一部分是我们真正查的记录，需要根据查到的记录再回表，rows*filtered就是真正的我们要查的记录。

Extra：
Using index condition：有些搜索条件中虽然出现了索引列，但却不能使用到索引，比如下边这个查询:

mysql> SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

先根据key1 > ‘z’ 这个条件，定位到二级索引 idx_key1中对应的二级索引记录。
对于指定的二级索引记录，先不着急回表，而是先检测一下该记录是否满足key1 LIKE ‘%a’ 这个条件，如果这个条件不满足，则该二级索引记录压根儿就没必要回表。
对于满足key1 LIKE '%a’这个条件的二级索引记录执行回表操作。

我们说回表操作其实是一个随机IO，比较耗时，所以上述修改虽然只改进了一点点，但是可以省去好多回表操作的成本。MySQL把他们的这个改进称之为索引条件下推(英文名: Index Condition Pushdown )。

join_buffer：在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法。之后的章节会详细学到。

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;

在这里插入图片描述

在对s2表的执行计划的Extra列显示了两个提示:

Using join buffer (Block Nested Loop)：这是因为对表s2的访问不能有效利用索引，只好退而求其次，使用join buffer来减少对s2表的访问次数，从而提高性能。
Using where: 可以看到查询语句中有一个s1.common_field = s2.common_field条件，因为s1是驱动表，s2是被驱动表，所以在访问s2表时, s1.common_field的值已经确定下来了，所以实际上查询s2表的条件就是 s2.common_field =一个常数，所以提示了Using where额外信息。
Not exists：当我们使用左(外）连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列又是不允许存储NULL值的，那么在该表的执行计划的Extra列就会提示Not exists额外信息，比如这样：

Using filesort：很多情况下排序操作无法使用到索引，只能在内存中(记录较少的时候）或者磁盘中(记录较多的时候）进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序(英文名: filesort)。

mysql> EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

在这里插入图片描述

如果查询中需要使用filesort的方式进行排序的记录非常多，那么这个过程是很耗费性能的，我们最好想办法将使用文件排序的执行方式改为使用索引进行排序。