【mysql】explain语句的输出格式

本文是翻译而来的，原文地址：EXPLAIN Output Format

explain语句会为我们显示select语句的执行计划相关的信息。

每个在select语句中用到的table，explain语句都会为其返回一行信息，而且这些table的顺序是按照MYSQL查询时的顺序输出的。MYSQL使用内嵌循环的方式来解决所有连表。这意味着，MYSQL会先从第一张表中读取一行数据，然后去第二张表中寻找匹配的行，然后以此类推。当所有的table都处理后，MYSQL输出查询的字段，然后通过explain输出的数据表列表回溯，直到它找到一个匹配了最多行的表。下一行从该表开始读取直到处理下一张表。

如果使用了extended关键字，那么explain会产生额外的信息，这些信息可以使用show warnings语句来查看。（注意：在explain语句中，不能同时使用extended和partitions这两个关键字）

一、explain的输出字段

这一小节介绍explain输出的各个字段的含义，下一小节会更加详细的介绍type和extra字段。

explain输出的每一行都对应的select查询所用到的table。每一行的字段和每个字段的含义如下表所示：

字段	含义
id	查询的标识
select_type	查询的类型
table	该行信息对应的table
partitions	匹配到的分区
type	连接类型
possible_keys	可以选择使用的索引
key	实际使用的索引
key_len	实际使用的索引的长度
ref	与索引对照的字段
rows	预估将要扫描的行
filtered	通过条件过滤的行数的占比
extra	额外信息

id
查询标识，它是一系列连续的数字。但如果该行表示的是从其他联合查询返回的结果，那么它的值将会是null，而且该行的table字段的值会是这个样子：<unionM,N>，它表示，改行是从id为M和N的联合查询中返回的结果。
select_type
查询的类型，它的值有如下几种：

select_type的值	该值的含义
SIMPLE	简单的查询，没有使用UNION或者子查询
PRIMARY	最外层的查询
UNION	在UNION语句中的第二层或者之后的查询
DEPENDENT UNION	UNION语句中的第二层或者之后的查询，取决于对外的查询
UNION RESULT	UNION查询的结果
SUBQUERY	第一层子查询
DEPENDENT SUBQUERY	第一层子查询，取决于对外的查询
DERIVED	衍生的查询
UNCACHEABLE SUBQUERY	不可缓存的子查询
UNCACHEABLE UNION	UNION语句的第二层查询的select_type是UNCACHEABLE SUBQUERY

DEPENDENT SUBQUERY不同于UNCACHEABLE SUBQUERY。对于DEPENDENT SUBQUERY来说，只有外层的值发生改变时，才会重新查询，并返回结果。而UNCACHEABLE SUBQUERY，外层的每一行，都会导致它重新执行查询。

可缓存的子查询并不是意味着把查询结果缓存在缓存池中。子查询的结果缓存只会发生在查询期间，当查询结束后，子查询在缓存中的数据就会被销毁。

table
该行所对应的表名称。但也有可能是如下值：
<unionM,N>：从union语句的结果中查询，这些union语句的id是从M到N；
<derivedN>：该行是从id为N的查询结果中衍生出来的。

partitions
被匹配到的分区。该字段只有当使用关键字 PARTITIONS时，才会出现。对于非分区表，该字段的值是NULL。

type
连接类型。这块内容详见下一小节。

possible_keys
该字段显示哪些索引是本次查询可以使用的。需要注意的是：该字段完全取决于所在行在explain输出中的顺序。这就意味着，有些索引虽然在该字段中显示了，但在实际的查询中，并不会用得上。

如果这个字段的值为NULL，那就意味着没有相关的索引。这种情况下，你可能需要通过修改你的where子句，使用那些有建立索引的字段来匹配，以此来优化查询。也可以自己建立一个新的索引，然后再用explain检查一下。

可以使用 SHOW INDEX FROM tbl_name 语句来查看表中有哪些索引。

key
该字段显示的是在查询中，实际使用的索引。如果mysql决定使用possible_keys中的一个索引来检索数据，那么该字段的值就会在possible_keys字段中。

也有可能，该字段会出现一个不在possible_keys中的值。这种情况一般出现在，possible_keys中的所有索引都不适合用于本次查询，但存在一个索引，涵盖了所有要查询的字段，所以，虽然直接检索这个索引，就可以得到想要的数据，索引检索远远比数据检索快得多。

如要要强制使用或者强制忽略某个在possible_keys中的索引，可以在查询中使用 FORCE INDEX、USE INDEX、IGNORE INDEX来实现。

对于MYISAM或者NDB来说，执行ANALYZE TABLE可以帮助优化器选择更好的索引。对于MYISAM表，执行myisamchk –analyze和执行ANALYZE TABLE是一样的。

