认真学习MySQL中的explain分析SQL-2

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;
• 1

执行计划中出现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不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• 部分统计信息是估算的，并非精确值