MYSQL EXPLAIN 工具

列名	描述
id	每次查询都对应一个唯一的id
table	对每张要访问的表的表名（有时不是真实的表名字，可能是简称）。
select_type	SELECT关键字对应的那个查询的类型
partitions	匹配的分区信息
type	针对单表的访问方法(system、const、eq_ref、ref、index、ALL等)
possible_key	可能用到的索引
key	实际用到的索引
key_len	实际用到的索引长度，主要针对于联合索引，有一定的参考意义。
ref	当时有索引列进行等值查询时，与索引列进行等值匹配的对象信息（const、func）
rows	预估需要读取的记录条数
filtered	某表经过搜索田间过滤后，剩余记录数的百分比，越大越好
extra	一些额外信息(using where、use index condition、using join buffer等)

三、EXPLAIN 字段详述

0.数据准备

创建表s1、s2、设置允许创建函数、创建一个函数、两个存储过程、最后两次存储过程的调用

CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE s2 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

SET GLOBAL log_bin_trust_function_creators=1;
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

DELIMITER //
CREATE FUNCTION rand_string1(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s1 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s2 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;

CALL insert_s1(10001,10000);
CALL insert_s2(10001,10000);

1.id

#使用索引查找,一次查询

#使用索引连接,一次查询

#使用嵌套循环连接,一次查询

#s1全表扫描、s2子查询走索引,两次查询

#子查询被优化器优化成连接，s1.key1 =s2.key2 ,然后 WHERE s2.common_field= 'a';,一次查询

#UINON操作需要去重，需要在临时表中保存去重结果，然后查询，作为结果集,三次查询

#将两次查询结果直接加入结果集。两次查询

2.table

#查询的每一行记录都对应着一个单表,一张表

#表连接,两张表

#union操作，存在一张临时表,三张表

3.select_type

SIMPLE

查询语句中不包含`UNION`或者子查询的查询都算作是`SIMPLE`类型

PRIMARY

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

UNION

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

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;

SUBQUERY

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

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';

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

对于包含`派生表`的查询，该派生表对应的子查询的`select_type`就是`DERIVED`。
EXPLAIN SELECT * FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1;

MATERIALIZED

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

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2); #子查询被转为了物化表

4. partition(略)

匹配的分区信息

5.type(*)

单表的访问方法

system

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

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

          #换成InnoDB
         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;

eq_ref

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

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

ref

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

ref_or_null

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

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

index_merge

索引合并，单表访问方法时在某些场景下可以使用`Intersection`、`Union`、`Sort-Union`这三种索引合并的方式来执行查询。

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';

range

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

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

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

index

当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是`index`
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

ALL

全表扫描

EXPLAIN SELECT * FROM s1;

6.key_len

实际使用到的索引长度(即：字节数), 帮你检查`是否充分的利用上了索引`，`值越大越好`,主要针对于联合索引，有一定的参考意义。

EXPLAIN SELECT * FROM s1 WHERE id = 10005;

EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;

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

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

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

不走索引,不满足最左前缀匹配。

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

7.ref

当使用索引列等值查询时，与索引列进行等值匹配的对象信息。比如只是一个常数或者是某个列。

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

被驱动表使用索引，s1的id与s2的索引列进行等值查询。

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

被驱动表使用索引，s1的key1函数与s2的key1索引列进行等值查询。

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

8. rows

预估的需要读取的记录条数，越小越好

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

EXPLAIN SELECT * FROM s1 WHERE id=11000;

9. filtered

某个表经过搜索条件过滤后剩余记录条数的百分比。

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

10.extra(*)

更准确的理解MySQL到底将如何执行给定的查询语句。

No Table Used

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

EXPLAIN SELECT 1;

Impossible WHERE

查询语句的`WHERE`子句永远为`FALSE`时将会提示该额外信息
EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

Using Where

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

当使用索引访问来执行对某个表的查询，并且该语句的`WHERE`子句中有除了该索引包含的列之外的其他搜索条件时，在`Extra`列中也会提示上述额外信息。
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';

Using Index

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

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

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

Using Index Condition

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

索引下推关闭下的的查询过程：

在key1索引上查找 key1>'z',得到主键id，然会回表，在主表上找到id对应的记录时，进行key1列的like '%a'比较。这种方式回表开销大。

索引下推开启后的查询过程：

在key1索引上查找 key1>'z'，得到索引记录，然后对每个索引记录的索引列进行like '%a'匹配，如果成功，则获取一个主键id。最后将获取的所有主键id回表，这种方式回表的id个数较第一种方式少。

Using Join Buffer

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

Not Exist

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

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

Using union

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

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

Zero Limit

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

EXPLAIN SELECT * FROM s1 LIMIT 0;

Using filesort

很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：`filesort`）。
EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

Using temporaty

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

EXPLAIN SELECT DISTINCT common_field FROM s1;