key_len
该字段显示本次查询，mysql使用的索引的长度。如果key字段的值是NULL ，那么该字段的值也会是NULL 。注意的是：该字段可以帮助你判断，在一次查询中，使用了联合索引中的几个字段来检索数据。

ref
该字段显示哪些字段或者常数与key一起从表中选择行。

rows
这字段显示mysql觉得将要有多少行的数据需要被检索。

对于Innodb表来说，这个字段的值是估算的，并不是总是精确的。

filtered
该字段显示mysql预估被过滤掉的数据占比。rows字段显示预估的行数，rows * filtered / 100 表示的是有多少行数据和前一个表连表。这一字段只有在使用explain extended时才显示。

extra
该字段显示mysql在本次查询中的额外的信息。在下下小节中，会详细说明该字段的含义。

二、explain的连接类型
explain语句输出结果中的type字段描述表与表之间是如何连接的。接下来会按从最优到最差列出所有的连接的类型：

system
该表中只有一行数据，这其实是const类型的一个特例。

const
该表中最多有一行数据匹配。由于只有一行数据，所以对于优化器来说，该行中的数据可以当成常量来处理。读取常量表的数据，只需读取一次，所以是很快的。
当你匹配主键中的所有字段或者唯一键中的所有字段时，就会使用const的连接类型。在下面的查询中，tbl_name表可以被当做是常量表。

SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

eq_ref
对于每个来自于前面的表的行组合，从该表中读取一行。除了system和const类型，这可能是最好的连接类型。当部分主键或者部分唯一建被使用时，连接类型就会是eq_ref。

当索引字段用等号（=）来匹配的时候，就会使用eq_ref类型。用来比较的值，可以是常数或者是其他表格的字段。以下列举了几个使用eq_ref的列子：

SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

ref
对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话）则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。

当索引字段用等号（=）或者（<=>）来匹配的时候，就会使用ref类型。以下列举了几个使用ref的列子：

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

fulltext
使用全文索引。

ref_or_null
这种连接类型与ref类似，但是mysql会额外判断是否含有null的行。在子查询中，优化器最经常使用这种连接类型。下面这个列子，mysql使用的就是ref_or_null

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

index_merge
这种类型表明优化器使用了索引合并。这种情况下，key字段的值会是一个列表，列出本次查询使用的所有索引，key_len字段也会是一个列表，列出每个索引使用的长度。

unique_subquery
在某些子查询中，会使用这种类型代替ref类型，如下面这种情况：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery只是之中使用索引来代替子查询，以此提高查询效率的方法。

index_subquery
这种类型和unique_subquery有点类似。它也是用来代替子查询，只是没有唯一索引。如下列子：

value IN (SELECT key_column FROM single_table WHERE some_expr)

range
使用一个索引来检索一个范围的数据。key字段显示哪个索引被使用，key_len显示使用索引的长度，这种情况下，ref字段的值为null。

当索引使用=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, 或者 IN()来匹配时，使用range类型。如下列子：

SELECT * FROM tbl_name WHERE key_column = 10;

SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name WHERE key_part1= 10 AND key_part2 IN (10,20,30);

index
这种类型和all类型有点类似，只不过这种方式扫描的是索引树。有两种情况会产生这种类型：

1. 如果要查询的列都在一个索引中，那么mysql将只扫描索引树来获取数据。这种情况下，extra字段会显示”Using index”。index会比all类型快的原因在于，索引树的大小一般比真实的数据表小。
2. 虽然是一个全表扫描，但会按着索引树的顺序，先扫描索引再去表中获取数据。这种情况下，extra字段不会显示”Using index”。

all
对于每个来自于先前的表的行组合，进行一次全表扫描。如果表是第一个没有标记为const的表，那么性能会比较差，而且在一般在所有的情况，性能都会极差。一般可以通过添加索引来避免产生all类型。

三、explain的extra字段信息

extra字段提供mysql在本次查询中的额外信息。下面列出该字段可能会出现的值，如果你想让你的查询尽可能的快，一定要留心该字段中出现”Using filesort”和”Using temporary”。

* Child of ‘table’ pushed join@1*
在NDB引擎中，该表被当做是一个其他表的子表。只有在NDB引擎7.2.0版本后会出现。

const row not found
比如这类查询：SELECT …… FROM tbl_name，然后表是空的。

Distinct
当mysql只需要查找唯一的值，如果它找到一行匹配的数据，它就会停止寻找这类数据。

Full scan on NULL key
在子查询中，如果优化没有使用索引来找出数据。

Impossible Having
having字段一直返回的是false，或者不能选择任何行。

Impossible WHERE noticed after reading const tables
mysql把所有的读完所有的常量表后发现where一直都是没有作用的。

No matching min/max row
没有相关的行来记录这类查询的数据：SELECT MIN(…) FROM … WHERE condition。

