那晚,电商平台的运维大群突然炸开了锅。
监控系统发出刺耳的警报:订单查询接口响应时间从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,
`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'
ORDERBY create_time DESC
LIMIT0, 20;
当用户查询第 1000 页的订单(每页 20 条),常见的分页写法如下。
SELECT * FROM orders
WhERE user_id = 'Chaya'
ORDERBY create_time DESC
LIMIT19980, 20;
执行流程解析:
使用联合索引 idx_userid_create_time读取 19980 + 20 条数据。
利用索引在内存中排序。
丢弃 19880 条数据,返回剩下的 20 条。
随着页码增加,需要处理的数据量会线性增长。当 offset 达到 10w 时,查询耗时会显著增加,达到 100w 时,甚至需要数秒。
三、游标分页(Cursor-based Pagination)
适用场景:支持连续分页(如无限滚动)。
实现原理:基于有序且唯一的字段(如自增主键 ID),通过记录上一页最后一条记录的标识(如主键 ID),将WHERE条件与索引结合,跳过已查询数据。
SELECT *
FROM orders
WhERE user_id = 'Chaya'
ORDERBY create_time DESC
LIMIT20;
SELECTid, user_id, amount
FROM orders
WHEREid > 1000AND user_id = 'Chaya'
ORDERBY create_time DESC
LIMIT20;
索引树直接定位到order_id=1000的叶子节点,仅扫描后续 1000 条记录,避免遍历前 100 万行数据。
优势
限制
不支持随机跳页(如直接跳转到第 1000 页)
需保证排序字段唯一且有序
实现原理:通过子查询先获取主键范围,再关联主表获取完整数据。减少回表次数,利用覆盖索引优化性能。
SELECT t1.*
FROM orders t1
INNERJOIN (
SELECTid
FROM orders
WhERE user_id = 'Chaya'
ORDERBY create_time DESC
LIMIT1000000, 20
) t2 ON t1.id = t2.id;
优势
实现原理:创建包含查询字段的联合索引,避免回表操作。例如索引设计为(user_id, id, create_time, amount)。
ALTERTABLE orders ADDINDEX idx_cover (user_id, id, create_time,amount);
SELECT id, user_id, amount, create_time
FROM orders USEINDEX (idx_cover)
WhERE user_id = 'Chaya'
ORDERBY create_time DESC
LIMIT1000000, 20;
这个问题问得好,我们可以设计订单列表和详情页,通过上述方案做订单列表的分页查询;点击详情页的时候,在使用订单 id 查询订单。
实现原理:将大表按时间或哈希值水平拆分。例如按月分区,每个分区独立存储,缩小扫描范围。
ALTERTABLE orders PARTITIONBYRANGE (YEAR(create_time)*100 + MONTH(create_time)) (
PARTITION p202501 VALUESLESSTHAN (202502),
PARTITION p202502 VALUESLESSTHAN (202503)
);
SELECT * FROM orders PARTITION (p202501)
WHERE user_id = 'chaya'
ORDERBY create_time DESC
LIMIT20;
七、预计算分页(Precomputed Pages)
实现原理:
通过异步任务预生成分页数据,存储到 Redis 或物化视图。适合数据更新频率低的场景。
实现步骤
定时任务生成热点页数据。
存储到 Redis 有序集合。
ZADD order_pages 0"page1_data"1000"page2_data"
-- 伪代码:获取第N页缓存
ZRANGEBYSCORE order_pages (N-1)*1000 N*1000
实现原理:利用 ES 的search_after特性,通过游标实现深度分页。结合数据同步工具保证一致性。
实现流程:canal+kafka 订阅 MySQL binlog 将数据异构到 elasticsearch。
elasticsearch 保存的数据主要就是我们的查询条件和订单 id。
订单表 → Binlog → Canal → Kafka → Elasticsearch、Hbase
在查询的时候,通过 Elasticsearch 查询得到订单 ID,最后在根据订单 ID 去 MySQL 查询。
或者我们可把数据全量同步到 Hbase 中查询,在 Hbase 中查询完整的数据。
dbaplus社群欢迎广大技术人员投稿,投稿邮箱:editor@dbaplus.cn