MySQL LIMIT 分頁優化實戰：避免深分頁性能崩潰的三大策略

LIMIT 基礎語法與陷阱

-- 基礎語法
LIMIT offset, count
LIMIT count OFFSET offset

-- 陷阱：offset 從 0 開始，非 1
LIMIT 0, 10  -- 第 1 頁
LIMIT 10, 10 -- 第 2 頁

深分頁問題：LIMIT 1000000, 10 需掃描 100 萬行再丟棄，性能隨 offset 線性惡化。

三大優化策略

策略一：延遲關聯 - 先查主鍵，再回表

-- 惡例：直接分頁查詢大表
SELECT * FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 1000000, 10;
-- 掃描 100 萬行，回表 100 萬次，極慢

-- 優例：子查詢只取主鍵，主查詢回表
SELECT o.*
FROM orders o
JOIN (
  SELECT id FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 1000000, 10
) t ON o.id = t.id;
-- 子查詢：覆蓋索引 (status, created_at, id) 只掃索引，無回表
-- 主查詢：只回表 10 次

索引要求：(status, created_at, id) 覆蓋 WHERE + ORDER BY + 主鍵。

策略二：游標/Keyset 分頁 - 記住上一頁最大/最小值

-- 首頁
SELECT * FROM orders WHERE status = 1 ORDER BY created_at DESC, id DESC LIMIT 20;

-- 下一頁：用上一頁最後一行的值作為游標
SELECT * FROM orders 
WHERE status = 1 
  AND (created_at < '2023-01-15 10:30:00' OR (created_at = '2023-01-15 10:30:00' AND id < 100500))
ORDER BY created_at DESC, id DESC LIMIT 20;

優勢：無 offset 掃描，性能恆定。限制：不支援跳頁、需唯一排序鍵（通常加上主鍵做 tie-breaker）。

策略三：覆蓋索引 + LIMIT 0 診斷技巧

-- 快速驗證查詢語法/索引是否生效，不返回數據
EXPLAIN SELECT * FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 0;
-- 或
SELECT * FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 0;
-- 返回 Empty set，但可看到 EXPLAIN 的 type/key/rows

LIMIT 0 用途：開發調試語法、檢查表結構、驗證索引命中、測試優化器行為，近零開銷。

GROUP BY + LIMIT 組合優化

-- 需求：每個用戶最新 1 條訂單，取前 100 用戶
-- 惡例：全表 GROUP BY 再 LIMIT
SELECT user_id, MAX(created_at) FROM orders GROUP BY user_id ORDER BY MAX(created_at) DESC LIMIT 100;

-- 優例：利用索引 (user_id, created_at DESC) 直接取每組第一行
SELECT o.user_id, o.created_at
FROM orders o
JOIN (
  SELECT user_id, MAX(created_at) AS max_dt
  FROM orders
  GROUP BY user_id
  ORDER BY max_dt DESC
  LIMIT 100
) t ON o.user_id = t.user_id AND o.created_at = t.max_dt;

實戰對比測試（千萬級訂單表）

框架層面最佳實踐

• MyBatis：PageHelper 物理分頁自動改寫為延遲關聯模式
• Spring Data JPA：Pageable + 自定義 QueryDsl 實現 Keyset 分頁
• Django ORM：queryset[offset:limit] 為 OFFSET，大分頁需手寫 Raw SQL
• API 設計：?cursor=xxx&limit=20 替代 ?page=50000&size=20

總結：分頁優化決策樹

分頁需求
├─ 支援跳頁/頁碼顯示 → 必須用 OFFSET
│   ├─ 數據量 < 10萬 / offset < 1000 → 直接 LIMIT 可接受
│   └─ 大數據/深分頁 → 延遲關聯 (覆蓋索引 + 子查詢主鍵)
└─ 僅需下一頁/無限滾動 → 強烈推薦游標分頁
    ├─ 唯一排序鍵存在 → 直接用 (排序列, 主鍵) 做游標
    └─ 無唯一排序 → 增加 ROW_NUMBER() 窗口函數生成游標 (MySQL 8.0+)