MySQL LIMIT 分頁優化：大偏移量性能提升 3 倍

直接使用 LIMIT offset, size 在大偏移量下性能急劇下降。測試環境：Win2003 + P4 雙核 3GHz + 4GB RAM + MySQL 5.0.19，表 yanxue8_visit。

性能對比實測

優化原理：延遲關聯

問題：LIMIT 10000,10 需掃描前 10000 行並丟棄，IO 浪費巨大。

解法：先通過覆蓋索引快速定位偏移位置的主鍵，再回表查詢：

SELECT * FROM yanxue8_visit 
WHERE vid >= (
    SELECT vid FROM yanxue8_visit ORDER BY vid LIMIT 10000, 1
) 
LIMIT 10;

• 內層子查詢只掃描索引（vid），極快定位第 10001 行的主鍵值
• 外層查詢利用主鍵範圍直接回表，避免大量無效行掃描

適用場景與注意事項

• 小偏移量（<100）：直接 LIMIT 更優，子查詢有額外開銷
• 大偏移量（>1000）：優化效果顯著，偏移越大優勢越明顯
• 要求：排序字段（vid）有索引，且為遞增/遞減序列
• MySQL 8.0+ 可用 LIMIT ... OFFSET ... 配合索引下推進一步優化

生產環境建議

• 游標分頁：記住上一頁最大 ID，下頁用 WHERE id > last_id LIMIT 10
• 避免深分頁：UI 限制最大頁碼，或提供「載入更多」模式
• 覆蓋索引：子查詢只選擇索引列，避免回表