MySQL 排序原理深度解析：filesort 算法演進、索引優化排序、分頁一致性陷阱與自定義排序實戰

排序無處不在

不僅 ORDER BY，GROUP BY、DISTINCT、UNION 等都隱含排序操作。

一、索引優化排序：避免 filesort 的黃金法則

假設索引 KEY k1(a,b), KEY k2(c)

✅ 可利用索引有序性避免排序

SELECT * FROM t ORDER BY a, b;
SELECT * FROM t WHERE a = 1 ORDER BY b;
SELECT * FROM t WHERE a > 1 ORDER BY a ASC;
SELECT * FROM t WHERE a = 1 AND b > 2 ORDER BY b;

❌ 無法利用索引排序

二、filesort 三代算法演進

執行計劃出現 Using filesort 觸發，不一定落盤，取決於 sort_buffer_size 與結果集大小。

1. 常規排序

1. 取滿足 WHERE 的行
2. 提取 (主鍵, 排序列) 進 sort_buffer
3. Buffer 滿 → 快排 + 歸並落盤臨時文件
4. 掃描有序 (pk, sort_key) → 回表取 SELECT 列（隨機 I/O）
5. 優化：回表前先在 read_rnd_buffer_size 內對 pk 排序，轉順序 I/O

2. 優化排序

1. 直接提取 (SELECT 列...) 進 sort_buffer
2. 排序後直接返回，**無回表**
3. 代價：元組更大 → Buffer 容納行數少 → 更易落盤
4. 受 max_length_for_sort_data 限制（元組長度超限強制退回常規）

3. 優先隊列排序 —— MySQL 5.6+ 針對 ORDER BY ... LIMIT M,N

• 堆排序實現，空間複雜度 O(M+N)
• 升序用大頂堆，降序用小頂堆
• 極大減少 LIMIT 10 OFFSET 1000000 這類大偏移排序開銷
• ⚠️ 堆排序不穩定：相同 sort_key 相對順序不可預期

三、分頁一致性陷阱實測

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

CREATE TABLE t1(id INT PK, c1 INT, c2 VARCHAR(128));
INSERT ... (c1=2 有 3 條記錄)

-- 第 1 頁
SELECT * FROM t1 ORDER BY c1 LIMIT 0,3;
-- 第 2 頁
SELECT * FROM t1 ORDER BY c1 LIMIT 3,3;

現象： id=4 的行同時出現在兩頁（5.6 優先隊列 + 不穩定排序導致）。

修復： ORDER BY c1, id 加入唯一列保證穩定性。

案例 2：同一查詢，只因 SELECT 列不同導致排序結果不同

SELECT id,status,c1,c2 FROM t2 FORCE INDEX(c1) WHERE c1>='b' ORDER BY status;
SELECT id,status FROM t2 FORCE INDEX(c1) WHERE c1>='b' ORDER BY status;

根因： 前者元組超長觸發常規排序（回表前 pk 排序改變相對序），後者觸發優化排序（保持排序後順序）。

修復： 調大 max_length_for_sort_data 統一走優化排序，或在 ORDER BY 加唯一列。

四、自定義排序三大函數實戰

需求：按指定 ID 順序 (2,3,5,4) 優先，其餘自然序

FIELD() —— 最直觀

SELECT * FROM `user` ORDER BY FIELD(id, 2,3,5,4) ASC;
-- 結果順序：2,3,5,4,1,6...
SELECT * FROM `user` ORDER BY FIELD(id, 2,3,5,4) DESC;
-- 結果順序：1,6...,4,5,3,2

INSTR() / LOCATE() —— 字符串位置排序

-- INSTR(str, substr) 返回 substr 在 str 中首次位置
SELECT * FROM `user` ORDER BY INSTR('2,3,5,4', id) DESC;
-- 位置越大（越靠後）排越前 → 4,5,3,2,1,6...

-- LOCATE(substr, str) 參數順序相反
SELECT * FROM `user` ORDER BY LOCATE(id, '2,3,5,4') DESC;
-- 同 INSTR 效果

最佳實踐模式

-- 目標順序 (2,3,5,4) 置頂，其餘按 id 升序
SELECT * FROM `user` 
ORDER BY 
    CASE WHEN id IN (2,3,5,4) THEN 0 ELSE 1 END,  -- 分組優先級
    FIELD(id, 2,3,5,4),                            -- 組內指定序
    id;                                             -- 其餘自然序

五、核心參數調優清單

總結

1. 優先用索引消除排序（覆蓋索引最佳）
2. 理解三代 filesort 算法觸發條件與權衡 3. LIMIT M,N 必加唯一列保證分頁穩定 4. 自定義排序用 FIELD() 或 CASE WHEN 組合 5. 參數調優以會話級動態調整為主，全局保守