MySQL ORDER BY 索引優化完整指南：避免 filesort 提升排序效能

建立索引核心原則

• 合理索引加速讀取，過多索引拖慢寫入（每次 INSERT/UPDATE/DELETE 需維護所有索引）
• 索引應建在 WHERE、JOIN、ORDER BY 條件欄位，而非 SELECT 選取欄位
• MyISAM 使用 B-Tree 索引較 InnoDB 適合純讀排序場景（但不支援事務）
• 善用 EXPLAIN 分析執行計畫，關注 Using filesort、Using index
• 瓶頸無法用 SQL/索引解決時，考慮 Redis/Memcached 等分散式快取

常見 ORDER BY 優化模式

1. ORDER BY + LIMIT

SELECT col1, col2 FROM tbl ORDER BY sort_col LIMIT offset, limit;

優化：在 sort_col 建立單欄索引即可避免 filesort。

2. WHERE + ORDER BY + LIMIT

SELECT col1, col2 FROM tbl WHERE filter_col = 'val' ORDER BY sort_col LIMIT offset, limit;

錯誤做法：分別在 filter_col、sort_col 建單欄索引 → 仍會 filesort。

正確做法：建立聯合索引 (filter_col, sort_col)。

3. WHERE + IN + ORDER BY + LIMIT（棘手案例）

SELECT col1, col2 FROM tbl WHERE filter_col IN ('v1','v2',...) ORDER BY sort_col LIMIT offset, limit;

問題：聯合索引 (filter_col, sort_col) 在 IN 條件下無法直接用於排序，EXPLAIN 顯示 Using where; Using filesort。

嘗試方案：UNION ALL 拆分

(SELECT col1, col2 FROM tbl WHERE filter_col = 'v1' ORDER BY sort_col LIMIT offset, limit)
UNION ALL
(SELECT col1, col2 FROM tbl WHERE filter_col = 'v2' ORDER BY sort_col LIMIT offset, limit)
...

實測結果：往往效能更差（多次掃描、臨時表合併）。實務上強制使用排序索引或應用層分頁效果較好。

4. 避免在 ORDER BY 欄位使用函數/運算式

-- 錯誤：索引失效
SELECT * FROM tbl ORDER BY YEAR(date_col) LIMIT 30;

-- 正確：範圍查詢可用索引
SELECT * FROM tbl WHERE date_col >= '2024-01-01' AND date_col < '2025-01-01' ORDER BY date_col LIMIT 30;

5. 多欄位排序 + WHERE

SELECT * FROM tbl WHERE uid = 1 ORDER BY x, y LIMIT 10;

直覺索引：(x, y, uid) ❌

最佳索引：(uid, x, y) ✅

原因：MySQL 先套用 WHERE 過濾（uid），再依索引順序 (x,y) 輸出，避免 filesort。

EXPLAIN 關鍵指標對照

實戰檢核清單