MySQL 高效分頁查詢優化：告別 LIMIT 深分頁效能災難

問題根源：LIMIT 深分頁掃描機制

LIMIT 10000, 20 意味著掃描 10,020 行、丟棄前 10,000 行、回傳後 20 行。
EXPLAIN 顯示 rows: 10020、type: index（全索引掃描）。
隨著偏移量增大，掃描行數線性增長，高併發下效能崩塌。

優化策略一：游標式分頁（僅支援上一頁/下一頁）

記住當前頁最大/最小 ID，以範圍查詢替代偏移：

-- 上一頁（ID 遞增）
SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20;

-- 下一頁（ID 遞減）
SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 20;

每次僅掃描 20 行，效能恆定。缺點：無法跳轉任意頁碼。

優化策略二：相對偏移分頁（支援頁碼跳轉）

利用當前頁邊界 ID 計算相對偏移，大幅縮小 m 值：

-- 當前第 10 頁，ID 範圍 9500~9527，欲跳第 8 頁（前移 2 頁 = 40 行）
SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 40, 20;

-- 跳第 13 頁（後移 3 頁 = 60 行）
SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 60, 20;

核心原理：傳統 LIMIT m,n 的基準永遠是第 1 頁（m 隨頁碼增大）；相對偏移基準是「當前頁」，m 僅與跳轉距離有關，不隨絕對頁碼增長。

實測數據（60 萬筆資料表）

• 傳統 LIMIT 10000,10：~18 ms
• 相對偏移優化後：~6 ms（約 1/3 耗時）
• 偏移量越大，優化效果越明顯

實作注意事項

1. ASC 查詢結果需在應用層反轉順序
2. 需在前端保存當前頁 min_id、max_id
3. 適合主鍵為遞增整數、資料較少刪除/更新的場景

適用場景建議