MySQL 高階分頁優化：避免深分頁效能陷阱

MySQL 高階分頁優化：避免深分頁效能陷阱

傳統 LIMIT offset, count 在 offset 極大時效能急劇下降，因 MySQL 需掃描並捨棄前 offset 行。本文介紹三種進階優化策略。

策略一：延遲關聯 (Deferred Join) / 覆蓋索引掃描

先在索引上定位目標 ID，再回表查詢完整欄位：

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

優點：避免掃描大量不需要的資料頁，僅掃描索引樹。

策略二：基於 ID 範圍查詢 (游標式分頁)

適用於 ID 連續遞增場景，記住上一頁最大 ID：

-- 第 1 頁
SELECT * FROM t WHERE id > 0 ORDER BY id ASC LIMIT 100;
-- 第 2 頁 (假設上頁最大 id=199)
SELECT * FROM t WHERE id > 199 ORDER BY id ASC LIMIT 100;

核心原則：盡量避免 LIMIT M,N 且 M 很大的寫法。

策略三：非唯一索引分頁的唯一性保證 (Yahoo 方案)

當排序欄位非唯一 (如 column1 有 11 個值為 100 的記錄)，單用 LIMIT 會導致下一頁重複資料。解決：引入唯一鍵 (PK/UK) 作為輔助排序與邊界條件。

標準分頁

-- 第 1 頁
SELECT * FROM t ORDER BY column1 DESC, id DESC LIMIT 10;
-- 第 2 頁 (假設上頁 min column1=100, min id=50)
SELECT * FROM t
WHERE column1 <= 100 AND (id < 50 OR column1 < 100)
ORDER BY column1 DESC, id DESC LIMIT 10;

進一步優化：延遲關聯版

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

核心思想總結

• 利用覆蓋索引縮小掃描範圍
• 改用「記住邊界值」的游標式分頁取代 OFFSET
• 非唯一排序欄位必須加入唯一鍵保證分頁邊界精確
• 最終目標：要取多少條，就只掃描多少條 (In-page scanning)

這些技巧在百萬級以上資料表深分頁場景極其有效，是高效能 MySQL 應用必備技能。