MySQL 索引優化 ORDER BY 實戰：五大場景、聯合索引設計與 EXPLAIN 驗證指南

索引不僅加速 WHERE，更能消除 ORDER BY 的 filesort。本文總結五大常見 ORDER BY 場景的索引設計模式、易犯錯誤與 EXPLAIN 驗證要點，助你寫出零排序開銷的高效 SQL。

建索引五大鐵律（先避坑）

1. 合理索引加速讀，濫建索引拖慢寫：每新增一個索引，INSERT/UPDATE/DELETE 皆需額外維護 B+ 樹。
2. 索引列出現在 WHERE / JOIN / ORDER BY / GROUP BY，不在 SELECT 列表（覆蓋索引除外）。
3. 優先考慮 MyISAM 場景（本文案例基於 MyISAM），InnoDB 叢集索引特性不同，需結合主鍵設計。
4. SQL/架構優化到極限仍有瓶頸，才引入 Redis/Memcached 等分散式快取。
5. 習慣用 EXPLAIN 驗證：關注 type、key、rows、Extra（Using filesort、Using index）。

五大 ORDER BY 索引優化場景

1. 純 ORDER BY + LIMIT

SELECT col1, col2 FROM t ORDER BY sort_col LIMIT offset, N;

✅ 單列索引 (sort_col) 即可避免 filesort，利用索引有序性直接取前 N 行。

2. WHERE + ORDER BY + LIMIT（等值查詢）

SELECT ... FROM t WHERE col_x = ? ORDER BY sort_col LIMIT offset, N;

❌ 單建 (sort_col)：WHERE 用不到索引，或用到但仍需 filesort。
✅ 聯合索引 (col_x, sort_col)：WHERE 精確匹配 col_x，ORDER BY 直接利用索引次序，Extra: Using index 或 Using where; Using index。

3. WHERE + IN + ORDER BY + LIMIT（多值匹配）

SELECT ... FROM t WHERE col_x IN (?, ?, ...) ORDER BY sort_col LIMIT offset, N;

⚠️ 難點：IN 屬於範圍查詢，MySQL 無法在聯合索引 (col_x, sort_col) 上同時滿足 WHERE 與 ORDER BY，Extra 會出現 Using where; Using filesort。
💡 實測對比：
• UNION ALL 拆分：SELECT ... WHERE col_x=? ORDER BY sort_col LIMIT ... UNION ALL ... + 索引 (col_x, sort_col) → 理論可行，但實測效率反而更低（多次掃描、臨時表合併開銷大）。
• 強制指定索引：FORCE INDEX (idx_col_x_sort_col) 讓優化器走索引掃描 + filesort，大多數場合效果更好。
• 應用層分頁：先查符合 IN 條件的主鍵 ID 列表，應用層排序分頁，再 WHERE id IN (...) 回表。

4. ORDER BY 列上使用函數/運算式

SELECT * FROM t ORDER BY YEAR(date_col) LIMIT 30;

❌ 索引失效：函數破壞列有序性，必須全表掃描 + filesort。
✅ 改寫：WHERE date_col >= '2024-01-01' AND date_col < '2025-01-01' ORDER BY date_col，走 (date_col) 索引。

5. WHERE + 多欄位 ORDER BY + LIMIT

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

❌ 直覺建 (x, y, uid) → 無法同時滿足 WHERE 與 ORDER BY。
✅ 正確順序 (uid, x, y)：WHERE 先定 uid（等值），ORDER BY 依索引次序 x, y 直接有序輸出。
原理：MySQL 優化器要求 WHERE 等值列在前，ORDER BY 列緊隨其後，方向一致（全 ASC 或全 DESC）。

實戰驗證清單

1. 建立候選索引。
2. EXPLAIN SELECT ... 觀察 key 是否命中、Extra 是否出現 Using filesort。
3. FLUSH STATUS; SELECT ...; SHOW SESSION STATUS LIKE 'Sort%'; 確認 Sort_rows、Sort_scan 為 0。
4. 大表場景對比 Handler_read_* 計數器，量化 I/O 降低幅度。

總結：索引設計口訣

WHERE 等值在前，ORDER BY 緊跟後，方向一致別混淆，IN 範圍最麻煩，強制索引或應用分頁來解憂。