MySQL 分頁優化案例實戰：INNER JOIN 方案平均提升 30%+，附執行計劃深度解析

場景還原

表 t1 約 99 萬行，主鍵 id，字段 ftype 為 tinyint。典型分頁 SQL：

SELECT * FROM t1 WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10;  -- 淺分頁
SELECT * FROM t1 WHERE ftype=1 ORDER BY id DESC LIMIT 935500, 10; -- 深分頁

執行計劃對比（關鍵列）

深分頁掃描行數是淺分頁的 1800 倍，耗時卻是 48 倍，說明大量 CPU 消耗在「跳過行」邏輯上。

優化改寫方案

方案一：子查詢延遲關聯

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;

執行計劃亮點：最內層子查詢 type=index, rows=935511, Extra=Using where（僅掃描索引），外層 rows=10。實測 **1.86s，提升 28.2%**。

方案二：INNER JOIN 延遲關聯（推薦）

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

執行計劃亮點：Derived 表 rows=935510 僅讀索引，主查詢 type=eq_ref, rows=1 精準回表。實測 **1.83s，提升 30.8%**。

無 WHERE 條件場景對比

綜合效能提升匯總表

深度解析：為何 INNER JOIN 更穩健？

1. 物化策略可控：Derived 表物化為臨時表後，主查詢走 eq_ref 主鍵查找，無 filesort。
2. 優化器提示友好：可配合 /*+ MAX_EXECUTION_TIME(1000) */ 等 Hint 精準控制。
3. 語義清晰：USING (id) 明確表達「按主鍵關聯」，減少優化器誤判概率。

生產環境落地規範

-- 標準分頁模板（帶業務過濾）
SELECT t.* 
FROM biz_table t
INNER JOIN (
    SELECT id FROM biz_table 
    WHERE status=1 AND tenant_id=100  -- 業務過濾下推到索引掃描層
    ORDER BY id DESC LIMIT ?, ?
) tmp USING (id)
ORDER BY t.id DESC;  -- 保證最終輸出順序

總結

通過 INNER JOIN + 覆蓋索引子查詢 重寫分頁，實現「索引定位主鍵 → 主鍵精準回表」模式。實測大分頁提升 30%+，小分頁提升 150%+，**建議作為團隊分頁 SQL 強制規範**。對於無條件主鍵序分頁，優先採用 id > last_id 範圍查詢獲取極致性能。