MySQL 高級分頁技巧：範圍查詢與唯一邊界值實現高性能翻頁

核心問題：OFFSET 深分頁的本質缺陷

MySQL LIMIT M, N 需要掃描並丟棄前 M 行。當 M 很大時，即使只需 N 行結果，引擎仍需遍歷 M 行索引/數據頁，導致 I/O 與 CPU 浪費。

優化演進路線圖

第一階段：INNER JOIN 延遲關聯（基礎優化）

SELECT * 
FROM t1 
INNER JOIN (SELECT id FROM t1 ORDER BY id DESC LIMIT 935500, 10) t2 USING (id);

優勢：子查詢僅掃描主鍵索引（覆蓋索引），外層通過 eq_ref 精準回表 10 行。

第二階段：主鍵範圍查詢（極致優化，適用無 WHERE 條件）

-- 第 1 頁（id 遞增）
SELECT * FROM t WHERE id > 0 ORDER BY id ASC LIMIT 100;

-- 第 2 頁（記住上頁最大 id = 99）
SELECT * FROM t WHERE id > 99 ORDER BY id ASC LIMIT 100;

-- 第 3 頁（記住上頁最大 id = 199）
SELECT * FROM t WHERE id > 199 ORDER BY id ASC LIMIT 100;

核心優勢：完全避開 OFFSET，利用主鍵聚簇索引定位，掃描行數恆等於 LIMIT N，性能與頁碼無關。

第三階段：非唯一索引分頁的唯一邊界值方案（Yahoo 方案）

場景：按 column1 排序分頁，但 column1 存在大量重複值（如 11 行同為 100）。

-- 第 1 頁
SELECT * FROM t ORDER BY column1 DESC, id DESC LIMIT 10;

-- 第 2 頁（假設上頁最後一行：column1=100, id=500）
SELECT * FROM t 
WHERE column1 < 100 
   OR (column1 = 100 AND id < 500)
ORDER BY column1 DESC, id DESC LIMIT 10;

進一步優化為 JOIN 形態（避免 filesort）：

SELECT m2.* 
FROM t m1, t m2
WHERE m1.id = m2.id
  AND m1.column1 <= 100
  AND (m1.id < 500 OR m1.column1 < 100)
ORDER BY m1.column1 DESC, m1.id DESC
LIMIT 10;

實戰對比數據（百萬級表）

工程落地建議

1. API 設計改為游標分頁：前端傳遞 last_id 或 last_sort_key，而非 page_no。
2. 封裝通用分頁庫：內部自動選擇範圍查詢或 JOIN 模式，業務代碼無感知。
3. 禁止 OFFSET > 10000 的 SQL 上線：靜態分析/Code Review 強制攔截。
4. 超大表（億級+）引入搜索引擎：Elasticsearch 的 search_after 原生支援深分頁。

總結

MySQL 高性能分頁核心思想：**用索引定位邊界，替代 OFFSET 掃描丟棄**。按業務場景分層選擇：
① 無條件主鍵序 → id > last_id 範圍查詢（最快）
② 非唯一排序 → sort_col + pk 唯一邊界值
③ 複雜條件 → INNER JOIN (SELECT pk ...) 延遲關聯（通用兜底）
徹底杜絕 LIMIT 1000000, 10 這類寫法進入生產環境。