Mysql之EXPLAIN显示using filesort

EXPLAIN 是mysql解释select查询的一个关键字,可以很方便的用于调试
语法格式如下
EXPLAIN tbl_name
或者:
EXPLAIN SELECT select_options
EXPLAIN 语句可以被当作 DESCRIBE 的同义词来用，也可以用来获取一个MySQL要执行的 SELECT 语句的相关信息。

EXPLAIN tbl_name 语法和 DESCRIBE tbl_name 或 SHOW COLUMNS FROM tbl_name 一样。

当在一个 SELECT 语句前使用关键字 EXPLAIN 时，MYSQL会解释了即将如何运行该 SELECT 语句，它显示了表如何连接、连接的顺序等信息。

以下信息为引用:

在explain我们所使用的sql的时候，经常会遇到using filesort这种情况，原以为是由于有相同列值的原因引起，结果昨天看到公司的一个sql，跟同事讨论了下加上自己又做了一些测试，突然发现自己原来的想法是错误的。

首 先，只有在order by 数据列的时候才可能会出现using filesort，而且如果你不对进行order by的这一列设置索引的话，无论列值是否有相同的都会出现using filesort。因此，只要用到order by 的这一列都应该为其建立一个索引。

其次，在这次测试中，使用了一个稍微有点复杂的例子来说明这个问题，下面详细用这个例子说一下：

SELECT * FROM DB.TB WHERE ID=2222 AND FID IN (9,8,3,13,38,40) ORDER BY INVERSE_DATE LIMIT 0, 5
里面建立的索引为一个三列的多列索引：IDX（ID,FID ,INVERSE_DATE） 。INVERSE_DATE这个是时间的反向索引。

对于这个sql我当时最开始认为应该是个优化好的状态，应该没有什么纰漏了，结果一explain才发现竟然出现了：Using where; Using filesort。

为 什么呢，后来经过分析才得知，原来在多列索引在建立的时候是以B-树结构建立的，因此建立索引的时候是先建立ID的按顺序排的索引，在相同ID的情况下建 立FID按 顺序排的索引，最后在FID 相同的情况下建立按INVERSE_DATE顺序排的索引，如果列数更多以此类推。有了这个理论依据我们可以看出在这个sql使用这个IDX索引的时候只 是用在了order by之前，order by INVERSE_DATE 实际上是using filesort出来的。。汗死了。。因此如果我们要在优化一下这个sql就应该为它建立另一个索引IDX（ID,INVERSE_DATE），这样就消 除了using filesort速度也会快很多。问题终于解决了

 

 

 

Using filesort表示在索引之外，需要额外进行外部的排序动作。导致该问题的原因一般和order by有者直接关系，一般可以通过合适的索引来减少或者避免。

 

 

explain SELECT * FROM table_item WHERE user_id = 2 ORDER BY item_id LIMIT 0, 5
user_id 和 item_id 分别建立一个索引，对此语句MySQL选择了 user_id索引，那么 item_id 的索引没有起到任何用处。当排序时记录数较多，内存中的排序 buffer满了，只能 Using filesort 进行外部排序。
解决方式为对这两列建立组合索引。

 

 

explain SELECT * FROM table_item WHERE user_id = 2 and user_age > 20 ORDER BY item_id LIMIT 0, 5
建立组合索引（user_id, user_age, item_id），看似非常完美。但由于user_age规则不是确定值，使用该组合索引的话实际上需要先按索引找出一个个user_age下的东西后再对其进行排序，仍然会filesort。
解决方式是使用组合索引（user_id, item_id），对排序好的item_id再过滤到不满足user_age > 20的条目，不会出现filesort。

 

 

explain SELECT * FROM table_item WHERE user_id = 2 ORDER BY item_attr desc, item_id LIMIT 0, 5
建立了组合索引（user_id, item_attr, item_id)，因为item_attr是降序而item_id是升序，从而仍然需要外部排序。
如果item_attr和item_id都是升序或者都是降序，则不会出现filesort；建立组合索引（user_id, item_attr desc, item_id)现象依旧。
Mysql5.1 在线文档“13.1.4. CREATE INDEX语法”中提到：“一个index_col_name规约可以以ASC或DESC为结尾。这些关键词将来可以扩展，用于指定递增或递减索引值存 储。目前，这些关键词被分析，但是被忽略；索引值均以递增顺序存储。”
可以看出无法通过修改索引来避免这个filesort，只能是如果可能的话修改查询的排序条件