MySQL LIMIT 大數據分頁優化終極指南：子查詢、複合索引、IN 子句與 PHP 實戰方案

核心痛點：深分頁效能崩塌

LIMIT 100000, 10 需掃描前 10 萬行再丟棄，耗時隨 OFFSET 線性增長。

優化方案對比測試（10萬筆測試資料）

四大優化核心模式

模式一：延遲關聯 / 子查詢定位主鍵

SELECT * FROM `table` 
WHERE id >= (SELECT id FROM `table` ORDER BY id LIMIT 90000, 1) 
LIMIT 10;

利用主鍵索引快速定位，避免掃描大量非目標行。

模式二：複合索引覆蓋 WHERE + ORDER BY

-- 針對 WHERE vtype=1 ORDER BY id LIMIT 90000,10
ALTER TABLE `collect` ADD INDEX idx_vtype_id (vtype, id);

-- 必須只 SELECT 主鍵才能用到覆蓋索引
SELECT id FROM `collect` WHERE vtype=1 ORDER BY id LIMIT 90000,10; -- 0.04s
-- 加上 title 即失效回表，退化為 8-9s

模式三：IN 子句二次查詢（工程落地首選）

-- 步驟1：高效取得目標頁 ID 字串 "90001,90002,..."
SELECT id FROM `collect` WHERE vtype=1 ORDER BY id LIMIT 90000,10;

-- 步驟2：主鍵 IN 查詢極快
SELECT id,title,url FROM `collect` WHERE id IN (90001,90002,...);

模式四：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)");
// 輸出結果...

百萬級資料實測心得

• 單表 100 萬行，INDEX(vtype, id) + IN 方案，90 萬 OFFSET 查詢穩定在 0.1-0.2 秒。
• 避免 SELECT *，只取必要欄位。
• 框架若隱藏 SQL，大專案請手寫關鍵查詢，掌控索引與執行計劃。