萬盛學電腦網

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

MySQL 分頁查詢優化實戰:從 LIMIT 深分頁災難到延遲關聯、ID 定位、子查詢多種方案對比

深分頁災難現場

SELECT * FROM `user` ORDER BY uid ASC LIMIT 1000000, 20;
-- MySQL 需掃描 1,000,020 行,丟棄前 100 萬,效能隨偏移量線性惡化

四大優化方案對比

方案一:ID 定位法(適用連續分頁、ID 遞增無大間隙)

-- 第 1 頁
SELECT * FROM `user` ORDER BY uid ASC LIMIT 20;
-- 記錄最後一行 uid = 10020

-- 第 2 頁
SELECT * FROM `user` WHERE uid > 10020 ORDER BY uid ASC LIMIT 20;
-- 索引範圍掃描,極快,LIMIT 偏移量固定為 0
  • 優點:零深分頁開銷,適合「下一頁/無限滾動」。
  • 缺點:不支援任意跳頁、ID 有刪除間隙會少查、並發寫入可能導致重複/遺漏(需配合唯一游標)。

方案二:BETWEEN 範圍查詢(適用 ID 連續、可預估偏移)

-- 第 50001 頁 (每頁 20) → ID 範圍 1000001 ~ 1000020
SELECT * FROM `user` WHERE uid BETWEEN 1000001 AND 1000020 ORDER BY uid;
  • 需應用層維護 ID 映射或預估,適用於 ID 連續且不常刪除的場景。

方案三:延遲關聯 / 子查詢先取 ID(通用、支援任意跳頁)

-- 子查詢只掃描主鍵索引(覆蓋索引),極快
SELECT t.* 
FROM (
    SELECT id FROM `user` ORDER BY uid LIMIT 1000000, 20
) s
JOIN `user` t ON t.id = s.id;
-- 或用 >= 子查詢
SELECT * FROM `user` 
WHERE id >= (SELECT id FROM `user` ORDER BY uid LIMIT 1000000, 1)
LIMIT 20;
  • 內層只讀主鍵索引(Using index),外層回表僅 20 行。
  • 適用任意頁碼跳轉,效能穩定。

方案四:冗餘索引表 / NoSQL / 分表(架構層面)

  • 建立專用索引表(只存 ID + 排序欄位),定期同步。
  • 熱點分頁下推 Redis/ZSet(ZRANGE key start stop),極速。
  • 按時間/業務分表,單表數據量控制在 2000 萬以內,深分頁自然消失。

方案選型決策樹

  1. 業務是「連續翻頁/無限滾動」 → **方案一(ID 定位)**。
  2. 業務需「任意跳頁」、ID 相對連續 → **方案三(延遲關聯)**。
  3. 單表 > 2000 萬、頻繁深分頁 → **方案四(分表/Redis 索引表)**。
  4. 避免直接使用 LIMIT offset, rows 當 offset > 10000。

EXPLAIN 驗證優化效果

EXPLAIN SELECT * FROM `user` WHERE uid > 10020 ORDER BY uid LIMIT 20;
-- type=range, key=PRIMARY, rows=20  ✅

EXPLAIN SELECT t.* FROM (SELECT id FROM `user` ORDER BY uid LIMIT 1000000, 20) s JOIN `user` t ON t.id=s.id;
-- 衍生表 type=index (覆蓋索引), 外層 type=eq_ref, rows=20  ✅
copyright © 萬盛學電腦網 all rights reserved