MySQL ORDER BY 排序機制原理與優化實戰指南

MySQL ORDER BY 排序機制原理與優化實戰指南

ORDER BY 是 SQL 查詢中常見的排序操作，其執行效率直接影響查詢效能。本文系統闡述 MySQL 排序的三種執行方式、Filesort 算法演進、JOIN 場景下的排序策略，並給出針對性優化建議。

一、ORDER BY 執行原理深度解析

1. 利用索引有序性避免排序（最優方案）

當 ORDER BY 子句的欄位順序與查詢執行計劃所用索引的索引鍵（或最左前綴）完全一致，且訪問類型為 range、ref 或 index 時，MySQL 可直接按索引順序讀取資料，省去實際排序操作。需注意：

• MySQL 單表查詢最多使用一個索引，若 WHERE 已用索引，排序則無法再利用索引；
• 聯接查詢中，若 ORDER BY 僅引用第一張表欄位，先對第一張表 Filesort 再聯接；否則需建立臨時表聯接後再排序。

實戰案例驗證

測試表 test 擁有聯合唯一索引 u(addtime, a, b)：

-- 查詢1：ORDER BY a, b 符合索引最左前綴，Extra 顯示 Using where（無 filesort）
explain select * from test where addtime='2015-10-13 15:38:32' order by a, b;

-- 查詢2：ORDER BY a, b, c 超出索引覆蓋範圍，Extra 顯示 Using filesort
explain select * from test where addtime='2015-10-13 15:38:32' order by a, b, c;

觸發 Filesort 的常見場景

• WHERE 與 ORDER BY 使用不同索引；
• 掃描行數過多且未使用覆蓋索引；
• 索引欄位混用 ASC/DESC；
• WHERE 或 ORDER BY 中對索引欄位使用表達式/函數；
• 滿足最左前綴但 WHERE 使用範圍查詢；
• LEFT JOIN 以右表欄位排序。

2. Filesort 算法演進：雙路 vs 單路排序

當無法利用索引排序時，MySQL 在 sort_buffer_size 分配的記憶體中進行排序：

• 雙路排序（舊版）：僅取出排序鍵與 RowID，排序後再回表讀取完整行，產生大量隨機 I/O；
• 單路排序（MySQL 4.1+）：一次性取出所有查詢欄位在排序區排序，以空間換時間，減少 I/O 但佔用更多 sort_buffer。

MySQL 通過比較 max_length_for_sort_data 與查詢欄位總長度決定使用哪種算法：欄位總長度較小時採用單路排序。

3. 臨時表與 Filesort 的關係

• 僅引用第一張表排序：Using filesort；
• 需聯接後排序：Using temporary; Using filesort。

關鍵澄清：filesort 並非一定寫磁盤臨時檔，而是「無法用索引排序需實際排序」的代稱；其與磁盤臨時表無直接綁定關係。

4. JOIN 排序三種執行策略

1. 索引有序輸出：Extra 為 NULL；
2. 第一張非常數表 Filesort：首行 Extra 顯示 Using filesort；
3. 結果存入臨時表再 Filesort：首行 Extra 顯示 Using temporary; Using filesort。

二、ORDER BY 優化實戰策略

1. 調大 max_length_for_sort_data：在記憶體充足時提高閾值，誘導 MySQL 選擇單路排序算法，減少隨機 I/O。
2. 精簡 SELECT 欄位：移除不必要的返回欄位，使總長度符合 max_length_for_sort_data 限制，避免因強行增大參數導致分段排序反而降效。
3. 適度增大 sort_buffer_size：減少排序分段與臨時表交換。注意：此為 Connection 級參數，高併發下過大易耗盡記憶體；傳聞超過 2MB 會改用 mmap() 分配記憶體，效率可能下降。