MySQL——EXPLAIN和执行计划

时间：2020-12-27 03:53:26

EXPLAIN

MySQL 里提供一个解释命令EXPLAIN。EXPLAIN的用法有两种，分别是：

1、解释表结构，等同于DESCRIBE，语法： 
EXPLAIN table_name; 
2、解释查询语句，获得关于 MySQL 如何执行SELECT语句的信息，语法： 
EXPLAIN [EXTENDED|PARTITIONS] SELECT select_options;

这里主要讨论一下EXPLAIN的第二种用法。

作用

在SELECT语句前加上关键词EXPLAIN，MySQL 会解释它将如何处理这句SELECT，比如如何联接有关表和联接的次序。

借助EXPLAIN，可以知道什么时候必须为表加入索引以得到一个使用索引来查找记录的更快的SELECT，还可以知道 MySQL优化器 是否以一个最佳次序来联接有关表（一般情况下，MySQL 会对查询语句进行优化后再执行，如果想强制优化器让一个SELECT语句按照表命名顺序的联接次序，语句前头可以加上STRAIGHT_JOIN）。

EXPLAIN为SELECT语句中用到的每个表都返回一行信息。被用到的表，会按它们将在处理查询过程中被 MySQL 读入的顺序列出。 
对于需要用到的表，MySQL 会用一遍扫描、多次联接（single-sweep & multi-join）的方式解决所有联接。这意味着 MySQL 从第一张表中读一行，然后在第二张表中查找一个匹配行，然后在第三张表中查找……依此类推；在所有关联的表中查找完后，MySQL会输出获得的记录（行）中被选中（SELECTE）的列，并且回溯相关的表，查找更多的匹配行（直至没有）；然后再从第一张表读入下一行，继续在后面的表中找匹配行。

当使用EXTENDED选项时，EXPLAIN结果会多出一列filtered：filtered列中的值势一个百分比，这个百分比值和rows列的值一起使用，可以估计与前一个表进行联接的行的数目。 
执行完EXPLAIN EXTENDED后，再执行SHOW WARNINGS语句可以查看经过 MySQL优化器 执行优化规则后的SELECT语句是什么样子的，而且还可能包括一些优化过程中的注解。

EXPLAIN的输出格式

EXPLAIN输出列：

列名 意义
id SELECT序列号。
select_type SELECT类型。
table 所引用表的名字。
type 联接类型。
possible_keys 可以用到的索引。
key 实际使用的索引。
key_len 所用索引的长度（单位Byte）。
ref 表中配合索引检索的列名。
rows MySQL认为执行查询时必须检查的行数。
Extra 关于MYSQL如何解析查询的额外信息 。

输出结果

id

id指明了MySQL对表的查询顺序。如果该行是其他行联合UNION的结果，那么此处id的值时NULL；而table的值是<union M,
N>。

select_type

select_type指明 MySQL 对表的查询类型。可选的值有：

  • SIMPLE：简单的SELECT（不使用联合UNION或子查询SUBQUERY）。
  • PRIMARY：最外层的SELECT。
  • UNION：处于UNION中的第二个的（或者说后面的）SELECT语句。
  • DEPENDENT UNION：处于UNION中第二个（或者说后面的）SELECT语句，依赖于外部查询。
  • UNION RESULT：UNION的结果。
  • SUBQUERY：子查询中的第一个SELECT。
  • DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询。
  • DERIVED：派生表的SELECT(FROM子句的子查询)。
  • UNCACHEABLE SUBQUERY：结果不能被缓存，必须重新扫描外部查询的每一行的子查询。
  • UNCACHEABLE UNION：属​于UNCACHEABLE SUBQUERY的UNION中第二个（或者说后面的）SELECT语句。

table

table显示该行所引用表的名字。如果该行引用的是一个联合UNION，那么table值的形式是<union M, N>，指的是由行id为M和N的SELECT的联合；如果该行引用的是一个派生表（例如，在FROM子句里做子查询），那么table值的形式是<derived N> ，指的是由行id为N的SELECT的派生。

type（重要）

