MySQL 排序原理深度解析：Filesort 算法、優化策略與一致性陷阱案例

MySQL 排序原理深度解析

排序是數據庫核心功能，ORDER BY、GROUP BY、DISTINCT 均隱含排序。本文涵蓋：索引避免排序條件、Filesort 內部算法演進、關鍵參數、排序一致性陷阱案例。

一、利用索引避免排序

假設表 t1 有索引 key1(key_part1, key_part2)、key2(key2)。

核心原則：索引列順序、排序方向、WHERE 條件類型需與索引定義嚴格對齊。

二、Filesort 三種實現算法演進

測試表：CREATE TABLE t1(id INT PRIMARY KEY, col1 VARCHAR(64), col2 VARCHAR(64), col3 VARCHAR(64), KEY(col1,col2));
SQL：SELECT col1,col2,col3 FROM t1 WHERE col1>100 ORDER BY col2;

1. 常規排序

1. 按 WHERE 篩選記錄。
2. 提取 (id, col2) 放入 sort_buffer。
3. 內存足夠 → 快速排序；不足 → 分塊排序寫臨時文件，再歸並排序合併。
4. 掃描有序 (id, col2)，回表撈取 (col1,col2,col3)（隨機 IO）。
5. 優化：回表前先在 read_rnd_buffer 中對 ID 排序，將隨機 IO 轉順序 IO。

2. 優化排序

• 直接將 (col1,col2,col3) 全字段放入 sort_buffer，排序後直接返回，省去回表。
• 代價：元組更大，sort_buffer 容納行數減少，易觸發臨時文件歸並。
• 受參數 max_length_for_sort_data 控制：元組長度超過該值強制退回常規排序。

3. 優先隊列排序

MySQL 5.6+ 針對 ORDER BY ... LIMIT M, N 引入，基於堆排序：

• 升序用大頂堆，維護最小的 M+N 個元素；降序用小頂堆。
• 空間複雜度 O(M+N)，極大減少 sort_buffer 壓力，小 LIMIT 場景幾乎不產生臨時文件。
• 所有行仍需參與比較，但內存佔用恆定。

三、排序一致性陷阱實戰案例

案例 1：5.5 升 5.6 分頁重複

表 t1(id PK, c1 INT, c2 VARCHAR)，c1 有 3 行值為 2。
ORDER BY c1 LIMIT 0,3 與 LIMIT 3,3 結果中 id=4 重複出現。
原因：5.6 引入優先隊列排序（堆排序），堆排序非穩定，相同 c1 值的行相對順序不固定。
修復：ORDER BY c1, id 引入唯一主鍵保證排序鍵唯一。

案例 2：相同 SQL 僅 SELECT 列不同，排序結果順序不同

表 t2(id PK, status INT, c1 VARCHAR(255) KEY, c2, c3)，c1 索引順序對應 status 為 2,3,2,4。
SQL1：SELECT id,status,c1,c2 ... ORDER BY status → status=2 的兩行順序為 id=5,6。
SQL2：SELECT id,status ... ORDER BY status → 順序為 id=6,5。
原因：SQL1 返回列長度超過 max_length_for_sort_data，使用常規排序，回表前對 ID 排序導致順序變化；SQL2 使用優化排序，保持索引掃描原始相對順序。
修復：增大 max_length_for_sort_data（如 2048）強制優化排序，或在 ORDER BY 加唯一列。