一、order by的原理
1、利用索引的有序性獲取有序數據
當查詢語句的 order BY 條件和查詢的執行計劃中所利用的 Index 的索引鍵（或前面幾個索引鍵）完全一致，且索引訪問方式為 range,ref 或者 index 的時候，MySQL 可以利用索引順序而直接取得已經排好序的數據。這種方式的 order BY 基本上可以說是最優的排序方式了,因為 MySQL 不需要進行實際的排序操作，需要注意的是使用索引排序也有很多限制。
當對連接操作進行排序時，如果ORDER BY僅僅引用第一個表的列，MySQL對該表進行filesort操作，然後進行連接處理，此時，EXPLAIN輸出“Using filesort”；否則，MySQL必須將查詢的結果集生成一個臨時表，在連接完成之後進行filesort操作，此時，EXPLAIN輸出“Using temporary;Using filesort”。
注意：MySQL在查詢時最多只能使用一個索引。因此，如果WHERE條件已經占用了索引，那麼在排序中就不使用索引了
mysql> show create table test \G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`addtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`a` int(11) unsigned NOT NULL,
`b` int(11) unsigned NOT NULL,
`c` int(11) unsigned NOT NULL,
`data` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `u` (`addtime`,`a`,`b`),
KEY `a` (`a`),
KEY `b` (`b`),
KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8
執行查詢1
mysql> explain select * from test where addtime='2015-10-13 15:38:32' order by a , b ; +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | test | ref | u | u | 8 | const | 4 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
執行查詢2
mysql> explain select * from test where addtime='2015-10-13 15:38:32' order by a , b ,c; +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | test | ref | u | u | 8 | const | 4 | Using where; Using filesort | +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+
使用filesort的情況
a、where語句與order by語句，使用了不同的索引 b、 檢查的行數過多，且沒有使用覆蓋索引 c、對索引列同時使用了ASC和DESC d、where語句或者ORDER BY語句中索引列使用了表達式，包括函數表達式 e、where 語句與ORDER BY語句組合滿足最左前綴，但where語句中使用了條件查詢 f、 當使用left join，使用右邊的表字段排序
2 利用內存/磁盤文件排序獲取結果
由於沒有可以利用的有序索引取得有序的數據，MySQL需要通過相應的排序算法，將取得的數據在sort_buffer_size系統變量所設置大小的排序區進行排序，這個排序區是每個Thread 獨享的，所以說可能在同一時刻在 MySQL 中可能存在多個 sort buffer 內存區域
MySQL中filesort 的實現算法有兩種:
1) 雙路排序:<sort_key, rowid> 是首先根據相應的條件取出相應的排序字段和可以直接定位行數據的行指針信息，然後在sort buffer 中進行排序（but this will be essentially hit the table in random order and is not very fast）。
2) 單路排序:是一次性取出滿足條件行的所有字段，然後在sort buffer中進行排序。
在 MySQL4.1 版本之前只有第一種排序算法,第二種算法是從MySQL4.1開始的改進算法,主要目的是為了減少第一次算法中需要兩次訪問表數據的IO操作,將兩次變成了一次,但相應也會耗用更多的 sort buffer 空間。典型的以空間換時間的優化方式。當然,MySQL4.1開始的以後所有版本同時也支持第一種算法
MySQL主要通過比較系統參數 max_length_for_sort_data的大小和Query語句所取出的字段類型大小總和來判定需要使用哪一種排序算法。如果max_length_for_sort_data更大,則使用第二種優化後的算法,反之使用第一種算法。所以如果希望 order BY 操作的效率盡可能的高,需要注意max_length_for_sort_data參數的設置。
3、使用Using temporary 臨時表來filesort
如果order by的子句只引用了聯接中的第一個表，MySQL會先對第一個表進行排序，然後進行聯接，expain中的Extra會出現Using Filesort
否則MySQL先把結果保存到臨時表(Temporary Table)，然後再對臨時表的數據進行排序，此時expain中的Extra的顯示Using temporary Using Filesort
4、Join排序
mysql中有三種方式
1）、Use index-based access method that produces ordered output -》 null
2）、Use filesort() on 1st non-constant table －》 “Using filesort” in the first row
3）、Put join result into a temporary table and use filesort() on it -》 “Using temporary; Using filesort” in the first row
5、說一下filesort，可以參考 What does Using filesort mean in MySQL?
摘自原文
The truth is, filesort is badly named. Anytime a sort can’t be performed from an index, it’s a filesort. It has nothing to do with files. Filesort should be called “sort.” It is quicksort at heart.
If the sort is bigger than the sort buffer, it is performed a bit at a time, and then the chunks are merge-sorted to produce the final sorted output. There is a lot more to it than this. I refer you to Sergey Petrunia’s article on How MySQL executes ORDER BY. You can also read about it in our book, but if you read Sergey’s article you won’t need to.
這裡有一個誤區，容易望文生義，Explain 命令輸出信息中的 filesort 到底是什麼意思呢？其實很簡單，就是告訴你 MySQL 需要進行實際的排序操作而不能通過索引獲得已排序數據
1）、filesort（其實就是排序） 可不一定會產生臨時表
2）、filesort 與臨時表數據寫入磁盤是沒有任何直接聯系的
二 、優化order by
當無法避免排序操作時,又該如何來優化呢？很顯然,優先選擇第一種using index 的排序方式，在第一種方式無法滿足的情況下，盡可能讓 MySQL 選擇使用第二種單路算法來進行排序。這樣可以減少大量的隨機IO操作,很大幅度地提高排序工作的效率。
1、加大 max_length_for_sort_data 參數的設置
在 MySQL 中,決定使用老式排序算法還是改進版排序算法是通過參數 max_length_for_ sort_data 來決定的。當所有返回字段的最大長度小於這個參數值時,MySQL 就會選擇改進後的排序算法,反之,則選擇老式的算法。所以,如果有充足的內存讓MySQL 存放須要返回的非排序字段,就可以加大這個參數的值來讓 MySQL 選擇使用改進版的排序算法。
2、去掉不必要的返回字段
當內存不是很充裕時,不能簡單地通過強行加大上面的參數來強迫 MySQL 去使用改進版的排序算法,否則可能會造成 MySQL 不得不將數據分成很多段,然後進行排序,這樣可能會得不償失。此時就須要去掉不必要的返回字段,讓返回結果長度適應 max_length_for_sort_data 參數的限制。
3 增大 sort_buffer_size 參數設置
這個值如果過小的話,再加上你一次返回的條數過多,那麼很可能就會分很多次進行排序,然後最後將每次的排序結果再串聯起來,這樣就會更慢,增大 sort_buffer_size 並不是為了讓 MySQL選擇改進版的排序算法,而是為了讓MySQL盡量減少在排序過程中對須要排序的數據進行分段,因為分段會造成 MySQL 不得不使用臨時表來進行交換排序。
但是這個值不是越大越好：
1 Sort_Buffer_Size 是一個connection級參數,在每個connection第一次需要使用這個buffer的時候,一次性分配設置的內存。
2 Sort_Buffer_Size 並不是越大越好,由於是connection級的參數,過大的設置+高並發可能會耗盡系統內存資源。
3 據說Sort_Buffer_Size 超過2M的時候,就會使用mmap() 而不是 malloc() 來進行內存分配,導致效率降低。
參考文章：
https://www.percona.com/blog/2009/03/05/what-does-using-filesort-mean-in-mysql/
http://s.petrunia.net/blog/?p=24
