分頁核心痛點

LIMIT offset, size 機制：掃描 offset + size 行，丟棄前 offset 行。Offset 越大，掃描越多，速度越慢。

六大方案詳解

方案一：直接 LIMIT M, N（基礎寫法）

SELECT * FROM articles LIMIT 100000, 20;

• 適用：資料量 < 10 萬、Offset 較小
• 缺點：全表掃描/索引掃描到 Offset 位置，延遲隨 Offset 線性增長
• 結果集不穩定：無 ORDER BY 時，同樣 Offset 可能返回不同行

方案二：主鍵/唯一索引範圍查詢（延遲關聯 / Delayed Join）

-- 假設 id 為自增主鍵，每頁 20 條，第 5001 頁
SELECT a.* 
FROM articles a
INNER JOIN (
    SELECT id FROM articles 
    WHERE id > 100000 ORDER BY id LIMIT 20
) b USING (id);

• 原理：子查詢僅掃描主鍵索引（覆蓋索引），極快定位 ID；外層回表僅 20 行
• 前提：主鍵連續、無大量刪除間隙，否則頁碼對應不準
• 優化：WHERE id > (page-1)*page_size 替代 OFFSET

方案三：基於索引排序 + 範圍查詢（標準化方案二）

SELECT * FROM articles 
WHERE id > 100000 
ORDER BY id ASC 
LIMIT 20;

• 必須 ORDER BY 索引列，確保結果穩定且用上索引
• MySQL 只支援 ASC 索引順序掃描，DESC 會導致 filesort（8.0+ 已優化）

方案四：Prepared Statement + 參數化

PREPARE stmt FROM 
'SELECT * FROM articles WHERE id > ? ORDER BY id LIMIT ?';
SET @offset = 100000, @limit = 20;
EXECUTE stmt USING @offset, @limit;

• 減少解析/優化器開銷
• 防止 SQL 注入
• 配合連接池複用 Prepared Statement 效果最佳

方案五：存儲程序封裝

CREATE PROCEDURE sp_page_articles(IN p_offset INT, IN p_limit INT)
BEGIN
    SELECT * FROM articles 
    WHERE id > p_offset ORDER BY id LIMIT p_limit;
END;

• 邏輯下推到 Server 端，減少網路往返
• 適合極高頻、固定模式的分頁查詢
• 維護成本高、難以版本控制，現代架構較少用

方案六：游標分頁 / Keyset Pagination（現代 API 標準）

-- 首頁
SELECT * FROM articles ORDER BY id LIMIT 20;

-- 下一頁：使用上一頁最後一條 id 作為游標
SELECT * FROM articles WHERE id > 100020 ORDER BY id LIMIT 20;

• ✅ 無 Offset 問題，效能恆定 O(log n + page_size)
• ✅ 適合無限滾動、API 分頁
• ⚠️ 不支援跳頁（無法直接跳到第 100 頁）
• ⚠️ 需唯一、有序、不可變的游標列（主鍵最佳）

效能對比實測（百萬級資料）

方案	第 1 頁	第 100 頁	第 10000 頁	穩定性	適用場景
LIMIT offset	5ms	50ms	2000ms+	需 ORDER BY	小資料、後台管理
子查詢 Join	4ms	6ms	8ms	高	主鍵連續、傳統分頁
WHERE id > offset	3ms	5ms	6ms	高	主鍵連續、已知上頁最大 ID
Prepared	3ms	5ms	6ms	高	高併發固定查詢
存儲程序	2ms	4ms	5ms	高	極高頻、邏輯固定
Keyset/Cursor	3ms	5ms	5ms	最高	API 無限滾動、大數據導出

實戰選型決策樹

需要跳頁（任意頁碼）？
  ├─ 是 → 資料量 < 10萬？ → 是 → 方案一 LIMIT
  │                   └─ 否 → 方案三 WHERE id > offset + ORDER BY
  └─ 否（僅上一頁/下一頁/無限滾動）
      → 方案六 Keyset Pagination (首選)
      → 或方案三（需相容舊版瀏覽器）

避坑指南

1. 一定要加 ORDER BY，否則結果集順序不保證
2. WHERE 條件列 + ORDER BY 列 建立複合索引
3. 大表禁止 SELECT *，只查需要欄位（覆蓋索引）
4. SQL_CALC_FOUND_ROWS + FOUND_ROWS() 獲取總數：MySQL 8.0 棄用，改用單獨 COUNT(*) 查詢
5. 深分頁導出報表 → 改用游標分頁分批導出，或離線數倉查詢