MySQL 千萬級數據深度分頁優化：從 Limit 偏移到覆蓋索引 + IN 查詢

問題現場

表 collect(id, title, info, vtype)，10 萬條數據測試：

• LIMIT 1000,10 → 0.01s
• LIMIT 90000,10 → 8-9s（隨偏移量線性增長）

優化演進路線

階段 1：主鍵回表法

SELECT id, title FROM collect
WHERE id >= (SELECT id FROM collect ORDER BY id LIMIT 90000, 1)
LIMIT 10;

利用主鍵索引定位起始 ID，再回表取列，速度提升至 0.04s。

階段 2：帶 Where 條件的深度分頁失效

SELECT id FROM collect WHERE vtype=1 ORDER BY id LIMIT 90000,10;  -- 8-9s

雖 vtype 有索引，但 LIMIT 大偏移仍需掃描大量索引葉子節點。

階段 3：覆蓋索引（複合索引）破局

-- 關鍵：WHERE 列在前，ORDER BY 列在後，僅 SELECT 索引列
ALTER TABLE collect ADD INDEX idx_vtype_id (vtype, id);

SELECT id FROM collect WHERE vtype=1 ORDER BY id LIMIT 90000,10;  -- 0.04s ✅

核心原則：WHERE 列優先，ORDER BY / LIMIT 列次之，SELECT 僅取索引列（覆蓋索引），避免回表。

階段 4：業務層雙查詢模式（終極方案）

-- 1. 只走覆蓋索引取 ID 字符串
SELECT id FROM collect WHERE vtype=1 ORDER BY id LIMIT 90000,10;
-- 拼成 '90001,90002,...'

-- 2. 主鍵 IN 查詢回表取完整數據
SELECT id, title, url, ... FROM collect WHERE id IN (90001,90002,...);  -- 0.00s ✅

PHP 代碼片段：

$db->pagesize = 20;
$sql = "SELECT id FROM collect WHERE vtype=$vtype";
$db->execute($sql);
$strpage = $db->strpage();  // 保存分頁 HTML

$ids = [];
while ($rs = $db->fetch_array()) $ids[] = $rs['id'];
$id_str = implode(',', $ids);

$db->pagesize = 0;  // 關閉分頁
$db->execute("SELECT id,title,url,... FROM collect WHERE id IN ($id_str)");
// 輸出數據 + $strpage

實測數據對比（160 萬行 / 15G 表）

核心心得

1. 索引設計決定性能上限：一個複合索引可帶來千倍差距。
2. 大偏移分頁本質是掃描：MySQL 無法跳躍到 B+ 樹中間節點，必須從頭遍歷。
3. 業務妥協優於技術強行：若業務允許「不看第 10 萬頁」，直接限制最大頁碼最省力。
4. 框架隱藏 SQL 是大忌：大型系統必須掌控每條 SQL，ORM 僅適合中小項目。

作者：ERDP技術架構