type显示了 MySQL 对该行引用表的连接方式，与后面的key值有关联。可选的值有（性能从高到低排列）：

  • system：检索的表中仅有一行(=系统表)，这是const连接类型的一个特例。

  • const：检索的表中最多只有一行匹配的记录，它在查询一开始的时候就会被读取出来。因为仅有一行，在余下的优化程序里该行记录的字段值可以被当作是一个常数值。const表查询起来非常快，因为它们只需要被读取一次！将主键（PRIMARY KEY）或唯一索引（UNIQUE INDEX）与常数值比较的时候，会用到const连接，如：

       
   
      

    1. SELECT * FROM tbl_name WHERE primary_key = 1;
      2. 
    
    
      3. 
    
    
    3. //联合主键
      4. 
    
    
    4. SELECT * FROM tbl_name WHERE primary_key_part1 = 1 AND primary_key_part2 = 2;
      5.

  • eq_ref：检索的表中会有一行记录被读取出来以和之前的表读取出来的记录进行组合。不同于system和const，eq_ref是最好的连接方式（system和const没有用到JOIN语句）。当一个索引的所有部分（所关联的列）被联接（JOIN ON）使用并且这个索引是主键（PRIMARY KEY）或唯一索引（UNIQUE INDEX）的时候，会用到eq_ref连接，在下面的例子中，MySQL可以使用eq_ref连接来处理ref_table表：

       
   
      

    1. SELECT * FROM ref_table, other_table WHERE ref_table.key_column = other_table.column;
      2. 
    
    
      3. 
    
    
    3. //联合主键
      4. 
    
    
    4. SELECT * FROM ref_table, other_table WHERE ref_table.key_column_part1 = other_table.column AND ref_table.key_column_part2 = 1;
      5.

  • ref：表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录进行组合。使用一个不是PRIMARY KEY或UNIQUE INDEX的索引或者该索引（如果这是个多列索引）的最左前缀来连接表的时候（换句话说，就是连接程序无法根据键值只取得一条记录），就会用到ref连接。当根据键值只查询到少数匹配的记录时，这时ref也是一个不错的连接类型。 ref只能用于检索字段使用=或<=>操作符来比较的时候。在下面的例子中，MySQL 将使用 ref连接来处理ref_table表：

       
   
      

    1. SELECT * FROM ref_table WHERE key_column <=> expr;
      2. 
    
    
      3. 
    
    
    3. SELECT *FROM ref_table, other_table WHERE ref_table.key_column = other_table.column;
      4. 
    
    
      5. 
    
    
    5. SELECT *FROM ref_table, other_table WHERE ref_table.key_column_part1 = other_table.column AND ref_table.key_column_part2 = 1;
      6.

  • fulltext：连接表时使用了fulltext全文本索引。

  • ref_or_null：ref_or_null类似于ref，不同的是 MySQL 会在检索的时候额外地搜索包含NULL值的记录。在下面的例子中，MySQL 会使用ref_or_null 类型来处理 ref_table表：

       
   
      

    1. SELECT * FROM ref_table WHERE key_column <=> expr OR key_column IS NULL;
      2.

  • index_merge：index_merge表示连接表时使用了索引合并优化方法。当type值为index_merge，key值会包含所有使用的索引，key_len值会包含所有使用的索引的长度。

  • unique_subquery：unique_subquery是一个索引查找函数，可以完全替换IN子查询，效率更高。unique_subquery会代替ref，被用在如下的一些IN子查询语句中： 
    value IN (SELECT primary_key FROM single_table WHERE some_expr)

  • index_subquery：类似unique_subquery，可以替换IN子查询，但只适合使用非唯一索引的子查询： 
    value IN (SELECT key_column FROM single_table WHERE some_expr)

  • range：在使用索引去检索时，只有键值符合给定范围的记录才会被取出来。当type值为range，key值指明使用哪个索引，key_len值指明索引的长度，而ref值是NULL。将一个有索引的字段用操作符=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或IN和一个常数值进行比较时，会用到range连接，如：

       
   
      

    1. SELECT * FROM tbl_name WHERE key_column = 10;
      2. 
    
    
      3. 
    
    
    3. SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20;
      4. 
    
    
      5. 
    
    
    5. SELECT * FROM tbl_name WHERE key_column IN (10,20,30);
      6. 
    
    
      7. 
    
    
    7. SELECT * FROM tbl_name WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
      8.

  • index：index连接类型跟ALL一样，不同的是它只扫描索引树。它一般会比 all快，因为索引文件通常都比数据文件小。MySQL 在查询的字段属于一个单列索引的情况下使用index连接类型。

  • ALL：将对该表做全部扫描，来与之前的表中取得的记录进行组合。这时候，如果第一个表没有被标识为const的话，整个执行效率就不大好了，而且通常情况下是非常糟糕的。一般来说，可以通过增加索引来避免ALL，使得能从表中更快的取得记录。

possible_keys

possible_keys显示了对于查询条件，MySQL 可以使用哪些索引。

key

key显示 MySQL实际决定使用的索引。如果没有选择索引，key值是NULL。要想强制 MySQL 使用或忽视possible_keys列中的索引，可以在SELECT语句中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

ref

ref显示了哪些字段或者常数值被用来和key值中指明的索引配合从表中查询记录。

row

row显示了 MySQL 认为在查询中应该检索这张表的记录数。

Extra

