MySQL filesort 深度解析：原理、觸發條件與優化實戰

在 EXPLAIN 輸出中出現 Using filesort 往往意味著查詢性能隱患。本文深入剖析 MySQL 兩種排序實現方式、filesort 觸發場景、雙路/單路排序算法差異，並給出參數調優與 SQL 改寫的實戰優化指南。

一、MySQL 兩種排序實現機制

⚠️ 關鍵限制：MySQL 單表查詢只能使用一個索引。若 WHERE 已用上索引，ORDER BY 通常無法再利用索引，從而觸發 filesort。

二、利用索引排序的嚴格條件（避免 filesort）

1. 覆蓋索引：SELECT 列必須在索引中

-- 表 test 有聯合索引 idx_rdate (rdate, inventid, customerid)
EXPLAIN SELECT inventid FROM test WHERE rdate='2011-12-14' ORDER BY inventid, customerid;
-- Using index ✅

EXPLAIN SELECT * FROM test WHERE rdate='2011-12-14' ORDER BY inventid, customerid;
-- Using filesort ❌ （SELECT * 不在索引覆蓋範圍）

2. ORDER BY 列順序、方向必須與索引完全一致

• 索引 (rdate, inventid) → ORDER BY rdate, inventid ✅
• ORDER BY inventid, rdate ❌ 順序不符
• ORDER BY rdate ASC, inventid DESC ❌ 混合 ASC/DESC

3. WHERE + ORDER BY 滿足最左前綴

WHERE rdate='2011-12-16' ORDER BY inventid;  -- 索引 (rdate, inventid) ✅
WHERE rdate > '2011-12-16' ORDER BY inventid; -- 範圍查詢破壞最左前綴 ❌ (Using filesort)

4. 其他觸發 filesort 場景

• 多表 JOIN 時 ORDER BY 非驅動表列
• WHERE/ORDER BY 列使用函數/表達式（如 ORDER BY UPPER(name)）
• LEFT JOIN 時按右表列排序

三、filesort 內部算法：雙路 vs 單路排序

MySQL 通過比較 max_length_for_sort_data 與查詢返回列總長度自動選擇算法。

四、優化 filesort 實戰三板斧

1. 增大 max_length_for_sort_data 強制單路排序

[mysqld]
max_length_for_sort_data = 4096  # 默認 1024，適當增大

案例：某同事 DB 大量排序等待，調大此參數後負載驟降、響應提速。

2. 精簡 SELECT 列，減少返回列總長度

避免 SELECT *，只取業務必需列，使總長度落入 max_length_for_sort_data 範圍，自然觸發單路排序。

3. 增大 sort_buffer_size 減少磁盤臨時表

[mysqld]
sort_buffer_size = 4M  # 每線程獨享，視內存與併發調整

排序數據超過 sort_buffer_size 會分段寫磁盤臨時表（多路合併排序），劇增 I/O。增大該值可減少分段次數。

五、索引優化：從根源消除 filesort

• 為 WHERE + ORDER BY 組合建立聯合索引，順序遵循「等值條件列 → 範圍條件列 → 排序列」。
• 覆蓋索引包含所有 SELECT 列，實現 Using index 雙重收益。
• 避免在索引列上使用函數/表達式。

總結

filesort 非洪水猛獸，但預示著額外 CPU/IO 開銷。優化優先級：索引覆蓋消除排序 > 參數調優單路排序 > 精簡列減少排序成本。配合 EXPLAIN 觀察 Extra 列，針對性調優，事半功倍。