MySQL 执行计划处理(Explain)

Explain

⼀条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后⽣成⼀个所谓的执⾏计划。

mysql>	EXPLAIN	SELECT	1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-
---------------+
|	id	|	select_type	|	table	|	partitions	|	type	|	possible_keys	|	key 	|	key_len	|	ref 	|	rows	|	filtered	|
Extra         	|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-
---------------+
| 	1	|	SIMPLE     	|	NULL 	|	NULL      	|	NULL	|	NULL         	|	NULL	|	NULL   	|	NULL	|	NULL	|    	NULL	|
No	tables	used	|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-
---------------+
1	row	inset,	1	warning	(0.01	sec)

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

列名	描述
id	在⼀个⼤的查询语句中每个SELECT关键字都对应⼀个唯⼀的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问⽅法
possible_keys	可能⽤到的索引
key	实际上使⽤的索引
key_len	实际使⽤到的索引⻓度
ref	当使⽤索引列等值查询时，与索引列进⾏等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分⽐
Extra	⼀些额外的信息

• id
  查询语句中每出现⼀个SELECT关键字，设计MySQL的⼤叔就会为它分配⼀个唯⼀的id值。这个id值就是EXPLAIN语句的第⼀个列，⽐如下边这个查询中只有⼀个SELECT关键字，所以EXPLAIN的结果中也就只有⼀条id列为1的记录：
• select_type
  为每⼀个SELECT关键字代表的⼩查询都定义了⼀个称之为select_type的属性。
  • SIMPLE
    查询语句中不包含UNION或者⼦查询的查询都算作是SIMPLE类型，⽐⽅说下边这个单表查询的select_type的值就是SIMPLE。
  • PRIMARY
    对于包含UNION、UNION ALL或者⼦查询的⼤查询来说，它是由⼏个⼩查询组成的，其中最左边的那个查询的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。
  • DEPENDENT SUBQUERY
    如果包含⼦查询的查询语句不能够转为对应的semi-join的形式，并且该⼦查询是相关⼦查询，则该⼦查询的第⼀个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY。
  • DERIVED
    对于采⽤物化的⽅式执⾏的包含派⽣表的查询，该派⽣表对应的⼦查询的select_type就是DERIVED。
  • MATERIALIZED
    当查询优化器在执⾏包含⼦查询的语句时，选择将⼦查询物化之后与外层查询进⾏连接查询时，该⼦查询对应的select_type属性就是MATERIALIZED。
  • UNCACHEABLE SUBQUERY
  • UNCACHEABLE UNION
• partitions
  分区，⼀般情况下我们的查询语句的执⾏计划的partitions列的值都是NULL。
• type
  可以看到type列的值是ref，表明MySQL即将使⽤ref访问⽅法来执⾏对s1表的查询。但是我们之前只唠叨过对使⽤InnoDB存储引擎的表进⾏单表访问的⼀些访
  问⽅法，完整的访问⽅法如下：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，
  index，ALL
  • system
    当表中只有⼀条记录并且该表使⽤的存储引擎的统计数据是精确的，⽐如MyISAM、Memory，那么对该表的访问⽅法就是system。
  • const
    当我们根据主键或者唯⼀⼆级索引列与常数进⾏等值匹配时，对单表的访问⽅法就是const
  • eq_ref
    在连接查询时，如果被驱动表是通过主键或者唯⼀⼆级索引列等值匹配的⽅式进⾏访问的（如果该主键或者唯⼀⼆级索引是联合索引的话，所有的索引列都必须进⾏等值⽐较），则对该被驱动表的访问⽅法就是eq_ref。
  • ref
    当通过普通的⼆级索引列与常量进⾏等值匹配时来查询某个表，那么对该表的访问⽅法就可能是ref
  • fulltext
    全⽂索引
  • ref_or_null
    当对普通⼆级索引进⾏等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问⽅法就可能是ref_or_null
  • index_merge
    ⼀般情况下对于某个表的查询只能使⽤到⼀个索引，但我们唠叨单表访问⽅法时特意强调了在某些场景下可以使⽤Intersection、Union、Sort-Union这三种索引合并的⽅式来执⾏查询。
  • unique_subquery
    类似于两表连接中被驱动表的eq_ref访问⽅法，unique_subquery是针对在⼀些包含IN⼦查询的查询语句中，如果查询优化器决定将IN⼦查询转换为EXISTS⼦查询，⽽且⼦查询可以使⽤到主键进⾏等值匹配的话，那么该⼦查询执⾏计划的type列的值就是unique_subquery
  • index_subquery
    index_subquery与unique_subquery类似，只不过访问⼦查询中的表时使⽤的是普通的索引.
  • index
    当我们可以使⽤索引覆盖，但需要扫描全部的索引记录时，该表的访问⽅法就是index
  • ALL
    最熟悉的全表扫描
