MySQL 优化索引 —— Using filesort

用Explain分析SQL语句的时候，经常发现有的语句在Extra列会出现Using filesort，根据mysql官方文档对他的描述：

引用
MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause.

中文手册上翻译的很别扭：

引用
“Mysql需要额外的一次传递，以找出如何按排序顺序检索行，通过根据联接类型浏览所有行并为 所有匹配where子句的行保存排序关键字和行的指针来完成排序，然后关键字被排序，并按排序顺序检索行。”


总的来说，Using filesort 是Mysql里一种速度比较慢的外部排序，如果能避免是最好的了，很多时候，我们可以通过优化索引来尽量避免出现Using filesort，从而提高速度。

这里举个简单的例子：

CREATE TABLE `testing` (
`id` int(10) unsigned NOT NULL auto_increment,
`room_number` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `room_number` (`room_number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


写个存储过程askwan，插入10万条测试数据

mysql> DELIMITER $$
DROP PROCEDURE IF EXISTS `askwan`.`askwan` $$
CREATE PROCEDURE `askwan`.`askwan` ()
BEGIN
DECLARE v INT DEFAULT 1;
WHILE v<100000;
DO
INSERT INTO testing VALUES(v,v);
SET v=v+1;
END WHILE;

END $$
mysql> DELIMITER ;
mysql> CALL askwan();
Query OK, 1 row affected (13.21 sec)


OK,数据准备好了，开始试验。

由上面例子中建立的表信息，我已经建立了两个索引，一个主键id，一个 room_number列索引
那现在来看一条SQL,

SELECT id FROM testing WHERE room_number=1000 ORDER BY id ;


分析一下

mysql> EXPLAIN SELECT id FROM testing WHERE room_number=1000 ORDER BY id ;+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------------+| 1 | SIMPLE | testing | ref | room_number | room_number | 4 | const | 1 | Using where; Using filesort | +----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------------+1 row in set (0.00 sec)

出现了Using filesort，并且用到了room_number这列索引，但是，在这里用到的索引是针对WHERE后面的room_number条件的，而最后面的 排序是根据id来的，这就是手册中说的，“额外的一次排序”！，于是就会出现Using filesort，根据我以前写过的一文章，我再建立一个联合索引 room_number_id

alter table testing add index room_number_id(room_number,id);
在来分析一下

mysql> EXPLAIN SELECT id FROM testing WHERE room_number=1000 ORDER BY id ;+----+-------------+---------+------+----------------------------+----------------+---------+-------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+------+----------------------------+----------------+---------+-------+------+--------------------------+| 1 | SIMPLE | testing | ref | room_number,room_number_id | room_number_id | 4 | const | 1 | Using where; | +----+-------------+---------+------+----------------------------+----------------+---------+-------+------+--------------------------+1 row in set (0.00 sec)
现在Using filesort不见了。

总结一下：
1.一般有order by语句，在索引加得不当的情况下，都有可能出现Using filesort，这时候就要对SQL语句和索引进行优化了，但是，并不是说出现Using filesort就是个严重的问题，不是这样的，此次举的例子比较极端，几乎不太可能出现这么傻瓜的查询，优化和不优化，要看它是不是影响了业务性能。
2. 从上面可以看到联合索引，也可以叫多列索引，形如 key ('A1','A2','A3' ,'A4')等的，排序的思路一般是，先按照A1来排序，A1相同，然后按照A2排序，以此类推，这样对于（A1）,(A1,A2), (A1,A2,A3)的索引都是有效的，但是对于（A2,A3）这样的索引就无效了。

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Mysql之EXPLAIN显示using filesort
关键字: mysql 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速度也会快很多。问题终于解决了。