Explain中各个输出列的含义

explain语法的作用是输出语句执行的底层逻辑，输出列包括：

• id：在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id

• select_type：SELECT 关键字对应的那个查询的类型

• table：表名

• partitions：匹配的分区信息

• type：针对单表的访问方法

• possible_keys：可能用到的索引

• key：实际上使用的索引

• key_len：实际使用到的索引长度

• ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息

• rows：预估的需要读取的记录条数

• filtered：某个表经过搜索条件过滤后剩余记录条数的百分比

• Extra：一些额外的信息

table

table表示语句执行的表名

当单表查询，这里就是一行记录，如果是涉及多表的查询，不论是子查询还是join连接，都会输出多行记录

id

每提交一个SELECT，都会为其分配一个id，因此连接查询虽然是多表，但是它们的id是相同的

例如：EXPLAIN SELECT * FROM s1 INNER JOIN s2;

而子查询因为有多个SELECT，id就会有差别

例如：EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

有时候查询优化器会把子查询优化成连接，想知道最终有没有优化，直接使用EXPLAIN分析就好了，只要id一样了就说明自然优化了

但时候查询结果中还有一个为NULL的id的记录，意思是一行是UNION过程中创建的临时表

select_type

有些枚举值，常用的如下：

• SIMPLE：查询语句中不包含 UNION 或者子查询的查询都算作是 SIMPLE 类型，也就是说是最简单的单表查询和连接查询

• PRIMARY/UNION：对于包含 UNION 、 UNION ALL 或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询 的 select_type 值就是 PRIMARY，例如EXPLAIN SELECT FROM s1 UNION SELECT FROM s2; 中，针对s1的查询就是PRIMARY，针对s2的查询就是UNION

• UNION RESULT：前面id的时候了解过有一条id为NULL的记录，也就是临时表，是这种类型

以下不算很常见：

• SUBQUERY：如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是不相关子查询，并且查询 优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个 SELECT 关键字代表的那个查 询的 select_type 就是 SUBQUERY，由于select_type为SUBQUERY的子查询由于会被物化，所以只需要执行一遍

• DEPENDENT SUBQUERY：如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是相关子查询，则该子查询 的第一个 SELECT 关键字代表的那个查询的 select_type 就是 DEPENDENT SUBQUERY，select_type为DEPENDENT SUBQUERY的查询可能会被执行多次

• DEPENDENT UNION：在包含 UNION 或者 UNION ALL 的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小 查询之外，其余的小查询的 select_type 的值就是 DEPENDENT UNION

• DERIVED：对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的 select_type 就是 DERIVED

• MATERIALIZED：当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对 应的 select_type 属性就是 MATERIALIZED

partitions

语句扫描了哪些分区

type

执行计划的一条记录就代表着 MySQL 对某个表的执行查询时的访问方法，即type

完整的访问方法枚举值如下： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ， index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL

• system：当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的 访问方法就是 system，在使用InnoDB的时候，我们看不到这种类型

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

• eq_ref：在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者 唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref

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

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

• index_merge：Intersection 、 Union 、 Sort-Union 这三种索引合并场景

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

• index：当我们可以使用索引覆盖，但需要扫描全部的二级索引树时

• ALL：全表扫描

possible_keys和key

possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用 到的索引有哪些， key 列表示实际用到的索引有哪些

possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成 本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引

key_len

key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度

ref

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const 、 eq_ref 、 ref 、 ref_or_null 、 unique_subquery 、index_subquery 其中之一时， ref 列展示的就是与索引列作等值匹配的内容是个啥

有可能是一个常数，或者一个列，甚至一个func

rows

如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的 rows 列就代表预计需要扫描的行 数，如果使用索引来执行查询时，执行计划的 rows 列就代表预计扫描的索引记录行数

filtered

连接查询成本相关的一个

Extra

extra一些常用值包括：

• No tables used：SELECT语句没有FROM子句的场景，比如SELECT 1

• Impossible WHERE：查询语句的 WHERE 子句永远为 FALSE 时将会提示该额外信息，例如EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

• No matching min/max row：当查询列表处有 MIN 或者 MAX 聚集函数，但是并没有符合 WHERE 子句中的搜索条件的记录时，将会提示该 额外信息

• Using index：只查索引，不需要回表，提示的就是这个，并不一定是全表扫描，只要不需要回表都是这个

• Using index condition：条件中虽然出现了索引列，但却不能使用到索引，例如SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

• Using where：全表扫描，且有where子句

• Using join buffer (Block Nested Loop)：基于块的嵌套循环算法，前面有提过

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

• Using intersect(...) 、 Using union(...) 和 Using sort_union(...)：索引合并

• Zero limit：LIMIT 子句的参数为 0 时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息，比如这 样：EXPLAIN SELECT * FROM s1 LIMIT 0;

• Using filesort：有一些情况下对结果集中的记录进行排序是可以使用到索引的，就是这个场景

• Using temporary：DISTINCT 、 GROUP BY 、 UNION 等子句的查询过程中，如果不能有效利用索引来完成查询， MySQL 很有可能寻求通过建立内部的临时表来执行查询

执行计划和优化流程

Json格式执行计划

在 EXPLAIN 单词和真正的查询语句中间加上 FORMAT=JSON就可以输出JSON格式执行计划

EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.co mmon_field = 'a'

其中会分析查询步骤中的成本

optimizer trace

如果还想了解更细致的查询优化器优化流程，可以使用这个表

完整的使用 optimizer trace 功能的步骤总结如下：

• 打开optimizer trace功能 (默认情况下它是关闭的):

SET optimizer_trace="enabled=on";

• 这里输入你自己的查询语句

SELECT ...; 

• 从OPTIMIZER_TRACE表中查看上一个查询的优化过程

SELECT * FROM information_schema.OPTIMIZER_TRACE;

• 可能你还要观察其他语句执行的优化过程，重复上边的第2、3步

• 当你停止查看语句的优化过程时，把optimizer trace功能关闭

SET optimizer_trace="enabled=off";