学习 MySQL中的 explain 分析 SQL

对于低性能的SQL语句的定位，重要也是有效的方法就是使用执行计划，MySQL提供了 explain 命令来查看语句的执行计划。
我们知道，不管是哪种数据库，或者是哪种数据引擎，在对一条SQL语句进行执行的过程中都会做到很多相关的优化，对于查询语句，最重要的优化方式就是使用索引。而执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等、

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

MySQL Query Optimizer

MySQL 中有专门负责优化 的 select 语句的优化器模块，其主要功能是通过计算分析系统中收集到的统计信息，为客户端请求的 Query 提供它认为最优的执行计划（它认为最优的数据检索方式）。
当客户端想 MySQL 请求一条 Query，命令解析器模块完成请求分类，区别出是 Select 并转发给 MySQL Query Optimizer。
MySQLQuery Optimizer 首先会对整条 Query进行优化，处理掉一些常量表达式的运算，直接换成常量值。并对 Query 中查询条件进行简化和转换，如去掉一些无用或显而易见的条件，结果调整等。
然后分析 Query 中的 Hint信息（如果有），看 Hint 信息是否可以完全确定该 Query 的执行计划。如果没有 Hint 或 Hint 信息不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据 Query 进行相应的计算分析，然后再得出最后的执行计划。
使用 explain 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理的你的 SQL 语句的，进而分析 SQL 语句或表结构的性能瓶颈。
MySQL 5.6.3 以前只能 explain select ，MySQL 5.6.3以后就可以 explain select, update, delete。
在 MySQL5.7 以前的版本中，想要显示 partitions 需要使用 explain partitions 命令。想要显示 filtered 需要使用 explain extended 命令。在 MySQL 5.7 版本后，默认 explain 直接显示 partitions 和 filtered 中的信息。
注意看：查看执行计划并没有真正执行语句，表的数据不会发生改变
如下所示，我们使用 explain 分析一条带有子查询的 SQL

