MySQL LIMIT 分頁查詢優化五大方案：子查詢、倒排表、反向查找、限制偏移、覆蓋索引

性能痛點

LIMIT offset, length 機制：掃描 offset + length 行，丟棄前 offset 行，返回後 length 行。offset 越大，掃描越多，性能指數級下降。

五大優化方案深度解析

1. 子查詢優化法（延遲關聯）

-- 原始：offset=100000 耗時 0.10s
SELECT * FROM Member LIMIT 100000, 100;

-- 優化：先定位 ID，再回表
SELECT * FROM Member
WHERE MemberID >= (SELECT MemberID FROM Member LIMIT 100000, 1)
LIMIT 100;  -- 耗時 0.02s

• 原理：子查詢僅掃描索引定位 ID，主查詢直接範圍查找，避免大量回表
• 前提：MemberID 為主鍵/唯一索引，且數據連續無洞（無刪除/間隙）
• 缺陷：有 WHERE 過濾條件時，連續性破壞，不可用

2. 倒排表優化法

• 建立映射表 page_map(page_no, start_id, end_id) 維護分頁邊界
• 查詢時 JOIN page_map 直接定位 ID 範圍
• 缺陷：數據增刪改需同步維護映射表，複雜度高，適合靜態/準靜態數據

3. 反向查找優化法

當 offset > 總行數/2 時，倒序查詢減少掃描行數。

-- 正向：offset=839960 耗時 1.87s
SELECT * FROM abc WHERE BatchID=123 LIMIT 839960, 40;

-- 反向：總行數 1,628,775，反向 offset=788775 耗時 1.83s
SELECT * FROM abc WHERE BatchID=123 ORDER BY InputDate DESC LIMIT 788775, 40;

-- 深分頁優勢明顯：第 30000 頁 (offset=1199960)
-- 正向 2.65s vs 反向 1.00s

• 前提：需知道精確總行數，有 ORDER BY 索引支撐
• 注意：結果為倒序，應用層需再次反轉

4. LIMIT 限制優化法（業務妥協）

• 前端限制最大翻頁深度（如僅允許前 100 頁）
• 超出範圍提示「使用搜索/篩選」而非翻頁
• 阿里巴巴 DBA 實踐：offset > 5000 直接返回空或導向搜索

5. 只查索引法（覆蓋索引）

-- 原始：回表開銷大
SELECT * FROM member ORDER BY last_active LIMIT 50, 5;

-- 優化：子查詢只掃描索引 (Covering Index)
SELECT * FROM member
INNER JOIN (
    SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5
) t USING (member_id);

• 原理：內層子查詢僅讀取索引葉子節點 (member_id, last_active)，外層通過主鍵精準回表 5 行
• 要求：(last_active, member_id) 聯合索引覆蓋查詢列
• 通用性最強：無連續性要求、無總行數要求、支援任意 WHERE

方案選型決策樹

1. 無 WHERE、主鍵連續 → 方案 1 (子查詢) 最簡單
2. 深分頁、知道總數、有排序索引 → 方案 3 (反向)
3. 通用場景、追求穩定 → 方案 5 (覆蓋索引) 首選
4. 極大數據集、業務允許 → 方案 4 (限制深度)
5. 靜態報表數據 → 方案 2 (倒排表)

實測數據對比 (Member 表 16.9 萬行)

結論：Offset 越大，優化收益越明顯。生產環境建議將方案 5 封裝為通用分頁組件。