MySQL ORDER BY 實現機制深度分析：索引順序 vs Filesort 圖解

測試表結構

CREATE TABLE A(c1 INT PRIMARY KEY, c2 CHAR(2), c3 VARCHAR(16), c4 DATETIME) ENGINE=MyISAM;
CREATE TABLE B(c1 INT PRIMARY KEY, c2 CHAR(2), c3 VARCHAR(16), KEY B_c2_ind(c2)) ENGINE=MyISAM;

方式一：索引順序直接返回（零排序開銷）

EXPLAIN SELECT A.* FROM A,B
WHERE A.c1 > 2 AND A.c2 < 5 AND A.c2 = B.c2
ORDER BY A.c1;

執行計劃：type=range, key=PRIMARY, Extra=Using where（無 Filesort）

原理：ORDER BY A.c1 與主鍵索引順序一致，按索引掃描天然有序。

流程圖：

索引掃描 (PRIMARY) → 滿足 WHERE → 直接輸出有序結果

方式二：Filesort 內存排序

EXPLAIN SELECT A.* FROM A,B
WHERE A.c1 > 2 AND A.c2 < 5 AND A.c2 = B.c2
ORDER BY A.c2;

執行計劃：Extra=Using where; Using filesort

流程圖：

索引/全表掃描 → 滿足 WHERE → 收集 (sort_key, row_ptr) → sort_buffer 排序 → 輸出

方式三：臨時表 + Filesort（JOIN 結果排序）

EXPLAIN SELECT A.* FROM A,B
WHERE A.c1 > 2 AND A.c2 < 5 AND A.c2 = B.c2
ORDER BY B.c3;

執行計劃：Extra=Using where; Using temporary; Using filesort

原因：ORDER BY 列來自非驅動表，MySQL 必須先構建臨時表存放 JOIN 結果，再排序。

流程圖：

JOIN 執行 → 寫入臨時表 → Filesort 臨時表 → 輸出

Filesort 兩種算法對比

關鍵參數 max_length_for_sort_data

• 默認 1024 字節
• 查詢選取列總長度 ≤ 此值 → 元組排序（快，但佔 buffer）
• 超過 → 雙值排序（省 buffer，但雙回表）
• 調大該值可強制元組排序，需配合增大 sort_buffer_size

優化實戰建議

1. EXPLAIN 必看 Extra：Using filesort 即需優化
2. 索引覆蓋 ORDER BY：建立 (where_col, order_col) 聯合索引
3. 減少 SELECT 列：觸發元組排序優化
4. 調大 sort_buffer_size：避免磁盤臨時文件
5. 分散 tmpdir：多物理盤並行寫臨時文件