explain select * from tb_sys_book tsb where tsb.id in (select book_id from tb_user_bool tub);
0f38857d9f134f7984c97f3579e10bd2.png
列名 描述
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 CAHRSET=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` (`key2`),
  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;
511ba0e4ab57420593d828e46698572d.png

这个查询语句只涉及对 s1 表的单表查询，所以 explain 输出中只有一条记录，其中的 table 列的值是 s1，表明这条记录是用来说明对 s1 表的单表访问方法的。
下面我们看看一个连接查询的执行计划

explain select * from s1 inner join s2;
d14fd5b46f074d3aa5369b2875b67976.png

可以看到这个连接查询的执行计划中有两条记录，这两条记录的 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_filed = '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';
8d3f9678bfee4e14b7291a9da5bf2de1.png

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

EXPLAIN SELECT * FROM s1 INNER JOIN s2;

f58d03077820475db612162e54038d68.png

可以看到，上述连接查询中参与连接的 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';
94421c7238e043978da65d092ba150c1.png

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

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

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

可以看到，虽然我们的查询语句是一个子查询，但是执行计划中 s1 和 s2 表对应的记录的id 值全部都是1，这就表明了 查询优化器将之查询转换为了连接查询
对于 包含 union 子句的查询语句来说，每个 select 关键字对应一个 id 值也是没错的，不过还是有点特别的东西，比如：

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
3c965507a1b340d9b3a842dc6ca480b0.png

这个语句的执行计划的第三题记录是什么？
为何 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;
28290a4b71184eaaa42b718adabf2631.png
  • 总结：执行计划包含的信息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 取值。

列名 描述
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 在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
UNCACHEALE SUBQUERY A subquery for which the result cannot be cached and must be re-evaluated for each rou of the outer query
UNCACHEALE 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;
429f85ab7d244c57866637ca7ed77ba3.png

连接查询也算是 SIMPLE 类型：

EXPLAIN SELECT * FROM s1 INNER JOIN s2;
97d87af0211f410bb482646639c69e6d.png
  • PRIMARY
    对于包含 UNION 或者 UNION ALL 或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的哪个查询的 select_type 值就是 PRIMARY。
EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
28290a4b71184eaaa42b718adabf2631.png

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

  • UNION
    对于包含 UNION 或者 UNION ALL 的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的 select_type 值就是 UNION。
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
3c965507a1b340d9b3a842dc6ca480b0.png
  • 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';
330934a0d0be4bfa8286b72bf44502e6.png
  • 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';
f95729fdee64453d807949f7ca4290b9.png

需要注意的是，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');
bbfc5a3becdb4baab5c0e07c437eacc5.png
  • 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;
b79592c39fa748bb966f7aaa500f77be.png

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

  • MATERIALIZED
    当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的 select_type 属性就是 MATERIALIZED。
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);
14b235b3ed82441393e065da7461f4ec.png

子查询被转为了物化表。

PARTITIONS

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

create table tmp (
  id int unsigned not null AUTO_INCREMENT,
  name varchar(255),
  PRIMARY KEY(id))
  engine=innodb
  partitions 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=MYISM;
INSERT INTO t VALUES()1;

EXPLAIN SELECT * FROM t;
c21c14c434a74c32a8d468a4ba7e772e.png

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

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

EXPLAIN SELECT * FROM tt;
f06adba17f7d472ebfacb9bbd48c6161.png
  • const
    当我们根据 主键 或者 唯一二级索引列 与常数进行等值匹配时，对单表的访问方法就是 const。
EXPLAIN SELECT * FROM s1 WHERE id = 10005;

EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;
ff76597f1de44012a8c8fb3fd14857ab.png

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

  • eq_ref
    在连接查询时，如果被驱动表是通过主键,唯一二级索引等值匹配 的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有 的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref。
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
1cd4709f68b44531812c95eaedba257a.png

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

  • ref
    当通过普通的 二级索引列 与 常量进行等值匹配时 来查询某个表，那么对该表的访问方法就可能是 ref。
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
a3dabfd7dc1d4b91b8ba9a3694e0c46b.png

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

  • ref_or_null
    当对 普通二级索引进行等值匹配查询，该索引列的值也可以是 NULL 值时，那么对该表的访问方法就可以能是 ref_or_null。
    ref_or_null 是 ref 值上的一个变体，它意味着 MySQL必须在初次查找的结果里进行第二次查找以找出 null 条目。
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;
ed98f609241a427ea96b4158ca7444f3.png
  • 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。
EXPLAN SELECT * FROM s1
WHERE key2 IN (SELECT id FROM s2 WHERE s1.key1 = s2.key1) OR key3 = 'a';
bc90b198eca446cfb10ef347a2beb4ce.png

可以看到执行计划的第二天记录的 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');
a2475150d58c4d8c8b1996fac2e31418.png
EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';
2f0c621686a647d284de17a7af16ff3f.png

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

EXPLAIN SELECT * FROM s1 WHERE key1 = 'aaaa' OR key1 = 'bbbbb';
b671b7f2fcc248b785111cc6fbd091cb.png
  • index
    当我们可以使用 索引覆盖，但是想要 扫描全部的索引记录时，该表的访问方法就是 index 。这个跟全表扫描一样，只是 MySQL 扫描表时按索引次序进行而不是逐行遍历。它的主要优点是避免了排序，最大的缺点是要承担按索引次序读取整个表的开销。
EXPLAIN SELECT key_part2 FROM s2 WHERE key_part3 = 'a';
c72995a18e2549e59dbee5e77accf105.png

上述查询中的搜索列表中只有 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';
e8cb576352814deeab6b1ccf90846970.png

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

key_len

该列显示了 MySQL 在索引里使用的字节数。如果 MySQL 正在使用的只是索引里的某些列，那么就可以用这个值来算出具体是哪些列。
key_len 显示的值为索引字段的最大可能长度，并非实际使用长度。即 key_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_nukk， unique_subquery，index_subquery 其中之一时，ref 列展示的就是与索引列作等值匹配的结果是什么，比如只是一个常数或者是某和列。

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

![0db214817bea4b74b33ac77ec428fdbc.png](https://upload-images.jianshu.io/upload_images/7399010-129b7053e49162e5.png?imageMogr2/auto-orient/strip%7ky CimageView2/2/w/1240)
可以看到 ref 列的值是 const，表明在使用 idx_key1 索引执行查询时，与 key1 列作等值匹配的对象是一个常数，当然有时候更复杂一点。

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
d3206583f4a54995ae95b9718bcb0bc7.png
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);
11064f73b886484ba30b3b8fd9cba71e.png

rows

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

EXPLAIN SELECT * FROM s1 WHERE key1 > 'Z';
98f64bcf4cdb4909971fef7b252aa816.png

filtered

其表示的是针对表里符合某个条件（where 子句或连接条件）的记录数与 rows 的估算百分比，将 rows 列与百分比相乘，就能看到 MySQL 估算它将和查询计划里前一个表关联的行数。
如果使用的是 索引执行的单表扫描，那么计算时需要估计出满足除使用对应索引的搜索条件外的其他搜索条件的记录有多少条。

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

5bbdcc67c2a6409cb0d24710b65a87f2.png

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

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

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

Extra

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

  • No tables used
    当查询语句的没有 FROM 字句时将会提示该额外的信息：No tables used
EXPLAIN SELECT 1;
b176a940cd1e421eb8f5f03e75dc1af4.png
  • Impossible WHERE
    查询语句的 WHERE 子句永远为 FALSE 时将会提示额外信息： Impossible WHERE
EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
6265cfee10f147ceae6342c8f81aefc4.png
  • Using where
    当我们使用 全表扫描 来执行对某个表的查询，并且该语句的 WHERE 字句中有针对该表的搜索条件时，在 Extra 列中会提示上述额外信息。
EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
e8011f1033654cbe9b5b6cd3456b3b91.png

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

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';
c5134c0dedd64422862baebdd05f4d77.png
  • No matching min/max row
    当查询列表处有 MIN 或者 MAX 聚合函数，但是并没有符合 WHERE 子句中的搜索条件的记录时，将会提示该额外信息。
EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';
7acf4d2fe2694df6926adca03759629f.png
EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = "cWAPuH"; #cWAPuH是 s1表中key1字段真实存在的数据
ec1deac9c47342079b0ff1df431f93c9.png
  • 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';
65c77b34afb3421a9bc9d23605f0fa47.png

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

  1. 先根据 key1 > 'z' 这个条件，从二级索引 idx_key1 中获取到对应的二级索引记录。
  2. 对于制定的二级索引记录，先不着急回表，而是先检查一下该记录是否满足 key1 like '%a' 这个条件，如果这个条件不满足，则该二级索引记录压根没有必要回表。
  3. 对于满足 key1 like '%a' 这个条件的二级索引记录执行回表操作。
    我们说回表操作其实是一个随机 IO，比较耗时，所以上述修改虽然只改进了一点点，但是可以省去好多回表操作的成本。MYSQL 把他们的这个改进称之为 索引条件下推(Index Condition Pushdown)。
    如果在查询语句的执行过程中将要使用 索引条件下推 这个特性，在 Extra 列中将会显示 Using index condition ，比如这样：
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
65c77b34afb3421a9bc9d23605f0fa47.png
  • Using where; Using join buffer (hash join)
    在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名加 join buffer 的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法。
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
66256865748341cca08f5e6632321a89.png

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

  1. Using join buffer(hash join)：这是因为对表 s2 的访问不能有效利用索引，只好退而求其次，使用 join buffer 来减少对表 s2 的访问次数，从而提高性能。
  2. 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 NILL;
e8c7aa39aee04660bafb5cdaf00cf071.png

上述查询中 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';
8282bae0487e43e3a9d7e695e6c01736.png

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

  • Zero limit
    当我们的 LIMIT 子句的参数为 0 时，表示根本不打算从表中读出任何记录，将会提示该额外信息。
EXPLAIN SELECT * FROM s1 LIMIT 0;
39ec1ccd9a2f487b9a9b667aecb69748.png
  • Using filesort
    有一些情况下对结果集中记录进行排序是可以使用索引的。
EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
9cd1ab892a5d4f94be3dd2cee6d820dd.png

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

EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

025705db2cb643348da1f48c0bdd2886.png

需要注意的是，如果查询中需要使用 filesort 的方式进行排序的记录非常多，那么这个过程是很耗费性能的，我们最好想办法将 使用文件排序的执行方式改为使用索引进行排序
MySQL 有两种文件排序算法（单路和双路），两种方式都可以在内存或磁盘上完成，explain 不会告诉你 MySQL 将使用哪一种文件排序，也不会告诉你排序会在内存还是磁盘上完成。

  • Using temporary
    在许多查询的执行过程中，MySQL 可能会借助临时表来完成一些功能，比如去重，排序之类的。比如 我们在执行许多包含 DISTINCT, GROUP BY, UNION 等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL 很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的 Extra 列将会显示 Using temporary 提示。这种情况比 filesort 更严重，需要优化。
EXPLAIN SELECT DISTINCT common_field FROM s1;
0a728940d2da4ebaa7b21aad63cd7e9c.png
EXPLAIN SELECT DICTINCT key1 FROM s1;
000a8a3d8ec1423e96c2848775481edb.png
EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;
838687a5b6c84778b87a741c3b6dedc7.png

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

EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;
0c4485c36c844fcb9b173d534e45d6e1.png

下面我们看一个更糟糕的例子，同时出现了 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

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

CREATE TABLE `testc` (
  `id` bigint(20) NOT NULL AUTO_INCRMENT,
  `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 testc WHERE c1='a1' ORDER BY c2, c3;
8dd759b777324c279ba1708aaf3e514d.png

如果同时出现 using where，表明索引被用来执行索引键值的查找。如果没有同时出现 using where,表明索引用来读取数据而非执行查找动作。
如果将 SELECT c1 修改为 SELECT * ，那么与前者执行计划不同的是这里 Extra 中是 Using index condition。

EXPLAIN SELECT * FROM  testc WHERE c1='a1' ORDER BY c2, c3;
bf4609a4029e4a8aa9d36f103d602f0b.png
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 不会告诉你关于触发器，存储过程的信息或用户自定义函数对查询的影响情况
  • 部分统计是估算的，并非精确值
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 194,911评论 5赞 460
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 82,014评论 2赞 371
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 142,129评论 0赞 320
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,283评论 1赞 264
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,159评论 4赞 357
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,161评论 1赞 272
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,565评论 3赞 382
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,251评论 0赞 253
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,531评论 1赞 292
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,619评论 2赞 310
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,383评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,255评论 3赞 313
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,624评论 3赞 299
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 28,916评论 0赞 17
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,199评论 1赞 250
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,553评论 2赞 342
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,756评论 2赞 335

推荐阅读更多精彩内容