深度分頁優化思路
思考以下問題
查詢以下SQL的流程是怎么樣的呢？
為什么只查詢10條數據需要7秒？
# 查詢時間7秒
SELECT * FROM user ORDER BY age LIMIT 1000000, 10
問題分析
為什么分頁查詢隨著翻頁的深入，會變得越來越慢。
其實，問題的根本就在于:
第一數據量太大
第二數據庫處理分頁的方法太笨
你以為LIMIT 100000,10是直接跳過后10萬條？ 太天真了！
數據庫的真實操作：
第一步： 把整張表的數據全撈出來（全表掃描），按年齡排好序（文件排序）。
第二步： 吭哧吭哧數到第100010條，再給你返回最后10條。
相當于：讓你從新華字典第1頁開始翻，翻到第1000頁才找到字，誰能不炸？
最坑爹環節：回表查數據
如果用了普通索引（比如按年齡建的索引）：
- 先查索引：按年齡找到對應的主鍵ID（快速）
- 再回表：用ID去主鍵索引里撈完整數據（慢！）
10萬次回表 = 10萬次IO操作，不卡你卡誰？
再說另一個常見的情況——排序。
大多數時候，分頁查詢都會帶有排序，比如按時間、按ID排序。
數據庫不僅要查數據，還得根據你的排序要求重新排一次，特別是在數據量大的時候，排序的開銷就變得非常大。
所以，翻越幾百頁的時候，你的查詢可能就開始慢得像蝸牛。
單表場景 limit 深度分頁 的優化方法
核心思路： 繞過全表掃描，直接定位到目標數據！
方案一：子查詢優化
mysql> explain SELECT *-> FROM user-> WHERE id >= -> (-> SELECT id-> FROM user-> ORDER BY age-> LIMIT 1000000, 1-> )-> limit 10;
+----+-------------+--------------------+------------+-------+---------------+--------------+---------+-------+---------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------------+------------+-------+---------------+--------------+---------+-------+---------+----------+--------------------------+
| 1 | PRIMARY | user | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 10 | 100.00 | Using where |
| 2 | SUBQUERY | user | NULL | ref | idx_age | idx_age | 2 | const | 432791 | 100.00 | Using index |
+----+-------------+--------------------+------------+-------+---------------+--------------+---------+-------+---------+----------+--------------------------+
2 rows in set, 1 warning (0.15 sec)
原理： 用覆蓋索引快速找到第100000條的ID，直接從這個ID開始拿數據，跳過前面10萬次回表。
缺點是，不適用于結果集不以ID連續自增的分頁場景。
在復雜分頁場景，往往需要通過過濾條件，篩選到符合條件的ID，此時的ID是離散且不連續的。如果使用上述的方式，并不能篩選出目標數據
方案二：延時關聯
SELECT * FROM user t1
JOIN (SELECT id FROM user ORDER BY age LIMIT 1000000,10) t2
ON t1.id = t2.id;
mysql> explain SELECT * -> FROM-> user t1-> JOIN (-> SELECT-> id-> FROM-> user-> ORDER BY-> age-> LIMIT 1000000,10-> ) AS t2 -> ON t1.id = t2.id-> LIMIT 10;
+----+-------------+--------------------+------------+--------+---------------+--------------+---------+-------+---------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------------+------------+--------+---------------+--------------+---------+-------+---------+----------+--------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 432791 | 100.00 | NULL |
| 1 | PRIMARY | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 8 | t2.id | 1 | 100.00 | NULL |
| 2 | DERIVED | user | NULL | ref | idx_age | idx_age | 9 | const | 432791 | 100.00 | Using index |
+----+-------------+--------------------+------------+--------+---------------+--------------+---------+-------+---------+----------+--------------------------+
3 rows in set, 1 warning (0.12 sec)
原理： 先用索引快速拿到10個目標ID，再一次性聯表查完整數據，減少回表次數。
方案三：索引覆蓋
索引覆蓋（Index Covering）是指一個查詢可以完全通過索引來執行，而無需通過回表來查詢其他字段數據。
例如：
ALTER TABLE user ADD INDEX idx_age_name(age, name); -- 查詢+排序全走索引
SELECT age, name FROM user ORDER BY age LIMIT 1000000,10; -- 0.08秒！
精髓： 索引里直接存了所有要查的字段，不用回表，直接起飛！
缺點： 如果每個查詢都建對應的索引的話，會浪費更多的空間存儲索引，也會影響插入時的速度。
方案四：書簽記錄
從原理上說，屬于是一種滾動查詢。也就是說我們必須從第一頁開始查詢，然后獲取本頁最大的記錄 ID，然后再根據大于最大記錄 ID 的數據向后持續滾動。也就是說，我們如果想查詢大于 1000000 后記錄的 10 條，那我們就得知道 1000000 條的 ID。因為 ID 是遞增的，所以直接查詢即可。
SELECT * FROM user WHERE id > 1000000 LIMIT 10; -- 500微秒！
精髓： 每次查詢都用上次查詢結果做書簽，直接走主鍵索引
缺點： 不支持條頁查詢，主鍵必須是自增的。
分庫分表后，翻頁為什么更慢了？
分庫分表的翻頁邏輯
假設訂單表分了3個庫，每個庫分了2張表（共6張表），按用戶ID分片。
當你執行：
SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 10;
你以為數據庫的操作：
智能跳過100萬條，從6張表各拿10條，合并完事？
實際上的操作：
- 每張表都老老實實查100萬+10條數據（共600萬+60條）。
- 把所有數據匯總到內存，重新排序（600萬條數據排序，內存直接炸穿）。
- 最后忍痛扔掉前650萬條，給你10條結果。
結果： 查一次耗時10秒+，數據庫CPU 100%！
分庫分表翻頁的存在的3個問題
- 數據分散，全局排序難
各分片數據獨立排序，合并后可能亂序，必須全量撈數據重排。 - 深分頁=分片全量掃描
每張表都要查offset + limit條數據，性能隨分片數量指數級下降。 - 內存歸并壓力大
100萬條數據 × 6個分片 = 600萬條數據在內存排序，分分鐘OOM！
一句話總結： 分庫分表后，翻頁越深，死得越慘！
3種解決分庫分表深度翻頁方案
方案1：禁止跳頁（青銅方案）
核心思想： 別讓用戶隨便跳頁，只能一頁一頁翻！其實就是上面的書簽記錄
實現方法：
1.第一頁查詢：
-- 按時間倒序，拿前10條
SELECT * FROM orders
WHERE user_id = 123
ORDER BY create_time DESC
LIMIT 10;
2.翻下一頁：
-- 記住上一頁最后一條的時間
SELECT * FROM orders
WHERE user_id = 123
AND create_time < '2023-10-01 12:00:00' -- 上一頁最后一條的時間
ORDER BY create_time DESC
LIMIT 10;
優點：
- 性能：每頁查詢只掃索引的10條，0回表。
- 內存：無需全量排序。
缺點：
- 用戶不能跳頁（比如從第1頁直接跳到第100頁）。
- 適合Feed流場景（如朋友圈、抖音），不適合后臺管理系統。
方案2：二次查詢法（黃金方案）
核心思想： 把分庫分表的“大海撈針”，變成“精準狙擊”！
實現步驟：
1. 第一輪查詢：每張分片查縮小范圍的數據
-- 每張分片查 (offset / 分片數量) + limit 條
SELECT create_time FROM orders
ORDER BY create_time DESC
LIMIT 166666, 10; -- 假設總offset=100萬，分6個分片：100萬/6 ≈ 166666
1.確定全局最小時間戳：
從所有分片結果中，找到最小的 create_time（比如 2023-09-20 08:00:00）。
2.第二輪查詢：根據最小時間戳查全量數據
SELECT * FROM orders
WHERE create_time >= '2023-09-20 08:00:00'
ORDER BY create_time DESC
LIMIT 10;
優點：
- 避免全量數據排序，性能提升6倍。
- 支持跳頁查詢（如直接從100萬頁開始查）。
缺點：
- 需要兩次查詢，邏輯復雜。
- 極端情況下可能有誤差（需業務容忍）。
方案3：ES+HBase核彈方案（王者方案）
核心思想： 讓專業的人干專業的事！
- ES：負責海量數據搜索+分頁（倒排索引碾壓數據庫）。
- HBase：負責存儲原始數據（高并發讀取無壓力）。
架構圖：
實現步驟：
- **寫入時：**訂單數據同時寫MySQL（分庫分表）、ES、HBase。
- 查詢時：
GET /orders/_search
{ "query": { "match_all": {} }, "sort": [{"create_time": "desc"}], "from": 1000000, "size": 10
}
List<Order> orders = es.search(...); // 從ES拿到10個ID
List<Order> details = hbase.batchGet(orders); // 從HBase拿詳情
- Step2：用ES返回的ID，去HBase批量查數據。
- Step1：用ES查分頁（只查ID和排序字段）。
優點： - 分頁性能碾壓數據庫，百萬級數據毫秒響應。
- 支持復雜搜索條件（ES的強項）。
缺點： - 架構復雜度高，成本飆升（ES集群要錢，HBase要運維）。
- 數據一致性難保證（延遲可能秒級）。
面試怎么答？
1. 面試官要什么？
- 原理理解： 知道分庫分表后翻頁的痛點（數據分散、歸并排序）。
- 方案靈活： 根據場景選方案（禁止跳頁、二次查詢、ES+HBase）。
- 實戰經驗： 遇到過真實問題，用過二次查詢或ES。
2. 標準答案模板
分庫分表后深度分頁的難點在于全局排序和內存壓力。
我們有三種方案：
- 禁止跳頁： 適合C端Feed流，用連續查詢代替跳頁。
- 二次查詢法： 通過兩次查詢縮小范圍，適合管理后臺。
- ES+HBase： 扛住億級數據分頁，適合高并發大廠場景。
在實際的場景中，訂單查詢需要支持搜索條件，我們最終用ES+HBase，性能從10秒降到50毫秒。”
加分的騷操作：
- 畫架構圖（分庫分表+ES+HBase數據流向）。
- 給性能對比數據（ES分頁 vs 數據庫分頁）。
- 提一致性解決方案（監聽MySQL Binlog同步到ES）。
總結
分庫分表后的深度分頁，本質是 “分布式數據排序” 的難題。
- 百萬以內數據： 二次查詢法性價比最高。
- 高并發大廠場景： ES+HBase是唯一選擇。
- 千萬別硬剛：
LIMIT 1000000,10就是自殺式操作！
最后一句忠告：
面試被問分頁，先拍桌子喊出“禁止跳頁”，再掏出ES，面試官絕對眼前一亮！
本文改編自
- 京東二面：分庫分表后翻頁100萬條，怎么設計？答對這題直接給P7！
- 《牛券oneCoupon優惠券系統設計》第21小節：優惠券分發失敗記錄深分頁優化
如侵權，請聯系刪除
)
