8.8.2. EXPLAIN Output Format（EXPLAIN输出格式）

    EXPLAIN语句提供了一个SELECT语句的执行计划信息。

    EXPLAIN返回的一行信息在SELECT语句中使用的每个表。它列出的表中的顺序是，MySQL会读他们在处理语句的输出。MySQL解决所有join使用嵌套循环连接方法。这意味着它们的MySQL读取从第一表中的一行，然后发现在第二个表匹配的行，在第三表，依此类推。当所有的表处理时，MySQL输出选中的列和通过回溯表列表直到表发现其中有更多的匹配行。下一行是从该表中读出并且该进程继续进行下一个表。

    当使用EXTENDED关键字时，EXPLAIN产生的可以通过发出SHOW WARNINGS语句后面的EXPLAIN语句来查看额外的信息。EXPLAIN EXTENDED还显示过滤列。详见，Section 8.8.3, “EXPLAIN EXTENDED Output Format”。

     | 注意：你不能使用EXTENDED和PARTITIONS关键字在同一个EXPLAIN语句。

• EXPLAIN  输出格式
  
  
• EXPLAIN Join Types
  
  
• EXPLAIN Extra Information
  
  
• EXPLAIN Output Interpretation（输出解释）
  

EXPLAIN Output Columns

    本节介绍EXPLAIN产生的输出列。后来部分提供了有关类型和额外的列的附加信息。

    从解释每个输出行提供了一个表的信息。每一行包含的值在Table 8.1, “EXPLAIN Output Columns”, 和在下面的表格更详细地描述。

    Table 8.1. EXPLAIN Output Columns

Column	Meaning
id [948]	该SELECT标识符
select_type [949]	SELECT 类型
table [949]	表对输出行
partitions [949]	匹配分区
type [949]	join类型
possible_keys [950]	可能的索引选择
key [950]	索引实际选择
key_len [950]	所选键的长度
ref [950]	相比于索引的列
rows [950]	估计要检查的行
filtered [950]	行的百分比由过滤条件表
Extra [951 ]	附加信息

• id
  

SELECT标识符。SELECT在查询中的顺序号。该值可以是NULL，如果该行是指其他行的union的结果。在这种情况下，该表列显示像以指示该行是指行的union与M和N的ID值的值。

• select_type
  

SELECT的类型，它可以是下表中所示的任何的。

select_type Value	Meaning
SIMPLE	简单的SELECT（不使用UNION或子查询）
PRIMARY	最外层的SELECT
UNION	在UNION中第二或更更后的SELECT语句
DEPENDENT UNION	在UNION中第二个或后面的SELECT语句，依赖于外部查询。
UNION RESULT	一个UNION的结果。
SUBQUERY	在子查询中的第一个SELECT
DEPENDENT SUBQUERY	在子查询中的第一个SELECT，依赖于外部查询
DERIVED	派生表SELECT（在FROM子句中的子查询）
MATERIALIZED	物化子查询
UNCACHEABLE SUBQUERY	对于该结果不能被缓存并且必须重新评估外部查询的每一行的子查询。
UNCACHEABLE UNION	第二个或者最面一个属于一个不可缓存的子查询在UNION中（见UNCACHEABLE SUBQUERY）

    DEPENDENT通常意味着使用相关子查询。详见，Section 13.2.10.7, “Correlated Subqueries”。

    DEPENDENTSUBQUERY评价不同于UNCACHEABLE SUBQUERY评价。对DEPENDENT SUBQUERY，子查询为每套从外部环境变量的不同值的重新评估一次。对于UNCACHEABLE SUBQUERY，子查询被重新评估外部环境中的每一行。

    子查询可缓存不同于查询结果的缓存在该查询缓存中（这是在Section 8.9.3.1, “How the Query Cache Operates”所描述）。查询执行过程中出现的子查询高速缓存，而查询缓存用于存储结果的查询执行完成之后。

• table
  

向其中输出的行是指该表的名称。这也可以是以下值之一：

• M, N> ： 该行是指行的联合与M和N的ID值。
• N> ： 该行指的是派生表结果行用N的ID值。派生表可能导致，例如，从一个子查询在FROM子句。
• N> ：行是指一种物化子查询的行的结果用N的值标识。详见，Section 8.13.16.2, “Optimizing Subqueries with Subquery Materialization”。
  

• partitions
  

分区从该记录将被查询相匹配。显示此列仅在使用PARTITIONS关键字。该值为NULL对于非分区表。详见，Section 17.3.5,“Obtaining Information About Partitions”。

