痛點：OFFSET 深分頁性能崩塌

SELECT * FROM content ORDER BY id DESC LIMIT 100000, 20;

MySQL 需掃描 100,020 行、丟棄前 100,000 行，隨偏移量增大耗時線性增長，高併發下極易拖垮數據庫。

原理：LIMIT m,n 執行機制

• 按索引順序掃描 m+n 行，丟棄前 m 行，返回後 n 行。
• 掃描行數 = offset + limit，offset 越大，掃描越多，I/O 與 CPU 開銷成倍增長。

優化思路一：延遲關聯 / 子查詢先取 ID 再回表

子查詢分頁（推薦）

SELECT * FROM `content`
WHERE id <= (
    SELECT id FROM `content`
    ORDER BY id DESC
    LIMIT 100000, 1
)
ORDER BY id DESC LIMIT 20;

執行計劃解析

id=2 (SUBQUERY): type=index, key=PRIMARY, Using index  -- 覆蓋索引掃描，極快
id=1 (PRIMARY):  type=range, key=PRIMARY, rows=20      -- 僅回表 20 行

• 子查詢在**主鍵索引**上完成定位，不回表，僅掃描索引葉子節點。
• 外層查詢利用 id <= 定位值 進行範圍掃描，直接定位到目標頁，僅回表 limit 行。
• 實測效率提升 **14-20 倍**（相對純 LIMIT）。

JOIN 分頁（同等效果）

SELECT t1.* FROM `content` AS t1
JOIN (
    SELECT id FROM `content` ORDER BY id DESC LIMIT 100000, 1
) AS t2 ON t1.id <= t2.id
ORDER BY t1.id DESC LIMIT 20;

• 衍生表 t2 同樣只掃描索引，外層通過 ref 或 range 訪問主表。
• 實測與子查詢性能持平，選擇取決於團隊代碼風格。

優化思路二：基於書籤/游標的「鄰近翻頁」（適用於「上一頁/下一頁」交互）

-- 當前頁最小 id = 9500，最大 id = 9527
-- 下一頁：id < 9500
SELECT * FROM content WHERE id < 9500 ORDER BY id DESC LIMIT 20;

-- 上一頁：id > 9527
SELECT * FROM content WHERE id > 9527 ORDER BY id ASC LIMIT 20; -- 取出後應用層倒序

• 每次僅掃描 limit 行，**完全消除 OFFSET 問題**。
• 缺點：不支持隨機跳頁（如「跳到第 50 頁」），需前端配合「無限滾動」或「載入更多」交互。

優化思路三：相對偏移跳頁（兼容「跳到第 N 頁」交互）

記錄當前頁 min_id、max_id，計算目標頁與當前頁的頁差 delta，轉化為相對偏移：

-- 當前第 10 頁 (max_id=9527)，跳到第 8 頁 (delta=-2)
SELECT * FROM content
WHERE id > 9527
ORDER BY id ASC
LIMIT 20*2, 20;  -- 跳過 40 行，取 20 行

-- 跳到第 13 頁 (delta=+3)
SELECT * FROM content
WHERE id < 9500
ORDER BY id DESC
LIMIT 40, 20;

• 偏移量 = |delta| * page_size，通常較小（用戶多在鄰近頁跳轉）。
• 極端跳轉（首頁↔尾頁）偏移量大，可回退到子查詢方案。

關鍵索引設計

• 分頁欄位**必須建立索引**（主鍵或二級索引）。
• 複合索引順序匹配 ORDER BY：INDEX (status, created_at DESC) 支持 WHERE status=1 ORDER BY created_at DESC。
• 避免 ORDER BY rand()、ORDER BY 非索引列 導致 FileSort。

避坑指南

• 子查詢/衍生表必須用主鍵/唯一鍵定位，非唯一列可能導致邊界行重複/遺漏。
• ASC/DESC 方向一致性：子查詢與外層 ORDER BY 方向相反時需注意邊界條件（<= vs <）。
• 并發寫入導致邊界漂移：高併發寫入場景，頁碼語義本身模糊，建議採用游標分頁或業務接受「近似分頁」。
• 覆蓋索引優化：若只查少數列，構建覆蓋索引可避免回表，進一步提速。

總結

千萬級分頁核心策略：**避免大 OFFSET 掃描**。
• 通用場景：子查詢 / JOIN 先取 ID 再回表，穩定提升 10 倍+。
• 瀏覽流場景：游標/書籤分頁，零 OFFSET，極致性能。
• 混合場景：相對偏移跳頁 + 兜底子查詢。
配合合理索引、覆蓋索引、統計信息維護，可支撐百萬級 QPS 的分頁查詢。