MySQL 分頁查詢優化：從 LIMIT 100000,20 到基於 ID 的高效翻頁

傳統 LIMIT offset, rows 在深分頁（大 offset）時效能急劇下降，因為 MySQL 必須掃描並捨棄前 offset 行。本文從原理切入，給出三種優化方案。

問題原理

EXPLAIN SELECT * FROM `message` ORDER BY `id` DESC LIMIT 10000, 20;
-- rows: 10020  -> 掃描 10020 行，丟棄 10000 行，只回 20 行

若 LIMIT 100000, 100 則掃描 100100 行，高併發下極其吃力。

方案一：只提供「上一頁 / 下一頁」（游標式分頁）

記住當前頁最大/最小 ID，下一頁用 WHERE id > max_id，上一頁用 WHERE id < min_id。

-- 下一頁
SELECT * FROM `message` WHERE `id` > 1020 ORDER BY `id` ASC LIMIT 20;
-- 上一頁
SELECT * FROM `message` WHERE `id` < 1000 ORDER BY `id` DESC LIMIT 20;

優點：每次只掃 20 行。缺點：無法跳轉到任意頁碼。

方案二：延伸「相對偏移」支援任意頁碼跳轉

記住當前頁 min_id、max_id，計算目標頁與當前頁的頁差 delta，轉為 LIMIT delta*page_size, page_size。因為 delta 通常很小，掃描行數大幅降低。

-- 當前第 10 頁，min_id=2500, max_id=2519，每頁 20 條
-- 跳第 9 頁（delta=-1）
SELECT * FROM `tb_goods_info` WHERE `auto_id` < 2500 ORDER BY `auto_id` DESC LIMIT 0, 20;
-- 跳第 8 頁（delta=-2）
SELECT * FROM `tb_goods_info` WHERE `auto_id` < 2500 ORDER BY `auto_id` DESC LIMIT 20, 20;
-- 跳第 11 頁（delta=+1）
SELECT * FROM `tb_goods_info` WHERE `auto_id` > 2519 ORDER BY `auto_id` ASC LIMIT 0, 20;

注意：ASC 取出的結果需在應用層反轉順序。

方案三：延遲關聯 / 子查詢優化（適用於複雜 JOIN）

SELECT t.*
FROM `message` t
JOIN (
  SELECT `id` FROM `message` ORDER BY `id` DESC LIMIT 100000, 20
) AS tmp USING (`id`);

子查詢只掃主鍵索引（覆蓋索引），回表僅 20 行，大幅降低 I/O。

實測數據（60 萬行表）

選型建議

• 單表、單鍵、需任意跳頁 → 方案二。
• 複雜 JOIN、多表排序 → 方案三。
• 純前後翻頁、無跳頁需求 → 方案一。

核心思想：**避免大 OFFSET 掃描，改用索引範圍定位**。