MySQL优化---EXPLAIN（mysq版本8.0）

Explain命令是MySQL提供的内置命令，它的作用是向我们展示MySQL是如何执行sql语句的。SELECT, DELETE, INSERT, REPLACE,  UPDATE 语句都可以使用Explain命令。

EXPLAIN为SELECT语句中使用的每个table返回一行信息。它以MySQL在处理语句时的读取顺序列出所有的table。MySQL使用嵌套循环的方式解决所有的表连接（join）。这意味着MySQL从第一个table读取一行，然后在第二个table、第三个table中查找匹配的行，以此类推。当所有的table都被处理过时，MySQL会输出所选的列并在所有的table中进行反向跟踪，直到找到一个有更多匹配行的table。从该table中读取下一行，并继续处理下一个table。

理解Explain输出的信息含义对SQL语句的优化有着至关重要的作用。 下面让我们详细解析下Explain命令输出的各字段的含义。

Explain 输出的列：

1. <span id ='explain_id'>id</span>

   SELECT的标识符。这是查询中的SELECT的序号。如果当前行引用了其他行的联合结果，则该值为空。举个例子，当table列显示<unionM,N>时，表示该行引用了id值为M和N的行的联合结果，这个时候id列的值就会为空。

   如下sql：

    EXPLAIN SELECT *FROM(SELECT 2 UNION ALL SELECT 3 )t;

   执行结果：

   
   

   

   1532432243263.png

2. <span id="explain_select_type">select_type</span>

   SELECT的类型，下表中展示了所有可能的值及其含义。

   名称	JSON 名	含义
   SIMPLE	无	简单的SELECT(没有使用UNION或者子查询)
   PRIMARY	无	最外层的查询
   UNION	无	在一个UNION中第二或后面的SELECT语句
   DEPENDENT UNION	dependent (true)	在一个UNION中第二或后面的SELECT语句，并且依赖于外层查询
   UNION RESULT	union_result	UNION的结果
   SUBQUERY	无	子查询中的第一个SELECT
   DEPENDENT SUBQUERY	dependent (true)	子查询中的第一个SELECT，并且依赖于外层查询
   DERIVED	无	派生表（Derived table）
   MATERIALIZED	materialized_from_subquery	实例化子查询（Materialized subquery）
   UNCACHEABLE SUBQUERY	cacheable (false)	不能缓存结果的子查询，并且必须为外部查询的每一行重新计算结果
   UNCACHEABLE UNION	cacheable (false)	在一个UNCACHEABLE SUBQUERY的UNION语句中第二或后面的SELECT语句

   DEPENDENT通常意味着使用了关联子查询。

   DEPENDENT SUBQUERY 不同于 UNCACHEABLE SUBQUERY 。对于DEPENDENT SUBQUERY，对来自其外部上下文的每一组变量的不同值只重新计算一次子查询。对于UNCACHEABLE SUBQUERY，将会为外层上下文的每一行重新计算子查询。

   对于非查询语句，select_type的值为语句类型。比如，对于一个DELETE语句，值为DELETE

3. <span id="explain_table">table</span>

   当前行引用的表名，除此之外也可能是以下几种值：

• <union*M*,*N*>：该行引用了id值为M和N的行的联合结果

• <derived*N*>：该行引用了id值为N的行的派生表结果。比如引用了从子查询中生成的派生表。

• <subquery*N*>：该行引用了id值为N的行的实例化子查询结果。

<span id="explain_partitions">partitions</span>

查询语句将从其中匹配记录的分区。对于非分区表，值为null。

<span id="explain_type">type</span>

本列用于向我们展示MySQL使用哪种方式join当前表的。下面按照最好到最坏的顺序详细解析可能出现的值。

• 当索引是一个覆盖索引，即包含(或覆盖)所有需要查询的字段的值，那么就只需要扫描索引树而无需回表，这个时候Extra列的值就会为Using index。index类型通常要比ALL类型要快，因为索引的长度通常要比表数据小得多。

• 按索引顺序查找数据行来执行全表扫描，这个时候Extra列的值不会为Using index。

• system

  该表只有一行数据。这种类型是下面const类型的一种特例。

• const

  该表最多只有一行数据被匹配，在查询开始时就会被读取。因为只有一行，所以这一行中列的值可以被优化器的其他部分视为常量。const表非常快，因为它们只需要读取一次。

  当把一个表的主键或唯一索引和一个常量值作比较时，const就会被使用。下面的语句中，t1就可以被作为一个const表。

  SELECT * FROM  t1 WHERE id=1;SELECT * FROM  t2 WHERE unique_index_part1=1 and unique_index_part1=2;

