共計 2935 個字符，預計需要花費 8 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

這篇文章給大家介紹如何在 Mysql 中優化 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 中優化 order by 語句就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。