数据分页问题

原文地址：We need tool support for keyset pagination

 

1、offset的定义

…the rows are first sorted according to the <order by clause> and then limited by dropping the number of rows specified in the <result offset clause> from the beginning…

SQL:2016, Part 2, §4.15.3 Derived tables

可以简单理解为：查询数据排序完后，使用游标读取数据，offset之前的数据全部“丢弃”掉，取出limit条数据即可。

 

2、offset分页的问题

2.1 性能影响（深度分页）

例如：每页100条数据，要获取第101页的数据，等价于select xxx from table offset 10000 limit 100。实际上数据库还是需要取出10100条数据，只不过是“丢弃”了前10000条，然后获取后面100条。

2.2 业务功能上的困扰

下图可以清晰的反映这个问题，如图：

由于第二步新纪录的插入，导致同一条数据出现在第一页（最后一条数据）和第二页（第一条数据）。

 

3、抛弃offset（Life Without Offset）

如何不使用offset进行分页？ 我们可以通过查询条件告诉数据库“上次看到哪里了”，然后接着看。

例如：

SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLY

这里同样支持“多个字段排序的情况”。

注意：这种方案的限制是——不能跳到指定的“页”，只能一页一页的翻。

参考：Pagination done the Right Way