关于mysql:最完整的Explain总结SQL优化不再困难

集体公众号：月伴飞鱼，欢送关注

先看看具体有哪些字段：

mysql> EXPLAIN SELECT 1;

其实除了以SELECT结尾的查问语句，其余的DELETE、INSERT、REPLACE以及UPDATE语句前边都能够加上EXPLAIN这个词儿，用来查看这些语句的执行打算

建两张测试表：

CREATE TABLE t1 (    id INT NOT NULL AUTO_INCREMENT,    key1 VARCHAR(100),    key2 VARCHAR(100),    key3 VARCHAR(100),    name VARCHAR(100),    PRIMARY KEY (id),    KEY idx_key1 (key1),    KEY idx_key2_key3(key2, key3)) Engine=InnoDB CHARSET=utf8;CREATE TABLE t2 (    id INT NOT NULL AUTO_INCREMENT,    key1 VARCHAR(100),    key2 VARCHAR(100),    key3 VARCHAR(100),    name VARCHAR(100),    PRIMARY KEY (id),    KEY idx_key1 (key1),    KEY idx_key2_key3(key2, key3)) Engine=InnoDB CHARSET=utf8;

两个变种

explain extended

会在 explain 的根底上额定提供一些查问优化的信息。紧随其后通过 show warnings 命令能够失去优化后的查问语句，从而看出优化器优化了什么

explain extended SELECT * FROM t1 where key1 = '11';show warnings;

explain partitions

相比 explain 多了个 partitions 字段，如果查问是基于分区表的话，会显示查问将拜访的分区。

EXPLAIN PARTITIONS SELECT * FROM t1 INNER JOIN t2 ON t1.key3 = t2.key3;

table列

这一列示意 explain 的一行正在拜访哪个表

mysql> EXPLAIN SELECT * FROM t1;

这个查问语句只波及对t1表的单表查问，所以EXPLAIN输入中只有一条记录，其中的table列的值是t1，表明这条记录是用来阐明对t1表的单表拜访。

mysql> EXPLAIN SELECT * FROM t1 INNER JOIN t2;

能够看到这个连贯查问的执行打算中有两条记录，这两条记录的table列别离是t1和t2，这两条记录用来别离阐明对t1表和t2表的拜访

留神：

当 from 子句中有子查问时，table列是 <derivenN> 格局，示意以后查问依赖 id=N 的查问，于是先执行 id=N 的查问。
当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2示意参加 union 的 select 行id。

id列

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的程序是按 select 呈现的程序增长的。

id列越大执行优先级越高，id雷同则从上往下执行，id为NULL最初执行

比方下边这个查问中只有一个SELECT关键字，所以EXPLAIN的后果中也就只有一条id列为1的记录：

mysql> EXPLAIN SELECT * FROM t1 WHERE key1 = 'e038f672a8';

对于连贯查问来说，一个SELECT关键字后边的FROM子句中能够追随多个表，所以在连贯查问的执行打算中，每个表都会对应一条记录，然而这些记录的id值都是雷同的，比方：

mysql> EXPLAIN SELECT * FROM t1 INNER JOIN t2;

能够看到，上述连贯查问中参加连贯的t1和t2表别离对应一条记录，然而这两条记录对应的id值都是1。

留神：

在连贯查问的执行打算中，每个表都会对应一条记录，这些记录的id列的值是雷同的，呈现在前边的表示意驱动表，呈现在后边的表示意被驱动表。所以从上边的EXPLAIN输入中咱们能够看出，查问优化器筹备让t2表作为驱动表，让t1表作为被驱动表来执行查问

对于蕴含子查问的查问语句来说，就可能波及多个SELECT关键字，所以在蕴含子查问的查问语句的执行打算中，每个SELECT关键字都会对应一个惟一的id值，比方这样：

mysql> EXPLAIN SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2) OR key3 = 'a1b6cee57a';

从输入后果中咱们能够看到，t1表在外层查问中，外层查问有一个独立的SELECT关键字，所以第一条记录的id值就是1，t2表在子查问中，子查问有一个独立的SELECT关键字，所以第二条记录的id值就是2。

然而这里大家须要特地留神，查问优化器可能对波及子查问的查问语句进行重写，从而转换为连贯查问。所以如果咱们想晓得查问优化器对某个蕴含子查问的语句是否进行了重写，间接查看执行打算就好了，比如说：

mysql> EXPLAIN SELECT * FROM t1 WHERE key1 IN (SELECT key3 FROM t2 WHERE t1.key1 = 'a1b6cee57a');

