认真学习MySQL中的explain分析SQL

关联博文：Explain的四种格式与查看优化器重写SQL

对于低性能的SQL语句的定位， 重要也是 有效的方法就是使用执行计划， MySQL提供了explain命令来查看语句的执行计划。

我们知道，不管是哪种数据库，或者是哪种数据库引擎，在对一条SQL语句进行执行的过程中都会做很多相关的优化，对于查询语句，最重要的优化方式就是使用索引。 而执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。

定位了查询慢的SQL之后，我们就可以使用explain或describe工具做针对性的分析查询语句。describe语句和explain是同样效果。

MySQL8.0关于explain官网文档：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

MySQL5.7关于explain官网文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

MySQL Query Optimizer

mysql中有专门负责优化select语句的优化器模块，其主要功能是通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划(它认为最优的数据检索方式)。

当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是Select并转发给MySQL Query Optimizer。

MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。

然后分析Query中的Hint信息(如果有)，看Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行相应的计算分析，然后再得出最后的执行计划。

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，进而分析SQL语句或表结构的性能瓶颈。

MySQL5.6.3以前只能explain select ，MySQL5.6.3以后就可以explain select，update，delete。

在MySQL5.7以前的版本中，想要显示partitions需要使用explain partitions命令。想要显示filtered需要使用explain extended命令。在MySQL5.7版本后，默认explain直接显示partitions 和 filtered中的信息。

注意：查看执行计划并没有真正执行语句，表的数据不会发生改变。

如下所示，我们使用explain分析一条带有子查询的SQL。

explain select * from tb_sys_book tsb  
where tsb.id in(select book_id from tb_user_book tub);

列名	描述
id	在一个大的查询语句中每个select关键字都对应一个唯一的id
select_type	select关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
extra	一些额外的信息

基础表结构，下面我们的查询都基于这两个表进行分析。

CREATE TABLE `s1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) DEFAULT NULL,
  `key2` int DEFAULT NULL,
  `key3` varchar(100) DEFAULT NULL,
  `key_part1` varchar(100) DEFAULT NULL,
  `key_part2` varchar(100) DEFAULT NULL,
  `key_part3` varchar(100) DEFAULT NULL,
  `common_field` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_key2` (`key2`),
  KEY `idx_key1` (`key1`),
  KEY `idx_key3` (`key3`),
  KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3;

CREATE TABLE `s2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) DEFAULT NULL,
  `key2` int DEFAULT NULL,
  `key3` varchar(100) DEFAULT NULL,
  `key_part1` varchar(100) DEFAULT NULL,
  `key_part2` varchar(100) DEFAULT NULL,
  `key_part3` varchar(100) DEFAULT NULL,
  `common_field` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_key2` (`key2`),
  KEY `idx_key1` (`key1`),
  KEY `idx_key3` (`key3`),
  KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3;

① table

无论我们的查询语句有多复杂，里面包含了多少个表，到最后也是需要对每个表进行单表访问的。所以MySQL规定explain语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。

explain select * from s1;

这个查询语句只涉及对s1表的单表查询，所以explain输出中只有一条记录，其中的table列的值是s1，表明这条记录是用来说明对s1表的单表访问方法的。

下边我们看一个连接查询的执行计划：

explain select * from s1 inner join s2;

可以看到这个连接查询的执行计划中有两条记录，这两条记录的table列分别是s1 和 s2，这两条记录用来分别说明s1表和s2表的访问方法是什么。

② id

我们写的查询语句一般都以select关键字开头。比较简单的查询语句里只有一个select关键字，比如下边这个查询语句：

SELECT * FROM s1 WHERE key1 = 'a';

稍微复杂一点的连接查询中也只有一个select关键字，比如：

 SELECT * FROM s1 INNER JOIN s2
 ON s1.key1 = s2.key1
 WHERE s1.common_field = 'a';

但是下边两种情况下，在一条查询语句中会出现多个select关键字：

