MySQL LIMIT 深分頁優化實測：子查詢定位 ID 再回表，效能提升 3 倍

問題現象

-- 同樣取 10 筆，效能天差地別
SELECT * FROM yanxue8_visit LIMIT 10000, 10;  -- 0.0187 秒
SELECT * FROM yanxue8_visit LIMIT 0, 10;       -- 0.0004 秒

LIMIT offset, size 掃描 offset + size 行，深分頁掃描行數線性增長。

優化改寫：子查詢先定位 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 >= ... 範圍查詢直接命中索引，僅掃描 10 行。

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

結論：Offset 越大，優化效果越明顯；淺分頁直接用 LIMIT 即可。

進階變體：覆蓋索引避免回表

若只查詢少數欄位，建立覆合索引 (vid, col1, col2)，子查詢與外層皆走索引覆蓋，完全避免回表，效能更強。

適用條件與限制

• 主鍵/唯一索引為遞增整數（或可排序單調欄位）
• 資料較少刪除導致 ID 稀疏（稀疏不影響正確性，但掃描行數會略增）
• 不適用於複雜 ORDER BY（多欄排序）或無合適索引的查詢