能够看到，尽管咱们的查问语句是一个子查问，然而执行打算中t1和t2表对应的记录的id值全副是1，这就表明了查问优化器将子查问转换为了连贯查问。

对于蕴含UNION子句的查问语句来说，每个SELECT关键字对应一个id值也是没错的，不过还是有点儿特地的货色，比方说下边这个查问：

mysql> EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2;

UNION子句是为了把id为1的查问和id为2的查问的后果汇合并起来并去重，所以在外部创立了一个名为<union1, 2>的长期表（就是执行打算第三条记录的table列的名称），id为NULL表明这个长期表是为了合并两个查问的后果集而创立的。

跟UNION比照起来，UNION ALL就不须要为最终的后果集进行去重，它只是单纯的把多个查问的后果集中的记录合并成一个并返回给用户，所以也就不须要应用长期表。所以在蕴含UNION ALL子句的查问的执行打算中，就没有那个id为NULL的记录，如下所示：

mysql> EXPLAIN SELECT * FROM t1 UNION ALL SELECT * FROM t2;

select_type列

MySQL每一个SELECT关键字代表的小查问都定义了一个称之为select_type的属性，意思是咱们只有晓得了某个小查问的select_type属性，就晓得了这个小查问在整个大查问中表演了一个什么角色

上面是官网文档介绍：

https://dev.mysql.com/doc/ref...

SIMPLE

查问语句中不蕴含UNION或者子查问的查问都算作是SIMPLE类型，比方说下边这个单表查问的select_type的值就是SIMPLE：

mysql> EXPLAIN SELECT * FROM t1;

PRIMARY

对于蕴含UNION、UNION ALL或者子查问的大查问来说，它是由几个小查问组成的，其中最右边的那个查问的select_type值就是PRIMARY，比方说：

mysql> EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2;

从后果中能够看到，最右边的小查问SELECT * FROM t1对应的是执行打算中的第一条记录，它的select_type值就是PRIMARY。

UNION

对于蕴含UNION或者UNION ALL的大查问来说，它是由几个小查问组成的，其中除了最右边的那个小查问以外，其余的小查问的select_type值就是UNION，能够比照上一个例子的成果

UNION RESULT

MySQL抉择应用长期表来实现UNION查问的去重工作，针对该长期表的查问的select_type就是UNION RESULT，同样比照下面的例子

SUBQUERY

如果蕴含子查问的查问语句不可能转为对应的semi-join的模式，并且该子查问是不相干子查问，并且查问优化器决定采纳将该子查问物化的计划来执行该子查问时，该子查问的第一个SELECT关键字代表的那个查问的select_type就是SUBQUERY，比方下边这个查问：

概念解释：

semi-join子查问，是指当一张表在另一张表找到匹配的记录之后，半连贯（semi-jion）返回第一张表中的记录。与条件连贯相同，即便在右节点中找到几条匹配的记录，左节点的表也只会返回一条记录。另外，右节点的表一条记录也不会返回。半连贯通常应用IN 或 EXISTS 作为连贯条件
物化：这个将子查问后果集中的记录保留到长期表的过程称之为物化（Materialize）。那个存储子查问后果集的长期表称之为物化表。正因为物化表中的记录都建设了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执行IN语句判断某个操作数在不在子查问后果集中变得十分快，从而晋升了子查问语句的性能。

mysql> EXPLAIN SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2) OR key3 = 'a1b6cee57a';

能够看到，外层查问的select_type就是PRIMARY，子查问的select_type就是SUBQUERY。

DEPENDENT SUBQUERY

如果蕴含子查问的查问语句不可能转为对应的semi-join的模式，并且该子查问是相干子查问，则该子查问的第一个SELECT关键字代表的那个查问的select_type就是DEPENDENT SUBQUERY，比方下边这个查问：

mysql> EXPLAIN SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2 WHERE t1.key2 = t2.key2) OR key3 = 'a1b6cee57a';

DEPENDENT UNION

在蕴含UNION或者UNION ALL的大查问中，如果各个小查问都依赖于外层查问的话，那除了最右边的那个小查问之外，其余的小查问的select_type的值就是DEPENDENT UNION。比方说下边这个查问：

mysql> EXPLAIN SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2 WHERE key1 = 'a1b6cee57a' UNION SELECT key1 FROM t1 WHERE key1 = 'a1b6cee57a');

这个查问比较复杂啊，大查问里蕴含了一个子查问，子查问里又是由UNION连起来的两个小查问。从执行打算中能够看进去，SELECT key1 FROM t2 WHERE key1 = 'a1b6cee57a'这个小查问因为是子查问中第一个查问，所以它的select_type是DEPENDENT SUBQUERY，而SELECT key1 FROM t1 WHERE key1 = 'a1b6cee57a'这个查问的select_type就是DEPENDENT UNION。

