ref/range/index 時,MySQL 直接按索引順序返回,零排序開銷,效能最優。sort_buffer_size 記憶體區排序,超出則落磁碟臨時表。每個線程獨享 Sort Buffer,高併發時記憶體壓力大。| 演算法 | 邏輯 | 特點 |
|---|---|---|
| 雙路排序 (雙通道) | 1. 讀取排序欄位 + Row ID 2. Sort Buffer 排序 3. 再按 Row ID 回表讀取其餘欄位 | MySQL 4.1 前唯一算法,兩次 I/O,佔用 Sort Buffer 小 |
| 單路排序 (單通道) | 一次性讀取所有需回傳欄位,在 Sort Buffer 直接排序 | 4.1+ 引入,以空間換時間,減少隨機 I/O,但佔用更多 Sort Buffer |
MySQL 依 max_length_for_sort_data 與 SELECT 列總長度比較決定用哪種:
列總長 < 參數值 → 單路;否則雙路。
SET GLOBAL max_length_for_sort_data = 4096; -- 預設 1024,視記憶體調大
前提:機器記憶體充裕,且 SELECT 列長度可控。過大導致 Sort Buffer 壓力劇增、甚至分段排序反而變慢。
SELECT *,僅取業務必要欄位。max_length_for_sort_data 門檻,誘導優化器選單路排序。SET GLOBAL sort_buffer_size = 4M; -- 預設 256K,連線級參數
關鍵權衡:
• 連線級參數 × 最大併發連線 = 總記憶體佔用上限,切勿盲目設大(如 256M × 1000 連線 = 250GB OOM)。
• 超過 2MB 可能觸發 mmap() 替代 malloc(),反而降低分配效率。
• 建議範圍:256K ~ 4MB,視併發與記憶體容量酌情。
EXPLAIN 確認是否 Using filesort。sort_buffer_size → 減少臨時表分段。Sort_merge_passes 狀態變數,非零表示發生分段,需優化。對於 ORDER BY id LIMIT 100000, 20 類深分頁,先用覆蓋索引取主鍵,再回表,可將掃描行數從 10 萬降至 20,配合上述參數調優效果更佳。