文章

为什么要用游标分页?如何更好使用游标分页

为什么要用游标分页?如何更好使用游标分页

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-丢弃子查询中查出来的前50000id
#       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_timeid分别为,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_timeid分别为,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 进行授权

热门标签