MySQL 百萬/千萬級分頁優化終極指南：複合索引 + IN 查詢實現毫秒級響應

傳統 LIMIT offset, size 在大偏移量下效能崩塌（需掃描並捨棄前 N 筆）。本文揭示從基礎優化到生產級方案的完整演進路徑。

優化演進路徑

❌ 初級寫法（百萬級慘不忍睹）

SELECT * FROM t ORDER BY id LIMIT 1000000, 10;  -- 數十秒

⚠️ 子查詢優化（僅改善部分）

SELECT * FROM t WHERE id >= (SELECT id FROM t ORDER BY id LIMIT 1000000, 1) LIMIT 10;  -- 0.x 秒

✅ 理想場景：連續 ID 範圍查詢（極快）

SELECT * FROM t WHERE id BETWEEN 1000000 AND 1000010;  -- 比上快 5-10 倍

🎯 通用最佳方案：複合索引覆蓋 + 兩階段查詢

核心洞察：WHERE 條件欄位 + ORDER BY/LIMIT 使用的主鍵建立複合索引，且僅查主鍵可走索引覆蓋掃描。

-- 針對 vtype=1 分頁場景
ALTER TABLE collect ADD INDEX idx_vtype_id (vtype, id);

-- 第一階段：只查主鍵，走覆蓋索引，極快
SELECT id FROM collect WHERE vtype=1 ORDER BY id LIMIT 90000, 10;  -- 0.04 秒

-- 第二階段：用 IN 查詢回表取完整欄位
SELECT id, title, url ... FROM collect WHERE id IN (90000, 90001, ...);  -- 0 秒級

生產級代碼模式

$sql = "SELECT id FROM collect WHERE vtype=$vtype ORDER BY id";
$db->execute($sql);
$ids = [];
while ($row = $db->fetch_array()) $ids[] = $row['id'];
$id_str = implode(',', $ids);

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

關鍵原則總結

1. 複合索引順序：WHERE 條件欄位在前，排序/分頁欄位（通常是主鍵）在後
2. 首階段只 SELECT 主鍵：觸發 Index Covering Scan，避免回表
3. 次階段用 IN 列表：MySQL 對 IN (主鍵列表) 同樣走索引查找，極高效
4. 避免框架封裝 SQL：大型系統須掌控 SQL 與索引設計，框架適用於小型專案

實測數據參考

• 100 萬資料、160 萬列、15G 表、190M 索引：走複合索引 LIMIT 900000,10 耗時 0.04 秒
• 同場景傳統 LIMIT 需 8-9 秒