为什么要用游标分页?如何更好使用游标分页
为什么要用游标分页?如何更好使用游标分页
1.传统分页的困境
1.1.limit分页
谈到mysql的分页,我们很容易想到limit语法:
1
2
# 跳过前5条,取后10条
SELECT * FROM table_name LIMIT 10 OFFSET 5;
或者简写形式:
1
SELECT * FROM table_name LIMIT 5,10;
然而,当offset特别大时,使用limit分页,需要扫描前offset条数据并丢弃,因此,limit分页会随着数据量增加达到性能瓶颈
那怎么做,才能推迟这种性能瓶颈的到来呢?
1.2.limit分页的优化策略
- WHERE字段加索引&&减少回表
- 由于B+树叶子结点双向链表的存在,加索引可以加快扫描速度
- 减少回表即减少扫描时间
- 覆盖索引+子查询
- 举个🌰:
1
2
3
4
5
6
7
8
9
10
11
12
CREATE INDEX idx_updateTime ON records(updateTime, id);
# 直接limit分页
# 流程:1-从idx_updateTime索引中查出前50005条数据id
# 2-利用查出来的ids回表50005条数据
# 3-丢弃前50000条数据
SELECT * FROM table_name ORDER BY `update_time` limit 50000,5;
# 子查询
# 流程:1-子查询,从idx_updateTime索引中查出前50005条数据id
# 2-丢弃子查询中查出来的前50000条id
# 3-用剩下5条数据ids回表查询
SELECT * FROM table_name WHERE id IN ( SELECT id FROM table_name ORDER BY `update_time` LIMIT 50000,5);
可以看出,第二种查询方式比第一种查询少了50000条数据的回表,只有外层回表了5条数据
- 提前计算偏移量
- 原理同上,本质上都是为了减少回表次数与回表数据量
- 缓存数据
- 将大表按时间分区,查询时只扫描某分区
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 分区
CREATE TABLE records (
id BIGINT PRIMARY KEY,
name VARCHAR(50),
createTime DATETIME,
updateTime DATETIME
) PARTITION BY RANGE (YEAR(createTime)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
# 查询
SELECT * FROM records PARTITION (p2023)
ORDER BY updateTime ASC
LIMIT 1000, 10;
通过上述方法,可以减少扫描的数据量或者减少回表数量,但还是会存在搜索前N条并丢弃的情况
2.游标分页(cursor分页)
为了解决查询前N条并丢弃这个问题,我们可以使用游标分页(cursor分页)
2.1.游标分页是什么
每次查询分页时,利用上一次分页后得出的新cursor,使用id>上一次cursor来提到offset
举个🌰:
1
2
3
4
5
6
7
8
9
10
# 第一次查询
SELECT * FROM table_name LIMIT 50;
# 从第一次查询出的结果集中,取出最大的id,假设这个id=prev_max_id
prev_max_id
# 第二次查询
SELECT * FROM table_name WHERE id > prev_max_id LIMIT 50;
# 将prev_max_id更新为最新结果集中的最大id,继续后续查询
2.2.游标分页的优缺点
- 相较于传统offset分页,cursor分页有什么优点?
- 直接使用主键索引的range查询,速度快
- 不需要查询并丢弃无用的数据
- cursor有什么缺点?
- 不能单纯使用id来计算cursor
- 场景:
1
2
3
4
5
6
7
8
9
10
11
# 某张表有id,name,update_time字段,现在要求分别根据update_time升序、降序,并且分页50条查询
# 问题:排序字段update_time与主键id没有直接关系,update_time可能会随着数据更新变化,不能直接用id生成cursor
# 如何解决:1-使用排序字段update_time+id,保证取到的id为最新时间内的id极限值(左极限or右极限)
# 2-索引字段(update_time+id),加快查询速度
ALTER TABLE table_name ADD INDEX idx_update_time(update_time,id);
# 假设上次查询出来的最新时间和id极限值(升序为最大时间+最大id,降序为最小时间+最小id)分别为prev_time,prev_id
# 升序
SELECT * FROM table_name WHERE update_time > prev_time OR (update_time = prev_time AND id > prev_id) ORDER BY update_time ASC,id ASC;
# 降序
SELECT * FROM table_name WHERE update_time < prev_time OR (update_time = prev_time AND id < prev_id) ORDER BY update_time DESC, id DESC;
- 向前分页不友好
- 向前分页时,需要反转向后分页的逻辑
1
2
3
4
5
6
7
8
9
# 升序,向后翻页
# 假设这一页最后一条数据的update_time和id分别为,last_time,last_id
SELECT * FROM table_name WHERE update_time > prev_time OR (update_time = prev_time AND id > prev_id) ORDER BY update_time ASC,id ASC;
# 升序,向前翻页
# 假设这一页第一条数据的update_time和id分别为,first_time,first_id
SELECT * FROM table_name WHERE update_time < first_time OR (update_time = first_time AND id < first_id) ORDER BY update_time ASC,id ASC;
# 降序同理
- 不支持页面跳转(跳转到100页)
- 对于页面跳转,游标分页支持难度很大,建议直接使用limit+子查询的方式分页
- 如果实在要使用cursor分页,可以异步将每一页的游标刷到缓存里(每次数据更新时异步刷缓存)
3.拓展:分页中的动态边界更新
3.1.什么是动态边界更新
- 动态更新
- 表中数据不断插入或更新
- 边界情况
- 分页过程中,数据变化发生在当前页的起点或终点附近,导致结果可能与预期不符
- 核心问题
- 游标分页依赖上一页的最后一个值,但数据变化可能使游标失效或偏移
- limit分页依赖每页的条数,如果上一页的数据插入,可能导致查询到重复数据
- 基于各种边界特殊情况,可能导致重复、遗漏、偏移等情况
3.2.如何处理动态更新边界情况
- 默认行为:接受动态性(更建议)
- 分页应当反映数据的最新状态,不追求保证”历史一致性”
- 实时性强,适合动态系统
- 但是可能有重复或遗漏记录
- 使用快照时间
- 新增一个快照字段,记录查询时的快照,snapshot_time
- 每次查询时,将snapshot_time作为条件之一,固定时间点查询过的数据不再查询
1
2
3
4
5
6
7
ALTER TABLE table_name ADD COLUMN snapshot_time DATETIME DEFAULT CURRENT_TIMESTAMP;
SELECT id, name, updateTime
FROM records
WHERE snapshot_time <= '上一个查询时间'
ORDER BY updateTime ASC, id ASC
LIMIT 2;
- 这种方式其实不建议,既需要额外维护一个字段,数据也会失去实时性
4.总结
对于数据量不大的场景来说,使用limit分页足够,limit分页简单,便于维护,也能支持动态跳转页面
对于数据量很大场景,为了解决深度分页的瓶颈问题,需要选择cursor分页方式,cursor分页避免了无用行的扫描,提高了每次查询的效率。 但是cursor分页对于向前跳转页面不友好,也不支持页面跳转,对于这些特殊情况需要有特殊处理
当碰到动态边界更新情况是,建议接受动态性,这样最符合动态系统状态
本文由作者按照 CC BY 4.0 进行授权