一、深度分頁為什么慢？

當執行?SELECT * FROM orders ORDER BY id LIMIT 1000000, 10?時：

• MySQL 會掃描前 1,000,010 行，丟棄前 100 萬行，僅返回 10 行。
• 偏移量（offset）越大，掃描行數越多，時間復雜度為?O(N+M)（N = 偏移量，M = 每頁條數）。

核心痛點：LIMIT?本質是 “跳過” 數據，而非 “直接定位”，導致全表掃描。

二、5 大優化方案（按優先級排序）

🔍 方案 1：覆蓋索引 + 延遲關聯（通用最優解）

原理：先通過索引定位主鍵，再批量回表，減少無效掃描。
實現：

sql

-- 1. 創建覆蓋索引（包含排序字段+主鍵）
ALTER TABLE orders ADD INDEX idx_created_at_id (created_at, id);-- 2. 優化查詢（子查詢只查索引，主查詢回表）
SELECT o.* 
FROM orders o
INNER JOIN (SELECT id  -- 僅需主鍵（覆蓋索引）FROM orders ORDER BY created_at, id  -- 利用索引排序LIMIT 1000000, 10  -- 僅掃描 10 行索引
) AS tmp ON o.id = tmp.id;

效果：掃描行數從?1,000,010?降至?10，速度提升 10 萬倍！
適用場景：所有帶排序的分頁（如訂單、日志）。

📌 方案 2：游標分頁（適合連續翻頁）

原理：記錄上一頁最后一條的?id，避免?OFFSET。
示例：

sql

-- 第 1 頁（初始查詢）
SELECT * FROM orders ORDER BY id DESC LIMIT 10;  -- 假設最后一條 id=1000-- 第 2 頁（基于游標）
SELECT * 
FROM orders 
WHERE id < 1000  -- 定位起始點
ORDER BY id DESC 
LIMIT 10;  -- 僅掃描 10 行

優點：時間復雜度?O(M)，與頁碼無關；適合無限滾動（如抖音刷視頻）。
缺點：不支持隨機跳頁（如直接跳轉到第 1000 頁）。

🗂? 方案 3：范圍分片（依賴有序數據）

原理：按時間 / ID 分段，縮小查詢范圍。
示例：

sql

-- 按月份分區查詢（假設數據按月遞增）
SELECT * 
FROM orders 
WHERE created_at BETWEEN '2023-10-01' AND '2023-10-31'
ORDER BY id 
LIMIT 10;

適用場景：時間序列數據（如報表、日志），用戶可接受 “按范圍篩選”。

💡 方案 4：預計算分頁（冷數據專用）

原理：提前計算分頁書簽，存入匯總表。
實現：

sql

-- 創建匯總表（每日凌晨更新）
CREATE TABLE order_pager (page_num INT PRIMARY KEY,min_id BIGINT,max_id BIGINT
);-- 插入分頁書簽（每頁 100 條）
INSERT INTO order_pager
SELECT FLOOR((id-1)/100) + 1 AS page_num,MIN(id) AS min_id,MAX(id) AS max_id
FROM orders
GROUP BY page_num;-- 查詢第 1000 頁
SELECT * 
FROM orders 
WHERE id BETWEEN (SELECT min_id FROM order_pager WHERE page_num=1000) AND (SELECT max_id FROM order_pager WHERE page_num=1000)
LIMIT 100;

優點：查詢時間穩定，適合歷史數據（如年報）；缺點：存儲冗余。

🚀 方案 5：Elasticsearch 兜底（海量數據）

原理：利用 ES 的?search_after?避免深度分頁性能衰減。
示例：

json

GET /orders/_search
{"size": 10,"sort": ["_id"],  -- 按主鍵排序"search_after": [1000000],  -- 上一頁最后一條的 _id"query": { ... }
}

適用場景：百萬級以上數據，需復雜查詢（如全文搜索）。

三、索引設計黃金法則

場景	索引建議	示例
時間 + 主鍵排序	復合索引（時間，主鍵）	(created_at, id)
多條件過濾 + 排序	最左匹配索引（WHERE > ORDER BY）	(status, created_at, id)
字符串排序	前綴索引（平衡長度與選擇性）	name(20)?（取前 20 字符）

?? 注意：索引非越多越好，單表索引不超過 5 個，避免冗余。

四、方案對比與選擇指南

方案	時間復雜度	適用場景	推薦指數
覆蓋索引 + 延遲關聯	O(M)	通用場景（90% 業務首選）	?????
游標分頁	O(M)	連續翻頁（如 App 列表）	????
預計算分頁	O(1)	冷數據歷史查詢	???
Elasticsearch	O(logN)	海量數據 + 復雜查詢	????

決策樹：

1. 數據量 < 10 萬：直接?LIMIT，無需優化。
2. 10 萬～100 萬：方案 1（覆蓋索引）。
3. 100 萬～1000 萬：方案 1 + 方案 2（游標）。
4. 千萬級以上：方案 5（ES）+ 方案 1 兜底。

五、實戰避坑指南

1. ** 避免 SELECT ***：只查必要字段，減少回表數據量。
2. 監控執行計劃：

   sql

   EXPLAIN SELECT * FROM orders ...;  -- 重點看 `rows` 列，理想值接近 LIMIT 數量。
   

3. 慢查詢日志：捕獲真實慢查詢（如?LIMIT 500000,10?超過 1 秒）。
4. 業務妥協：限制最大分頁深度（如 App 最多顯示 500 頁），避免極端場景。

總結

深度分頁的核心優化思路是?“跳過掃描，直接定位”：

• 熱數據：用覆蓋索引 + 延遲關聯，確保每次查詢只掃描少量索引。
• 冷數據：預計算分頁或歸檔到 ES，犧牲空間換時間。
• 業務層：結合游標分頁和分頁深度限制，避免數據庫被拖垮。

通過這一套組合拳，可將深度分頁的耗時從 “秒級” 優化到 “毫秒級”，輕松應對百萬級數據分頁！