DERIVED

对于采纳物化的形式执行的蕴含派生表的查问，该派生表对应的子查问的select_type就是DERIVED，比方说下边这个查问：

mysql> EXPLAIN SELECT * FROM (SELECT key1, count(*) as t FROM t1 GROUP BY key1) AS derived_t1 where t > 1;

从执行打算中能够看出，id为2的记录就代表子查问的执行形式，它的select_type是DERIVED，阐明该子查问是以物化的形式执行的。id为1的记录代表外层查问，大家留神看它的table列显示的是<derived2>，示意该查问是针对将派生表物化之后的表进行查问的。

MATERIALIZED

当查问优化器在执行蕴含子查问的语句时，抉择将子查问物化之后与外层查问进行连贯查问时，该子查问对应的select_type属性就是MATERIALIZED，比方下边这个查问：

mysql> EXPLAIN SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2);

执行打算的第三条记录的id值为2，阐明该条记录对应的是一个单表查问，从它的select_type值为MATERIALIZED能够看出，查问优化器是要把子查问先转换成物化表。而后看执行打算的前两条记录的id值都为1，阐明这两条记录对应的表进行连贯查问，须要留神的是第二条记录的table列的值是<subquery2>，阐明该表其实就是id为2对应的子查问执行之后产生的物化表，而后将s1和该物化表进行连贯查问。

type列

这一列示意关联类型或拜访类型，即MySQL决定如何查找表中的行，查找数据行记录的大略范畴。
顺次从最优到最差别离为：system > const > eq_ref > ref > range > index > ALL
一般来说，得保障查问达到range级别，最好达到ref

NULL

mysql可能在优化阶段合成查问语句，在执行阶段用不着再拜访表或索引。例如：在索引列中选取最小值，能够独自查找索引来实现，不须要在执行时拜访表

mysql> explain select min(id) from t1;

eq_ref

primary key 或 unique key 索引的所有局部被连贯应用，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简略的 select 查问不会呈现这种 type。

在连贯查问时，如果被驱动表是通过主键或者惟一二级索引列等值匹配的形式进行拜访的（如果该主键或者惟一二级索引是联结索引的话，所有的索引列都必须进行等值比拟），则对该被驱动表的拜访办法就是eq_ref，比方说：

mysql> EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;

从执行打算的后果中能够看出，MySQL打算将t2作为驱动表，t1作为被驱动表，重点关注t1的拜访办法是eq_ref，表明在拜访t1表的时候能够通过主键的等值匹配来进行拜访。

ref

当通过一般的二级索引列与常量进行等值匹配时来查问某个表，那么对该表的拜访办法就可能是ref

相比 eq_ref，不应用惟一索引，而是应用一般索引或者唯一性索引的局部前缀，索引要和某个值相比拟，可能会找到多个符合条件的行。

mysql> EXPLAIN SELECT * FROM t1 WHERE key1 = 'a';

能够看到type列的值是ref，表明MySQL行将应用ref拜访办法来执行对t1表的查问

system，const

mysql能对查问的某局部进行优化并将其转化成一个常量（能够看show warnings 的后果）。用于 primary key 或 unique key 的所有列与常数比拟时，所以表最多有一个匹配行，读取1次，速度比拟快。system是const的特例，表里只有一条元组匹配时为system

mysql> EXPLAIN SELECT * FROM t1 WHERE id = 5;

ref_or_null

当对一般二级索引进行等值匹配查问，该索引列的值也能够是NULL值时，那么对该表的拜访办法就可能是ref_or_null，比如说：

mysql> EXPLAIN SELECT * FROM t1 WHERE key1 = 'a' OR key1 IS NULL;

index_merge

个别状况下对于某个表的查问只能应用到一个索引，但在某些场景下能够应用多种索引合并的形式来执行查问，咱们看一下执行打算中是怎么体现MySQL应用索引合并的形式来对某个表执行查问的：

mysql> EXPLAIN SELECT * FROM t1 WHERE key1 = 'a' OR key2 = 'a';

从执行打算的type列的值是index_merge就能够看出，MySQL打算应用索引合并的形式来执行对t1表的查问。

unique_subquery

相似于两表连贯中被驱动表的eq_ref拜访办法，unique_subquery是针对在一些蕴含IN子查问的查问语句中，如果查问优化器决定将IN子查问转换为EXISTS子查问，而且子查问能够应用到主键进行等值匹配的话，那么该子查问执行打算的type列的值就是unique_subquery，比方下边的这个查问语句：

