MySQL · 答疑释惑· using filesort VS using temporary

**背景** MySQL 执行查询语句， 对于order by谓词，可能会使用filesort或者temporary。比如explain一条语句的时候，会看到Extra字段中可能会出现，using filesort和using temporary。下面我们就来探讨下两个的区别和适用场景。 **解释** 1\. using filesort filesort主要用于查询数据结果集的排序操作，首先MySQL会使用sort_buffer_size大小的内存进行排序，如果结果集超过了sort_buffer_size大小，会把这一个排序后的chunk转移到file上，最后使用多路归并排序完成所有数据的排序操作。 MySQL filesort有两种使用模式: 模式1: sort的item保存了所需要的所有字段，排序完成后，没有必要再回表扫描。 模式2: sort的item仅包括，待排序完成后，根据rowid查询所需要的columns。 很明显，模式1能够极大的减少回表的随机IO。 2\. using temporary MySQL使用临时表保存临时的结构，以用于后续的处理，MySQL首先创建heap引擎的临时表，如果临时的数据过多，超过max_heap_table_size的大小，会自动把临时表转换成MyISAM引擎的表来使用。 从上面的解释上来看，filesort和temporary的使用场景的区别并不是很明显，不过，有以下的原则: filesort只能应用在单个表上，如果有多个表的数据需要排序，那么MySQL会先使用using temporary保存临时数据，然后再在临时表上使用filesort进行排序，最后输出结果。 **适用场景** 我们看一下下面的三个case: ~~~ create table t1( id int, col1 int, col2 varchar(10), key(id, col1)); ~~~ ~~~ create table t2( id int, col1 int, col2 varchar(10), key(col1)); ~~~ case 1: ~~~ mysql> explain select * from t1 force index(id), t2 where t1.id=1 and t1.col1 = t2.col2 order by t1.col1 ; +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | t1 | ref | id | id | 5 | const | 1 | Using where | | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ ~~~ case1: order by字段能够使用index的有序性，所以没有使用filesort，也没有使用temporary。 case 2: ~~~ mysql> explain select * from t1 force index(id), t2 where t1.id=1 and t1.col1 = t2.col2 order by t1.col2; +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | t1 | ref | id | id | 5 | const | 1 | Using where; Using filesort | | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+ ~~~ case2: order by谓词，是在第一个表t1上完成，所以只需要在t1表上使用filesort，然后排序后的结果集join t2表。 case 3: ~~~ mysql> explain select * from t1 force index(id), t2 where t1.id=1 and t1.col1 = t2.col2 order by t2.col1 ; +----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------+ | 1 | SIMPLE | t1 | ref | id | id | 5 | const | 1 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using join buffer | +----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------+ ~~~ case 3: order by的字段在t2表上，所以需要把t1，t2表join的结果保存到temporary表上，然后对临时表进行filesort，最后输出结果。 **特别优化** MySQL对order by + limit的filesort做了特别优化，使用Priority queue来保存结果，即一个堆的结构，只保留top n的数据满足limit条件。 **另外** filesort和temporary都会在tmp目录下创建文件，temporary创建的是MYI，MYD文件。但filesort的文件, 因为MySQL使用了create->open->unlink->使用->close的方式，隐藏了文件，以便进程异常结束的时候，临时文件能够自动回收掉，所以在评估tmp目录空间的时候，需要特别注意。