MySql性能分析工具Explain

1、前提知识

1.1、MySql Query Optimizer

mysql中有个专门负责优化select语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他（MySQL优化器）认为最优对的数据检索方式，但不见得是DBA或者程序员认为最优的，这部分最耗时间）。
当客户端想MySQL请求一条Query，命令解析器模块完成请求分类，区别出事select并转发给MySQL Query Optimizer时，MySQL Query Optimizer 首先会对整条Query进行优化，处理一些常量表达式的预算，直接换算成常量值。并对query中的Hint信息（如果有），看现实Hint细腻是否可以完全确定该Query的执行计划。如果没有Hint或者Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

1.2、MySQL性能瓶颈

①、CPU：CPU在饱和的时候一般发生在数据装入内存或者从磁盘读取数据的时候
②、IO：磁盘IO瓶颈发生在装入数据远大于内存容量的时候
③、服务器硬件的性能瓶颈：top，free，iostat，vmstat来查看系统的性能状态

2、Explain

2.1、是什么

使用EXPLAIN关键字可以模拟优化器（MySql Query Optimizer
）执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或者表结构的性能瓶颈。
我们也可以从官网了解有关信息：

https://dev.mysql.com/doc/refman/8.0/en/using-explain.html

我使用谷歌翻译下：

2.2、能做什么

①、表的读取顺序；
②、数据读取操作的类型；
③、哪些索引可以使用；
④、哪些索引被实际使用；
⑤、表之间的引用；
⑥、每张表有多少行被优化器查询。

2.3、如何使用

使用方式：

EXPLAIN  <查询语句>

如：explain select * from tb_emp;

我们看到执行结果集中有这些信息：

id,select_type,table,type,possible_keys,key,key_len,ref,rows,extra

下面我们将一一分析每个列的作用

2.4、id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。它有三种情况，分别是：

我们假设有三张表t1,t2和t3，每个表的注解都为id，下面我们来分析下这三种情况。

2.4.1、id相同

sql语句：

exlpain select t2.*
from t1,t2,t3
where t1.id = t2.id 
and t1.id = t3.id

执行结果：

分析：我们看到执行结果的id都唯一，我们可以先看看table那一列，从上而下，分别是t1、t2、t3。代表sql是先执行t1表，最后执行t3表。

2.4.2、id不同

sql语句：

explain select t2.*
from t2
where id in (
  select id 
  from t1
  where id in (
    select id
    from t3
  )
)

执行结果：

分析：id一列分别是1，2，3，都不相同，执行顺序是id值越大越执行，所以是先执行t3，然后再执行t1，最后执行t2。

3.4.3、id相同不同，同时存在

sql语句：

explain select t2.*
from (
  select t3.id 
  from t3
) s1,t2
where  s1.id = t2.id

执行结果：

分析：id为1的看作一组，id为2的看作一组，那么id不同的话，越大越先执行，所以先执行t3，而id相同的1，是先上而下的执行，所以先执行t2表，然后再执行，他是一个衍生表，就是s1，这个我们后面讲table列的时候具体说明。

2.5、select_type

查询的类型，主要是用于区别普通查询，联合查询，子查询等复杂查询，它有如下值：

2.6、table

显示这一行数据是关于哪张表，除了显示我们数据库中具体的表，他可能还有以下几种情况：

2.7、type

显示查询使用了何种类型，主要有以下几种情况：

当然，还有其他的类型，如：fulltext、ref_or_null index_merage unique_subquery index_subquery 等。
类型排序(执行效率)如下：

system>const>eq_ref>ref>fulltext>ref_or_null>index_merage>unique_subquery >range>index>all

但是如果只考虑上面表格中的类型，他们的执行排序为(是较为重要的一个指标，结果值从最好到最坏依次是)：

system>const>eq_ref>ref>range>index>all

一般来说，得保证查询至少达到了range的级别，最好达到ref

2.7.1、system/const

sql语句：

explain select * from (select * from t1 where id =1) d1

执行结果：

分析：我们看到type列，对于t1表，因为在where子句中使用了主键id，所以查询类型为const；而是t1查询结果的衍生表，而这个衍生表正好只有一个记录，所以为system。

2.7.2、eq_ref

sql语句：

explain select * from t1,t2 where t1.id = t2.id

执行结果：

2.7.3、ref

sql语句：

explain select * from t2 where t2.name='t2Name1'

执行结果：

分析：注意此时，我给t2表name属性和detail属性建了个复合索引（唯一的），当我们只按name属性作为条件查询是便出现ref类型。

2.7.4、range

sql语句（这里我们使用in作为例子）：

explain select * from t3 where t3.id in (1,3)

执行结果：

2.7.5、index

sql语句（查询某个表的所有id值）：

explain select id from t3

** 执行结果：**

2.7.6、all

sql语句（查询所有记录，不加任何查询条件）：

