MySQL LIMIT 深分頁效能優化五大方案：從子查詢到延遲關聯的實戰比較

MySQL LIMIT 深分頁效能優化五大方案：從子查詢到延遲關聯的實戰比較

問題根源

LIMIT offset, length 會掃描前 offset + length 行，再丟棄前 offset 行。當 offset 達百萬級，掃描龐大中間結果集導致極慢甚至阻塞。

-- 例：500萬資料
LIMIT 0, 10       -- 掃 10 行，極快
LIMIT 200000, 10  -- 掃 200,010 行，明顯變慢
LIMIT 450000, 30  -- 掃 450,030 行，可能堵死

方案一：延遲關聯 / 子查詢優化 (最通用)

先只查主鍵 ID (覆蓋索引，極快)，再回表關聯完整欄位。

-- 原始慢查詢
SELECT * FROM wl_tagindex WHERE byname='f' ORDER BY id LIMIT 300000, 10;

-- 優化：子查詢只走索引
SELECT * FROM (
    SELECT id FROM wl_tagindex WHERE byname='f' ORDER BY id LIMIT 300000, 10
) a LEFT JOIN wl_tagindex b ON a.id = b.id;

-- 執行時間：3.21s → 0.11s

前提： (byname, id) 建立**複合索引**，確保子查詢為「覆蓋索引」純索引掃描。

方案二：記錄上次最大 ID (游標分頁，效能最佳)

適用於「下一頁」場景，不支援任意跳頁。

-- 首頁
SELECT id, name, content FROM users ORDER BY id ASC LIMIT 20;

-- 記錄最後一筆 id = 100073，下一頁
SELECT id, name, content FROM users WHERE id > 100073 ORDER BY id ASC LIMIT 20;
-- 僅掃描 20 行

優點： 無 offset 掃描，效能恆定。缺點： 不支援跳轉指定頁碼、ID 需連續遞增 (自增主鍵最佳)。

方案三：反向查找 (大翻頁場景)

當頁碼超過總頁數一半時，改用 DESC 從尾部倒數。

-- 總記錄 1,628,775，每頁 40，共 40,720 頁
-- 第 30,000 頁 (正向 offset=1,199,960)
SELECT * FROM abc WHERE BatchID=123 LIMIT 1199960, 40;  -- 2.65s

-- 反向：第 30,000 頁 = 倒數 10,720 頁
SELECT * FROM abc WHERE BatchID=123 ORDER BY InputDate DESC LIMIT 428775, 40;  -- 1.00s

注意： 結果為降序，需應用層反轉。需知道總記錄數、建立排序欄位索引。

方案四：限制最大翻頁深度 (業務妥協)

阿里巴巴等大廠做法：offset > 5000 直接返回空或引導用戶用搜尋/篩選。
理由： 用戶極少翻超過 100 頁，深分頁多為爬蟲或異常流量，保護資源優於體驗。

方案五：只查索引 + IN 子查詢 (含大字段表)

若表含 TEXT/BLOB 大欄位，傳輸量大時適用：

SELECT id, title, content
FROM items
WHERE id IN (SELECT id FROM items ORDER BY id LIMIT 900000, 10);

內層只掃描主鍵索引，外層按主鍵回表 10 行。

方案對比總結

最佳實踐建議

1. 首選游標分頁 (ID>) 適用於無限滾動、App 列表、按時間倒序流
2. 次選延遲關聯 需支援跳頁時，務必建立覆蓋索引
3. 大頁碼自動切換反向查找 邏輯封裝於 DAO 層
4. 前端限制頁碼輸入 最大頁碼 1000 或 總頁數/2
5. 監控慢查詢 設置 long_query_time=1 專門捕獲深分頁 SQL

深分頁優化無銀彈，**業務場景決定技術選型**。理解 LIMIT 掃描機制，結合索引設計與業務妥協，才能在效能與體驗間找到平衡點。