• type
  

join类型。对于不同类型的描述，详见，EXPLAIN Join Types。

• possible_keys
  

possible_keys列指出哪些索引MySQL能够从使用选择找到此表中的行。注意，作为显示在由EXPLAIN输出该列是完全独立的表的顺序。这意味着，在一些possible_keys的键可能无法在实践中与所生成的表的顺序使用。

如果该值为NULL，有没有相关的索引。在这种情况下，您可以通过检查WHERE子句来检查它是否是指一些列或多列，这将是适合索引以改善查询性能。如果是这样，创建一个适当的索引和查询再次用EXPLAIN。详见，Section 13.1.6, “ALTER TABLE Syntax”。

看到一个表有什么索引，使用SHOW INDEX FROM tbl_name。

• key
  

键列指示键（索引），MySQL的实际决定使用。如果MySQL决定使用possible_keys索引中的一个来查找的行，该索引列为键值。

这是可能的键将命名一个索引中不存在该possible_keys值。

• key_len
  

该key_len列指出MySQL决定使用的密钥的长度。长度为NULL如果键列表示NULL。

• ref
  

ref列显示哪个列或常量比较键列命名的索引从表中选择行。

• rows
  

该行列显示MySQL的相信它必须检验以执行查询的行数。

对于InnoDB表，这个数量是一个估计值，并且可能不总是准确。

• filtered
  

过滤后的列指示的表中的行的估计百分比将由表条件进行过滤。也就是说，行显示检查的行估计数目和rows × filtered / 100 示出了将与前面的表中被join的行数。如果使用EXPLAIN EXTENDED这个列显示。

• Extra
  

这列包含MySQL解决查询的额外信息。为不同的值的说明，详见，EXPLAIN Extra Information。

EXPLAIN Join Types

    EXPLAIN输出的类型列描述了如何表的连接。下面的列表介绍了join类型，从最好的典型有序

• system [951 ]
  

表只有一行（=系统表）。这是const[951]联接类型的一个特例。

• const [951 ]
  

表中有至多一个匹配行，这是读取在查询的开始。因为仅存在一个行，从该行中的列的值可以通过优化的其余部分被视为常数。const[951]表非常快，因为它们只读取一次。

const的[951]当你比较PRIMARY KEY或UNIQUE索引常量的所有部分被使用。在下面的查询，tbl_name可以用作一个const[951]表：

SELECT * FROM tbl_name WHERE primary_key=1;

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

• eq_ref [951 ]
  

一行从该表中读出用于从前面的表中的行的每一种组合。
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[951]

相匹配的索引值的所有行从该表中读取来自于先前的表行的每个组合。
SELECT * FROM ref_tableWHERE 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[952]

联接使用FULLTEXT索引执行。

• ref_or_null[952]

类似ref[951]，但是与MySQL的确实为包含NULL值的行额外的搜索增加。

SELECT * FROM ref_table

WHERE key_column=expr OR key_column IS NULL;

•  index_merge[952]

该join类型指示索引合并优化使用。

• unique_subquery[952]

这种类型的替代ref[951]对于一些在下面的表单子查询：

value IN (SELECT primary_keyFROM single_tableWHERE some_expr)

• index_subquery[952]

此连接类型类似于unique_subquery[952]。

value IN (SELECT key_columnFROM single_tableWHERE some_expr)

• range[952]

仅是在给定的范围内的行检索，利用一个索引来选择行。范围[952]可以在一个键列相比于恒定被使用使用任何= [1233]，<>[1234]，> [1234]，> =[1234]，<[1234]<=〔1234]，IS NULL[1235]<=>[1234]，BETWEEN[1235]，或IN（）[1236]运算符：
SELECT * FROM tbl_name

WHERE key_column= 10;

SELECT * FROM tbl_name

WHERE key_columnBETWEEN 10 and 20;

SELECT * FROM tbl_name

WHERE key_columnIN (10,20,30);

SELECT * FROM tbl_name

WHERE key_part1= 10 AND key_part2IN (10,20,30);

• index[953]

索引join 类型是类似ALL，除了使用索引树被扫描。发生这种情况有两种方法：

• 如果该索引是用于查询覆盖索引并且可以用来满足从所述表中所要求的所有的数据，仅索引树被扫描。在这种情况下，Extra的列表示Using index。
• 使用进行全表扫描从索引读取查找数据行的索引顺序。Uses  index 没有出现在Extra列。

