MYSQL Explan 结果分析--Extra详解

目录

Using Index Condition

Using where; Using index

Using index

Using where

Using filesort

Using temporary

Using join buffer (Block Nested Loop)：

奇葩的情况：（Using index condition; Using where; Using temporary; Using filesort）

总结

---

Using Index Condition

using index condition则是在MySQL 5.6版本后加入的新特性（Index Condition Pushdown）;mysql会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。也就是在查找使用了索引的字段后，还是需要回表查询数据如下所示:除了索引字段还有其他字段需要查询。

Using where; Using index

查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据。很简单的例子如下。Type 就是index。

Using index

只查询索引字段的数据。只查询索引字段。

Using where

using where 意味着mysql服务器将在存储引擎检索行后再进行过滤。

对于简单没有使用索引的的where条件，一般都会出现。在下面的例子中，我们很明显的看到type 为ALL，就是全表扫描。

Using filesort

主要是order by  和 group by 都会有排序，Mysql 8.0 以后group by 不再隐式排序。

单独使用ORDER BY 或者GROUP BY大多数情况下都会用到文件排序（Using filesort）。如果WHERE 条件和 ORDER BY使用相同的索引，并且ORDER BY的顺序和索引顺序相同，并且ORDER BY的字段都是升序或者都是降序，可以避免这个问题。也就是在很多情况下，MySQL可以使用一个索引来满足ORDER BY子句，而不需要额外的排序。

 

但是如果select 用了DISTINCT 那么文件排序将是很难避免的。

有了MySQL特性（Index Condition Pushdown），可以使用group by来避免文件排序，前提依然是 WHERE 条件和 ORDER BY以及 group by使用相同的索引。

有一个现象需要注意：如果既用了索引字，又用了非索引字段，那么临时缓存和文件排序都会用到。

Using temporary

MySQL sql 执行过程中用到了临时表。项DISTINCT、 GROUP BY 、ORDER BY  这些隐含排序的查询，且查询条件中的字段又没有建立索引，就会出现使用临时表的情况。如下图所示：

要修改也比较简单，添加一下索引，但是要注意的地方是使用的是组合索引，where和group by中字段的顺序要跟索引一致。

Using join buffer (Block Nested Loop)：

说明使用了Nested Loop Join算法，Block Nested-Loop Join(BNL)算法。BNL 算法:将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数。举例来说，外层循环的结果集是100行，使用NLJ 算法需要扫描内部表100次，如果使用BNL算法，先把对Outer Loop表(外部表)每次读取的10行记录放到join buffer,然后在InnerLoop表(内部表)中直接匹配这10行数据，内存循环就可以一次与这10行进行比较, 这样只需要比较10次，对内部表的扫描减少了9/10。所以BNL算法就能够显著减少内层循环表扫描的次数。JOIN查询的时候，最经常出现的情况。

 

如下所示：

 

奇葩的情况：（Using index condition; Using where; Using temporary; Using filesort）

下面的这个SQL，各种情况都出现了，肯定是有问题的。稍微研究下，就可以看出是索引创建和使用有问题。索引应该是plan_id 和 batch_id的组合索引，而且where  和 group by 中字段出现的顺应该符合索引最左匹配原则。实际上只有batch_id字段有索引，所以才出现即用了batch_id字段索引，但是对于plan_id字段却需要临时表和文件排序，其中using where是指对临时表的全表扫描。

如果创建plan_id 和 batch_id组合索引，完全就不一样了：

Open_full_table; Scanned all databases;

一般应用中的SQL基本不会出现这种情况，这种情况我只在查询information_schema中的数据时出现，比如

 

总结

Extra中如果只出现 Using where; Using index表示SQL最快了，不需要优化。Extra中如果只出现 Using temporary; Using filesort表示SQL很慢了，需要及时优化。

 

 

参考：https://my.oschina.net/xinxingegeya/blog/495897?_t_t_t=0.08525431518307358