Extra显示了 MySQL 在查询中的一些附加信息。可选的值有：

  • Distinct：MySQL 发现第1个匹配行后，停止为当前的行组合搜索更多的行。

  • Not
    exists    ：MySQL 在查询时会对LEFT JOIN进行优化，找到一个匹配LEFT JOIN条件的记录行后，不再为前面的的行组合在该表内检查更多的行。下面是一个可以这样优化的例子，MySQL使用t1.id的值扫描t1表并查找t2表中的行。如果 MySQL 在t2表中发现一个匹配的行，它知道t2.id绝不会为NULL，并且不再扫描t2内有相同的id值的行。换句话说，对于t1的每个行，MySQL 只需要在t2中查找一次，无论t2内实际有多少匹配的行：

       
   
      

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

  • range
    checked for each record (index map: N)    ：MySQL 没有找到有效的索引来使用，但发现来自前面的表的字段值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来获取行。这并不很快，但比执行没有索引的连接要快得多。

  • Using
    filesort    ：只有在使用ORDER BY的时候才可能会出现Using filesort。作为排序规则的字段如果没有建立索引，那么就会出现文件排序。

  • Using
    index    ：作为排序规则的字段如果有建立索引，那么就会使用索引排序，即Using index。

  • Using
    temporary    ：为了解决多重查询，MySQL需要创建一些临时表来保存结果集。使用GROUP BY或ORDER BY语句时，经常出现需要临时表的情况。

  • Using
    where    ：WHERE子句用于限制表中哪一行去匹配下一张表或者返回数据。只有在使用WHERE语句的时候才可能会出现Using where。作为限制规则的字段如果没有建立索引，那么就会出现Using where，如果有建立索引，那么就会使用索引限制，即Using index。

  • Using
    index for group-by    ：类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。

额外

最左前缀：MySQL在建立索引的时候是以B-树结构建立的，建立一个多列索引INDEX(a, b, c)是，会先建立a的按顺序排列的索引，在a相同的情况下建立b的按顺序排列的索引，最后在b相同的情况下建立c的按顺序排的索引，如果列数更多也依此类推。a是这个索引的最左前缀，这个索引必须要配合a来使用，没有a时这个索引不起作用。相当于建立了INDEX(a)、INDEX(a, b)和INDEX(a, b, c)。

注意

  1. type：当type值为ALL时，说明查询语句需要全表扫描，这时候要添加索引来提高查询效率。
  2. Extra：当Extra值为Using filesort或Using where时，应该考虑添加索引，使得Extra值变为Using index；当Extra值为Using temporary时，应该考虑修改程序语句，避免使用临时表。

范例

下面是一个多表连接查询是如何使用EXPLAIN提供的信息逐步优化的例子。

现有SELECT语句：

 
 
    

  1. EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
    2. 
  
  
  2.  tt.ProjectReference, tt.EstimatedShipDate,
    3. 
  
  
  3.  tt.ActualShipDate, tt.ClientID,
    4. 
  
  
  4.  tt.ServiceCodes, tt.RepetitiveID,
    5. 
  
  
  5.  tt.CurrentProcess, tt.CurrentDPPerson,
    6. 
  
  
  6.  tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
    7. 
  
  
  7.  et_1.COUNTRY, do.CUSTNAME
    8. 
  
  
  8.  FROM tt, et, et AS et_1, do
    9. 
  
  
  9.  WHERE tt.SubmitTime IS NULL
    10. 
  
  
  10.  AND tt.ActualPC = et.EMPLOYID
    11. 
  
  
  11.  AND tt.AssignedPC = et_1.EMPLOYID
    12. 
  
  
  12.  AND tt.ClientID = do.CUSTNMBR;
    13.

已知，被比较的字段的声明如下：

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字段的值是不均匀分布的。

下面开始优化：

1、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行记录。如果表更大，花费的时间更多。

这里连接类型type都是ALL的原因是WHERE语句不起作用，例如tt.ActualPC始终不等于et.EMPLOYID，因为两者的数据长度不同（数据类型是相同的）。

使用ALTER TABLE将ActualPC的长度从10个字符变为15个字符，改为VARCHAR(15)而不是CHAR(15)，VARCHAR(15)可以与CHAR(15)匹配，同时可以节省空间（只占用原来10个长度）：

 
 
    

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

2、再次执行EXPLAIN语句：

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  

使用ALTER TABLE将AssignedPC和ClientID的长度从10个字符变为15个字符：

 
 
    

  1. mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
    2. 
  
  
  2.  -> MODIFY ClientID VARCHAR(15);
    3.

3、继续执行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 where
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1  
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1  

这是的查询语句的效率已经很不错了。还有个问题，一般情况下，MySQL 认为表中字段的值是均匀分布的，但是tt表中的ActualPC字段的值是不均匀分布的。这时可以通过ANALYZE语句来统计整理表的信息和更新索引内容：

 
 
    

  1. mysql> ANALYZE TABLE tt;
    2.

4、最后再执行一次EXPLAIN语句：

table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC, ClientID, ActualPC NULL NULL NULL 3872 Using where
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  

现在的连接可以说是“完美”的了。

from: http://breeze.leanote.com/post/mysql_command_explain

标签：

相关文章