SQL 分頁查詢三大模式：OFFSET、Keyset (Seek)、游標分頁效能對比

分頁痛點

SELECT * FROM t LIMIT 100000, 20 需掃描並丟棄前 10 萬列，深分頁效能線性惡化。

模式一：OFFSET/LIMIT (傳統)

SELECT * FROM articles WHERE cat_id=5 ORDER BY id DESC LIMIT 20 OFFSET 100000;

• 優點：語法簡單、支援任意頁跳轉。
• 缺點：深分頁全表掃描、filesort、記憶體壓力大。
• 適用：總資料 < 10 萬、或僅前幾頁。

模式二：Keyset Pagination / Seek Method (推薦)

利用唯一排序鍵 (PK/UK) 記住上一頁邊界值：

-- 第一頁
SELECT * FROM articles WHERE cat_id=5 ORDER BY id DESC LIMIT 20;

-- 下一頁 (假設上一頁最小 id = 99980)
SELECT * FROM articles WHERE cat_id=5 AND id < 99980 ORDER BY id DESC LIMIT 20;

-- 上一頁 (假設上一頁最大 id = 100000)
SELECT * FROM articles WHERE cat_id=5 AND id > 100000 ORDER BY id ASC LIMIT 20;

• 優點：索引範圍掃描 O(log N + page_size)、無深分頁罰則、適合無限捲軸/Feed 流。
• 缺點：無法直接跳到第 N 頁、需維護邊界游標、排序鍵必須唯一。
• 適用：時間線、動態牆、API 分頁、大資料集。

模式三：游標分頁

資料庫端維護伺服器端游標：

-- MySQL
DECLARE cur CURSOR FOR SELECT * FROM articles WHERE cat_id=5 ORDER BY id DESC;
OPEN cur;
FETCH NEXT 20 FROM cur;  -- 重複呼叫

• 優點：狀態在 Server、網路傳輸最小。
• 缺點：連線佔用久、鎖爭用、不支援 HTTP 無狀態架構。
• 適用：批次作業、ETL、報表匯出。

效能對比 (百萬級測試)

總計筆數取得

-- 精確但較慢
SELECT COUNT(*) FROM articles WHERE cat_id=5;

-- 近似快速 (MyISAM/InnoDB 統計資訊)
SHOW TABLE STATUS LIKE 'articles';  -- Rows 欄位

-- 應用層快取總數，定期異步更新

架構建議

1. API 回傳 next_cursor (Base64 編碼邊界值) 而非 page_number。
2. 前端無限捲軸 → Keyset；後台管理分頁 → OFFSET (資料少) 或 Keyset + 快取總數。
3. 複合排序 (如 ORDER BY created_at DESC, id DESC) 需將複合值編碼為游標。

Keyset 分頁是大資料集高效分頁的標準解法，犧牲「任意跳頁」換取穩定低延遲。