explain plan mysql_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 输出的列：列名JSON 名(FORMAT=JSON)含义select_idSELECT 语句的id

无SELETE 语句的类型

table_name表名

partitions分区

access_typejoin 类型

possible_keys可供选择使用的索引

key实际使用的索引

key_length实际使用索引的长度

ref与索引比较的列

rows预估检测行数

filtered依据表条件过滤行占比

无其他信息id

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

如下sql：EXPLAIN SELECT *FROM(SELECT 2 UNION ALL SELECT 3 )t;

执行结果：

1532432243263.png

select_type

SELECT的类型，下表中展示了所有可能的值及其含义。名称JSON 名含义SIMPLE无简单的SELECT(没有使用UNION或者子查询)

PRIMARY无最外层的查询

UNION无在一个UNION中第二或后面的SELECT语句

DEPENDENT UNIONdependent (true)在一个UNION中第二或后面的SELECT语句，并且依赖于外层查询

UNION RESULTunion_resultUNION的结果

SUBQUERY无子查询中的第一个SELECT

DEPENDENT SUBQUERYdependent (true)子查询中的第一个SELECT，并且依赖于外层查询

DERIVED无派生表(Derived table)

MATERIALIZEDmaterialized_from_subquery实例化子查询(Materialized subquery)

UNCACHEABLE SUBQUERYcacheable (false)不能缓存结果的子查询，并且必须为外部查询的每一行重新计算结果

UNCACHEABLE UNIONcacheable (false)在一个UNCACHEABLE SUBQUERY的UNION语句中第二或后面的SELECT语句

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

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

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

table

当前行引用的表名，除此之外也可能是以下几种值：：该行引用了id值为M和N的行的联合结果

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

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

partitions

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

type

本列用于向我们展示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可用于使用 =, <>, >, >=, ,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

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

possible_keys

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

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

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

key

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

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

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

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

key_len

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

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

ref

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

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

rows

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

filtered

本列表示将被表条件过滤的表行的估计百分比。最大值为100，这意味着没有对行进行筛选。

Extra

作者：lkd_whh

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