MySQL 5.6 LIMIT 工作機制與 ORDER BY LIMIT 優化原理

MySQL 5.6 LIMIT 工作機制與 ORDER BY LIMIT 優化原理

LIMIT 執行流程

1. 優化器根據 WHERE、JOIN、ORDER BY 確定存取路徑 (全表掃描 / 索引掃描 / ref / range 等)。
2. 執行引擎按順序讀取符合條件的行，計數器遞增。
3. 計數器達到 offset + limit 時立即停止，返回結果集。

ORDER BY LIMIT 關鍵優化 (5.6 引入)

問題：早期版本 ORDER BY col LIMIT 10 需先全量排序 (filesort) 再截取，大表極慢。

優化邏輯：

• 若 ORDER BY 欄位有索引，且排序方向與索引順序一致 (或可反向掃描)，優化器直接按索引順序讀取，邊讀邊計數，讀滿 limit 行即停止，完全避免 filesort。
• 若需 OFFSET (如 LIMIT 10000, 10)，仍需跳過前 10000 行，但僅掃描索引葉節點，無需排序。
• 條件：ORDER BY 列必須為索引前綴，且無混合 ASC/DESC (除非 8.0+ 支援降序索引)。

EXPLAIN 關鍵標識

• Using index：覆蓋索引，無需回表。
• Using filesort：未用到索引排序，觸發 filesort (需優化)。

最佳實踐

1. 為高頻 ORDER BY col LIMIT N 查詢建立對應索引。
2. 避免大 OFFSET (深分頁) → 改用游標分頁 (記住上一頁最大 ID，WHERE id > last_id ORDER BY id LIMIT N)。
3. 複合索引時，將 WHERE 等值列放前，ORDER BY 列放後 (最左前綴原則)。

掌握此機制，可寫出極致高效的分頁查詢。