• eq_ref

  在与前一个表的每一行做联合时，只需要从该表中读取一行。除了system和const类型，这个是最好的join类型。当一个join语句中使用了主键或一个非空唯一索引的全部部分时，join类型就为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

  在与前表的每个行组合中，从该表中读取具有匹配索引值的所有行。当join仅仅使用一个索引的左前缀，或者使用的索引不是一个主键，又或者不是唯一索引，ref就会被使用。如果使用的索引匹配的行数很少，这也不失为一个很好的join类型。

  eq_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

  使用全文索引执行join

• ref_or_null

  这个join类型和ref有点像，但是对于包含null值行，MySQL会做一个额外的查询。这种join类型优化最常用于解析子查询。

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

• index_merge

  这个join类型表示使用了索引合并优化。这个时候，key列显示了一系列使用到的索引，key_len显示了使用到的索引的最长键长。

• unique_subquery

  在以下形式的某些in子查询中，这个join类型会替代eq_ref。

  value IN (SELECT primary_key FROM single_table WHERE some_expr)

  unique_subquery只是一个索引查找函数，它完全替代了子查询以提高效率。

• index_subquery

  这个join类型和unique_subquery有些类似。它在子查询中替换，但在以下形式的子查询中适用于非唯一索引:

  value IN (SELECT key_column FROM single_table WHERE some_expr)

• range

  当使用一个索引去选择行时只检索了给定范围的行。key列显示了使用了哪个索引，key_len显示了使用到的索引的最长键长。当join类型为range时，ref列值为null。

  range可用于使用 =, <>, >, >=, <, <=, IS NULL, <=>,BETWEEN, LIKE, IN() 运算符和一个常量进行比较的索引列。

  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

  这个join类型和ALL相似，区别是扫描了索引数。以下两种情况：

• ALL

  为了和前表的每一行做组合而全表扫描。这种类型通常不太好，应当尽量避免。我们可以通过给表添加索引来避免这种情况。

<span id="explain_possible_keys">possible_keys</span>

这一列显示了可供MySQL选择用来查询使用的所有索引。需要注意的是其中的一些索引在实际运行时可能不会被使用。

如果这一列为null，那就表示没有想关的索引。这个时候，可以检查where子句引用的列是否适合建立索引，通过建立合适的索引增强执行性能。索引建立完成后，可以再次执行EXPLAIN查看效果。

想要知道表已经建立了哪些索引，可以通过SHOW INDEX FROM table_name查看。

<span id="explain_key">key</span>

本列显示的MySQL实际选择使用的索引。如果本列为空，则表示MySQL没有找到索引来提升执行效率。

这个索引可能不在possible_keys里。如果所有possible_keys索引都不适合查找行，但是其他有个索引包含了查询语句列举出的所有的列,就会发生这种情况，换句话说，这个索引覆盖了查询列。尽管它不能用来确定要检索哪些行，但扫描索引的效率比扫描数据行要高。

在InnoDB引擎中，即使查询列包含主键，辅助索引也可能会覆盖所选列。因为InnoDB将主键值存储在每个辅助索引中。

我们可以通过在查询语句中使用FORCE INDEX, USE INDEX,  IGNORE INDEX 来强制MySQL使用或忽略possible_keys列中列出的索引。

<span id="explain_key_len">key_len</span>

本列显示了MySQL决定使用的索引的长度。本列的值能够使你确定MySQL实际使用了复合索引的多少部分。如果key列的值为null，那么key_len列的值也为null。

由于索引存储格式，一个允许空值的列，索引长度要比非空列要长。

<span id="explain_ref">ref</span>

本列显示了哪一列或那个常量被用来和key列中的索引做比较的从而从表中筛选行。

如果值为func,表明使用了某些函数的结果。这个函数实际上可能是一个运算符，比如算术运算符 。

<span id="explain_rows">rows</span>
本列表示MySQL认为执行查询语句时必须查找的行数。在InnoDB引擎中，这个数值是个预估值，可能会不精确。

<span id="explain_filtered">filtered</span>
本列表示将被表条件过滤的表行的估计百分比。最大值为100，这意味着没有对行进行筛选。

<span id="explain_extra">Extra</span>

作者：lkd_whh
链接：https://www.jianshu.com/p/19c3072b86dd