**背景** 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目錄空間的時候，需要特別注意。