MySQL LIMIT 深分頁效能優化：從掃描 9 萬行到索引定位 1 行

MySQL LIMIT offset, rows 機制是「先掃描 offset+rows 行，再捨棄前 offset 行」。當 offset 巨大（如第 9 萬頁），效能呈線性下降。本文展示經典優化模式：子查詢先定位 ID，主查詢再範圍掃描。

問題重現

-- 取第 90001-90100 行（offset=90000）
SELECT * FROM `table` LIMIT 90000, 100;
-- 執行時間：0.23 秒

優化思路

1. 利用覆蓋索引 SELECT id FROM table ORDER BY id LIMIT 90000, 1 僅掃描索引，極快定位起始 ID
2. 主查詢改為 WHERE id >= 起始ID LIMIT 100，走主鍵範圍掃描

優化後 SQL

SELECT * FROM `table`
WHERE id >= (SELECT id FROM `table` ORDER BY id LIMIT 90000, 1)
LIMIT 100;
-- 執行時間：0.19 秒（提升 ~17%）

適用條件與避坑

• 表需有單調遞增主鍵/唯一索引（如自增 ID、時間戳）
• 子查詢需走覆蓋索引（EXPLAIN 顯示 Using index）
• 若 ID 不連續（刪除導致空洞），結果行數可能 < 100，需應用層補查
• 極大 offset 仍建議業務層面改為「游標式分頁」（WHERE id > last_id LIMIT 100）

延伸：游標式分頁範例

-- 首頁
SELECT * FROM `table` ORDER BY id LIMIT 20;

-- 後續頁（前端傳回上頁最大 id）
SELECT * FROM `table` WHERE id > 12345 ORDER BY id LIMIT 20;

游標式分頁無 offset 陷阱，效能恆定，適合無限滾動、API 分頁等高併發場景。