MySQL 百萬級分頁性能優化：從 Limit 深分頁痛點到複合索引與 IN 查詢重構

痛點：深分頁的性能崩塌

測試表 collect（10 萬條，1.6GB），主鍵 id，索引 vtype：

• SELECT id,title FROM collect LIMIT 1000,10 → 0.01s
• SELECT id,title FROM collect LIMIT 90000,10 → 8-9s（慢 900 倍！）

根因：MySQL 的 LIMIT offset, rows 需掃描並丟棄前 offset 行，offset 越大，掃描成本線性增長，與表大小強相關。

第一階段：主鍵索引覆蓋掃描

-- 僅查主鍵，走聚簇索引，極快
SELECT id FROM collect ORDER BY id LIMIT 90000,10;  -- 0.04s

-- 經典改寫：子查詢定位起始 ID
SELECT id,title FROM collect 
WHERE id >= (SELECT id FROM collect ORDER BY id LIMIT 90000,1) 
LIMIT 10;  -- 0.04s

原理：利用主鍵有序特性，將「掃描丟棄」轉為「索引定位 + 範圍查詢」。

第二階段：Where 條件下的複合索引設計

加入 WHERE vtype=1 後，原優化失效：

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

實驗發現：單列索引 vtype 只能過濾，無法避免 ORDER BY id 的 filesort 或大量回表。

複合索引黃金法則

-- 正確設計：WHERE 條件列在前，ORDER BY/LIMIT 列在後
ALTER TABLE collect ADD INDEX idx_vtype_id (vtype, id);

-- 驗證
SELECT id FROM collect WHERE vtype=1 LIMIT 90000,10;  -- 0.04s ✅
SELECT id,title FROM collect WHERE vtype=1 LIMIT 90000,10;  -- 8-9s ❌ (需回表)

結論：Where 條件列優先 + Limit 使用的排序列次之 + 僅 Select 主鍵，才能實現真正的覆蓋索引掃描。

第三階段：業務層重構 - 兩次查詢 + IN 列表

為兼容框架分頁組件（需返回完整字段），採用「ID 先行」策略：

核心邏輯

1. 第一次查詢：走複合索引，只取 ID，拼成字符串 '123,90000,12000'
2. 第二次查詢：SELECT * FROM collect WHERE id IN (...)，主鍵查找極快

輕量級框架整合代碼

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

// 拼接 ID 字符串
$strid = '';
while($rs = $db->fetch_array()) {
    $strid .= $rs['id'].',';
}
$strid = substr($strid, 0, -1);

// 第二次查詢：關閉分頁，直接按 ID 列表查詢
$db->pagesize = 0;
$db->execute("SELECT id,title,url,sTime,gTime,vtype,tag FROM collect WHERE id IN ($strid)");

// 模板輸出
while($rs = $db->fetch_array()): ?>

    
    
    ...