MySQL 深分頁（如?LIMIT 100000, 10）本質是?高代價的偏移量掃描，可通過以下方案優化，附核心原理和實操示例：

---

一、深分頁為什么慢？

SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 10;

執行過程：

1. 通過二級索引或全表掃描定位到第 1 行

2. 順序掃描 100000 + 10 行

3. 丟棄前 100000 行，返回最后 10 行
   ??問題：掃描 100010 行但僅返回 10 行，I/O 和 CPU 浪費嚴重

---

二、優化方案詳解

方案 1：延遲關聯法（最優解）

原理：
先查主鍵，再用主鍵回表取數據，減少回表量

SELECT * FROM orders 
INNER JOIN (SELECT id FROM orders ORDER BY create_time DESC LIMIT 100000, 10  -- 只取主鍵
) AS tmp USING(id);

性能對比：

原查詢 (直接 LIMIT)	延遲關聯法
1.2s	0.05s

??適用場景：任何排序字段的深分頁
📌?關鍵點：子查詢必須使用覆蓋索引

---

方案 2：游標分頁（連續分頁神器）

原理：
記錄上一頁最后一條記錄的標識值，作為下一頁起點

-- 第一頁
SELECT * FROM orders 
ORDER BY id DESC 
LIMIT 10;-- 下一頁（假設上一頁最后 id=10200）
SELECT * FROM orders 
WHERE id < 10200  -- 游標定位
ORDER BY id DESC 
LIMIT 10;

性能：
???恒定為 10 行掃描，與頁碼無關

???限制：

1. 只能連續頁順序訪問（不可跳頁）

2. 排序字段必須唯一（否則丟數據）

---

方案 3：范圍分頁（時間維度優化）

原理：
用時間范圍縮小掃描區間

SELECT * FROM orders 
WHERE create_time < '2023-01-01'  -- 上頁的時間邊界
ORDER BY create_time DESC 
LIMIT 10;

性能：
🚀 掃描行數 = 目標數據行數 + 索引定位開銷

??適用場景：按時間排序的分頁
📌?優化關鍵：在?create_time?上建索引

---

方案 4：業務層優化（終極方案）

1. 禁止跳頁：
   只提供“上一頁/下一頁”按鈕（本質是游標分頁）

2. 數據歸檔：
   將歷史數據遷移到歸檔表，減少主表數據量

   -- 遷移3個月前數據
   INSERT INTO orders_archive 
   SELECT * FROM orders WHERE create_time < NOW()-INTERVAL 3 MONTH;DELETE FROM orders WHERE create_time < NOW()-INTERVAL 3 MONTH;

3. 搜索引擎分流：
   將分頁查詢交給 Elasticsearch 等專用引擎

---

三、索引設計黃金法則

錯誤示例：

SELECT * FROM users ORDER BY name LIMIT 800000, 10;
-- 全表掃描 filesort

正確索引方案：

1. 覆蓋索引加速：

   ALTER TABLE users ADD INDEX idx_cover(name, id, age);

2. 延遲關聯寫法：

   SELECT * FROM users 
   JOIN (SELECT id FROM users ORDER BY name LIMIT 800000, 10
   ) tmp USING(id)

---

四、各方案性能對比（百萬數據）

方案	掃描行數	執行時間	適用場景
原生 LIMIT	800010	1.8s	前100頁
延遲關聯	800010 + 10行回表	0.15s	通用
游標分頁	10	0.01s	連續分頁
范圍分頁	依賴篩選區間	0.03s	按時間/數值排序
業務層禁止跳頁	10	0.01s	C端用戶場景

💡?延遲關聯?是通用性最強的優化方案

---

五、實戰排查工具

用?EXPLAIN?診斷掃描類型

EXPLAIN SELECT * FROM orders LIMIT 100000, 10;

• ???危險信號：
  type: ALL（全表掃描）
  Extra: Using filesort（內存排序）

---

總結：優化決策樹

最終建議：

1. 優先用?延遲關聯 + 覆蓋索引

2. 次選用?游標分頁（體驗犧牲換性能）

3. 終極方案?Elasticsearch 專庫分頁查詢