no matching row in const table
在一个连表查询中 ，所连接的表是空表，或者该表中没有匹配的数据。

No tables used
查询没有from字段。

Not exists
在本次查询中，mysql可以执行left join，但根据left join的条件，为当前的行组合查找到一条数据之后，就知道没有其他匹配的数据了。以下给出一个可以被优化成这个方式的实例

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

假设t2.id被定义为not null。在这种情况下，mysql先扫面t1，然后通过t1的id去t2中匹配数据。当mysql在t2中id与t1相等的行，但它知道t2的id将永远不会是null，那么mysql就不会继续在t2表中需找与t1表id相同的行了。换句话说，每扫描一行t1的数据，当去t2中寻找匹配的数据时，mysql只需要找到一行id与t1相同的数据，就不需要继续扫描了，不论t2表中有多少行的id与t1中相等。

Range checked for each record (index map : N)
mysql发现没有一个好的索引可以使用，但是当前面表中的数据已知之后，有些索引是可以用的。这种情况下，mysql会为前面表的每个行组合判断是否可以使用rang或者index_merge的方法去匹配行数据。这种方式并不快，但是总比没有使用索引的连表来得快。

索引是从1开始编号的，展示的顺序和使用show index时是一样的。索引映射值N是一个位掩码表明哪些索引会被考虑使用。举个列子，如果值是0x19（二进制是11001），则表明，索引编号为1、4和5会被考虑使用。

Scanned N databases
这表明当查询表中数据时，有多少个目录扫面了服务器执行，该值可以是0、1或者all。

Select tables optimized away
该查询只包含聚合的函数（min()、max())，那些只是用一个索引就可以解决的，或者是count(*)，而且没有groub by字段。那么优化器只需要返回一行数据。

Skip_open_table,Open_frm_only,Open_trigger_only,Open_full_table
这些值表明在表中查询数据时，文件打开的优化选择，这些值的含义如下：

1. Skip_open_table：数据表对应的文件不需要被打开，它的信息已经变成变量包含检索数据库目录的查询中。
2. Open_frm_only：只需要打开数据表对应的.frm文件就可以了。
3. Open_trigger_only：只需要打开数据表对应的.TRG文件就可以了。
4. Open_full_table：没有优化的信息可以使用，表格对应的.frm、.MYD和.MYI文件都需要被打开。

unique row not found
对于这样的查询：select …… from tbl_name，没有任何行可以满足唯一索引或者主键的条件。

Using filesort
MySQL需要额外的一次操作，以找出如何按排序顺序检索行。这次排序是通过扫面所有的行，根据连接类型，先把那些符合刷选添加的行对应的key的值和行号存起来。然后排序这些key，排序完以后，安装key的顺序把行数据返回。

Using index
字段信息只需要从索引树种获取，而不需要做一次额外的查询去读取真实的行数据。当查询中需要的字段都在索引中时，可以使用这种方法。

如果这时候Extra字段还显示“Using where”，这意味着该索引被用来查找行数据对应的指针。如果没有Using where ，优化器只需要读取索引树。举个例子，如果索引覆盖了要查询的字段，那么优化器则是扫描索引数，而不是用它来查询数据。

对于InnoDB的表来说，它有用户定义的聚合索引，这种索引即使Extra字段中没有显示“Using where”时，也会被使用。这种情况出现在，当type字段是index，且key字段是PRIMARY时。

Using index for group-by
获取表数据的方式和Using index类似，Using index for group-by表明mysql发现有一个索引可以返回group by子句或者distinct子句的所有字段，这样就不用为存储排序的key而额外扫描一次表。另外，对于分组操作，索引是最有效的方法，只有少数的索引需要被读取。

Using join buffer
在join前面的表中的部分数据被存放到缓存中，这样，在后面的表查询中就可以从缓存中获取数据。

Using sort_union(…),Using union(…),Using interesect(…)
当type为index_merge时，这些信息表明索引查找出来的数据是如何合并的。

Using temporary
为了执行本次查询，mysql需要建一个临时表来存放结果集。出现这种情况，有代表性的列子是，查询中包含group by和order by子句，而且这两个子句包含的字段是不同的。

Using where
WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。即使where子句中所有的字段都包含在一个索引中 ，也可以能会看到Using where，因为有些字段可能会是NULL。

Using where with pushed condition
这种情况只会出现在NDB表中。

四、explain输出解释

从explain的输出结果中，你可以得到一个很好地启示。它会大致的告诉你，mysql会检查多少行来完成本次查询。如果你通过系统变量max_join_size来限制查询，那么explain的输出也可以告诉你在select语句中的哪些表被查询了，哪些表被放弃了。

以下列子展示一个多表的连表查询，通过explain提供的信息，是如何渐渐地被优化的。

