萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 千萬級資料分頁優化:從 9 秒到 0.04 秒的實戰紀錄

問題現場

表 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 越大越慢。

優化演進

階段 1:利用主鍵索引覆蓋掃描

-- 僅查主鍵,極快 0.04 秒
SELECT id FROM collect ORDER BY id LIMIT 90000, 10;

階段 2:子查詢定位 ID 起點

SELECT id, title FROM collect 
WHERE id >= (SELECT id FROM collect ORDER BY id LIMIT 90000, 1) 
LIMIT 10;

原理:子查詢走主鍵索引快速定位 offset 位置的 ID,主查詢利用 id >= ? 範圍掃描。

階段 3:帶 WHERE 條件的殺手鐧 — 覆蓋索引

加條件後上述方法失效:

-- 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)。

階段 4:兩階段查詢落地應用

// 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 萬行場景。

copyright © 萬盛學電腦網 all rights reserved