本文是翻译而来的，原文地址：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类型有点类似，只不过这种方式扫描的是索引树。有两种情况会产生这种类型：
- 如果要查询的列都在一个索引中，那么mysql将只扫描索引树来获取数据。这种情况下，extra字段会显示”Using index”。index会比all类型快的原因在于，索引树的大小一般比真实的数据表小。
- 虽然是一个全表扫描，但会按着索引树的顺序，先扫描索引再去表中获取数据。这种情况下，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
这些值表明在表中查询数据时，文件打开的优化选择，这些值的含义如下：
- Skip_open_table：数据表对应的文件不需要被打开，它的信息已经变成变量包含检索数据库目录的查询中。
- Open_frm_only：只需要打开数据表对应的.frm文件就可以了。
- Open_trigger_only：只需要打开数据表对应的.TRG文件就可以了。
- 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的表的顺序变换一下，以此来减小差距。
616
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
