MySQL中如何高效優化ORDER BY語句

本篇文章我們將瞭解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.排序字段順序與索引列順序不一致,無法利用索引排序

畫外音：這條是針對組合索引而言的，我們都知道使用組合索引必要要遵循

排序字段順序與索引列順序不一致,無法利用索引排序：

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