explain select * from t3

执行结果：

2.8、possible_key

显示可能应用在该张表中的索引，一个或者多个，查询设计到的字段上若存在索引，则索引将被列出，但不一定被查询实际使用

2.9、key

查询实际使用的索引，如果为null，则没有使用索引（没有建索引或者索引失效）
注意：并不是一定要possible_key中出现的索引，才会在key中出现，如下这种情况，possible_key为null，但实际确使用了索引：

2.10、key_len

表示索引中使用的字节数，可以通过该列计算查询中使用索引的长度，在不损失精确性情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用的长度，即key_len是根据表定义计算而得，不是通过内检索出的。

2.11、ref

显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或者常量被用于查找索引的列上的值

其中：const是常量，我们看到sql中，t2.name指定了一个值，而该列是被建立唯一索引的，所以只有一行会被匹配，即被指定为常量。db02.t2.id表示db02这个数据库中t2这张表的id属性，也就是t1这张表查询使用了t2表id主键索引查询。

2.12、rows

根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数，该值越小越好。
我们分析下下面的sql语句：

explain select * from t1,t2 where t1.id = t2.id and t2.name='t2Name1'

在为给t2表建立索引idx_t2_nameDetail时的结果：

我们看到t1这张表有13行数据被优化器检索到，现在我们给t2建立一个复合索引idx_t2_nameDetail（表示name字段和detail的复合索引，建索引语句省略）：

此时，t2表被检索的行数变成了一行。

2.13、Extra

Extra的定义为包含不适合在其他列显示但十分重要的额外信息。注意是十分重要的信息，它有这些内容：

在第3个using index是，提到了覆盖索引（Covering Index）,在2.9节中我们也提到了覆盖索引，它的理解方式为：

就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖

注意：如果要使用覆盖索引，一定要注意select列中只取出需要的列，不可以select * 或者出现其他数据列，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。说白了就是select中出现的字段要和建立索引字段一致。

其实前3个Using filesort、Using temporary以及Using index才是最重要的，其他几个只需要了解便是，下面我们将举例说明出现这三种情况的场景。

2.13.1、Using filesort

sql语句：

explain select detail from t2 where t2.name = 'we' order by col4

执行结果：

分析：列Extra中出现了Using filesort，再看order by子句中使用了属性col4进行排序，而key一列的索引：idx_t2_nameDeatilCol4，表示name、detail、col4三个列属性作为复合索引，再看order by子句使用col4进行排序，而索引是从name属性开始，这打破了原有的索引该有的顺序，所有MySQL优化器再无法使用原有的索引顺序进行排序，使用一个外部的索引排序，这是非常危险情况，必须马上优化。

2.13.2、Using temporary

根据Using temporary一般出现在order by和group by中，所有这里参试使用group 不要，sql语句为：

explain select detail,name from t2 where t2.name in ('we','t2Name2') group by detail,name;

执行结果：

分析：Extra列不仅出现了Using filesort，还出现了Using temporary，如果说出现Using filesort是九死一生，那么Using temporary就是十死零生了，赶紧修改你的sql吧

2.13.3、Using Index

sql语句如下：

 explain select name,detail,col4 from t2 where t2.name in ('we','t2Name2') group by name,detail,col4

explain select name,detail,col4 from t2 group by name,detail,col4

分析：恭喜，这是一个非常好的查询，效率不错。

3、总结

Explain：使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或者表结构的性能瓶颈。
一、使用方式：

EXPLAIN  <查询语句>

二、结果列如下：

三、它的作用（能做什么）：
①、表的读取顺序
读表的顺序由id值决定，id的值有三种情况，分别是：id相同、id不同以及id相同不同，同时存在。表被执行的顺序是：id值越大执行越先执行，id相同由上而下顺序执行。
②、数据读取操作的类型
它也是sql查询优化的一个重要的指标，结果值从最好到最坏依次是：

system>const>eq_ref>ref>range>index>all

一般来说：得保证查询至少达到了range的级别，最好达到ref。
③、哪些索引可以使用
哪些索引可以使用是通过列possible_key决定。
④、哪些索引被实际使用
哪些索引被实际使用是由列key决定。
⑤、表之间的引用
表之间的引用是由列ref决定，如果可能的话，是一个常数，哪些列或者常量被用于查找索引的列上的值。
⑥、每张表有多少行被优化器查询
这个当然是列rows，它的值越小，代表效率越高。
四、Extra
在对sql优化分析还有一个重要的指标是Extra，如果出现Using filesort和Using temporary，那么就需要考虑优化sql语句了，如果出现了Using Index就表示sql效率不错，建议使用覆盖索引。

最后更新： 2019年07月03日 09:39

原始链接： https://www.sunnymaple.cn/2019/07/02/MySql性能分析工具Explain/

汪志斌

赏

× 请我吃糖~