MySQL LIMIT 深分頁優化實戰：從百萬數據測試看延遲優化 100 倍的核心原理

問題本質：OFFSET 掃描機制

LIMIT offset, rows 語法要求 MySQL：

1. 定位到第 offset 行 (需掃描前 offset 行)
2. 返回後續 rows 行

隨著 offset 增大，掃描成本線性增長，百萬偏移 = 百萬行掃描，即使有索引也需遍歷 B+樹葉子節點鏈表。

測試環境與基線數據

實測對比數據 (無 WHERE 條件)

結論： Offset < 100 時優化無優勢甚至更慢；Offset > 100 顯著提升，**Offset 越大優化越明顯**，5萬偏移優化 **7 倍**。

優化核心方案：延遲關聯 / 子查詢定位

原始寫法

SELECT * FROM testtable LIMIT 50290, 10;

優化寫法 (主鍵遞增場景)

SELECT * FROM testtable 
WHERE id >= (SELECT id FROM testtable LIMIT 50290, 1) 
LIMIT 10;

原理：

1. 子查詢 SELECT id FROM testtable LIMIT 50290, 1 僅掃描主鍵索引 (極窄)，定位目標 ID，成本極低
2. 外層 WHERE id >= target_id LIMIT 10 走主鍵範圍查詢 (type=range)，直接定位葉子節點，無全掃描

帶 WHERE 條件場景測試 (leibie=1, 滿足 > 5000 行)

原因分析： 子查詢 SELECT id FROM testtable WHERE leibie=1 LIMIT 5690, 1 仍需掃描二級索引 5690 行，且外層 WHERE id >= ... AND leibie=1 可能導致回表或索引合併失效，優化器選擇不佳。

通用優化策略決策樹

需要深分頁？
  ├─ 否 (Offset < 100) → 直接 LIMIT offset, rows
  └─ 是 → 是否有高選擇性 WHERE 條件？
          ├─ 無 (全表分頁) → 延遲關聯優化 (主鍵遞增) / 游標分頁
          └─ 有 → WHERE 列有覆蓋索引？
                  ├─ 是 → 延遲關聯優化 (子查詢走覆蓋索引)
                  └─ 否 → 游標分頁 (記住上頁最大 ID) / 應用層緩存 / 搜索引擎

四大深分頁優化方案對比

游標分頁最佳實踐 (推薦首選)

-- 第一頁
SELECT * FROM articles WHERE status=1 ORDER BY id DESC LIMIT 10;
-- 記住最後一行 id = 9990

-- 下一頁 (無 OFFSET，極快)
SELECT * FROM articles WHERE status=1 AND id < 9990 ORDER BY id DESC LIMIT 10;

-- 通用模板 (支持任意排序列，需唯一)
SELECT * FROM tbl 
WHERE sort_col > last_sort_val  -- 或 < 視排序方向
  [AND tie_breaker_col > last_tie_val]  -- 排序列非唯一時需加斷鍵列
ORDER BY sort_col [, tie_breaker_col] 
LIMIT 10;

避坑指南

• 主鍵非遞增/有洞： UUID、雪花算法 ID 仍可用游標分頁，不可用「主鍵連續」假設
• ORDER BY 非索引列： 必須 Filesort，深分頁無解，需加索引或改業務
• 子查詢優化器陷阱： 5.6+ 衍生表物化可能導致子查詢物化為臨時表，反而更慢，EXPLAIN 驗證
• COUNT(*) 總數查詢： 深分頁常伴隨總數查詢，MyISAM 秒級，InnoDB 需 EXPLAIN 估算或單獨統計表
• 並發修改導致頁面漂移： 游標分頁天然免疫 (基於 ID 邊界)，OFFSET 分頁會重複/遺漏

總結

LIMIT 深分頁優化的本質是**「避免掃描無用行」**。游標分頁 是架構級最優解：無 OFFSET、穩定毫秒級、天然適配無限滾動。延遲關聯 是存量 SQL 改造最低成本方案，但受限於優化器選擇。應用層緩存/搜索引擎 是高併發熱點數據的終極歸宿。**選型原則：業務允許 → 游標分頁；存量改造 → 延遲關聯驗證後上線；熱點列表 → Redis 緩存；複雜檢索 → ES。** 記住：Offset 是惡魔，Keyset 是天使。