MySQL ORDER BY 兩代排序演算法深度解析與 5.7 優化

三大關鍵參數

• sort_buffer_size：排序緩衝區（預設 2MB）。
• read_rnd_buffer_size：二次排序/隨機讀取緩衝區（預設 2MB）。
• max_length_for_sort_data：單行排序資料最大長度（預設 1024 bytes），超過則退回第一種演算法。

第一代：一般排序（<sort_key, rowid>）

1. 全表掃描取出 排序欄位 + rowid 放入 sort_buffer 排序。
2. 按排序後的 rowid 回表隨機讀取完整行，放入 read_rnd_buffer 返回客戶端。
3. 特點：記憶體省，但需**二次隨機 I/O**。

第二代：冗餘排序（<sort_key, additional_fields>）

1. 全表掃描取出 **所有 SELECT 欄位** 放入 sort_buffer 排序。
2. 直接從緩衝區返回，**無二次回表**。
3. 缺點：佔用大量記憶體，易觸發臨時表落盤。

MySQL 5.7+ 優化：打包排序（<sort_key, packed_additional_fields>）

對變長欄位（如 VARCHAR(255) 實際存 3 字元）進行壓縮打包，大幅減少排序緩衝區佔用，降低落盤機率。優化器追蹤中 sort_mode 顯示 <sort_key, packed_additional_fields> 即表示啟用。

實測驗證

-- 參數確認
SELECT @@sort_buffer_size, @@read_rnd_buffer_size, @@max_length_for_sort_data;

-- 開啟優化器追蹤
SET OPTIMIZER_TRACE="enabled=on", END_MARKERS_IN_JSON=on;
SELECT * FROM t1 WHERE id < 10 ORDER BY id;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

追蹤結果中 filesort_summary.sort_mode 可見具體演算法模式。