前言

那晚，大約晚上 11 點，我與 Chaya 在麗江的洱海酒店享受兩人世界的快樂，電商平臺的運維大群突然炸開了鍋。

監控系統發出刺耳的警報：訂單查詢接口響應時間從200ms 飆升到 12 秒，數據庫 CPU 利用率突破 90%。

發現事故根源竟是一個看似平常的查詢——用戶中心的歷史訂單分頁查詢。

這背后隱藏的正是MySQL 深度分頁的典型問題——數據越往后查，速度越讓人抓狂。

其本質是傳統分頁機制在數據洪流下的失效：LIMIT 100000,10這樣的查詢，會讓數據庫像逐頁翻閱千頁文檔的抄寫員，機械地掃描前 10 萬條記錄再丟棄。

當數據量突破千萬級時，這種暴力掃描不僅造成 I/O 資源的巨大浪費，更會導致關鍵業務查詢的鏈式阻塞。

本文將深入拆解深度分頁的技術黑箱，通過電商訂單表等真實場景，揭示 B+樹索引與分頁機制的碰撞奧秘，并給出 6 種經過實戰檢驗的優化方案。

深度分頁

假設電商平臺的訂單表存儲了 2000 萬條記錄，表結構如下，主鍵是 id，(user_id + create_time )聯合索引。

REATE TABLE `orders` (
  `id` int NOT NULL AUTO_INCREMENT, -- id自增
  `user_id` int DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP, -- 創建時間默認為當前時間
  PRIMARY KEY (`id`),
  KEY `idx_userid_create_time` (`user_id`, `create_time`) -- 創建時間設置為普通索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

我們的分頁語句一般這么寫。

SELECT * FROM orders
WhERE user_id = 'Chaya'
ORDER BY create_time DESC
LIMIT 0, 20;

當用戶查詢第 1000 頁的訂單（每頁 20 條），常見的分頁寫法如下。

SELECT * FROM orders
WhERE user_id = 'Chaya'
ORDER BY create_time DESC
LIMIT 19980, 20;

執行流程解析：

1. 使用聯合索引 idx_userid_create_time讀取 19980 + 20 條數據。
2. 利用索引在內存中排序。
3. 丟棄 19880 條數據，返回剩下的 20 條。

隨著頁碼增加，需要處理的數據量會線性增長。當 offset 達到 10w 時，查詢耗時會顯著增加，達到 100w 時，甚至需要數秒。

游標分頁（Cursor-based Pagination）

適用場景：支持連續分頁（如無限滾動）。

實現原理：基于有序且唯一的字段（如自增主鍵 ID），通過記錄上一頁最后一條記錄的標識（如主鍵 ID），將WHERE條件與索引結合，跳過已查詢數據。

-- 第一頁
SELECT *
FROM orders
WhERE user_id = 'Chaya'
ORDER BY create_time DESC
LIMIT 20;

-- 后續頁（記錄上一頁查詢得到的 id，id=1000）
SELECT id, user_id, amount
FROM orders
WHERE id > 1000 AND user_id = 'Chaya'
ORDER BY create_time DESC
LIMIT 20;

索引樹直接定位到order_id=1000的葉子節點，僅掃描后續 1000 條記錄，避免遍歷前 100 萬行數據。

優勢

• 完全避免 OFFSET掃描，時間復雜度從 O(N)降為 O(1)
• 天然支持順序分頁場景（如無限滾動加載）

限制

• 不支持隨機跳頁（如直接跳轉到第 1000 頁）
• 需保證排序字段唯一且有序

延遲關聯（Deferred Join）

實現原理：通過子查詢先獲取主鍵范圍，再關聯主表獲取完整數據。減少回表次數，利用覆蓋索引優化性能。

SELECT t1.*
FROM orders t1
INNER JOIN (
    SELECT id
    FROM orders
    WhERE user_id = 'Chaya'
		ORDER BY create_time DESC
    LIMIT 1000000, 20
) t2 ON t1.id = t2.id;

優勢

• 子查詢僅掃描索引樹，避免回表開銷。
• 主查詢通過主鍵精確匹配，效率極高。
• 性能提升可達 10 倍以上（實測從 1.2 秒降至 0.05 秒）。

覆蓋索引優化

實現原理：創建包含查詢字段的聯合索引，避免回表操作。例如索引設計為(user_id, id, create_time, amount)。

ALTER TABLE orders ADD INDEX idx_cover (user_id, id, create_time,amount);

SELECT id, user_id, amount, create_time
FROM orders USE INDEX (idx_cover)
WhERE user_id = 'Chaya'
ORDER BY create_time DESC
LIMIT 1000000, 20;

Chaya：訂單很多字段的，我想查看更多訂單細節怎么辦？

這個問題問得好，我們可以設計訂單列表和詳情頁，通過上述方案做訂單列表的分頁查詢；點擊詳情頁的時候，在使用訂單 id 查詢訂單。

分區表

實現原理：將大表按時間或哈希值水平拆分。例如按月分區，每個分區獨立存儲，縮小掃描范圍。

-- 按月份RANGE分區
ALTER TABLE orders PARTITION BY RANGE (YEAR(create_time)*100 + MONTH(create_time)) (
    PARTITION p202501 VALUES LESS THAN (202502),
    PARTITION p202502 VALUES LESS THAN (202503)
);

-- 查詢特定月份數據
SELECT * FROM orders PARTITION (p202501)
WHERE user_id = 'chaya'
ORDER BY create_time DESC
LIMIT 20;

預計算分頁（Precomputed Pages）

實現原理：通過異步任務預生成分頁數據，存儲到 Redis 或物化視圖。適合數據更新頻率低的場景。

實現步驟

• 定時任務生成熱點頁數據。
• 存儲到 Redis 有序集合。

ZADD order_pages 0 "page1_data" 1000 "page2_data"

• 查詢的時候直接獲取緩存數據

-- 偽代碼：獲取第N頁緩存
ZRANGEBYSCORE order_pages (N-1)*1000 N*1000

集成 Elasticsearch

實現原理：利用 ES 的search_after特性，通過游標實現深度分頁。結合數據同步工具保證一致性。

實現流程：canal+kafka 訂閱 MySQL binlog 將數據異構到 elasticsearch。

elasticsearch 保存的數據主要就是我們的查詢條件和訂單 id。

訂單表 → Binlog → Canal → Kafka → Elasticsearch、Hbase

在查詢的時候，通過 Elasticsearch 查詢得到訂單 ID，最后在根據訂單 ID 去 MySQL 查詢。

或者我們可把數據全量同步到 Hbase 中查詢，在 Hbase 中查詢完整的數據。