MySQL LIMIT 分頁優化：從深分頁 8 秒到 0.06 秒的索引覆蓋實戰

問題：OFFSET 大時 LIMIT 變慢

SELECT * FROM cyclopedia LIMIT 90000, 100;  -- 8.88 秒

MySQL 必須掃描前 90,000 行並丟棄，再取 100 行。隨 OFFSET 增大線性惡化。

優化核心：延遲關聯 / 索引覆蓋

利用主鍵 ID 有序、唯一、聚簇索引特性：

1. 先在次級索引（或主鍵）上只掃描 ID 找到目標起始點。
2. 再回表取完整行。

方案對比實測

最佳寫法（簡化版）

SELECT * FROM cyclopedia
WHERE ID >= (SELECT ID FROM cyclopedia ORDER BY ID LIMIT 90000, 1)
LIMIT 100;

• 子查詢：ORDER BY ID LIMIT offset, 1 走主鍵索引，極快定位單一 ID。
• 外查詢：WHERE ID >= target_id LIMIT page_size 走主鍵範圍掃描，只讀需求行。
• 關鍵： 只選 ID 的子查詢觸發 Index Only Scan（覆蓋索引），不回表。

應用層分頁模式（游標分頁 / Keyset Pagination）

若前端支援「上一頁/下一頁」而非跳頁碼：

-- 首頁
SELECT * FROM cyclopedia ORDER BY ID LIMIT 100;

-- 下一頁（記住上頁最大 ID）
SELECT * FROM cyclopedia WHERE ID > 90099 ORDER BY ID LIMIT 100;

• 無 OFFSET，效能恆定 O(1)。
• 適合無限滾動、API 分頁、資料同步。

其他資料庫的 LIMIT 等價寫法

避坑指南

1. SELECT * 殺手： 只需幾欄時列出欄位，觸發覆蓋索引，避免回表。
2. 複合索引排序： WHERE cat=1 ORDER BY id LIMIT 100 需 INDEX (cat, id)。
3. WHERE + ORDER BY 欄位不同： 易產生 filesort，檢查 EXPLAIN。
4. 大 OFFSET 必改： 超過 1 萬行 OFFSET 即應改寫為延遲關聯或游標分頁。

總結

MySQL 深分頁優化核心：用子查詢在索引上定位起始主鍵，外層範圍掃描取資料。將 O(N) 全掃描降為 O(log N + page_size) 索引查找。配合游標分頁模式，徹底解決大資料集分頁效能問題。