MySQL 高效分頁優化：游標法與相對偏移實戰（雅虎最佳實踐）

核心問題

EXPLAIN SELECT * FROM message ORDER BY id DESC LIMIT 100000, 20;
-- 掃描 100,020 行，僅返回 20 行，隨 offset 增大線性惡化

方案一：僅「上一頁/下一頁」—— ID 游標法（最優）

-- 當前頁 min_id=9500, max_id=9527

-- 下一頁
SELECT * FROM message 
WHERE id < 9500 
ORDER BY id DESC 
LIMIT 20;

-- 上一頁
SELECT * FROM message 
WHERE id > 9527 
ORDER BY id ASC  -- 應用層反轉
LIMIT 20;

• 固定掃描 20 行，性能 O(1)
• 適用：移動端無限滾動、Feed 流

方案二：支援任意頁碼—— 相對偏移優化

-- 當前第 10 頁，跳轉第 8 頁（前翻 2 頁 = 40 行偏移）
SELECT * FROM message 
WHERE id > 9527 
ORDER BY id ASC 
LIMIT 40, 20;   -- 掃描 60 行

-- 跳轉第 13 頁（後翻 3 頁 = 60 行偏移）
SELECT * FROM message 
WHERE id < 9500 
ORDER BY id DESC 
LIMIT 60, 20;   -- 掃描 80 行

• 偏移量 = |目標頁 - 當前頁| × 頁大小
• 前端需維護當前頁 min_id/max_id

方案三：延遲關聯（複雜查詢適用）

SELECT m.* FROM message m
JOIN (
  SELECT id FROM message 
  WHERE status=1 ORDER BY id DESC LIMIT 100000, 20
) tmp USING (id);

• 子查詢走覆蓋索引 Using index，主查詢僅 20 次主鍵回表

60 萬行實測對比

選型決策樹

需要跳轉任意頁？
  ├─ 否 → 游標法（最快、最簡單）
  └─ 是 → 查詢是否複雜（多表/多條件）？
          ├─ 否 → 相對偏移法
          └─ 是 → 延遲關聯法