mysql> EXPLAIN SELECT * FROM t1 WHERE key2 IN (SELECT id FROM t2 where t1.key1 = t2.key1) OR key3 = 'a';

能够看到执行打算的第二条记录的type值就是unique_subquery，阐明在执行子查问时会应用到id列的索引。

range

范畴扫描通常呈现在 in(), between ,> ,<, >= 等操作中。应用一个索引来检索给定范畴的行。

mysql> EXPLAIN SELECT * FROM t1 WHERE key1 IN ('a', 'b', 'c');

index

当咱们能够应用索引笼罩，但须要扫描全副的索引记录时，该表的拜访办法就是index

扫描全表索引，这通常比ALL快一些。（index是从索引中读取的，而all是从硬盘中读取）

ALL

最相熟的全表扫描

mysql> explain select * from t2;

一般来说，这些拜访办法依照咱们介绍它们的程序性能顺次变差。其中除了All这个拜访办法外，其余的拜访办法都能用到索引，除了index_merge拜访办法外，其余的拜访办法都最多只能用到一个索引。

possible_keys和key列

possible_keys列显示查问可能应用哪些索引来查找。

explain 时可能呈现 possible_keys 有列，而 key 显示 NULL 的状况，这种状况是因为表中数据不多，mysql认为索引对此查问帮忙不大，抉择了全表查问。

如果possible_keys列是NULL，则没有相干的索引。在这种状况下，能够通过查看 where 子句看是否能够发明一个适当的索引来进步查问性能，而后用 explain 查看成果。

key列显示mysql理论采纳哪个索引来优化对该表的拜访。如果没有应用索引，则该列是 NULL。如果想强制mysql应用或漠视possible_keys列中的索引，在查问中应用 force index、ignore index。

比方说下边这个查问：

mysql> EXPLAIN SELECT * FROM t1 WHERE key1 > 'z' AND key2 = 'a';

上述执行打算的possible_keys列的值是idx_key1,idx_key2_key3，示意该查问可能应用到idx_key1,idx_key2_key3两个索引，而后key列的值是idx_key3，示意通过查问优化器计算应用不同索引的老本后，最初决定应用idx_key3来执行查问比拟划算。

须要留神的一点是，possible_keys列中的值并不是越多越好，可能应用的索引越多，查问优化器计算查问老本时就得破费更长时间，所以如果能够的话，尽量删除那些用不到的索引。

key_len列

这一列显示了mysql在索引里应用的字节数，通过这个值能够算出具体应用了索引中的哪些列

对于应用固定长度类型的索引列来说，它理论占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，比方某个索引列的类型是VARCHAR(100)，应用的字符集是utf8，那么该列理论占用的最大存储空间就是100 × 3 = 300个字节。

如果该索引列能够存储NULL值，则key_len比不能够存储NULL值时多1个字节。

对于变长字段来说，都会有2个字节的空间来存储该变长列的理论长度。

当字符串过长时，mysql会做一个相似左前缀索引的解决，将前半部分的字符提取进去做索引。

key_len计算规定如下：
字符串
char(n)：n字节长度
varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2
数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节　　
工夫类型　
date：3字节
timestamp：4字节
datetime：8字节

比方下边这个查问：

mysql> EXPLAIN SELECT * FROM s1 WHERE id = 5;

因为id列的类型是INT，并且不能够存储NULL值，所以在应用该列的索引时key_len大小就是4。

对于可变长度的索引列来说，比方下边这个查问：

mysql> EXPLAIN SELECT * FROM t1 WHERE key1 = 'a';

因为key1列的类型是VARCHAR(100)，所以该列理论最多占用的存储空间就是300字节，又因为该列容许存储NULL值，所以key_len须要加1，又因为该列是可变长度列，所以key_len须要加2，所以最初ken_len的值就是303。

rows列

这一列是mysql预计要读取并检测的行数，留神这个不是后果集里的行数。

如果查问优化器决定应用全表扫描的形式对某个表执行查问时，执行打算的rows列就代表预计须要扫描的行数，如果应用索引来执行查问时，执行打算的rows列就代表预计扫描的索引记录行数。比方下边这个查问：

mysql> EXPLAIN SELECT * FROM t1 WHERE key1 > 'a';

咱们看到执行打算的rows列的值是113，这意味着查问优化器在通过剖析应用idx_key1进行查问的老本之后，感觉满足key1 > 'a'这个条件的记录只有113条。

ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：t1.id）

ref列展现的就是与索引列作等值匹配的值什么，比方只是一个常数或者是某个列。大家看下边这个查问：

