mysql中explain出现using filesort的优化方法

explain是mysql解释select查询语句的一个关键字,它可以很方便的对mysql语句进行调试,看索引是否使用.

语法格式如下

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=2224AND FID IN(11,8,13,123,382,40)ORDER BY INVERSE_DATE LIMIT0,4

里面建立的索引为一个三列的多列索引：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速度也会快很多。