MySQL LIMIT 深分頁為什麼越來越慢？原理圖解與優化方案

現象

SELECT * FROM t LIMIT 0, 10;      -- 极快
SELECT * FROM t LIMIT 1000000, 10; -- 极慢

底層原理：郵局信件比喻

• 索引 = 按區域分架的信件
• WHERE = 找到西湖區架子（快）
• ORDER BY = 架上按時間排序（快）
• LIMIT 1000000, 10 = 從頭數到第 100 萬封，再取 10 封 → 必須掃描並丟棄前 100 萬行

MySQL 無法「跳躍」到偏移量，只能順序讀取並計數。

優化方案：把「數數」換成「定位」

方案一：索引鍵值定位（推薦）

-- 記住上一頁最大 ID
SELECT * FROM t WHERE id > 1000000 ORDER BY id LIMIT 10;

適用：單向翻頁、ID 連續/單調遞增、無需跳頁。

方案二：延遲關聯

SELECT t.* FROM t
JOIN (SELECT id FROM t ORDER BY id LIMIT 1000000, 10) AS tmp USING (id);

子查詢只掃描主鍵索引（覆蓋索引），極快；外層回表僅 10 行。

方案三：業務妥協

• 禁止大偏移跳頁，UI 改為「加載更多」/ 游標翻頁
• 增加過濾條件縮小掃描範圍（如時間窗口）

核心結論

LIMIT 偏移量大 = 全表掃描到該位置。優化本質是「減少無效掃描行數」，而非調參數。