【MySQL】EXPLAIN SQL分析器

Kimizu0
83 阅读8分钟

EXPLAIN

基本语法

EXPLAIN SELECT select_options
或者
DESCRIBE SELECT select_options

EXPLAIN 语句输出的各个列的作用如下：

image.png

EXPLAIN各列作用

table

💡 不论我们的查询语句有多复杂，里边包含了多少个表 ，到最后也是需要对每个表进行单表访问的，所 以MySQL规定`EXPLAIN`语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。 
mysql > EXPLAIN SELECT * FROM s1;

image.png

image.png

id

💡 查询语句中每出现一个 `SELECT` 关键字，MySQL就为他分配一个唯一 `id` 值，这个 `id` 值就是EXPLAIN语句的第一个列。 特殊情况： 1.当一条SQL为包含`子查询`或`UNION`的时候会出现两个SELECT。 2.SQL执行器会对涉及子查询优化成连接查询，SELECT的数量会减少。 3.进行UNION（去重）查询的时候会出现id为 `NULL` 的中间表。

select_type

💡 一条大的查询语句里边可以包含若干个SELECT关键字，`每个SELECT关键字代表着一个小的查询语句`，而每个SELECT关键字的FROM子句中都可以包含若干张表(这些表用来做连接查询)，`每一张表都对应着执行计划输出中的一条记录`，对于在同一个SELECT关键字中的表来说，它们的id值是相同的MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为 `select_type` 的属性，意思是我们只要知道了某个小查询的 select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色，我们看一下select_type都能取哪些值， 请看官方文档:

image.png

SIMPLE

💡 查询语句中不包含**`UNION`**或者子查询的查询都算作是**`SIMPLE`**类型，比方说下边这个查询**`select_type`**的值就是**`SIMPLE`** 
mysql> EXPLAIN SELECT * FROM s1;

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;

PRIMARY

💡 对于包含**`UNION、UNION ALL`**或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的**`select_type`**的值就是**`PRIMARY`**,比方说 
 mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

Untitled

  • 从结果中可以看到，最左边的小查询SELECT * FROM s1对应的是执行计划中的第一条记录，它的select_type的值就是PRIMARY。
  • UNION 对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的select_type值就是UNION，可以对比上一个例子的效果。
  • UNION RESULT MySQL 选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT, 例子上边有。
  • SUBQUERY 如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY，比如下边这个查询：
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

image.png

  • DEPENDENT SUBQUERY

    mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';

  • DEPENDENT UNION

    mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');

  • DERIVED

    mysql> EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1;

    从执行计划中可以看出，id为2的记录就代表子查询的执行方式，它的select_type是DERIVED, 说明该子查询是以物化的方式执行的。id为1的记录代表外层查询，大家注意看它的table列显示的是derived2，表示该查询时针对将派生表物化之后的表进行查询的。

  • MATERIALIZED

    当查询优化器在执行包含子查询的语句时，选择将子查询物化之后的外层查询进行连接查询时，该子查询对应的select_type属性就是DERIVED，比如下边这个查询：

    mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);

  • UNCACHEABLE SUBQUERY

    不常用，就不多说了。

  • UNCACHEABLE UNION

    不常用，就不多说了。

partitions

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 。

type

possible_keys和key

💡 在EXPLAIN语句输出的执行计划中，**`possible_keys`**列表示在某个查询语句中，对某个列执行**`单表查询时可能用到的索引`**有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。** `key`**列表示**`实际用到的索引`**有哪些，如果为NULL，则没有使用索引。比方说下面这个查询： 
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';

image.png 上述执行计划的possible_keys列的值是idx_key1, idx_key3，表示该查询可能使用到idx_key1, idx_key3两个索引，然后key列的值是idx_key3，表示经过查询优化器计算使用不同索引的成本后，最后决定采用idx_key3。

key_len

💡 实际使用到的索引长度 (即：字节数)

帮你检查**是否充分的利用了索引 ， 值越大越好**，主要针对于联合索引，有一定的参考意义。

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

ref

💡 具体匹配的那一列。

rows

💡 预估的需要读取的记录条数，**`值越小越好`**。

filterd

💡 它指返回结果的行占需要读到的行(rows列的值)的百分比。

Extra

💡 顾名思义，**`Extra`**列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来**`更准确的理解MySQL到底将如何执行给定的查询语句`**。MySQL提供的额外信息有好几十个，我们就不一个一个介绍了，所以我们只挑选比较重要的额外信息介绍给大家。
  • **No tables used：当查询语句没有FROM**子句时将会提示该额外信息。
  • **Impossible WHERE：当查询语句的WHERE子句永远为FALSE**时将会提示该额外信息。
  • **Using where：**不用读取表中的所有信息，仅仅通过索引即可获取字段信息，当条件中除了包含索引列外，还有其他的也会提示这个。
  • **No matching min/max row：当查询列表处有MIN或者MAX聚合函数，但是并没有符合WHERE**子句中的搜索条件的记录时。
  • **Using index：当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用覆盖索引的情况下，在Extra**列将会提示该额外信息。
  • Using index condition：有些搜索条件中虽然出现了索引列，但却不能使用到索引。

索引下推

💡 索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询，减少回表带来的随机I/O。

在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

ICP的使用条件：

  • 如果表的访问类型为 range 、 ref 、 eq_ref 或者 ref_or_null 可以使用ICP。

  • ICP可以使用**InnDB和MyISAM表，包括分区表InnoDB和MyISAM**表

  • 对于**InnoDB表，ICP仅用于二级索引**。ICP的目标是减少全行读取次数，从而减少I/O操作。

  • 当SQL使用覆盖索引时，不支持ICP优化方法。因为这种情况下使用ICP不会减少I/O。

  • 相关子查询的条件不能使用ICP

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

  • **Not exists：当我们使用左(外)连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列是不允许存储NULL**值的，那么在该表的执行计划的Extra列就会提示这个信息。

  • Using intersect(...) 、 Using union(...) 和 Using sort_union(...)：

    如果执行计划的Extra列出现了Using intersect(...)提示，说明准备使用Intersect索引合并的方式执行查询，括号中的...表示需要进行索引合并的索引名称；

    如果出现Using union(...)提示，说明准备使用Union索引合并的方式执行查询;

    如果出现Using sort_union(...)提示，说明准备使用Sort-Union索引合并的方式执行查询。

  • **Zero limit：当我们的LIMIT子句的参数为0**时，表示压根儿不打算从表中读取任何记录，将会提示该额外信息。

  • **Using filesort：**有一些情况下对结果集中的记录进行排序是使用文件排序方式进行查询的。

  • **Using temporary：**在查询中借助了临时表。

avatar
文章
阅读
粉丝