MySQL能使用这种连接类型时查询使用仅是单个索引的一部分列。

• ALL
  全表扫描完成从以前的表行的每个组合。

EXPLAIN Extra Information

    EXPLAIN输出的Extra列包含MySQL解决查询的额外信息。下面的列表说明了可能出现在此列中的值。如果你想使你的查询尽可能快，看出来使用Using filesort和Using temporary的额外值。

• const row not found

对于一个查询，如SELECT... FROM tbl_name，该表是空的。

• Deleting all rows
  对于DELETE，一些存储引擎（如MyISAM）支持删除所有表行的一个简单和快速方式处理方法。
  
  
• Distinct
  MySQL正在寻找不同的值，所以停止寻找当前行结合更多的行后它已找到的第一个匹配行。

• FirstMatch(tbl_name )
  该半连接第一个匹配加入shortcutting策略用于tbl_name。
  
  
• Full scan on NULL key
  出现这种情况的子查询优化的当优化程序无法使用索引的查找访问方法的后备战略。
  
  
• Impossible HAVING
  HAVING子句永远是虚假和不能选择任何行。
  
  
• Impossible WHERE
  WHERE子句总是虚假和不能选择任何行。
  
  
• Impossible WHERE noticed after reading const tables
  MySQL已经读取所有const[951]（和system[951]）表并注意WHERE子句中始终为false。
  
  
• LooseScan(m..n)
  该半连接LooseScan策略使用。m和n是关键的部件编号。 
  
  
• Materialize, Scan
  在MySQL5.6.7之前，这表明使用单一的物化临时表。
  
  
• No matching min/max row
  没有行满足条件，如查询SELECT MIN（...）FROM... WHERE条件。
  
  
• no matching row in const table
  对于连接的查询，有一个空表或没有行满足的唯一索引条件的表。
  
  
• No matching rows after partition pruning
  对于DELETE 或UPDATE，优化在分区修剪后没有发现任何删除或更新。
  
  
• No tables used
  查询没有FROM子句，或有一个FROM DUAL子句。
  
  
• Not exists
  MySQL是能够做一个LEFT JOIN优化查询和不检查更多的行此表的上一行组合发现一行匹配LEFT JOIN条件后。
  SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id 
  WHERE t2.id IS NULL;
  
  
• Range checked for each record (index map: N)
  MySQL发现没有好的索引使用，但发现有些索引可能列值后即可使用来自前表是已知的。
  
  
• Scanned N databases
  这表明处理的查询时很多目录如何扫描服务器执行 INFORMATION_SCHEMA表，
  
  
• Select tables optimized away
  该查询只包含聚合函数（MIN（）[1359]，MAX（）[1358]）的全部使用索引，或COUNT（*）[1357]对MyISAM，并且没有GROUP BY子句解决。
  
  
• Skip_open_table, Open_frm_only, Open_trigger_only, Open_full_table
  这些数值表明，适用于查询INFORMATION_SCHEMA表文件打开优化，
• Skip_open_table：表文件不需要被打开。
• Open_frm_only：只有表.frm文件需要被打开。
• Open_full_table：未经优化的信息查询。.frm，.MYD和.MYI文件必须被打开。

• Start materialize, End materialize, Scan
  MySQL5.6.7之前，这表明使用多个物化临时表。如果当前是Scan，还没有临时表索引用于表读。
  
  
• Start temporary, End temporary
  这表明临时表使用的半连接复制Weedout策略。
  
  
• unique row not found
  对于一个查询，如SELECT... FROM tbl_name，没有行满足在表中的UNIQUE索引或PRIMARY KEY条件。
  
  
• Using filesort
  MySQL必须做一个额外地传递找到了如何检索排序顺序的行。详见，Section 8.13.13, “ORDER BY Optimization”。
  
  
• Using index
  列信息，从通过索引树中唯一信息的表中检索，而无需做额外寻求读取实际的行。
  
  
• Using index condition
  表由访问索引元组和测试他们首先要确定是否要读全表中的行读取。详见，Section 8.13.4, “Index Condition Pushdown Optimization”。
  
  
• Using index for group-by
  类似Using index表的访问方法，Using index for group-by由表示的MySQL发现可用于检索GROUP BY或DISTINCT查询的所有列，没有任何额外的磁盘访问实际的表的索引。详见，Section 8.13.14, “GROUP BY Optimization”.
  
  
• Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)
  从早期的表联接读入部到连接缓冲中并且它们的行被从缓冲器用来执行联接与当前表。
  
  