• 查询中包含子查询的情况

 SELECT * FROM s1 
 WHERE key1 IN (SELECT key3 FROM s2);

• 查询中包含union语句的情况

 SELECT * FROM s1 UNION SELECT * FROM s2;

查询语句中每出现一个select关键字，MySQL就会为它分配一个唯一的id值。这个id值就是explain语句的第一个列，比如下边这个查询中只有一个select关键字，所以explain的结果中也就只有一条id列为1 的记录。

 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

对于连接查询来说，一个select关键字后边的from子句中可以跟随多个表。所以在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的 id 值都是相同的。比如：

 EXPLAIN SELECT * FROM s1 INNER JOIN s2;

可以看到，上述连接查询中参与连接的 s1 和 s2表分别对应一条记录，但是这两条记录对应的 id 值都是1。这里需要大家记住的是，在连接查询的执行计划中，每个表都会对应一条记录，这些记录的 id 列的值是相同的，出现在前边的表表示驱动表，出现在后边的表表示被驱动表。所以从上边的explain输出中我们可以看出，查询优化器准备让 s1 表作为驱动表，让 s2 表作为被驱动表来执行查询。

对于包含子查询的查询语句来首，就可能涉及多个select关键字。所以在包含子查询的查询语句的执行计划中，每个select关键字都会对应一个唯一的id值，比如这样：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

从输出结果中我们可以看到，s1表在外层查询中，外层查询有一个独立的select关键字，所以第一条记录的id值就是1，s2表在子查询中，子查询有一个独立的select关键字，所以第二条记录的id值就是2.

但是之类大家需要特别注意，查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。所以如果我们想知道查询优化器对某个包含子查询的语句是否进行了重写，直接查看执行计划就好了，比如说：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');

可以看到，虽然我们的查询语句是一个子查询，但是执行计划中s1和s2表对应的记录的id值全部都是1，这就表明了查询优化器将子查询转换为了连接查询。

对于包含union子句的查询语句来说，每个select关键字对应一个id值也是没错的，不过还是有点特别的东西，比如：

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

这个语句的执行计划的第三条记录是什么？为何id值是null，而且table列也很奇怪。union会把多个查询的结果集合并起来并对结果集中的记录进行去重，怎么去重呢？MySQL使用的是内部的临时表。正如上边的查询计划中所示，union子句是为了把id为1 的查询和id为2的查询的结果集合并起来并去重，所以在内部创建了一个名为<union,2>的临时表（就是执行计划第三条记录的table列的名称），id为null表明这个临时表是为了合并两个查询的结果集而创建的。

跟union对比起来，union all 就不需要为最终的结果集进行去重，它只是单纯的把多个查询的结果集中的记录合并成一个并返回给用户，所以也就不需要使用临时表。故而在包含union all子句的查询的执行计划中，就没有那个id为null的记录，如下所示：

EXPLAIN SELECT * FROM s1  UNION ALL SELECT * FROM s2;

总结：执行计划包含的信息 id 有一组数字组成，表示一个查询中各个子查询的执行顺序：

• id相同，可以认为是一组，执行顺序由上至下。
• id不同，id值越大优先级越高，越先被执行。
• id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。
• id号每个号码，表示一趟独立的查询，一个SQL的查询趟数越少越好

这一列总是包含一个编号，表示select所属的行。如果在语句当中没有子查询或联合，那么只会有唯一的select，于是每一行在这个列中都将显示一个1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置。

MySQL将select查询分为简单和复杂类型，复杂类型可以分成三大类：简单子查询、所谓的派生表（在from子句中的子查询）以及union查询。

③ select_type

一条大的查询语句里边可以包含若干个select关键字，每个select关键字代表着一个小的查询语句，而每个select关键字的from子句中都可以包含若干张表（这些表用来做连接查询），每一张表都对应着执行计划输出中的一条记录，对于在同一个select关键字中的表来说，它们的id值是相同的。

