MySQL LIMIT 大偏移量效能優化：子查詢鎖定 ID 範圍避免全表掃描

問題現象

同樣取 10 筆資料：

• SELECT * FROM yanxue8_visit LIMIT 0,10 → 耗時 0.0004~0.0005 秒
• SELECT * FROM yanxue8_visit LIMIT 10000,10 → 耗時 0.0187 秒（慢 40 倍）

原因：MySQL 需先掃描並捨棄前 10000 列，再取後 10 列，偏移量越大效能越差。

優化策略：先查 ID 再回表

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

• 子查詢僅掃描索引（vid 主鍵）定位第 10001 筆的 ID，極快。
• 外層查詢直接用 WHERE vid >= ... LIMIT 10 回表取資料，避免大偏移掃描。

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

結論與建議

• 小偏移量（< 1000）：直接 LIMIT 即可，子查詢反而略慢。
• 大偏移量：採用「子查詢鎖定起始 ID + 回表 LIMIT」模式，效能提升顯著，偏移量越大優勢越明顯。
• 前提：表需有遞增主鍵或唯一索引（如 vid），且查詢順序與索引順序一致。
• 分頁系統建議：記錄「上一頁最大 ID」，下一頁直接 WHERE id > $last_id LIMIT 10，完全避免 OFFSET。