本篇文章我們將瞭解ORDER BY語句的優化，在此之前，你需要對索引有基本的瞭解，不瞭解的朋友們可以先看一下我之前寫過的索引相關文章。現在讓我們開始吧。
MySQL中的兩種排序方式
1.通過有序索引順序掃描直接返回有序資料
因為索引的結構是B+樹，索引中的資料是按照一定順序進行排列的，所以在排序查詢中如果能利用索引，就能避免額外的排序操作。EXPLAIN分析查詢時，Extra顯示為Using index。
2.Filesort排序，對返回的資料進行排序
所有不是通過索引直接返回排序結果的操作都是Filesort排序，也就是說進行了額外的排序操作。EXPLAIN分析查詢時，Extra顯示為Using filesort。
ORDER BY優化的核心原則
儘量減少額外的排序，通過索引直接返回有序資料。
ORDER BY優化實戰
用於實驗的customer表的索引情況：
首先要注意：
MySQL一次查詢只能使用一個索引，如果要對多個欄位使用索引，建立複合索引。
ORDER BY優化
1.查詢的欄位，應該只包含此次查詢使用的索引欄位和主鍵，其餘的非索引欄位和索引欄位作為查詢欄位則不會使用索引。
只查詢用於排序的索引欄位，可以利用索引排序：
explain select store_id,email from customer order by store_id,email;
但是要注意，排序欄位在多個索引中,無法使用索引排序,查詢一次只能使用一個索引：
explain select store_id,email,last_name from customer order by store_id,email,last_name;
只查詢用於排序的索引欄位和主鍵，可以利用索引排序：
畫外音：MySQL預設的InnoDB引擎在物理上採用聚集索引這種方式，按主鍵進行搜尋，所以InnoDB引擎要求表必須有主鍵，即使沒有顯式指定主鍵，InnoDB引擎也會生成唯一的隱式主鍵，也就是說索引中必定有主鍵。
explain select customer_id,store_id,email from customer order by store_id,email;
查詢用於排序的索引欄位和主鍵之外的欄位，不會利用索引排序：
explain select store_id,email,last_name from customer order by store_id,email;
explain select * from customer order by store_id,email;
WHERE + ORDER BY 優化
1.排序欄位在多個索引中,無法利用索引排序
排序欄位在多個索引（不在同一個索引）中,無法利用索引排序：
explain select * from customer where last_name='swj' order by last_name,store_id;
畫外音：當排序欄位不在同一個索引時，無法滿足在一顆B+樹中完成排序，必須再進行一次額外的排序
排序欄位在一個索引中,並且WHERE條件和ORDER BY使用相同的索引,可以利用索引排序：
explain select * from customer where last_name='swj' order by last_name;
當然組合索引也可以利用索引排序：
注意欄位store_id,email在一個組合索引中
explain select * from customer where store_id = 5 order by store_id,email;
2.排序欄位順序與索引列順序不一致,無法利用索引排序
畫外音：這條是針對組合索引而言的，我們都知道使用組合索引必要要遵循最左原則，WHERE子句必須有索引中第一列，雖然ORDER BY子句沒有這個要求，但是也要求排序欄位順序和組合索引列順序匹配。我們平常在使用組合索引的時候，一定要養成按照組合索引列順序書寫的好習慣。
排序欄位順序與索引列順序不一致,無法利用索引排序：
explain select * from customer where store_id > 5 order by email,store_id;
應該確保排序欄位順序與索引列順序一致,這樣可以利用索引排序：
explain select * from customer where store_id > 5 order by store_id,email;
ORDER BY子句不要求必須索引中第一列,沒有仍然可以利用索引排序。但是有個前提條件，只有在等值過濾時才可以，範圍查詢時不可以：
explain select * from customer where store_id = 5 order by email;
explain select * from customer where store_id > 5 order by email;
畫外音：
其原因其實也很簡單，範圍查詢時，第一列a肯定是排序好的（預設是升序），而第二個欄位b其實就不是排序的了。但是如果a欄位有相同的值時，那麼b欄位就是排序的了。所以如果是範圍查詢，就只能對b做一次額外的排序。
3.升降序不一致,無法利用索引排序
ORDER BY排序欄位要麼全部正序排序，要麼全部倒序排序，否則無法利用索引排序。
explain select * from customer where store_id > 5 order by store_id,email;
explain select * from customer where store_id > 5 order by store_id desc,email desc;
explain select * from customer where store_id > 5 order by store_id desc,email asc;
總結：
上面的優化其實可以彙總為：WHERE條件和ORDER BY使用相同的索引，並且ORDER BY的順序和索引順序相同，並且ORDER BY的欄位都是升序或者降序。否則肯定需要額外的排序操作，就會出現Filesort。
Filesort優化
通過建立合適的索引能夠減少Filesort的出現，但是在某些情況下，無法完全讓Filesort消失，此時只能想辦法加快Filesort的操作。
Filesort的兩種排序演算法：
1.兩次掃描演算法
首先根據條件取出排序欄位和行指標資訊，之後在排序區sort buffer中排序。這種排序演算法需要訪問兩次資料，第一次獲取排序欄位和行指標資訊，第二次根據行指標獲取記錄，第二次讀取操作可能會導致大量隨即I/O操作。優點是排序的時候記憶體開銷較小。
2.一次掃描演算法
一次性取出滿足條件的行的所有欄位，然後在排序區sort buffer中排序後直接輸出結果集。排序的時候記憶體開銷比較大，但是排序效率比兩次掃描演算法要高。
根據兩種排序演算法的特性，適當加大系統變數max_length_for_sort_data的值，能夠讓MySQL選擇更優化的Filesort排序演算法。並且在書寫SQL語句時，只使用需要的欄位，而不是SELECT * 所有的欄位，這樣可以減少排序區的使用，提高SQL效能。
參考
《深入淺出MySQL》
推薦閱讀
