前言

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

監(jiān)控系統(tǒng)發(fā)出刺耳的警報(bào)：訂單查詢接口響應(yīng)時間從200ms 飆升到 12 秒，數(shù)據(jù)庫 CPU 利用率突破 90%。

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

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

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

當(dāng)數(shù)據(jù)量突破千萬級時，這種暴力掃描不僅造成 I/O 資源的巨大浪費(fèi)，更會導(dǎo)致關(guān)鍵業(yè)務(wù)查詢的鏈?zhǔn)阶枞?/p>

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

深度分頁

假設(shè)電商平臺的訂單表存儲了 2000 萬條記錄，表結(jié)構(gòu)如下，主鍵是 id，(user_id + create_time )聯(lián)合索引。

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, -- 創(chuàng)建時間默認(rèn)為當(dāng)前時間
  PRIMARY KEY (`id`),
  KEY `idx_userid_create_time` (`user_id`, `create_time`) -- 創(chuàng)建時間設(shè)置為普通索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

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

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

執(zhí)行流程解析：

1. 使用聯(lián)合索引 idx_userid_create_time讀取 19980 + 20 條數(shù)據(jù)。
2. 利用索引在內(nèi)存中排序。
3. 丟棄 19880 條數(shù)據(jù)，返回剩下的 20 條。

隨著頁碼增加，需要處理的數(shù)據(jù)量會線性增長。當(dāng) offset 達(dá)到 10w 時，查詢耗時會顯著增加，達(dá)到 100w 時，甚至需要數(shù)秒。

游標(biāo)分頁（Cursor-based Pagination）

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

實(shí)現(xiàn)原理：基于有序且唯一的字段（如自增主鍵 ID），通過記錄上一頁最后一條記錄的標(biāo)識（如主鍵 ID），將WHERE條件與索引結(jié)合，跳過已查詢數(shù)據(jù)。

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

-- 后續(xù)頁（記錄上一頁查詢得到的 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的葉子節(jié)點(diǎn)，僅掃描后續(xù) 1000 條記錄，避免遍歷前 100 萬行數(shù)據(jù)。

優(yōu)勢

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

限制

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

延遲關(guān)聯(lián)（Deferred Join）

實(shí)現(xiàn)原理：通過子查詢先獲取主鍵范圍，再關(guān)聯(lián)主表獲取完整數(shù)據(jù)。減少回表次數(shù)，利用覆蓋索引優(yōu)化性能。

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;

優(yōu)勢

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

覆蓋索引優(yōu)化

實(shí)現(xiàn)原理：創(chuàng)建包含查詢字段的聯(lián)合索引，避免回表操作。例如索引設(shè)計(jì)為(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：訂單很多字段的，我想查看更多訂單細(xì)節(jié)怎么辦？

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

分區(qū)表

實(shí)現(xiàn)原理：將大表按時間或哈希值水平拆分。例如按月分區(qū)，每個分區(qū)獨(dú)立存儲，縮小掃描范圍。

-- 按月份RANGE分區(qū)
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)
);

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

預(yù)計(jì)算分頁（Precomputed Pages）

實(shí)現(xiàn)原理：通過異步任務(wù)預(yù)生成分頁數(shù)據(jù)，存儲到 Redis 或物化視圖。適合數(shù)據(jù)更新頻率低的場景。

實(shí)現(xiàn)步驟

• 定時任務(wù)生成熱點(diǎn)頁數(shù)據(jù)。
• 存儲到 Redis 有序集合。

ZADD order_pages 0 "page1_data" 1000 "page2_data"

• 查詢的時候直接獲取緩存數(shù)據(jù)

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

集成 Elasticsearch

實(shí)現(xiàn)原理：利用 ES 的search_after特性，通過游標(biāo)實(shí)現(xiàn)深度分頁。結(jié)合數(shù)據(jù)同步工具保證一致性。

實(shí)現(xiàn)流程：canal+kafka 訂閱 MySQL binlog 將數(shù)據(jù)異構(gòu)到 elasticsearch。

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

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

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

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