假设你有一个查询语句如下，然后你想通过explain来检测该语句。

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
               tt.ProjectReference, tt.EstimatedShipDate,
               tt.ActualShipDate, tt.ClientID,
               tt.ServiceCodes, tt.RepetitiveID,
               tt.CurrentProcess, tt.CurrentDPPerson,
               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
               et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
          AND tt.ActualPC = et.EMPLOYID
          AND tt.AssignedPC = et_1.EMPLOYID
          AND tt.ClientID = do.CUSTNMBR;

对于这个列子，做如下假设：
这些表的字段定义如下：

表名	字段	字段类型
tt	ActualPC	CHAR(10)
tt	AssignedPC	CHAR(10)
ttd	ClientID	CHAR(10)
et	EMPLOYID	CHAR(15)
do	CUSTNMBR	CHAR(15)

该表有如下索引

表名	索引
tt	ActualPC
tt	AssignedPC
tt	ClientID
et	EMPLOYID (primary key)
do	CUSTNMBR (primary key)

tt表的ActualPC字段的值不是均匀分布的。

一开始，没有任何的优化，explain的结果如下：

table	type	possible_keys	key	key_len	ref	rows	Extra
et	ALL	PRIMARY	NULL	NULL	NULL	74
do	ALL	PRIMARY	NULL	NULL	NULL	2135
et_1	ALL	PRIMARY	NULL	NULL	NULL	74
tt	ALL	AssignedPC,ClientID,ActualPC	NULL	NULL	NULL	3872	Range checked for each record (index map: 0x23)

由于所有的type都是All，这个结果表明，对于每个表，mysql都产生了笛卡尔积。这表明，每个组合的行，都会使用相当长的时间，由于每张表的每行数据都要被检索。对于上述情况，会产生 74 × 2135 × 74 × 3872 = 45,268,558,720行。如果表格更大一些，你可以想象这会执行多长的时间。

这里的一个问题是，如果这些字段有相同的类型，那么mysql可以使用索引来使查询更加的高效。VARCHAR和CHAR，如果定义的长度相同，那么会被认为是相同的类型。tt.ActuralPC被定义为CHAR(10)，et.EMPLOYID被定为CHAR(15)，所以长度不匹配。为了解决这个问题，用alter table命令把ActualPC的长度变成15。

ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

现在，tt.ActuralPC和et.EMPLOYID的长度都是15了。再次执行上述语句，结果如下：

table	type	possible_keys	key	key_len	ref	rows	Extra
tt	ALL	AssignedPC,ClientID,ActualPC	NULL	NULL	NULL	3872	Using where
do	ALL	PRIMARY	NULL	NULL	NULL	2135	Range checked for each record (index map: 0x1)
et_1	ALL	PRIMARY	NULL	NULL	NULL	74	Range checked for each record (index map: 0x1)
et	eq_ref	PRIMARY	PRIMARY	15	tt.ActualPC	1

这并不完美，但是已经好多了。它产生的行数，比之前少了74倍。这个版本需要执行几秒钟。

第二可以做如下改变，消除tt.AssignedPC = et_1.EMPLOYID 和tt.ClientID = do.CUSTNMBR的长度不匹配问题。

ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),MODIFY ClientID VARCHAR(15);

做了这一改变后，再次执行explain，结果如下：

table	type	possible_keys	key	key_len	ref	rows	Extra
et	ALL	PRIMARY	NULL	NULL	NULL	74
tt	ref	AssignedPC,ClientID,ActualPC	ActualPC	15	et.EMPLOYID	52	Using index
et_1	eq_ref	PRIMARY	PRIMARY	15	tt.AssignedPC	1
do	eq_ref	PRIMARY	PRIMARY	15	tt.ClientID	1

到目前为止，这个查询已经尽可能的优化了。剩下的问题是，mysql会默认假设tt.ActualPC字段上的数据是均匀分布的，然后tt表的情况并不是这样。幸运的是，这是很容易告诉mysql去分析键的分布。

ANALYZE TABLE tt

加上额外的索引信息，该连表会变得完美。explain的输出结果如下：

table	type	possible_keys	key	key_len	ref	rows	Extra
tt	ALL	AssignedPC,ClientID,ActualPC	NULL	NULL	NULL	3872	Using index
et	eq_ref	PRIMARY	PRIMARY	15	tt.ActualPC	1
et_1	eq_ref	PRIMARY	PRIMARY	15	tt.AssignedPC	1
do	eq_ref	PRIMARY	PRIMARY	15	tt.ClientID	1

explain输出的rows字段的值，是根据mysql优化器的分析得出的。判断这个rows字段的值是否接近于实际的值，可以将该值与本次查询返回的行数进行比较。如果这两个值相差很大，你可以在select子句中加入STRAIGHT_JOIN语句，然后尝试把join的表的顺序变换一下，以此来减小差距。