执行计划 explain

1、一个SQL 语句之前加上 explain 就可以看其执行计划，然后对其做相应的优化了。

2、下面是我 explain 之后的结果截图，你能看到都是有哪些列。

3、执行计划包含的信息：

id，select_type，table，partitions，type，possible_keys，key，key_len，ref，rows，filtered，Extra

官方文档对其有详细的解释：MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format

也可继续往下看一些简单解释。

4、各个字段的意思：

（1）id: select 查询的序列号，包含一组数字，表示查询中执行 select 字句或操作的顺序。

id有三种值：

• id 相同，执行顺序由上至下。
• id 不同，如果是子查询，id 的序号会递增，id 越大优先级越高，先被执行。
• id 相同不同，如1，1，2，id 相同可以认为是一组，从上往下顺序执行。在所有组中，id 越大，优先级越高，越先被执行。

注意：在连接查询的执行计划中，每个表都会对应一条记录，这些记录的 id 列的值是相同的，出现在前面的表表示驱动表，出现在后面的表表示被驱动表。

而对于包含子查询的查询语句，可能涉及多个 select 关键字，每个子查询都会对应一个 id 值，一般情况下每个子查询的 id 值都不相同。

但是有些时候查询优化器可能对涉及子查询的语句进行重写，从而转换为连接查询，这时候子查询的 id 列的值就和连接查询是一样的了都相等。

（2）select_type：

查询的类型，主要是用于区别普通查询，联合查询，子查询等的复杂查询。

• Select_type 的值有：simple, primary, subQuery, derived, union, union result.
• Simple：简单的 select 查询，查询中不包含子查询或者 union；
• Primary：主查询，查询中若包含任何子查询，最外层查询则被标记为主查询；
• subQuery：子查询，在 select 或者 where 列表中包含子查询。
• Derived：临时(衍生)表，在 from 列表中包含的子查询被标记为 derived (衍生)；对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的 select_type 就是 Derived；

物化：这个将子查询结果集中的记录保存到临时表的过程称之为物化（Materialize）。那个存储子查询结果集的临时表称之为物化表。正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。

• Union：第二个 select 出现在 union 之后，则被标记为 union；对于包含 union 或者 union all 的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的 select_type 值就是 union
• Union result：从 union 表中获取结构的 select；MySQL 选择使用临时表来完成 union 查询的去重工作，针对该临时表的查询的 select_type 就是 union result

（3）table：显示这一行的数据是关于哪张表的；

（4）partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

（5）type：访问类型排列；

显示查询使用了哪一种类型，从最好到最差依次是：

System > const > eq_ref > ref > range > index > ALL

一般来说，得保证查询到 range 级别，最好能到 ref 级别。

System：表中只有一行记录（等于系统表），这是 const 类型的特例，平时不会出现，这个可以忽略不计。

• Const：表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。例如将主键置于 where 列表中，mysql 将该查询转为一个常量。
• Eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
• Ref：非唯一性索引扫描，返回匹配某个单独值得所有行。
• Range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的 where 语句中出现了 between, , in 等的查询。这种范围扫描比全表扫描快，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
• Index：full index scan 全索引扫描，index 与 all 的区别为：index 类型只遍历索引树，这通常比 all 快，因为索引文件通常比数据文件小。也就是说虽然 index 和 all 都是读全表，但 index 是从索引中读的，而 all 是从硬盘中读的。
• All：全表扫描，是最慢的类型。

（6）possible_keys：显示可能应用在这张表中的索引，一个或者多个。

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

（7）key：实际使用的索引。

如果为null，则没有使用索引。查询中若出现了覆盖索引，则该索引仅出现在 key 列表中。

（8）key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

在不损失精确性的情况下，长度越短越好。

Key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的。

（9）ref：显示索引的具体一列被使用了，也可能是一个 const。

哪些列或者常量被用于查找索引列上的值。

（10）rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。数值越低越好。

（11）filtered：这个字段会指示出表的条件所过滤的表中行数的百分比

（12）Extra：不适合在其他列显示，但十分重要的额外信息。

Extra有如下几个值：

Using filesort

Using temporary

Using index

Using where

using join buffer

impossible where,

Select tables optimized away

distinct

值的解释：

• Using filesort：

说明 mysql 会对结果使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。Mysql 中无法利用索引完成的排序操作称为“文件排序”。InnoDB 表排序的时候优先使用 全字段 排序方法。

• Using temporary：

在许多查询的执行过程中，MySQL可能会借助临时表保存中间结果来完成一些功能，比如去重、排序，比如我们执行许多包含 DISTINCT、GROUP BY、UNION、ORDER BY等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL 很有可能通过建立内部临时表来执行查询。

临时表分两种：内存临时表和磁盘临时表。

tmp_table_size 这个配置限制了内存临时表的大小，默认值是16M。如果临时表大小超过了tmp_table_size，那么内存临时表就会转成磁盘临时表。

内存临时表排序的时候使用的 rowid 排序方法。

磁盘临时表使用的引擎默认是InnoDB，排序的时候优先使用 全字段 排序方法。

Using filesort 和 Using temporary 都是不太好的结果，会影响性能，所以这两总情况都是要考虑去优化的 SQL。

• Using where：表示使用了 where 过滤。

• Using index：表示相应的 select 中使用了覆盖索引，避免访问了表的数据行，效率可以。

  如果同时出现了 using where，表明索引被用来执行索引键值的查找,即在 where 条件中使用了索引；

  如果没有同时出现 using where，表明索引用来只是读取数据而非执行查找动作。

• Using index condition ：表示索引条件
• Using join buffer：使用了连接缓存。在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL 一般会为其分配一块名叫 join buffer 的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法
• impossible where：表示 where 条件总是 false。例如：where name=”zs” and name=”ls”;Select tables optimized away：没有 group by 下，基于索引优化 Max/Min 操作。
• Distinct：优化 distinct，在找到第一匹配的元组后即停止找到同样值得动作。