深分頁災難現場
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 萬以內,深分頁自然消失。
方案選型決策樹
- 業務是「連續翻頁/無限滾動」 → **方案一(ID 定位)**。
- 業務需「任意跳頁」、ID 相對連續 → **方案三(延遲關聯)**。
- 單表 > 2000 萬、頻繁深分頁 → **方案四(分表/Redis 索引表)**。
- 避免直接使用
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 ✅