MySQL ORDER BY 排序完整指南：語法、多欄位排序、索引優化與分頁陷阱

ORDER BY 決定結果集輸出順序，也是分頁、Top-N、報表輸出的關鍵。本文涵蓋語法變體、多欄位排序規則、Filesort 機制、索引消除排序條件、LIMIT 配合大偏移量優化與常見陷阱。

一、語法與排序規則

SELECT col1, col2 FROM t
ORDER BY col1 ASC, col2 DESC, col3;  -- 預設 ASC

-- 表達式排序
ORDER BY LENGTH(name) DESC;
ORDER BY (CASE status WHEN 1 THEN 0 ELSE 1 END), created_at;  -- 自定義優先級

-- NULL 排序控制（8.0+）
ORDER BY col IS NULL, col ASC;  -- NULL 最後
ORDER BY col IS NULL DESC, col ASC;  -- NULL 最前

二、Filesort：排序的執行機制

當無法利用索引順序直接輸出時，MySQL 會啟動 Filesort：

• 雙路排序（舊版）：讀取排序欄位 + 行指針 → 排序 → 再回表讀取選欄位。I/O 兩次。
• 單路排序（新版預設）：一次讀取所有選欄位 → 內存排序 → 直接輸出。佔用 sort_buffer_size，超量則用磁碟臨時表（Created_tmp_disk_tables）。

EXPLAIN 中 Extra: Using filesort 表示觸發。

三、索引消除排序（Index Order By）

若 ORDER BY 欄位順序、方向與索引完全一致（或反向），且查詢符合最左前綴，則可直接按索引掃描輸出，零排序開銷。

-- 索引 (a, b, c)
ORDER BY a, b, c;           -- ✅ 完全匹配
ORDER BY a DESC, b DESC, c DESC;  -- ✅ 反向匹配（8.0+）
ORDER BY a, b;              -- ✅ 前綴匹配
ORDER BY b, a;              -- ❌ 順序不符
ORDER BY a, c;              -- ❌ 跳過 b
WHERE a = 1 ORDER BY b, c;  -- ✅ a 為常數，等同前綴匹配

四、LIMIT 大偏移量優化

-- ❌ 慢：掃描 100 萬行再丟棄 99 萬
SELECT * FROM t ORDER BY id LIMIT 1000000, 20;

-- ✅ 快：覆蓋索引定位主鍵，再回表
SELECT * FROM t
WHERE id >= (SELECT id FROM t ORDER BY id LIMIT 1000000, 1)
ORDER BY id LIMIT 20;

-- ✅ 或使用延遲關聯
SELECT t.* FROM t
JOIN (SELECT id FROM t ORDER BY id LIMIT 1000000, 20) tmp USING (id)
ORDER BY t.id;

五、常見陷阱

1. 未加 ORDER BY 的 LIMIT 結果不確定：無序集合取前 N 行，每次可能不同。永遠加 ORDER BY。
2. 字符集排序規則影響順序： utf8mb4_general_ci vs utf8mb4_bin，大小寫、重音符處理不同。跨表連接時確保排序規則一致。
3. 排序欄位含 NULL 且未控制位置：導致分頁邊界數據跳動。用 col IS NULL 固定 NULL 位置。
4. UNION 預設去重會排序： UNION ALL 保留順序且更快，除非業務需去重。

六、最佳實踐清單

• ✅ 所有分頁/Top-N 查詢強制 ORDER BY 唯一鍵（如主鍵）。
• ✅ 高頻排序查詢建立對應複合索引，實現 Using index 消除 Filesort。
• ✅ sort_buffer_size 設定 2~4MB（會話級），避免磁碟臨時表。
• ✅ 大偏移分頁改用「覆蓋索引定位 + 回表」或「游標式分頁（WHERE id > last_id）」。
• ✅ 監控 Sort_merge_passes、Created_tmp_disk_tables 指標，發現排序落盤及時優化。

排序看似簡單，實則是索引設計、記憶體管理、分頁策略的綜合考驗。將 ORDER BY 視為效能優化的第一現場，系統性解決 Filesort 與大偏移問題。