MySQL 大數據分頁優化：Limit 深分頁效能問題與解決方案

MySQL 大數據分頁優化：Limit 深分頁效能問題與解決方案

從 Oracle 轉向 MySQL 的開發者常發現：相同的 LIMIT offset, rows 在不同資料量級下效能差異極大。本文深入分析 Offset 導致的效能瓶頸，並提供多種優化策略。

問題現象

假設表 ibmng(id, title, info)，id 為主鍵，title 有索引：

SELECT * FROM ibmng LIMIT 1000000, 10;  -- 極慢
SELECT * FROM ibmng LIMIT 10, 10;       -- 極快

差距可達 10 倍以上，核心原因在於 Offset 掃描機制：MySQL 必須先掃描並丟棄前 1,000,000 筆記錄，再取得 10 筆結果。

優化策略

1. 利用主鍵索引覆蓋掃描（延遲關聯）

SELECT * FROM ibmng
WHERE id >= (
    SELECT id FROM ibmng ORDER BY id LIMIT 1000000, 1
) LIMIT 10;

雖然子查詢仍有 LIMIT 1000000, 1，但因只掃描 id 索引（而非整行資料），速度大幅提升。

2. 非主鍵條件查詢的索引陷阱

-- 慢：需回表 + filesort
SELECT id FROM ibmng WHERE title='mysql' ORDER BY id LIMIT 1000000, 10;

-- 快：覆蓋索引，無需回表
SELECT id FROM ibmng WHERE title='mysql' LIMIT 1000000, 10;

加入 ORDER BY id 會破壞覆蓋索引，導致效能暴跌。解決方案：建立 複合索引 (title, id)。

3. 千萬級資料批次處理：主鍵範圍查詢

-- 不推薦
SELECT * FROM big_table LIMIT 1000000, 1000;

-- 推薦：主鍵範圍分頁
SELECT * FROM big_table
WHERE id > 1000000 AND id <= 1001000;

此方式避免 Offset 掃描，直接利用 B+ 樹索引定位，效能穩定且可預測。

核心原則總結

1. 深分頁時，優先使用 主鍵範圍查詢 替代 LIMIT offset, rows
2. 若必須用 Offset，確保查詢可利用 覆蓋索引（避免回表與排序）
3. 複雜條件分頁考慮建立 複合索引 滿足 Where + Order By + Limit