MySQL 分頁優化實戰：從 LIMIT 深分頁效能陷阱到延遲關聯/游標分頁方案

問題：深分頁效能雪崩

EXPLAIN SELECT * FROM message ORDER BY id DESC LIMIT 100000, 20;
-- type=index, rows=100020  → 掃描 10 萬行丟棄 10 萬，極慢

LIMIT offset, rows 會掃描 offset + rows 行，**offset 越大越慢**，高併發下直接拖垮資料庫。

方案一：延遲關聯 / 覆蓋索引回表 (適用中等偏移)

-- 1. 先用覆蓋索引快速定位主鍵
SELECT id FROM message ORDER BY id DESC LIMIT 100000, 20;

-- 2. 再回表查詢完整列
SELECT * FROM message 
WHERE id IN (上述子查詢)
ORDER BY id DESC;  -- 保持順序

或單條 SQL (衍生表)：

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

• ✅ 掃描行數僅 offset + rows → 仍隨 offset 增長，但**不回表大字段**，I/O 大幅降低。
• ⚠️ 深分頁 (百萬+) 仍慢，需方案二。

方案二：游標分頁 / Seek Pagination (適用無限滾動、API 分頁) 🚀 最佳

-- 首頁
SELECT * FROM message ORDER BY id DESC LIMIT 20;

-- 下一頁 (客戶端記住上一頁最小 id = 1000)
SELECT * FROM message WHERE id < 1000 ORDER BY id DESC LIMIT 20;

-- 上一頁 (記住上一頁最大 id = 1020)
SELECT * FROM message WHERE id > 1020 ORDER BY id ASC LIMIT 20;  -- 取完再反轉

• ✅ **無 OFFSET，每次固定掃描 20 行**，效能恆定 O(1)。
• ✅ 利用索引範圍查詢 (id < 1000)，極快。
• ⚠️ **不支援跳轉任意頁碼** (如「第 50 頁」)，僅適合「上一頁/下一頁」或無限滾動流。
• ⚠️ 需 **唯一、不變、有序** 的游標列 (主鍵/唯一索引 + 時間戳組合)。

方案三：記住當前頁邊界 ID 的跳轉優化 (支援頁碼跳轉)

若業務**必須**顯示「1 2 3 ... 10」頁碼跳轉：

-- 當前第 10 頁，id 範圍 2500~2519 (每頁 20)
-- 跳第 9 頁 (前一頁)
SELECT * FROM message WHERE id < 2500 ORDER BY id DESC LIMIT 20;

-- 跳第 8 頁 (前兩頁)
SELECT * FROM message WHERE id < 2500 ORDER BY id DESC LIMIT 20 OFFSET 20;

-- 跳第 11 頁 (後一頁)
SELECT * FROM message WHERE id > 2519 ORDER BY id ASC LIMIT 20;  -- 取完應用層反轉

-- 跳第 12 頁
SELECT * FROM message WHERE id > 2519 ORDER BY id ASC LIMIT 20 OFFSET 20;

• ✅ 利用**相對偏移** (當前頁邊界為基準)，而非絕對偏移 (從第一頁)。
• ✅ 相鄰頁跳轉偏移極小，掃描行數可控。
• ⚠️ 跨度大 (如第 1 頁跳第 100 頁) 仍退化為大 OFFSET，需前端限制跳轉範圍或結合方案二。

方案四：預聚合 / 物化視圖 (超大數據量、複雜排序)

• 定期任務將排序結果物化到分頁表 (page_id, item_id, sort_key)。
• 分頁查詢直接 JOIN 物化表，極快。
• 適用：排序規則固定、數據更新頻率可接受延遲 (如商品列表、排行榜)。

60 萬測試數據實測對比

選型決策樹

1. **無限滾動 / 只有上下頁** → 游標分頁 (最佳)。
2. **需頁碼跳轉、偏移不大 (< 10000)** → 延遲關聯。
3. **需頁碼跳轉、偏移大、相鄰頁跳轉為主** → 邊界 ID 相對偏移。
4. **超大數據、複雜排序、可接受秒級延遲** → 物化視圖。

核心原則

「用索引定位，不掃描垃圾行；用游標替代偏移，讓分頁效能與資料量無關。」

根據業務交互模式選擇方案，徹底解決 MySQL 深分頁效能難題。