在編寫SQL 語句時常常會用到 order by 進行排序，那麼排序過程是什麼樣的？為什麼有些排序執行比較快，有些排序執行很慢？又該如何去優化？
索引排序
索引排序指的是在通過索引查詢時就完成了排序，從而不需要再單獨進行排序，效率高。索引排序是通過聯合索引實現的。因為聯合索引是從最左邊的列開始起按大小順序進行排序，如下圖。
比如現在查詢條件是 where sex=1 order by name，那麼查詢過程就是會找到滿足 sex=1 的記錄，而符合這條的所有記錄一定是按照 name 排序的，所以也不需要額外進行排序。而如果是 where sex >1 order by name，那麼根據 sex>1 得到的記錄 sex 值並不是固定值，所以得到的記錄是按照 sex，其次才是 name 進行排列的。也就沒有實現索引排列。
額外排序
額外排序可以分別兩種方式來看待。
按執行位置劃分
1、Sort_Buffer
MySQL 為每個執行緒各維護了一塊記憶體區域 sort_buffer ，用於進行排序。sort_buffer 的大小可以通過 sort_buffer_size 來設定。如果用於排序的記錄欄位總長度小於 sort_buffer_size 便使用 sort_buffer 排序；如果超過則使用 sort_buffer + 臨時檔案進行排序。
2、Sort_Buffer + 臨時檔案
MySQL 會使用臨時檔案搭配 Sort_Buffer 進行排序。主要是使用歸併演算法來得出最終排序後的結果。臨時表會儲存主要資料（如果），sort_buffer 儲存 rowid（表示行記錄的位置標識，主鍵存在就是主鍵，不存在會自動建立一個6位元組的唯一標識）和用於排序的資料。
臨時檔案種類：
臨時表種類由引數 tmp_table_size 與臨時表大小決定，如果記憶體臨時表大小超過 tmp_table_size ，那麼就會轉成磁碟臨時表。因為磁碟臨時表在磁碟上，所以使用記憶體臨時表的效率是大於磁碟臨時表的。
1、記憶體臨時表
2、磁碟臨時表 磁碟臨時表預設使用的是 InnoDB，如果想要切換執行引擎，可以修改引數 internal_tmp_disk_storage_engine。
按執行方式劃分
執行方式是由 max_length_for_sort_data 引數與用於排序的單條記錄欄位長度決定的，如果用於排序的單條記錄欄位長度 <= max_length_for_sort_data ，就使用全欄位排序；反之則使用 rowid 排序。
1、全欄位排序
全欄位排序就是將查詢的所有欄位全部載入進來進行排序。
優點：查詢快，執行過程簡單
缺點：需要的空間大。
例子（不考慮臨時檔案）：select city,name,age from t where city='杭州' order by name limit 1000 ; city 有索引
1、初始化 sort_buffer，確定放入兩個欄位，即 name 和 id；
2、從索引 city 找到第一個滿足 city='杭州’條件的主鍵 id，也就是圖中的 ID_X；
3、到主鍵 id 索引取出整行，取 name、id 這兩個欄位，存入 sort_buffer 中；
4、從索引 city 取下一個記錄的主鍵 id；
5、重複步驟 3、4 直到不滿足 city='杭州’條件為止，也就是圖中的 ID_Y；
6、對 sort_buffer 中的資料按照欄位 name 進行排序；
7、遍歷排序結果，取前 1000 行，並按照 id 的值回到原表中取出 city、name 和 age 三個欄位返回給客戶端。
2、rowid 排序
rowid 表示位置資訊，如果整張表有主鍵那麼 rowid 就是主鍵，如果沒有主鍵就會自動建立一個 6 位元組的唯一標識。所以 rowid 排序就表示只載入用於排序的欄位以及 rowid ，然後進行排序，然後根據排序好的 rowid 去表中回表查詢所要的結果。
缺點：會產生更多次數的回表查詢，查詢可能會慢一些。
優點：所需的空間更小。
例子（不考慮臨時檔案）：select city,name,age from t where city='杭州' order by name limit 1000 ; city 有索引
1、初始化 sort_buffer，確定放入兩個欄位，即 name 和 id；
2、從索引 city 找到第一個滿足 city='杭州’條件的主鍵 id，也就是圖中的 ID_X；
3、到主鍵 id 索引取出整行，取 name、id 這兩個欄位，存入 sort_buffer 中；
4、從索引 city 取下一個記錄的主鍵 id；
5、重複步驟 3、4 直到不滿足 city='杭州’條件為止，也就是圖中的 ID_Y；
6、對 sort_buffer 中的資料按照欄位 name 進行排序；
7、遍歷排序結果，取前 1000 行，並按照 id 的值回到原表中取出 city、name 和 age 三個欄位返回給客戶端。
執行案例分析
rand() 執行
select word from words order by rand() limit 3; 表資料有10000行 SQL是從20000行記錄中隨機獲取3條記錄返回。
分析： 這裡查詢的欄位只有一個，所以使用全欄位查詢。 加上記錄數過多，但是單條記錄的欄位長度不長，所以會使用 sort_buffer + 記憶體臨時表。所以總結來看這條語句會使用 全欄位查詢 + sort_buffer + 記憶體臨時表 來排序。
執行過程：
1、從緩衝池依次讀取記錄，每次讀取後都呼叫 rand() 函式生成一個 0-1 的數存入記憶體臨時表，W 是 word 值，R 是 rand() 生成的隨機數。到這掃描了 10000 行。
2、初始化 sort_buffer，從記憶體臨時表中將 rowid（這張表自動生成的） 以及 排序資料 R 存入 sort_buffer。到這因為要遍歷記憶體臨時表所以又掃描了 10000 行。
3、在 sort_buffer 中根據 R 排好序，然後選擇前三個記錄的 rowid 逐條去記憶體臨時表中查到 word 值返回。到這因為取了三個資料去記憶體臨時表去查詢所以又掃描了 3 行。總共 20003 行。
rand() 優化
通過上面的例子可以看出當要從表中隨機獲取幾條記錄使用 rand() 函式是非常消耗資源的，同時觸發了 Using temporary 和 Using filesort。並且進行了 20003 行記錄的掃描，非常消耗資源。所以我們可以自己去計算一個隨機值，避免使用 rand() 函式。
查詢隨機的一條記錄：
1、取得整個表的行數，並記為 C。
2、取得 Y = floor(C * rand())。floor 函式在這裡的作用，就是取整數部分。
3、再用 limit Y,1 取得一行。
select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat("select * from t limit ", @Y, ",1");
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;
如果查詢多條，只要將第二步執行多次，然後依次執行就可以了。
使用這樣的方式就可以避免 MySQL 去使用臨時表以及 filesort 排序，提高執行效率。
優化佇列排序演算法
在 5.6 中對 rand() 排序進行一些優化，在某些情況下，會使用優化佇列排序演算法。
例如有一個 20000 行記錄的表，執行 select word from words order by rand() limit 3;
因為這條語句只取三條記錄，對這剩餘的 19993 行進行排序比較浪費CPU資源且耗時。所以使用優化佇列排序演算法。因為查詢的欄位只有一個，且查詢的行數很多，所以還是使用 全欄位查詢 + sort_buffer + 記憶體臨時表 。
過程：先讀取前三行記錄併為其分別通過 rand() 函式為其設定一個0-1的隨機數，取這三條記錄的 rowid、隨機陣列成一個堆，然後依次設定隨機數並與當前堆中的隨機數比較。如果這個隨機數比堆中某個記錄的隨機數小，就替換，然後移除，如果沒有小的就直接移除，取下一個。最後根據堆中的 rowid 去臨時表中讀取對應的 word 值返回。
失效場景：因為要拿指定的記錄數的排序資料以及rowid去挨個比較，所以如果需要返回的記錄數過多，導致所有的欄位長度超過了設定的 sort_buffer_size ，那麼此演算法就會失效。
索引排序案例
問題：有 (city,name) 聯合索引，select * from t where city in (“杭州”," 蘇州 ") order by name limit 100; 這個 SQL 語句是否需要排序？有什麼方案可以避免排序？
答案：需要排序。因為city 的條件有兩個，總體上來看就是以 city優先進行排序的。可以優化成下面三步：
1、執行 select * from t where city=“杭州” order by name limit 100; 這個語句是不需要排序的，客戶端用一個長度為 100 的記憶體陣列 A 儲存結果。
2、執行 select * from t where city=“蘇州” order by name limit 100; 用相同的方法，假設結果被存進了記憶體陣列 B。
3、現在 A 和 B 是兩個有序陣列，然後你可以用歸併排序的思想，得到 name 最小的前 100 值，就是我們需要的結果了。
如果將 " limit 100" 改成 " limit 10000,100 "。可以優化成下面三步：
1、select id,name from t where city="杭州" order by name limit 10100;
2、select id,name from t where city="蘇州" order by name limit 10100。
3、用歸併排序的方法取得按 name 順序第 10001~10100 的 name、id 的值，然後拿著這 100 個 id 到資料庫中去查出所有記錄。
優化總結
優化總體上就是圍繞了 “儘量不使用額外排序，避免使用臨時表”。
1、儘量使用索引完成排序，如果該查詢語句執行的頻率比較高，可以為其建立一個聯合索引。而如果使用的頻率很低，那麼就不需要去建立，因為索引的維護需要成本。
2、如果需要額外去排序，那麼可以適當調整 sort_buffer_size（sort_buffer） 和 tmp_table_size（記憶體臨時表） ，使排序只在記憶體中執行。
3、如果 sort_buffer 空間設定足夠大，也可以適當調整 max_length_for_sort_data 的值，使用全欄位排序。