MySQL为每一个select关键字代表的小查询都定义了一个称之为select_type的属性，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色，我们看一下select_type都能取哪些值。下表来源于MySQL8.0的官网文档：

名称	描述
SIMPLE	Simple SELECT (not using UNION or subqueries)不包含任何子查询 或union等查询
PRIMARY	Outermost SELECT 包含子查询 外层查询就显示为 PRIMARY
UNION	Second or later SELECT statement in a UNION
DEPENDENT UNION	Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT	Result of a UNION. mysql使用临时表来完成union的去重工作， 针对该临时表的查询的select type为union result
SUBQUERY	First SELECT in subquery <br在select或 where字句中包含的查询
DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query
DERIVED	Derived table from字句中包含的查询
DEPENDENT DERIVED	Derived table dependent on another table
MATERIALIZED	Materialized subquery 查询优化器在执行包含子查询的语句时， 选择将子查询物化之后与外层查询进行连接查询时， 该子查询对应的select_type为materialized
UNCACHEABLE SUBQUERY	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

① simple

查询语句中不包含union或者子查询的查询都算作为 simple 类型。

EXPLAIN SELECT * FROM s1;

连接查询也算是SIMPLE类型：

 EXPLAIN SELECT * FROM s1 INNER JOIN s2;

② PRIMARY

对于包含UNION或者UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type值就是PRIMARY。

EXPLAIN SELECT * FROM s1  UNION ALL SELECT * FROM s2;

从结果中可以看到，最左边的小查询 select* from s1 对应的是执行计划中的第一条记录，它的select_type值就是 primary。

③ union

对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的select_type值就是UNION。

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

④ UNION RESULT

MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT。

 EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
 
 EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;

用来从union的匿名临时表检索结果的select被标记为union result。

除了这些值，subquery和union还可以被标记为dependent和uncacheable。

• dependent意味着select依赖于外层查询中发现的数据；
• uncacheable意味着select中的某些特性阻止结果被缓存于一个item_cache中（item_cache与查询缓存不是一回事，尽管它可以被一些相同类型的构件否定，例如rand()函数）。

⑤ SUBQUERY

如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY。

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

⑥ DEPENDENT SUBQUERY

如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是相关子查询，则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY。

 EXPLAIN SELECT * FROM s1 
 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';

需要注意的是，select_type为DEPENDENT SUBQUERY的查询可能会被执行多次。

⑦ DEPENDENT UNION

在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION。

 EXPLAIN SELECT * FROM s1 
 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');

⑧ DERIVED

derived值用来表示包含在from子句的子查询中的select，MySQL会递归执行并将结果放到一个临时表中。服务器内部称其“派生表”，因为该临时表是从子查询中派生来的。
对于包含派生表的查询，该派生表对应的子查询的select_type就是DERIVED 。

 EXPLAIN SELECT * 
 FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1;

从执行计划中可以看出，id为2的记录就代表子查询的执行方式，它的 select_type 是derived，说明该子查询是以物化的方式执行的。id为1的记录代表外层查询，大家注意看它的table列显示的是 <derived2>，表示该查询是针对将派生表物化之后的表进行查询的。

⑨ MATERIALIZED

当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的select_type属性就是MATERIALIZED 。

 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2); 

子查询被转为了物化表。

④ partitions

代表分区表中的命名情况，非分区表，该项为null。表分区、表创建的时候可以指定通过那个列进行表分区。 一般情况下我们的查询语句的执行计划的partitions列的值都是null。

举个例子：

create table tmp (
 id int unsigned not null AUTO_INCREMENT,
 name varchar(255),
 PRIMARY KEY(id))
 engine=innodb 
 partition by key(id) partitions 5;

⑤ type

执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，又称“访问类型”，其中的type列就表明了这个访问方法是啥，是较为重要的一个指标。比如，看到type列的值是ref，表明MySQL即将使用ref访问方法来执行对s1表的查询。

