MySQL LIMIT 分頁查詢優化實戰：深度分析偏移量對效能的影響與改寫策略

LIMIT 分頁查詢效能痛點

MySQL 的 LIMIT offset, row_count 語法雖然簡單易用，但在資料量龐大時效能會急劇下降。核心原因在於 MySQL 必須先掃描並捨棄前 offset 筆記錄，再取得所需的 row_count 筆。當 offset 達到數萬甚至數十萬時，查詢時間會從毫秒級飆升至秒級，嚴重影響使用者體驗。

實測環境與基準數據

• 測試環境：Windows 2003 + P4 雙核 3GHz + 4GB RAM + MySQL 5.0.19
• 測試表：yanxue8_visit（含主鍵 vid）
• 每次取 10 筆資料

場景一：小偏移量（offset=10）

結論：小偏移量時，直接使用 LIMIT 即可，無需複雜改寫。

場景二：大偏移量（offset=10,000）

結論：大偏移量時，「先取 ID 再回表」策略顯著優於直接 LIMIT。偏移量越大，優勢越明顯。

優化原理解析

• 直接 LIMIT：全索引掃描至 offset 位置 → 捨棄前 offset 行 → 取 row_count 行。IO 與 CPU 成本隨 offset 線性增長
• 子查詢改寫：內層 SELECT vid ... LIMIT offset, 1 僅掃描主鍵索引（極窄），定位目標 ID → 外層 WHERE vid >= 目標ID LIMIT row_count 直接範圍查詢，避免大量捨棄操作
• 適用條件：表需有有序唯一鍵（如自增主鍵），且查詢順序與該鍵一致

生產環境完整優化方案

1. 游標式分頁（推薦）：前端傳遞上一頁最大 ID，查詢 WHERE id > last_id ORDER BY id LIMIT page_size。完全避免 OFFSET，效能恆定
2. 延遲關聯：SELECT t.* FROM table t INNER JOIN (SELECT id FROM table ORDER BY id LIMIT offset, page_size) tmp ON t.id = tmp.id。利用覆蓋索引減少回表
3. 覆蓋索引：確保查詢欄位包含於複合索引中，避免回表
4. 應用層緩存：熱門頁面（首頁、熱門列表）使用 Redis 等快取，減少資料庫壓力

避坑指南

• 不要盲目套用「子查詢優化」於小偏移量場景，反而增加優化器負擔
• 若無有序唯一鍵，需先評估加索引成本與收益
• 極大資料量（億級+）建議配合分庫分表、搜尋引擎（Elasticsearch）或 OLAP 引擎（ClickHouse）

掌握 LIMIT 優化核心邏輯，結合業務場景選擇適當策略，能讓分頁查詢在百萬級甚至千萬級資料下依然保持毫秒級回應。