MySQL LIMIT 深度優化：百萬/千萬級分頁效能從 8 秒降至 0.04 秒的複合索引與 IN 子查詢實戰

問題現場：深分頁效能崩塌

-- 表結構
CREATE TABLE collect (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title CHAR(100),           -- 定長
  info TEXT,
  vtype TINYINT,
  KEY idx_vtype (vtype)
) ENGINE=InnoDB;

-- 資料量：100萬行，表大小 1.6GB

-- 慢查詢 (9秒)
SELECT id, title FROM collect WHERE vtype=1 ORDER BY id LIMIT 900000, 10;

-- 快查詢 (0.04秒)
SELECT id FROM collect WHERE vtype=1 ORDER BY id LIMIT 900000, 10;

根因剖析：為什麼 SELECT * / 非主鍵列 會慢？

• LIMIT offset, rows 機制：MySQL 必須掃描並丟棄 offset 行，再取 rows 行。
• 無覆蓋索引時：
  1. 走 idx_vtype 定位符合條件的主鍵 id。
  2. 按 id 排序 (已有序)。
  3. 回表 讀取聚簇索引取得 title, info。
  4. 丟棄前 900,000 行，取後 10 行。
• 回表 90 萬次隨機 IO → 效能災難。

解決方案一：覆蓋索引 (只查主鍵/索引列)

-- 建立複合索引 (WHERE 條件列在前，ORDER BY 列在後)
ALTER TABLE collect ADD INDEX idx_vtype_id (vtype, id);

-- 查詢僅取索引覆蓋列
SELECT id FROM collect WHERE vtype=1 ORDER BY id LIMIT 900000, 10;
-- 0.04 秒！Extra: Using index

核心法則：WHERE 列 + ORDER BY 列 + SELECT 列 全在索引中 → 覆蓋索引 → 避免回表 → 極速。

解決方案二：延遲關聯 / 子查詢優化 (需查非索引列)

-- 步驟 1：子查詢只走覆蓋索引取 id
SELECT id FROM collect WHERE vtype=1 ORDER BY id LIMIT 900000, 10;

-- 步驟 2：應用層拼接 id 列表，再用 IN 查詢完整列
SELECT id, title, info FROM collect WHERE id IN (900001, 900002, ...);
-- 0.00 秒！主鍵點查，極速

實戰代碼模式 (PHP 框架整合)

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

// 收集當頁所有 id
$strid = '';
while ($rs = $db->fetch_array()) {
    $strid .= $rs['id'] . ',';
}
$strid = rtrim($strid, ',');

// 關閉分頁，直接查詳細資料
$db->pagesize = 0;
$db->execute("SELECT id, title, url, sTime, gTime, vtype, tag FROM collect WHERE id IN ($strid)");

// 輸出結果
while ($rs = $db->fetch_array()) { ... }
echo $strpage;

效能對照表 (100萬/1000萬資料)

進階：游標式分頁 (無 Offset、適合無限滾動/APP 列表)

-- 首頁
SELECT id, title FROM collect WHERE vtype=1 ORDER BY id LIMIT 20;

-- 記錄最後一筆 id = 900020
-- 下一頁
SELECT id, title FROM collect WHERE vtype=1 AND id > 900020 ORDER BY id LIMIT 20;
-- 無 Offset 掃描，極速穩定，適合高併發

避坑指南

• ❌ LIMIT 1000000, 10 直接用於大表 → 全表掃描 + 回表。
• ❌ 複合索引順序錯誤 INDEX (id, vtype) → 無法服務 WHERE vtype=1 ORDER BY id。
• ❌ IN 列表過長 (>1000) → 解析慢、優化器可能轉表掃描 → 分批或用臨時表 JOIN。
• ✅ 覆蓋索引設計原則：WHERE 等值列 + 範圍/排序列 + SELECT 列。
• ✅ 大表分頁優先考慮游標式；必須 Offset 時用「子查詢取 ID + IN 回查」雙查詢模式。

分頁優化本質是「減少掃描行」與「避免回表」。複合索引覆蓋 + 雙查詢模式 + 游標式分頁，三板斧解決 99% 深分頁效能問題。千萬級資料同樣適用，核心在於索引設計與查詢改寫，而非分表分庫。