MySQL LIMIT 分頁優化深度解析：子查詢定位法大幅提升大偏移量查詢效能

問題現象

LIMIT offset, size 在大偏移量時效能崩潰：

SELECT * FROM yanxue8_visit LIMIT 0,10      -- 0.0004~0.0005s
SELECT * FROM yanxue8_visit LIMIT 10000,10  -- 0.0187s（慢 40 倍）

MySQL 需掃描並丟棄前 offset 行，偏移量越大越慢。

優化方案：子查詢定位 + 主鍵範圍查詢

-- 先用子查詢取得目標 offset 的主鍵值
SELECT vid FROM yanxue8_visit ORDER BY vid LIMIT 10000,1

-- 再用主鍵範圍查詢
SELECT * FROM yanxue8_visit 
WHERE vid >= (上述子查詢結果) 
LIMIT 10

實測數據對比（Win2003 + P4雙核 3GHz + 4GB RAM + MySQL 5.0.19）

結論與建議

• 小偏移量：直接用 LIMIT 更簡單高效
• 大偏移量：子查詢定位法顯著優越，偏移量越大優勢越明顯
• 前提：表需有有序主鍵或唯一索引（如自增 ID）
• 生產環境建議：封裝為儲存程序或應用層邏輯，動態選擇策略

延伸思考

若無有序主鍵，可考慮「游標分頁」（記住上一頁最大 ID，下一頁 WHERE id > last_id LIMIT size），完全避開 OFFSET 掃描。