萬盛學電腦網

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

MySQL 千萬級數據快速分頁優化:從 LIMIT 偏移到覆蓋索引與 ID 回表實戰

問題本質:深分頁性能崩塌

SELECT * FROM t ORDER BY id LIMIT 1000000, 10;  -- 掃描 100 萬行再丟棄,極慢

MySQL LIMIT offset, rows 需掃描 offset + rows 行,偏移量大時 I/O 與 CPU 暴增。

優化演進路線

階段 1:子查詢定位 ID(利用覆蓋索引)

SELECT * FROM t 
WHERE id >= (SELECT id FROM t ORDER BY id LIMIT 1000000, 1) 
LIMIT 10;

子查詢只掃描主鍵索引(覆蓋索引),速度提升數十倍。

階段 2:BETWEEN 範圍查詢(ID 連續時最快)

SELECT * FROM t WHERE id BETWEEN 1000000 AND 1000010;

直接索引範圍掃描,比子查詢再快 5-10 倍。前提:ID 連續無洞。

階段 3:ID 列表 IN 查詢(ID 不連續通用方案)

SELECT * FROM t WHERE id IN (10000, 100000, 1000000, ...);

兩步驟:

  1. 高效查詢取目標 ID 列表(利用覆蓋索引)
  2. 主查詢 IN 回表取完整數據

實戰案例:百萬級新聞系統分頁優化

表結構

collect(id PK, title, info TEXT, vtype TINYINT INDEX)

優化前後對比

查詢9萬偏移耗時說明
SELECT id,title FROM collect LIMIT 90000,108-9 秒全表掃描+文件排序
SELECT id FROM collect ORDER BY id LIMIT 90000,100.04 秒主鍵覆蓋索引
SELECT id FROM collect WHERE vtype=1 ORDER BY id LIMIT 90000,108-9 秒vtype 索引無法避免排序
SELECT id FROM collect WHERE vtype=1 LIMIT 90000,100.05 秒無排序,但非深分頁

終極優化:復合索引 (vtype, id)

ALTER TABLE collect ADD INDEX idx_vtype_id (vtype, id);
SELECT id FROM collect WHERE vtype=1 ORDER BY id LIMIT 90000,10;  -- 0.04 秒
SELECT id,title FROM collect WHERE vtype=1 ORDER BY id LIMIT 90000,10;  -- 8-9 秒(回表慢)

關鍵洞察:復合索引僅加速 SELECT id(覆蓋索引),SELECT * 仍需回表。

工程化落地:兩階段查詢模式

$db = dblink();
$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,vtype FROM collect WHERE id IN ($id_str)");

// 輸出數據行 + $strpage 分頁條

擴展優化技巧

  • 長字符串字段:存儲 CRC32(url) 或 MD5(url) 索引,查詢時比對哈希值
  • 定長表:MyISAM 固定行長可加速 LIMIT 定位,但 InnoDB 無此優勢
  • 分表:單表超 30 萬配合上述方案,超百萬建議分表 + 復合索引
  • 避免框架封裝:大數據量項目必須掌控 SQL,ORM 框架易產生低效查詢

性能極限測試

  • 160 萬行、15G 表、190M 索引:LIMIT 走索引 0.49s
  • 同規模 IN (id列表):< 0.01s
  • 結論:覆蓋索引取 ID + IN 回表 是百萬/千萬級分頁的通用最優解

總結

  1. 深分頁用 LIMIT offset, n 是性能殺手
  2. 建立 (WHERE欄位, ORDER BY欄位) 復合索引
  3. 先 SELECT id 利用覆蓋索引快速定位
  4. 再 SELECT * ... WHERE id IN (...) 回表取詳情
  5. 分頁邏輯下推應用層,SQL 層只負責高效取數
copyright © 萬盛學電腦網 all rights reserved