MySQL 大數據量分頁優化：反向索引掃描技巧

深分頁效能痛點

某論壇回覆表 reply_0004 單執行緒擁有 123 萬條資料，索引為 (thread_id, deleted, id)。查詢最後一頁：

SELECT id 
FROM reply_0004 
WHERE thread_id = 5616385 AND deleted = 0 
ORDER BY id ASC LIMIT 1236785, 10;

耗時 1.32 秒。隨著偏移量增大，MySQL 需掃描並丟棄大量前置行，導致延遲線性增長。

優化思路：利用索引有序性反向掃描

B+ 樹索引天然有序，DESC 掃描同樣高效。將「查最後幾頁」改為「反向查前幾頁」再排序：

SELECT * 
FROM (
  SELECT id 
  FROM group_thread_reply_0004 
  WHERE thread_id = 5616385 AND deleted = 0 
  ORDER BY id DESC LIMIT 0, 10
) t 
ORDER BY t.id ASC;

實測效果對比

• 原方案（正向深分頁）：1.32 秒
• 優化方案（反向掃描 + 外層排序）：0.87 秒
• 效能提升 34%，且不隨頁碼增長而劣化

應用場景與限制

1. 適用：使用者習慣查看首頁與末頁（如論壇、評論區）
2. 不適用：需任意頁碼跳轉的後台管理介面
3. 進階：MySQL 8.0+ 支援 LATERAL JOIN 或 CTE 寫法更優雅

延伸優化：游標式分頁

若業務允許，改用 WHERE id < last_seen_id ORDER BY id DESC LIMIT 10 可完全消除 OFFSET 掃描成本，實現真正的 O(1) 分頁。