MySQL 分頁優化實戰：子查詢與 INNER JOIN 兩種改寫方案效能對比

大偏移 LIMIT 935500, 10 導致查詢從 0.05 秒暴增至 2.39 秒。本文提出「子查詢先取 ID」與「INNER JOIN 關聯 ID」兩種改寫方案，實測分別提速 28% 與 30%，並分析適用場景。

測試環境

• 表 t1：994,584 行，主鍵 id，欄位 ftype TINYINT。
• 查詢：SELECT * FROM t1 WHERE ftype=1 ORDER BY id DESC LIMIT 935500, 10（原始 2.39 秒）。

方案一：子查詢先定位起始 ID

SELECT * FROM (
    SELECT * FROM t1 
    WHERE id > (SELECT id FROM t1 WHERE ftype=1 ORDER BY id DESC LIMIT 935510, 1)
    LIMIT 10
) t ORDER BY id DESC;

• 執行計劃：衍生表 + 子查詢索引掃描。
• 耗時：1.86 秒（提速 28.2%）。
• 注意：雙重 ORDER BY DESC 導致 LIMIT 起始值需 +10（935510）。

方案二：INNER JOIN 關聯 ID 列表（推薦）

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

• 執行計劃：衍生表物化 + 主鍵 eq_ref 關聯。
• 耗時：1.83 秒（提速 30.8%）。
• 無需調整 LIMIT 起始值，語義更直觀。

無 WHERE 條件分頁對比

各場景提速比例總結

• 大偏移 + WHERE：INNER JOIN 最優（~30%）。
• 小偏移 + WHERE：INNER JOIN 提速高達 156%。
• 無 WHERE：INNER JOIN 穩定 ~30%，子查詢波動大。

核心優化邏輯

1. 衍生表先從索引取得目標 ID 列表（覆蓋索引掃描，無回表）。
2. 外層通過主鍵 eq_ref 精準回表取完整列。
3. 避免了原查詢「掃描 93 萬行 → 排序 → 取 10 行」的巨大開銷。

最佳實務建議

• 新專案直接採用 INNER JOIN 寫法 作為分頁標準模式。
• 確保 ORDER BY 欄位與 WHERE 條件欄位建立複合索引（如 INDEX (ftype, id DESC)），進一步消除排序開銷。
• 極大分頁（百萬級）考慮游標式分頁或搜尋引擎（Elasticsearch/Sphinx）離線分頁。
• 測試時重啟實例並加 SQL_NO_CACHE 排除緩存干擾。