• possible_keys和key
  列表示在某个查询语句中，对某个表执⾏单表查询时可能⽤到的索引有哪些，key列表示实际⽤到的索引有哪些。注意：是，possible_keys列中的值并不是越多越好，可能使⽤的索引越多，查询优化器计算查询成本时就得花费更⻓时间，所以如果可以的
  话，尽量删除那些⽤不到的索引。
• key_len
  表示当优化器决定使⽤某个索引执⾏查询时，该索引记录的最⼤⻓度，它是由这三个部分构成的：
  • 对于使⽤固定⻓度类型的索引列来说，它实际占⽤的存储空间的最⼤⻓度就是该固定值，对于指定字符集的变⻓类型的索引列来说，⽐如某个索引列的类型是VARCHAR(100)，使⽤的字符集是utf8，那么该列实际占⽤的最⼤存储空间就是100 × 3 = 300个字节。
  • 如果该索引列可以存储NULL值，则key_len⽐不可以存储NULL值时多1个字节。
  • 对于变⻓字段来说，都会有2个字节的空间来存储该变⻓列的实际⻓度。
• ref
  当使⽤索引列等值匹配的条件去执⾏查询时，也就是在访问⽅法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之⼀时，ref列展示的就是与索引列作等值匹配的东东是个啥，⽐如只是⼀个常数或者是某个列。
• rows
  如果查询优化器决定使⽤全表扫描的⽅式对某个表执⾏查询时，执⾏计划的rows列就代表预计需要扫描的⾏数，如果使⽤索引来执⾏查询时，执⾏计划的rows列就代表预计扫描的索引记录⾏数。
• filtered
  之前在分析连接查询的成本时提出过⼀个condition filtering的概念，就是MySQL在计算驱动表扇出时采⽤的⼀个策略：
  • 如果使⽤的是全表扫描的⽅式执⾏的单表查询，那么计算驱动表扇出时需要估计出满⾜搜索条件的记录到底有多少条。
  • 如果使⽤的是索引执⾏的单表扫描，那么计算驱动表扇出的时候需要估计出满⾜除使⽤到对应索引的搜索条件外的其他搜索条件的记录有多少条。
• Extra
  义，Extra列是⽤来说明⼀些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执⾏给定的查询语句。
  • No tables used
    当查询语句的没有FROM⼦句时将会提示该额外信息
  • Impossible WHERE
    查询语句的WHERE⼦句永远为FALSE时将会提示该额外信息。
  • No matching min/max row
    当查询列表处有MIN或者MAX聚集函数，但是并没有符合WHERE⼦句中的搜索条件的记录时
  • Using index
    当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使⽤索引覆盖的情况下，在Extra列将会提示该额外信息。
  • Using index condition
    有些搜索条件中虽然出现了索引列，但却不能使⽤到索引。
  • Using where
    当我们使⽤全表扫描来执⾏对某个表的查询，并且该语句的WHERE⼦句中有针对该表的搜索条件时，在Extra列中会提示上述额外信息。
  • Using join buffer (Block Nested Loop)
    在连接查询执⾏过程中，当被驱动表不能有效的利⽤索引加快访问速度，MySQL⼀般会为其分配⼀块名叫join buffer的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法。
  • Not exists
    当我们使⽤左（外）连接时，如果WHERE⼦句中包含要求被驱动表的某个列等于NULL值的搜索条件，⽽且那个列⼜是不允许存储NULL值的，那么在该表的执⾏计划的Extra列就会提示Not exists额外信息，
  • 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
    在许多查询的执⾏过程中，MySQL可能会借助临时表来完成⼀些功能，⽐如去重、排序之类的，⽐如我们在执⾏许多包含DISTINCT、GROUP BY、UNION等⼦句
    的查询过程中，如果不能有效利⽤索引来完成查询，MySQL很有可能寻求通过建⽴内部的临时表来执⾏查询。如果查询中使⽤到了内部的临时表，在执⾏计划的Extra列将会显示Using temporary提示。
  • Start temporary, End temporary
    我们前边唠叨⼦查询的时候说过，查询优化器会优先尝试将IN⼦查询转换成semi-join，⽽semi-join⼜有好多种执⾏策略，当执⾏策略为DuplicateWeedout时，也就是通过建⽴临时表来实现为外层查询中的记录进⾏去重操作时，驱动表查询执⾏计划的Extra列将显示Start temporary提示，被驱动表查询执⾏计划的Extra列将显示End temporary提示。
  • LooseScan
    在将In⼦查询转为semi-join时，如果采⽤的是LooseScan执⾏策略，则在驱动表执⾏计划的Extra列就是显示LooseScan提示。
  • FirstMatch(tbl_name)
    在将In⼦查询转为semi-join时，如果采⽤的是FirstMatch执⾏策略，则在被驱动表执⾏计划的Extra列就是显示FirstMatch(tbl_name)提示。

Json格式的执⾏计划

mysql>	EXPLAIN	FORMAT=JSON	SELECT	*	FROM	s1	INNER	JOIN	s2	ON	s1.key1	=	s2.key2	WHERE	s1.common_field	=	'a'\G