• Using MRR
  表使用的是多范围读取优化策略读取。详见，Section 8.13.11, “Multi-Range  Read Optimization”。
  
  
• Using sort_union(...), Using union(...), Using intersect(...)
  这表明索引如何扫描合并为index_merge[952]连接类型。详见，Section 8.13.2, “Index Merge Optimization”。
  
  
• Using temporary
  要解决查询，MySQL需要创建一个临时表来容纳结果。这通常发生如果查询包含GROUP BY和ORDER BY子句列表列不同。
  
  
• Using where
  WHERE子句用于限制哪个行匹配下一个表或发送到客户端。
  
  
• Using where with pushed condition
  此产品仅适用于NDB表。这意味着，MySQL簇正在使用条件下推优化，以改善一非索引列和常数之间的直接比较的效率。

EXPLAIN Output Interpretation（EXPLAIN输出解读）

    可以得到多么好的一个连接是通过利用值的产品在EXPLAIN输出的rows列一个很好的迹象。这应该粗略地告诉你多少行的MySQL必须检查执行查询。如果限制查询用max_join_size个[534]系统变量，此行产品还用于确定多个表的SELECT语句来执行它并中止。详见，Section 8.11.2, “Tuning Server Parameters”。

    下面的示例说明多表join如何可以逐步优化的基础在EXPLAIN提供的信息。

    假设你有如下所示的SELECT语句，并且您计划使用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;

    对于这个例子，进行以下假设：

• 被比较的列已声明如下。
  

  Table	Column	Data Type
  tt	ActualPC	CHAR(10)
  tt	AssignedPC	CHAR(10)
  tt	ClientID	CHAR(10)
  et	EMPLOYID	CHAR(15)
  do	CUSTNMBR	CHAR(15)

• 该表有下列索引。
  

  Table	Index
  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,               NULL           NULL                NULL         3872

                                                ClientID,

                                                ActualPC

                            Range checked for each record (index map: 0x23)

    由于type是ALL为每个表，这个输出显示MySQL正在生成所有表的笛卡尔乘积;即，行的每一个组合。这需要相当长的时间，因为在每一个表中的行数的乘积必须检查。为待处理情况下，这款产品是74×2135×74× 3872=45268558720行。如果表更大，你只能想象它会需要多长时间。

    这里的一个问题是MySQL能更有效地利用索引列上，如果它们被声明为相同的类型和大小。在这种情况下，VARCHAR和CHAR被认为是相同的，如果它们被声明为相同的大小。tt.ActualPC被声明为CHAR（10）及et.EMPLOYID为CHAR（15），所以有一个 长度不匹配。

    要解决列长度上的不同，使用ALTER TABLE从10个字符ActualPC的长度为15个字符：

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

    现在tt.ActualPC和et.EMPLOYID均为VARCHAR（15）。再次执行EXPLAIN语句产生这样的结果：

    table    type    possible_keys    key        key_len    ref        rows      Extra

    tt          ALL     AssignedPC,      NULL     NULL        NULL    3872     Using

                           ClientID,                                                                     where

                           ActualPC

    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比较：

mysql> 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,      ActualPC   15             et.EMPLOYID    52         Using 

                 ClientID,                                                                                                   where 

                 ActualPC 

    et_1     eq_ref     PRIMARY         PRIMARY     15             tt.AssignedPC 1 

    do     eq_ref       PRIMARY         PRIMARY     15             tt.ClientID      1

    在这一点上，查询优化几乎尽可能好。剩下的问题是，默认下，MySQL假定在tt.ActualPC列值是均匀分布的，即不能为TT表的情况。幸运的是，很容易告诉MySQL来分析键分布：

mysql> ANALYZE TABLE tt;

    随着更多的索引信息，该join是完美的，并EXPLAIN产生这样的结果：

    table     type         possible_keys     key         key_len     ref             rows     Extra 

    tt           ALL          AssignedPC        NULL      NULL         NULL         3872    Using 

                                 ClientID,                                                                            where 

                                 ActualPC 

    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联结优化。检查是否数字甚至接近真相的行产品，该查询返回行的实际数目作比较。如果数字有很大的不同，你可能会在你的SELECT语句中使用STRAIGHT_JOIN并试图列出表以不同的顺序在FROM子句中获得更好的性能。

    它有可能在某些情况下，执行修改数据时，EXPLAIN SELECT一起使用子查询语句; 更多信息，详见 Section 13.2.10.8, “Subqueries in the FROM Clause”。