傳統 OFFSET 分頁的效能崩潰點

SELECT * FROM articles WHERE category='tech' ORDER BY created_at DESC LIMIT 100000, 20;
-- MySQL 必須掃描/排序前 100,020 行，丟棄前 100,000 行，極度浪費 IO/CPU

隨著頁碼增大，延遲線性增長，深分頁 (第 1 萬頁) 甚至超時。

優化方案演進

方案 1：延遲關聯 / 子查詢回表 (最通用)

-- 1. 只掃描索引取 ID (覆蓋索引)
SELECT id FROM articles WHERE category='tech' ORDER BY created_at DESC LIMIT 100000, 20;

-- 2. 應用層拿到 ID 列表，再回表查詳情
SELECT * FROM articles WHERE id IN (id1, id2, ..., id20);

**索引建議**：INDEX idx_cat_created (category, created_at, id) → 子查詢走覆蓋索引，不回表。

方案 2：書籤/游標分頁 (適用無限滾動/翻頁)

-- 首頁
SELECT * FROM articles WHERE category='tech' ORDER BY created_at DESC, id DESC LIMIT 20;

-- 下一頁 (記住最後一筆的 created_at, id)
SELECT * FROM articles 
WHERE category='tech' 
  AND (created_at < '2024-01-15 10:30:00' OR (created_at = '2024-01-15 10:30:00' AND id < 98765))
ORDER BY created_at DESC, id DESC LIMIT 20;

• **無 OFFSET**，索引範圍掃描極快
• **不支援跳頁**，僅適合「下一頁」/無限滾動
• 需 ORDER BY 欄位含唯一鍵 (如 id) 保證順序確定性

方案 3：MySQL 8.0+ 窗口函數 (CTE + ROW_NUMBER)

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY created_at DESC) AS rn
    FROM articles
)
SELECT * FROM ranked WHERE category='tech' AND rn BETWEEN 100001 AND 100020;

優點：單條 SQL、語意清晰；缺點：仍需全分區排序，大資料量不如方案 1/2。

方案 4：應用層快取分頁結果 (讀多寫少)

• 首頁/熱門分頁 → Redis 快取 JSON 結果，TTL 5-30 分鐘
• 寫入時主動失效相關分頁快取 (或用版本號標記)
• 適合：首頁、熱門榜單、分類首頁

模擬「儲存分頁」機制 (文章原文思路現代化)

若業務需支援**任意跳頁**且資料量極大，可建立**分頁快取表**：

CREATE TABLE query_page_cache (
    query_hash CHAR(32) NOT NULL,      -- MD5(sql + params)
    page_num INT NOT NULL,
    ids TEXT NOT NULL,                 -- JSON 陣列或逗號分隔 ID
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (query_hash, page_num)
) ENGINE=InnoDB;

1. 首次查詢 → 計算所有符合條件 ID (或分批) → 切分存入快取表
2. 後續請求 → 直接讀快取表 IDs → WHERE id IN (...) 回表
3. 定時任務清理過期快取 (如 1 小時無存取)

**權衡**：寫入放大換讀取極致快，適合「查詢條件固定、結果集相對穩定」場景。

避坑清單

• LIMIT offset, rows 中 offset 僅接受整數常數，不可用變數/子查詢 (預處理語句除外)
• SQL_CALC_FOUND_ROWS + FOUND_ROWS() 獲取總數，**同樣觸發全掃描**，大表慎用
• 覆蓋索引欄位順序：WHERE 欄位, ORDER BY 欄位, SELECT 欄位
• 字串類型 ID (UUID) 無序，書籤分頁需改用 created_at + id 複合排序