MySQL 排序原理深度解析：從索引優化到 Filesort 實戰避坑

MySQL 排序原理深度解析：從索引優化到 Filesort 實戰避坑

ORDER BY、GROUP BY、DISTINCT 等操作均會觸發排序。理解排序機制、掌握索引消除排序的條件、識別 Using filesort 的性能陷阱，是 SQL 調優的核心技能。

一、索引消除排序的黃金法則

索引本身有序，若查詢的排序列與索引列順序、方向完全一致，且 WHERE 條件為索引前綴等值匹配，即可直接利用索引順序輸出，避免排序開銷。

✅ 可利用索引避免排序

-- 假設索引 key1(key_part1, key_part2)
SELECT * FROM t1 ORDER BY key_part1, key_part2;
SELECT * FROM t1 WHERE key_part1 = 100 ORDER BY key_part2;
SELECT * FROM t1 WHERE key_part1 > 100 ORDER BY key_part1 ASC;
SELECT * FROM t1 WHERE key_part1 = 1 AND key_part2 > 2 ORDER BY key_part2;

❌ 無法利用索引排序

-- 排序列跨多個索引
SELECT * FROM t1 ORDER BY key_part1, key_part2, key2;
-- 排序順序與索引列順序不一致
SELECT * FROM t1 ORDER BY key_part2, key_part1;
-- 混合升降序
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
-- 範圍查詢導致後續索引列失效
SELECT * FROM t1 WHERE key_part1 > 100 ORDER BY key_part2;

二、Filesort 三大算法演進

當無法用索引排序時，MySQL 在 sort_buffer_size 內存區執行 Filesort（不一定落盤）。演進歷程：

三、排序不一致經典案例與修復

案例 1：MySQL 5.6 升級後分頁重複

現象： ORDER BY c1 LIMIT 0,3 與 LIMIT 3,3 同時出現 id=4 記錄。
根因： 5.6 引入優先隊列排序（堆排序），c1=2 有 3 條記錄，堆排序非穩定導致相同鍵值順序翻轉。
修復： ORDER BY c1, id 引入唯一主鍵作為 tie-breaker，保證排序確定性。

案例 2：SELECT 列不同導致順序翻轉

現象： 同樣 ORDER BY status，SELECT id,status,c1,c2 與 SELECT id,status 結果順序不同。
根因： 前者元組長度超過 max_length_for_sort_data 走常規排序，MySQL 為優化回表 I/O 會先對 row_id 排序再批量讀取，改變了相同 status 值的相對順序；後者走優化排序，保持原序。
修復： 調大 max_length_for_sort_data（如 2048）或顯式 ORDER BY status, id。

四、自定義排序函數實戰

FIELD()、INSTR()、LOCATE() 可實現按指定 ID 集合順序輸出：

-- 按 2,3,5,4 順序優先，其餘按 ID 升序
SELECT * FROM `user` ORDER BY FIELD(id, 2,3,5,4) DESC, id ASC LIMIT 10;
-- 等價 INSTR/LOCATE 寫法（注意字串構造順序）
SELECT * FROM `user` ORDER BY INSTR('4,5,3,2', id) DESC LIMIT 10;

五、調優檢查清單

• EXPLAIN 出現 Using filesort → 檢查索引覆蓋與排序列順序
• 調大 sort_buffer_size（會話級，建議 2-8MB）與 read_rnd_buffer_size 減少臨時文件
• 分頁查詢務必加唯一鍵 ORDER BY col, pk 避免 5.6+ 重複/缺失
• 大字段排序考慮只排主鍵再回表，或使用覆蓋索引

掌握排序內核，才能寫出高性能、行為確定的 SQL。建議在測試環境用 OPTIMIZER_TRACE 觀察實際排序算法選擇，做到心中有數。