MySQL 深度分頁效能優化：OFFSET 陷阱與三大破局方案

LIMIT offset, count 在深度分頁 (大 offset) 時效能崩塌，本文深度解析原理，提供延遲關聯、游標/ID 範圍分頁、覆蓋索引 三大核心方案，實現毫秒級響應。

問題根源：OFFSET 掃描機制

-- ❌ 深度分頁殺手
SELECT * FROM huge_table WHERE condition ORDER BY id LIMIT 1000000, 20;
-- MySQL 必須掃描前 1,000,020 行，丟棄前 1,000,000 行，僅返回 20 行
-- 效能隨 offset 線性惡化，百萬級 offset 秒級→分鐘級

EXPLAIN 驗證

EXPLAIN SELECT * FROM t WHERE c=1 ORDER BY id LIMIT 1000000, 20;
-- type: ref → ALL (全表掃描)
-- rows: 1000020 (掃描行數 = offset + limit)

方案一：延遲關聯 (Delayed Join) - 最通用

核心思想： 先在索引上完成排序+定位，再回表查詢完整欄位。

-- ✅ 優化後：子查詢只查主鍵，回表僅 20 行
SELECT t.* 
FROM huge_table t
INNER JOIN (
    SELECT id FROM huge_table WHERE condition ORDER BY id LIMIT 1000000, 20
) tmp USING (id);

效能對比

方案二：游標/ID 範圍分頁 (Keyset Pagination) - 效能最優

核心思想： 不用 OFFSET，用上一頁最後一條記錄的排序鍵值作為下一頁起點。

前端協議變更

-- 第 1 頁 (客戶端不傳游標)
SELECT * FROM huge_table WHERE condition ORDER BY id LIMIT 20;

-- 第 2 頁 (客戶端傳上一頁最後 id=1000020)
SELECT * FROM huge_table WHERE condition AND id > 1000020 ORDER BY id LIMIT 20;

-- 第 N 頁
SELECT * FROM huge_table WHERE condition AND id > last_seen_id ORDER BY id LIMIT 20;

優勢

• O(1) 效能：無論第幾頁，皆為索引範圍掃描 + LIMIT
• 無重複/遺漏：新資料插入不影響已讀頁面 (ID 遞增假設)
• 支援無限滾動：天然適配前端 Infinite Scroll

限制與變通

方案三：覆蓋索引優化 (Covering Index)

若查詢欄位全在索引中，無需回表，即使 OFFSET 掃描也極快。

-- 建立覆蓋索引 (查詢欄位: id, name, status)
CREATE INDEX idx_cover ON huge_table (condition_col, id, name, status);

-- 查詢只需掃描索引樹
SELECT id, name, status FROM huge_table WHERE condition_col = ? ORDER BY id LIMIT 1000000, 20;

適用性判斷

• ✅ 查詢欄位固定、較少 (≤5-10 個)
• ✅ 表寬度大 (含 TEXT/BLOB/大 VARCHAR)
• ❌ 查詢欄位多變、或需 SELECT *

綜合選型決策樹

需要跳頁 (直接輸入頁碼)？
├── 是 → 延遲關聯 (方案一) + 適度覆蓋索引
└── 否 (僅下一頁/無限滾動)
    ├── 排序鍵唯一且單調？ → 游標分頁 (方案二) **最優**
    └── 排序鍵非唯一？ → 複合游標 (方案二變體) + 覆蓋索引

避坑指南

• 不要在 ORM 中隱式使用 OFFSET：如 Django queryset[1000000:1000020]、Laravel skip(1000000)->take(20)
• 監控慢查詢日誌：關注 Rows_examined 遠大於 Rows_sent 的查詢
• 統計資訊準確：ANALYZE TABLE 確保優化器正確選擇索引
• 避免 SELECT *：阻礙覆蓋索引，增加 IO

架構層面補充：分頁狀態外部化

本文提到的「分頁狀態儲存」屬於應用層設計：

• Redis 儲存 ID 清單：首次查詢將所有符合條件 ID 存入 Redis List，分頁直接 LRANGE
• 搜尋引擎：Elasticsearch/Solr 原生支援深度分頁與游標
• 資料倉庫：ClickHouse/Apache Druid 專為大數據分頁優化

核心原則： 將「定位」與「取數」分離，利用索引有序性避免全掃描 — 這是所有關聯式資料庫分頁優化的通用邏輯。