网上有不少关于filesort的文章，有更多的文章是关于文档对于filesort翻译的吐槽。

Using filesort

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. The keys then are sorted and the rows are retrieved in sorted order.

意思是指mysql要在内存中对符合条件的记录集进行一次额外的排序。

 

先看表结构：

CREATE TABLE `TimelineEvent` (
`id` bigint(20) NOT NULL DEFAULT ’0′,
`PublishTime` bigint(20) DEFAULT NULL,
`EventType` tinyint(4) NOT NULL DEFAULT ’0′,
`SubType` bigint(20) DEFAULT ’0′,
KEY `PublishTime` (`PublishTime`,`EventType`,`SubType`),
KEY `EventType` (`EventType`,`SubType`,`PublishTime`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk

很多时候我们有这样的查询需求：

mysql> desc select * from TimelineEvent FORCE INDEX(EventType) where EventType in (1,2) and SubType=2 order by PublishTime desc limit 10;
+—-+————-+—————+——-+—————+———–+———+——+——+—————————–+
| id | select_type | table         | type  | possible_keys | key       | key_len | ref  | rows | Extra                       |
+—-+————-+—————+——-+—————+———–+———+——+——+—————————–+
|  1 | SIMPLE      | TimelineEvent | range | EventType     | EventType | 10      | NULL |    2 | Using where; Using filesort |
+—-+————-+—————+——-+—————+———–+———+——+——+—————————–+
1 row in set (0.00 sec)

mysql> desc select * from TimelineEvent FORCE INDEX(EventType) where EventType>3 and SubType=2 order by PublishTime desc limit 10;
+—-+————-+—————+——-+—————+———–+———+——+——-+—————————–+
| id | select_type | table         | type  | possible_keys | key       | key_len | ref  | rows  | Extra                       |
+—-+————-+—————+——-+—————+———–+———+——+——-+—————————–+
|  1 | SIMPLE      | TimelineEvent | range | EventType     | EventType | 1       | NULL | 65715 | Using where; Using filesort |
+—-+————-+—————+——-+—————+———–+———+——+——-+—————————–+
1 row in set (0.00 sec)

当索引的前缀列（比如这里的EventType）是在做range查询的时候，并不能使用到最后一列索引PublishTime进行排序，desc查看的时候出现filesort。

mysql> desc select * from TimelineEvent FORCE INDEX(EventType) where EventType=3 and SubType=2 order by PublishTime desc limit 10;
+—-+————-+—————+——+—————+———–+———+————-+——+————-+
| id | select_type | table         | type | possible_keys | key       | key_len | ref         | rows | Extra       |
+—-+————-+—————+——+—————+———–+———+————-+——+————-+
|  1 | SIMPLE      | TimelineEvent | ref  | EventType     | EventType | 10      | const,const |    1 | Using where |
+—-+————-+—————+——+—————+———–+———+————-+——+————-+
1 row in set (0.00 sec)

使用=查询是非常完美的。

 

当符合where条件的数据量比较小的时候，filesort排序没有什么问题。但当符合where条件的数据量特别大的时候，使用filesort进行几万甚至几十万的记录排序效率比较低，每个线程都比较占用内存而且容易阻塞，而且结果往往只是取排序后的其中几十条，大数据量排序很浪费。

改进思路就是采用上述表的PublishTime索引：

mysql> desc select * from TimelineEvent FORCE INDEX(PublishTime) where EventType>=3 and SubType=2 order by PublishTime desc limit 10;
+—-+————-+—————+——-+—————+————-+———+——+——+————-+
| id | select_type | table         | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+—-+————-+—————+——-+—————+————-+———+——+——+————-+
|  1 | SIMPLE      | TimelineEvent | index | NULL          | PublishTime | 19      | NULL |   10 | Using where |
+—-+————-+—————+——-+—————+————-+———+——+——+————-+
1 row in set (0.00 sec)

虽然没有了filesort，但我们发现索引的使用情况，type是index。以下是摘自官方文档，对于type是range和index的解释：

range

Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type.

index

This join type is the same as ALL, except that only the index tree is scanned. This usually is faster than ALL because the index file usually is smaller than the data file.

MySQL can use this join type when the query uses only columns that are part of a single index.

index这种type按照官方解释接近于all的检索速度，区别在于前者是遍历索引要稍微快一些。而range的话则是返回符合区间条件的记录。这里加入一个无用条件：publishtime>=0（本例中publishtime肯定大于等于0）可以看到变化：

mysql>  desc select * from TimelineEvent FORCE INDEX(PublishTime) where EventType>=3 and SubType=2 and publishtime>=0 order by PublishTime desc limit 10;
+—-+————-+—————+——-+—————+————-+———+——+——+————-+
| id | select_type | table         | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+—-+————-+—————+——-+—————+————-+———+——+——+————-+
|  1 | SIMPLE      | TimelineEvent | range | PublishTime   | PublishTime | 19      | NULL |    1 | Using where |
+—-+————-+—————+——-+—————+————-+———+——+——+————-+
1 row in set (0.00 sec)

这个条件的加入对于这个例子检索速度应该是没有影响的，只是告诉desc代码增加了一个检索条件，因为理论上是存在publishTime<0的情况，只是我们的应用不存在而已。

 

这里还有个问题不知道大家能不能解释，>和>=有区别，表现在key_len上，也就是说>=可以使用到3列索引，而>只用到了EventType一列索引。

mysql> desc select * from TimelineEvent FORCE INDEX(EventType) where EventType>=3 and SubType=2 and publishtime=344 order by PublishTime desc limit 10;
+—-+————-+—————+——-+—————+———–+———+——+——-+————-+
| id | select_type | table         | type  | possible_keys | key       | key_len | ref  | rows  | Extra       |
+—-+————-+—————+——-+—————+———–+———+——+——-+————-+
|  1 | SIMPLE      | TimelineEvent | range | EventType     | EventType | 19      | NULL | 65715 | Using where |
+—-+————-+—————+——-+—————+———–+———+——+——-+————-+
1 row in set (0.00 sec)

mysql> desc select * from TimelineEvent FORCE INDEX(EventType) where EventType>3 and SubType=2 and publishtime=344 order by PublishTime desc limit 10;
+—-+————-+—————+——-+—————+———–+———+——+——-+————-+
| id | select_type | table         | type  | possible_keys | key       | key_len | ref  | rows  | Extra       |
+—-+————-+—————+——-+—————+———–+———+——+——-+————-+
|  1 | SIMPLE      | TimelineEvent | range | EventType     | EventType | 1       | NULL | 65715 | Using where |
+—-+————-+—————+——-+—————+———–+———+——+——-+————-+
1 row in set (0.00 sec)