MySQL 高效分頁深度解析：LIMIT 深分頁性能崩塌根因與三種優化方案實戰

現象：深分頁查詢極慢

EXPLAIN SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20;
-- type: index, rows: 10020, Extra: Using index
-- 實際掃描 10020 行，丟棄前 10000 行，返回後 20 行

隨著偏移量 M 增大，掃描行數 = M + N 線性增長，大表深分頁 (如第 10000 頁) 直接導致全表掃描、緩存污染、鎖爭用、響應超時。

根因：B+ 樹索引掃描機制

InnoDB 聚簇索引按主鍵有序。LIMIT M, N 需從最左葉子節點順序掃描 M 條記錄定位起始位置，再讀取 N 條。**無法跳躍**，必須逐行訪問。

方案一：游標式分頁 / 基於 ID 範圍查詢 (最推薦，性能 O(1))

適用場景：前端只需「上一頁/下一頁」或「加載更多」，不需跳轉任意頁碼 (如 Feed 流、評論列表、日誌查詢)。

原理

記住當前頁**最大/最小 ID** (或排序字段值)，下一頁直接用範圍查詢定位，利用索引範圍掃描 (Range Scan) 直接定位，掃描行數固定為 N。

實戰 SQL

-- 假設按 id 降序，每頁 20 條
-- 當前頁最大 id = 9527，最小 id = 9500

-- 下一頁 (id < 9500)
SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 20;

-- 上一頁 (id > 9527)
SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20;  -- 取出後應用層反轉

優勢：無論翻多深，每次只掃描 20 行，毫秒級響應。**缺點**：無法直接跳轉第 N 頁。

方案二：延遲關聯 / 子查詢回表 (支援任意頁碼跳轉，性能 O(log N + M))

適用場景：後台管理、報表、需「首頁/尾頁/頁碼跳轉」的傳統分頁。

原理

先在**覆蓋索引**上按排序掃描定位主鍵 ID (極快，不回表)，再用主鍵 ID 回表查詢完整列。

實戰 SQL

-- 原始慢查詢
SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20;

-- 優化：子查詢只取 id (覆蓋索引)，外層回表
SELECT m.* 
FROM message m
INNER JOIN (
    SELECT id FROM message ORDER BY id DESC LIMIT 10000, 20
) tmp ON m.id = tmp.id
ORDER BY m.id DESC;

原理解析：內層 SELECT id ... LIMIT 10000, 20 走主鍵索引掃描 10020 行，**只讀取 id 列 (覆蓋索引)，不回表**，極快。外層僅 20 次主鍵點查。總開銷 ≈ 索引掃描 10020 行 (極窄) + 20 次點查，遠優於原查詢 10020 次完整行回表。

方案三：基於當前頁 ID 的相對偏移跳轉 (折中方案，支援就近頁碼跳轉)

適用場景**：用戶多在當前頁附近跳轉 (如 10→12 頁)，極少直接跳首/尾頁。

實戰 SQL

-- 當前頁：id 最大 9527，最小 9500，每頁 20 條
-- 跳轉第 8 頁 (向前 2 頁，偏移 40 行)
SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 40, 20;
-- 結果需應用層反轉

-- 跳轉第 13 頁 (向後 3 頁，偏移 60 行)
SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 60, 20;

原理**：利用當前頁邊界 ID 作為錨點，計算目標頁相對偏移。偏移量 = |目標頁 - 當前頁| × 頁大小。就近跳轉偏移量小，掃描行數可控。極端跳轉 (首→尾) 退化為全掃描，可回退方案二。

方案對比總結

工程落地建議

1. 前端交互重設計：優先推行「加載更多/無限滾動」替代頁碼跳轉，從源頭消除深分頁需求。
2. 搜索/列表服務分層**：簡單列表用游標式；複雜篩選/排序用 Elasticsearch (原生支援深分頁 scroll/search_after) 或 ClickHouse/Apache Doris 等 OLAP 引擎。
3. 緩存首頁/熱門頁**：Redis 緩存前 10 頁數據，90% 請求命中緩存。
4. 監控慢查詢**：long_query_time=1 捕獲深分頁慢 SQL，定期分析優化。
5. 60 萬實測驗證**：原文 60 萬數據表測試，方案一/二/三效果顯著，建議自建同量級測試驗證。