针对单表的访问方法，非常重要，可以看到有没有走索引。访问类型，可以显示查询使用了何种类型。

从最好到最差依次是system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all。

通常我们只需要考虑：system > const > eq_ref > ref > range > index > all。一般来说，得保证查询至少达到range级别，要求是能达到ref级别，最好是consts级别。

null 这种访问方式意味着MySQL能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引。
例如从一个索引列里选取最小值可以通过单独查找索引来完成，不需要在执行时访问。

① system

当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system。

CREATE TABLE t(i INT) ENGINE=MYISAM;
INSERT INTO t VALUES(1);
 
EXPLAIN SELECT * FROM t;

我们尝试换为InnoDB，可以看到type为all。

CREATE TABLE tt(i INT) ENGINE=INNODB;
INSERT INTO tt VALUES(1);

EXPLAIN SELECT * FROM tt;

② const

当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const 。

 EXPLAIN SELECT * FROM s1 WHERE id = 10005;
 
 EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;

当MySQL能对查询的某部分进行优化并将其转换为一个常量时，它就会使用这些访问类型。其通过索引一次就找到了，constr用于比较primary key或unique索引。

③ eq_ref

在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq_ref 。

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

从执行计划的结果中可以看到，MySQL打算将s2作为驱动表，s1作为被驱动表，重点关注s1的访问方法是eq_ref，表明在访问s1表的时候可以通过主键的等值匹配来进行访问。

④ ref

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

 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

这是一种索引访问（有时也叫索引查找），它返回所有匹配某个单个值的行。然而它可能会找到多个符合条件的行，因此，它是查找和扫描的混合体。

⑤ ref_or_null

当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法 就可能是ref_or_null。

ref_or_null是ref之上的一个变体，它意味着MySQL必须在初次查找的结果里进行第二次查找以找出null条目。

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;

⑥ index_merge

一般情况下对于某个表的查询只能使用到一个索引，但是单表访问方法时在某些场景下可以使用Intersection、Union、Sort-Union这三种索引合并的方式来执行查询。

 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

⑦ unique_subquery

unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery。

 EXPLAIN SELECT * FROM s1 
 WHERE key2 IN (SELECT id FROM s2 WHERE s1.key1 = s2.key1) OR key3 = 'a';

可以看到执行计划的第二条记录的type值就是 unique_subquery , 说明在执行子查询时会使用到 id 列的索引。

index_subquery 与 unique_subquery 类似，只不过访问子查询中的表时使用的是普通的索引，比如：

explain select * from s1 where common_field in ( select key3 from s2 where s1.key1=s2.key1) or key3='a';

⑧ range

范围扫描就是一个有限制的索引扫描，它开始于索引里的某一点，返回匹配这个值域的行。这比全索引扫描好一些，因为它用不着遍历全部索引。显而易见的范围扫描是带有between或者在where子句里带有>的查询。

如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法

 EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');

EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';

当MySQL使用索引去查找一系列值时，例如in和 or 列表，也会显示为范围扫描。然而这两者其实是相当不同的访问类型，在性能上有重要的差异。

explain SELECT* from s1 where key1='cWAPuH' or  key1='ccencx'

⑨ index

