前言

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

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

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

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

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

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

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

深度分頁(yè)

假設(shè)電商平臺(tái)的訂單表存儲(chǔ)了 2000 萬(wàn)條記錄，表結(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)建時(shí)間默認(rèn)為當(dāng)前時(shí)間
  PRIMARY KEY (`id`),
  KEY `idx_userid_create_time` (`user_id`, `create_time`) -- 創(chuàng)建時(shí)間設(shè)置為普通索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

我們的分頁(yè)語(yǔ)句一般這么寫(xiě)。

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

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

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 條。

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

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

適用場(chǎng)景：支持連續(xù)分頁(yè)（如無(wú)限滾動(dòng)）。

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

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

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

索引樹(shù)直接定位到order_id=1000的葉子節(jié)點(diǎn)，僅掃描后續(xù) 1000 條記錄，避免遍歷前 100 萬(wàn)行數(shù)據(jù)。

優(yōu)勢(shì)

• 完全避免 OFFSET掃描，時(shí)間復(fù)雜度從 O(N)降為 O(1)
• 天然支持順序分頁(yè)場(chǎng)景（如無(wú)限滾動(dòng)加載）

限制

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

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

實(shí)現(xiàn)原理：通過(guò)子查詢先獲取主鍵范圍，再關(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)勢(shì)

• 子查詢僅掃描索引樹(shù)，避免回表開(kāi)銷(xiāo)。
• 主查詢通過(guò)主鍵精確匹配，效率極高。
• 性能提升可達(dá) 10 倍以上（實(shí)測(cè)從 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é)怎么辦？

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

分區(qū)表

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

-- 按月份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ì)算分頁(yè)（Precomputed Pages）

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

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

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

ZADD order_pages 0 "page1_data" 1000 "page2_data"

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

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

集成 Elasticsearch

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

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

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

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

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

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