mysql> EXPLAIN SELECT * FROM t1 WHERE key1 = 'a';

能够看到ref列的值是const，表明在应用idx_key1索引执行查问时，与key1列作等值匹配的对象是一个常数，当然有时候更简单一点：

mysql> EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;

能够看到对被驱动表t1的拜访办法是eq_ref，而对应的ref列的值是canal_manager.t2.id，这阐明在对被驱动表进行拜访时会用到PRIMARY索引，也就是聚簇索引与一个列进行等值匹配的条件，于t2表的id作等值匹配的对象就是canal_manager.t2.id列（留神这里把数据库名也写进去了）。

有的时候与索引列进行等值匹配的对象是一个函数，比方说下边这个查问

mysql> EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t2.key1 = UPPER(t1.key1);

咱们看执行打算的第二条记录，能够看到对t2表采纳ref拜访办法执行查问，而后在查问打算的ref列里输入的是func，阐明与t2表的key1列进行等值匹配的对象是一个函数。

Extra列

顾名思义，Extra列是用来阐明一些额定信息的，咱们能够通过这些额定信息来更精确的了解MySQL到底将如何执行给定的查问语句。

Using index

查问的列被索引笼罩，并且where筛选条件是索引的前导列，是性能高的体现。个别是应用了笼罩索引(索引蕴含了所有查问的字段)。对于innodb来说，如果是辅助索引性能会有不少进步

mysql> EXPLAIN SELECT key1 FROM t1 WHERE key1 = 'a';

Using where

当咱们应用全表扫描来执行对某个表的查问，并且该语句的WHERE子句中有针对该表的搜寻条件时，在Extra列中会提醒上述额定信息。比方下边这个查问

mysql> EXPLAIN SELECT * FROM t1 WHERE name= 'a1b6cee57a';

Using where Using index

查问的列被索引笼罩，并且where筛选条件是索引列之一然而不是索引的前导列，意味着无奈间接通过索引查找来查问到符合条件的数据

mysql> EXPLAIN SELECT id FROM t1 WHERE key3= 'a1b6cee57a';

NULL

查问的列未被索引笼罩，并且where筛选条件是索引的前导列，意味着用到了索引，然而局部字段未被索引笼罩，必须通过“回表”来实现，不是纯正地用到了索引，也不是齐全没用到索引

mysql> EXPLAIN SELECT * FROM t1 WHERE key2= 'a1b6cee57a';

Using index condition

与Using where相似，查问的列不齐全被索引笼罩，where条件中是一个前导列的范畴；

mysql>  EXPLAIN SELECT * FROM t1 WHERE key1 like '1';

Using temporary

在许多查问的执行过程中，MySQL可能会借助长期表来实现一些性能，比方去重、排序之类的，比方咱们在执行许多蕴含DISTINCT、GROUP BY、UNION等子句的查问过程中，如果不能无效利用索引来实现查问，MySQL很有可能寻求通过建设外部的长期表来执行查问。如果查问中应用到了外部的长期表，在执行打算的Extra列将会显示Using temporary提醒，比方说这样：

name没有索引，此时创立了张长期表来distinct

mysql> explain select distinct name from t1;

key1建设了idx_key1索引，此时查问时extra是using index,没有用长期表

mysql> explain select distinct key1 from t1;

Using filesort

mysql 会对后果应用一个内部索引排序，而不是按索引秩序从表里读取行。此时mysql会依据联接类型浏览所有符合条件的记录，并保留排序关键字和行指针，而后排序关键字并按程序检索行信息。这种状况下个别也是要思考应用索引来优化的。

name未创立索引，会浏览t1整个表，保留排序关键字name和对应的id，而后排序name并检索行记录

mysql> explain select * from t1 order by name;

key1建设了idx_key1索引,此时查问时extra是using index

mysql> explain select * from t1 order by key1;

Using join buffer (Block Nested Loop)

在连贯查问执行过程中，当被驱动表不能无效的利用索引放慢访问速度，MySQL个别会为其调配一块名叫join buffer的内存块来放慢查问速度，也就是咱们所讲的基于块的嵌套循环算法，比方下边这个查问语句：

mysql> EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.key3 = t2.key3;

No tables used

当查问语句的没有FROM子句时将会提醒该额定信息，比方：

mysql> EXPLAIN SELECT 1;

Impossible WHERE

查问语句的WHERE子句永远为FALSE时将会提醒该额定信息，比方说：

mysql> EXPLAIN SELECT * FROM t1 WHERE 1 != 1;

参考：

https://dev.mysql.com/doc/ref...