当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index。这个跟全表扫描一样，只是MySQL扫描表时按索引次序进行而不是逐行遍历`。它的主要优点是避免了排序，最大的缺点是要承担按索引次序读取整个表的开销。

EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

上述查询中的搜索列表中只有key_part2一个列，而且搜索条件中也只有key_part3一个列，这两个列又恰好包含在idx_key_part这个索引中，可是搜索条件key_part3不能直接使用该索引进行ref或者range方式的访问，只能扫描整个idx_key_part索引的记录，所以查询几乎的type列的值就是index。

实际上按照联合索引的规则来说，这里是用不到索引的(因为key_part1不在)。之所以下图中key列显示使用了索引在于MySQL认为查询字段和where中条件字段都是联合索引的一部分，那么索性使用联合索引进行查找，从联合索引文件上读取需要的数据。

如果在Extra列中看到“Using index”，说明MySQL正在使用覆盖索引，它只扫描索引的数据而不是按索引次序的每一完整行记录。它比按索引次序全表扫描的开销要少很多。

再一次强调，对于使用InnoDB存储引擎的表来说，二级索引的记录只包含索引列和主键列的值，而聚簇索引中包含用户定义的全部列以及一些隐藏列，所以扫描二级索引的代价比直接全表扫描，也就是扫描聚簇索引的代价更低一些。

⑩ all

这就是人们所称的全表扫描，通常意味着MySQL必须扫描整张表，从头到尾，去找到需要的行。（这里也有个例外，例如在查询里使用了limit或者在extra列中显示“using distinct/not exists”。）

 EXPLAIN SELECT * FROM s1;

index与all的区别在于index只遍历索引树，这通常比all快，因为索引文件通常比数据文件小。

一般来说，这些访问方法中除了all这个访问方法外，其余的访问方法都能用到索引，除了 index_merge 访问方法外，其余的访问方法都最多只能用到一个索引。

⑥ possible_keys 和key

在explain语句输出的执行计划中，possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。key列表示实际用到的索引有哪些，如果为null，则没有使用索引。

possible_keys

可能使用到的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。

key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';

上述执行计划的possible_keys列的值是 idx_key1,idx_key3，表示该查询可能使用到idx_key1,idx_key3两个索引，然后key列的值是idx_key3，表示经过查询优化器计算使用不同索引的成本后，最后决定使用idx_key3来执行查询比较划算。成本越低越好，这里成本越低并不意味着时间越短。

⑦ key_len

该列显示了MySQL在索引里使用的字节数。如果MySQL正在使用的只是索引里的某些列，那么就可以用这个值来算出具体是哪些列。

key_len显示的值为索引字段的最大可能长度，并非实际使用长度。即ken_len是根据表定义计算而得，不是通过表内检索出的。

# key_len = 5
EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;
# key_len=303
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
# key_len=303
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';
# key_len=606
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';
# key_len=909
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
# 没有用到索引
EXPLAIN SELECT * FROM s1 WHERE key_part3 = 'a';

索引长度字节数计算实例

varchar(10)变长字段且允许NULL  = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL    = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)

char(10)固定字段且不允许NULL  = 10 * ( character set：utf8=3,gbk=2,latin1=1)

如果字符集是utf8mb4，那么上面的公式中变成： character set：utf8mb4=4，utf8=3,gbk=2,latin1=1

注意索引长度是有限制的，否则可能会遇到诸如 Specified key was too long; max key length is 1000 bytes; 或者 Specified key was too long; max key length is 767 bytes错误。当然，这和MySQL版本有关系，不过还是建议索引长度尽量小一点。

⑧ ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之一时，ref列展示的就是与索引列作等值匹配的结构是什么，比如只是一个常数或者是某个列。

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

可以看到ref列的值是const，表明在使用idx_key1索引执行查询时，与key1列作等值匹配的对象是一个常数，当然有时候更复杂一点。

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

⑨ rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。也就是预估的需要读取的记录条数，该值越小越好。

这个数字是内嵌循环关联计划里的循环数目。也就是说它不是MySQL认为它最终要从表里读取出来的行数，而是MySQL为了找到符合查询的每一点上标准的那些行而必须读取的平均行数。（这个标准包括SQL里给定的条件，以及来自连接次序上前一个表的当前列。）

 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';

⑩ filtered

其表示的是针对表里符合某个条件(where子句或连接条件)的记录数与rows的估算百分比，将rows列与百分比相乘，就能看到MySQL估算它将和查询计划里前一个表关联的行数。

如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';

对于单表查询来说，这个filtered列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的filtered值，它决定了被驱动表要执行的次数(即：rows * filtered)

 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';

从执行计划中可以看出来，查询优化器打算把S1当作驱动表，s2当作被驱动表。我们可以看到驱动表s1表的执行计划的rows列为9895，filtered列为10，这意味着驱动表s1的扇出值就是9895*10%=989.5，这说明还要对被驱动表执行大约989次查询。

(11) Extra

顾名思义，Extra列是用来说明一些额外信息的，包含不适合在其他列显示但十分重要的额外信息，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。

No tables used

当查询语句的没有FROM子句时将会提示该额外信息：No tables used

EXPLAIN SELECT 1;

Impossible WHERE

查询语句的WHERE子句永远为FALSE时将会提示该额外信息：Impossible WHERE

 EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

Using where

当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件时，在Extra列中会提示上述额外信息。

 EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';

当使用索引访问来执行对某个表的查询，并且该语句的WHERE子句中有除了该索引包含的列之外的其他搜索条件时，在Extra列中也会提示上述额外信息。比如下面这个查询虽然使用idx_key1索引执行查询，但是搜索条件中除了包含key1的搜索条件key1=‘a’，还包含common_field的搜索条件，所以Extra列会显示Using where的提示：

 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';

No matching min/max row

当查询列表处有MIN或者MAX聚合函数，但是并没有符合WHERE子句中的搜索条件的记录时，将会提示该额外信息。

EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';

EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'cWAPuH'; #cWAPuH是 s1表中key1字段真实存在的数据

Using index

当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用覆盖索引的情况下，在Extra列将会提示该额外信息。比方说下边这个查询中只需要用到idx_key1而不需要回表操作：

 EXPLAIN SELECT key1,id FROM s1 WHERE key1 = 'a';

Using index condition

有些搜索条件中虽然出现了索引列，但却不能使用到索引。

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

其中的key1>'z'可以使用索引，但是key1 like '%a'却无法使用到索引，在以前版本的MySQL中，是按照下边步骤来执行这个查询的：

• 先根据key1>'z' 这个条件，从二级索引idx_key1中获取到对应的二级索引记录。
• 根据上一步骤得到的二级索引记录中的主键值进行回表，找到完整的用户记录再检测该记录是否符合 key1 like '%a'这个条件，将符合条件的记录加入到最后的结果集。

但是虽然 key1 like '%a' 不能组成范围区间参与 range 访问方法的执行，但这个条件毕竟只涉及到了 key1 列，所以MySQL把上边的步骤改进了一下：

• 先根据key1>'z' 这个条件，从二级索引idx_key1中获取到对应的二级索引记录。
• 对于制定的二级索引记录，先不着急回表，而是先检测一下该记录是否满足 key1 like '%a'这个条件，如果这个条件不满足，则该二级索引记录压根就没必要回表。
• 对于足 key1 like '%a'这个条件的二级索引记录执行回表操作。

我们说回表操作其实是一个随机IO，比较耗时，所以上述修改虽然只改进了一点点，但是可以省去好多回表操作的成本。MySQL把他们的这个改进称之为 索引条件下推（英文名:Index Condition Pushdown）。

如果在查询语句的执行过程中将要使用索引条件下推这个特性，在Extra列中将会显示 Using index condition，比如这样：

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

Using where; Using join buffer (hash join)

在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法 。

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field

可以在对s2表的执行计划的Extra列显示了两个提示：

• Using join buffer(hash join):这是因为对表s2的访问不能有效利用索引，只好退而求其次，使用join buffer来减少对表s2的访问次数，从而提高性能。
• Using where：可以看到查询语句中有一个s1.common_field = s2.common_field条件，因为s1是驱动表，s2是被驱动表，所以在访问s2表时，s1.common_field的值已经确定下来了，所以实际上 查询s2表的条件就是s2.common_field=一个常数，所以提示了Using where额外信息。

Using where; Not exists

当我们使用左（外）连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列又是不允许存储NULL值的，那么在该表的执行计划的Extra列就会提示Not exists额外信息 。

EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;

上述查询中s1表是驱动表，S2表是被驱动表，s2.id是不允许存储null值的，而where子句中又包含s2.id is null的搜索条件，这意味着必定是驱动表的记录在被驱动表中找不到匹配on子句条件的记录才会把该驱动表的记录加入到最终的结果集。所以对于某条驱动表中的记录来说，如果能在被驱动表中找到1条符合on子句条件的记录，那么该驱动表的记录就不会被加入到最终的结果集。也就是说我们没有必要到被驱动表中找到全部符合on子句条件的记录，这样可以稍微节省一点性能。

Using union(idx_key1,idx_key3); Using where

如果执行计划的Extra列出现了Using intersect(...)提示，说明准备使用Intersect索引合并的方式执行查询，括号中的...表示需要进行索引合并的索引名称。

如果出现了Using union(...)提示，说明准备使用Union索引合并的方式执行查询。

出现了Using sort_union(...)提示，说明准备使用Sort-Union索引合并的方式执行查询。

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

其中Extra列就显示了Using union(idx_key1,idx_key3)，表明MySQL即将使用idx_key3和idx_key1这两个索引进行Union索引合并的方式执行查询。

Zero limit

当我们的LIMIT子句的参数为0时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息。

EXPLAIN SELECT * FROM s1 LIMIT 0;

Using filesort

有一些情况下对结果集中的记录进行排序是可以使用到索引的。

EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;

这个查询语句可以利用idx_key1索引直接取出key1列的1条记录，然后再进行回表操作就好了。但是很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort）。

如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort提示。

 EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

需要注意的是，如果查询中需要使用filesort的方式进行排序的记录非常多，那么这个过程是很耗费性能的，我们最好想办法将使用文件排序的执行方式改为使用索引进行排序。

MySQL有两种文件排序算法(单路和双路)，两种方式都可以在内层或磁盘上完成。explain不会告诉你MySQL将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。

Using temporary

在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含DISTINCT、GROUP BY、UNION等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示Using temporary提示。这种情况比fileSort更严重，需要优化。

EXPLAIN SELECT DISTINCT common_field FROM s1;

EXPLAIN SELECT DISTINCT key1 FROM s1;

EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;

执行计划中出现Using temporary并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以我们最好能使用索引来替代掉使用临时表。比如：扫描指定的索引idx_key1即可

EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;

下面我们看一个更糟糕的例子，同时出现了Using temporary; Using filesort。

explain select * from user where userName ='admin' 
group by phone  
order by  userDate ,phone 

Extra一列信息如下：

# 这种情况极其糟糕，临时表、文件排序
Using index condition; Using temporary; Using filesort

json格式的explain

EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 
WHERE s1.common_field = 'a';

下面给出几个实例来说明，如下所示我们创建表并为其创建组合索引(c1,c2,c3)。

CREATE TABLE `testc` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `c1` varchar(100) DEFAULT NULL,
  `c2` varchar(100) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` varchar(100) DEFAULT NULL,
  `c5` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `testc_c1_IDX` (`c1`,`c2`,`c3`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Using where; Using index

如下创建联合索引(c1,c2,c3)，select查询的数据在索引上。

explain select c1 from agriculture.testc  where c1='a1' order by c2,c3

如果同时出现using where，表明索引被用来执行索引键值的查找。如果没有同时出现using where ，表明索引用来读取数据而非执行查找动作。

如果将select c1 修改为select *，那么与前者执行计划不同的是这里Extra中是Using index condition。

explain select * from agriculture.testc  where c1='a1' order by c2,c3

select tables optimized away

在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

CREATE TABLE t(i INT) ENGINE=MYISAM;
INSERT INTO t VALUES(1);
 
EXPLAIN SELECT * FROM t;

explain select COUNT(*) from t

最后需要说明的是：

• EXPLAIN不考虑各种Cache
• Explain不能显示MySQL在执行查询时所做的优化工作
• Explain不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• 部分统计信息是估算的，并非精确值