一条查询语句在经过 MySQL 查询优化器的各种基于成本和规则的优化后会生成一个所谓的执行计划，它展示了接下来具体执行查询的方式。
使用Explain语句来查看执行计划。
| 列名 | 描述 |
|---|---|
| id | 在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id |
| select_type | SELECT 关键字对应的查询类型 |
| table | 表名 |
| partitions | 匹配的分区信息 |
| type | 针对单表的访问方法 |
| possible_keys | 可能用到的索引 |
| key | 实际上使用的索引 |
| key_len | 实际使用到的索引长度 |
| ref | 当使用索引列等值查询时，与索引列进行等值匹配的对象信息 |
| rows | 预估的需要读取的记录条数 |
| filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比 |
| Extra | 一些额外的信息 |
15.1 执行计划输出中各列详解
15.1.1 table
EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法，该条记录的 table 列代表着该表的表名。
EXPLAIN SELECT * FROM s1;
EXPLAIN SELECT * FROM s1 INNER JOIN s2;
15.1.2 id
查询语句中每出现一个SELECT关键字，MySQL 就会为它分配一个唯一的 id。
在连接查询的执行计划中，每个表都会对应一条记录，这些记录的 id 是相同的，出现在前边的表是驱动表，后面表的是被驱动表。
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。
对于包含 UNION 子句的查询语句，id 可能为 NULL
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传
UNION 去重时内部创建了一个名为 <union1,2> 的临时表，它的 id 为 NULL。
UNION 因为不需要去重，所以没有这个问题。
15.1.3 select_type
| 名称 | 描述 | 举例 |
|---|---|---|
| SIMPLE | 查询语句中不包含 UNION 或者子查询 | EXPLAIN SELECT * FROM s1 |
| PRIMARY | 包含 UNION、UNION ALL 或者子查询的查询，其中最左边的那个查询。 | EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2; |
| UNION | 包含 UNION、UNION ALL 或者子查询的查询，除最左边的那个以外的查询。 | 同上 |
| UNION RESULT | MySQL 使用临时表来完成 UNION 的去重工作，该临时表的查询。 | 同上 |
| SUBQUERY | 不相关子查询，不能转为 semi-join，使用物化方案的子查询。 | EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a'; |
| DEPENDENT SUBQUERY | 相关子查询，不能转为 semi-join 的子查询。该类型的查询可能被会执行多次 | EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a'; |
| DEPENDENT UNION | 在包含 UNION 或者 UNION ALL 的大查询中，如果各个小查询都依赖于外层查询的话，除了最左边的那个小查询的查询。 | EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b'); |
| DERIVED | 采用物化方式执行的，包含派生表的查询。 | EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1; |
| MATERIALIZED | 包含子查询，并将子查询物化之后与外层查询进行连接的查询。 | EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2); |
| UNCACHEABLE SUBQUERY | 不常用，略 | |
| UNCACHEABLE UNION | 不常用，略 |
15.1.4 partitions
分区，略
15.1.5 type
| type | 描述 | 举例 |
|---|---|---|
| system | 当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM、Memory 时 | CREATE TABLE t(i int) Engine=MyISAM; INSERT INTO t VALUES(1); EXPLAIN SELECT * FROM t; |
| const | 根据主键或唯一二级索引列与常数进行等值匹配时 | EXPLAIN SELECT * FROM s1 WHERE id = 5; |
| eq_ref | 连接查询时，被驱动表是通过主键或者唯一二级索引列进行等值匹配时 | EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id; |
| ref | 使用普通二级索引与常量进行等值匹配时 | EXPLAIN SELECT * FROM s1 WHERE key1 = '1'; |
| fulltext | 全文索引，略 | |
| ref_or_null | 使用普通二级索引进行等值匹配，但索引列的值可以是 NULL 时 | EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL; |
| index_merge | 发生索引合并时 | EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a'; |
| unique_subquery | 将 IN 转换为 EXISTS 并且子查询可以使用主键进行等值匹配时 | EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = 'a'; |
| 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 | 使用索引获取某些区间范围的记录时 | EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c'); EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b'; |
| index | 使用索引覆盖，但需要扫描全部的索引记录时 | EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'; |
| ALL | 全表扫描 | EXPLAIN SELECT * FROM s1 |
15.1.6 possible_keys 和 key
可能用到的索引和实际用到的索引。
15.1.7 key_len
表示使用某个索引执行查询时该索引记录的最大长度，它由三个部分构成：
- 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值。比如字符集utf8，类型VARCHAR(100)，则占用 100 × 3 = 300 字节
- 如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值时多 1 个字节
- 对于变长字段，都会有 2 个字节的存储该变长列的实际长度
key_len 的主要作用是为了让我们区分某个使用联合索引的查询具体用了几个索引列
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';
15.1.8 ref
当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery 之一时，ref 展示的就是与索引列作等值匹配的是什么。
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
15.1.9 rows
预计扫描的记录行数。
15.1.10 filtered
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';
从执行计划可以看出，查询优化器打算把 s1 当作驱动表， s2 当作被驱动表。驱动表的 rows 为 10141，filtered 列为 10.00，意味着驱动表的扇出值就是 10141 × 10.00% = 1014.1，说明还要对被驱动表执行大约 1014 次查询。
15.1.11 Extra
| 名称 | 含义 | 举例 |
|---|---|---|
| No tables used | 当没有 FROM 子句时 | EXPLAIN SELECT 1; |
| Impossible WHERE | 当 WHERE 子句永远为 FALSE 时 | EXPLAIN SELECT * FROM s1 WHERE 1 != 1; |
| No matching min/max row | 当查询列表有 MIN 或 MAX 聚集函数，但是并没有符合 WHERE 子句条件的记录时 | EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg'; |
| Using index | 当可以使用索引覆盖时而不用回表时 | EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a'; |
| Using index condition | 当有些搜索条件中虽然出现了索引列，但却不能使用索引而将要进行索引下推时 | EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a'; |
| Using where | 使用全表扫描并且 WHERE 子句中有针对该表的条件或使用索引访问但 WHERE 子句中有除了该索引列以外的查询条件时 | EXPLAIN SELECT * FROM s1 WHERE common_field = 'a'; |
| Using join buffer(Block Nested Loop) | 连接查询时，使用基于块的嵌套循环算法 | EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field; |
| Not exists | 使用左外连接，如果 WHERE 子句中包含被驱动表中某个列等于 NULL 的条件，而那个列又不允许为 NULL 时 | EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL; |
| Using intersect/union/sort union | 出现索引合并时 | EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND key3 = 'a'; |
| Zero limit | 当 LIMIT 子句的参数为 0 时 | EXPLAIN SELECT * FROM s1 LIMIT 0; |
| Using filesort | 在内存或磁盘上进行排序时 | EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10; |
| Using temporary | 当使用临时表时，如 DISTINCT、GROUP BY、UNION 等 | EXPLAIN SELECT DISTINCT common_field FROM s1; |
| Start temporary, End temporary | 当 IN 子查询转换成 semi-join，并且使用 DuplicateWeedout 执行策略时 | EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_field = 'a'); |
| LooseScan | 当 IN 子查询转换成 semi-join，并且使用 LooseScan执行策略时 | EXPLAIN SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 >'z'); |
| FirstMatch(tbl_name) | 当 IN 子查询转换成 semi-join，并且使用 FirstMatch(tbl_name)执行策略时 | EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key1 FROM s2 where s1.key3 = s2.key3); |
索引条件下推：
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';其中
key1 > 'z'可以使用索引，但是key1 LIKE '%a'却无法使用索引。先根据前者定位到具体记录，回表前先检测下该记录是否满足后者，满足才回表。这种方式可以大大减少回表的次数，称为索引条件下推（Index Condition Pushdown）
15.2 Json 格式的执行计划
EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a';
15.3 Extented EXPLAIN
使用 EXPLAIN 查看执行计划后，还可以使用 SHOW WARNINGS 来查看这个查询计划的一些扩展信息。
EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;
SHOW WARNINGS;
15.4 总结
- 通过 EXPLAIN 语句可以查看某个语句的执行计划
