表 collect(10 萬行,1.6 GB),欄位:id(PK), title, info(TEXT), vtype(TINYINT, 索引)。
-- 快:0.01 秒
SELECT id, title FROM collect LIMIT 1000, 10;
-- 慢:8-9 秒!
SELECT id, title FROM collect LIMIT 90000, 10;
原因:LIMIT offset, n 需掃描並丟棄前 offset 行,offset 越大越慢。
-- 僅查主鍵,極快 0.04 秒
SELECT id FROM collect ORDER BY id LIMIT 90000, 10;
SELECT id, title FROM collect
WHERE id >= (SELECT id FROM collect ORDER BY id LIMIT 90000, 1)
LIMIT 10;
原理:子查詢走主鍵索引快速定位 offset 位置的 ID,主查詢利用 id >= ? 範圍掃描。
加條件後上述方法失效:
-- 8-9 秒
SELECT id FROM collect WHERE vtype=1 ORDER BY id LIMIT 90000, 10;
解法:建立複合索引 (vtype, id),且只查主鍵:
ALTER TABLE collect ADD INDEX idx_vtype_id (vtype, id);
-- 0.04 秒!
SELECT id FROM collect WHERE vtype=1 ORDER BY id LIMIT 90000, 10;
鐵律:WHERE 條件列在前,ORDER BY / LIMIT 使用的主鍵在後,SELECT 僅包含索引覆蓋的列(此處為 id)。
// 1. 只查 ID,走覆蓋索引,極快
$sql = "SELECT id FROM collect WHERE vtype=$vtype";
$db->execute($sql);
while ($rs = $db->fetch_array()) $ids[] = $rs['id'];
$idStr = implode(',', $ids);
// 2. 關閉分頁,按 ID 列表精準取回完整欄位
$db->pagesize = 0;
$db->execute("SELECT id,title,url,... FROM collect WHERE id IN ($idStr)");
// 3. 輸出結果...
此法將「大 offset 掃描」轉為「索引覆蓋掃描 + 主鍵精準查找」,百萬級分頁穩定在 0.0x 秒。
測試發現:單表 100 萬行 + 覆蓋索引 + 兩階段查詢,效能優於分表且維護成本低。分表適用於寫入極高或單表超 5000 萬行場景。