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

1.传统分页的困境

1.1.limit分页

谈到mysql的分页，我们很容易想到limit语法:

# 跳过前5条，取后10条
SELECT * FROM table_name LIMIT 10 OFFSET 5;

或者简写形式:

SELECT * FROM table_name LIMIT 5,10;

然而，当offset特别大时，使用limit分页，需要扫描前offset条数据并丢弃，因此，limit分页会随着数据量增加达到性能瓶颈

那怎么做，才能推迟这种性能瓶颈的到来呢？

1.2.limit分页的优化策略

• WHERE字段加索引&&减少回表
  • 由于B+树叶子结点双向链表的存在，加索引可以加快扫描速度
  • 减少回表即减少扫描时间
• 覆盖索引+子查询
  • 举个🌰：

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条数据

• 提前计算偏移量
  • 原理同上，本质上都是为了减少回表次数与回表数据量
• 缓存数据
• 将大表按时间分区，查询时只扫描某分区

# 分区
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

举个🌰：

# 第一次查询
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
  • 场景：

# 某张表有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;

• 向前分页不友好
  • 向前分页时，需要反转向后分页的逻辑

# 升序，向后翻页
# 假设这一页最后一条数据的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作为条件之一，固定时间点查询过的数据不再查询

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分页对于向前跳转页面不友好，也不支持页面跳转，对于这些特殊情况需要有特殊处理

当碰到动态边界更新情况是，建议接受动态性，这样最符合动态系统状态