使用Seek method做分页时offset predicate的注意事项

Seek method pagination是最近流行的分页概念。其核心思想是：不再依赖index作为偏移量，而是使用条件表达式作为分页的依据。具体原理我在这里就不再废话了，感兴趣的朋友可以去搜一搜。

由于“条件表达式”成为了分页依据，那么如何准备分页条件表达式（seek predicate）就成为了关键。

首先，使用seek method pagination的前提条件是：必须是有序结果集。对应于SQL script即需要有order by子句。

下面就是组织seek predicate时需要注意的问题了：

1、order by的列（或多个列）中必须有一个包含全表唯一值的列（就是类似主键那样的列，列中的值必须not null而且unique）。主键列就符合这一特征。

2、order by中出现的列，也必须出现在where子句中，而且排列顺序不能错。比如，order by A asc, B asc, C asc，那么where ... and (A, B, C) > (x, y, z)。where中可以出现其他与seek predicate无关的其他predicate，但要保证order by中出现的column都要出现在where子句中；

3、order by的列必须是同向的，不可以order by A asc, B desc, C asc；

4、(A, B, C, ...) > (x, y, z, ...) 的逻辑运算符必须与order by中的方向对应起来。规则：

如果你的结果集顺序是ASC，那么，如果你想翻到下一页，你的seek predicate应该用>作为逻辑操作符；如果你想翻到上一页，seek predicate的操作符应该是<。

如果你的结果集顺序是DESC，那么，如果你想翻到下一页，seek predicate的操作符应该是<，反之，用>号。

Note1:

这里需要注意一件事情，就是null值问题。之前说过，order by中至少要有一列为not null unique，而order by中的其他列则没有这个要求，列值可以为null，可以出现重复值。如果翻页时，游标正好指在当前页的最后一行，且这一行中的order by的某个列包含null值，那么就不能用>或<号来拼where条件子句了，要用is null 和 is not null.

现在where中的A、B、C、...都有了，那么每次翻页时，如何找到对应的x,y,z呢？

首先，第一屏。第一屏需要一个batch size（假设为30），也就是每屏加载多少数据。由于第一屏你不知道具体的x,y,z值，所以直接从“头”读取30条记录。获得这30条记录后，紧接着，把第30条记录的A、B、C的值分别记下来，作为x, y, z(假设第一批30条记录的最后一条记录的A='Yan' B='Male' C=1200。下一批30条数据的where子句中的seek predicate就应该为：

select ...
from ...
where ... and (A, B, C) > ('Yan', 'Male', 1200)
order by A, B, C
limit 30;

执行上面的SQL会得到下面一页30条记录（如果有30条的话），然后重复刚才采集条件值的操作，例如，第二屏30条记录的最后一条记录的A='Ye' B='Male' C=335。将获得的新的x、y、z值重新拼成第三屏的SQL：

select ...
from ...
where ... and (A, B, C) > ('Ye', 'Male', 335)
order by A, B, C
limit 30;

执行上面的SQL会得到下面一页30条记录（如果有30条的话）。如此往复，一直到数据全部加载完毕。

(A, B, C) > ('Ye', 'Male', 335)

这个东西是seek predicate，可以理解为condition offset。传统分页方式用的是index offset。传统分页方式下，你需要知道一共有多少条记录，每屏显示多少条（batch size），然后查询出整个结果集（DBMS的缓存里）， 然后使用index offset偏移量skim offset行记录后，开始读取batch条记录。全集查询和skim的过程是很要命的。随着你翻页越往后，比如1000页以后，skim速度就会越慢，内存开销也会越大，数据库负担越重。

但是seek method分页则不同，数据库并不需要查询出所有符合条件的数据，他只需要按照你的condition定位到符合condition的第一条记录，然后往后读取batch size条记录，就完成了那个页面的读取。

目前很多数据库都支持seek predicate的这种(A, B, C) op (x, y, z)写法，而JPA还不支持。但是，我们可以根据所学的数学知识，将不等式变换一下方式，换成等效公式。

• (A, B) > (x, y) = A>x OR (A=x AND B>y)

• (A, B, C) > (x, y, z) = A>x OR (A=x AND (B>y OR (B=y AND C>z)))

以此类推，你可以写个递归来自动分解拼装(A, B, C, ...) > (x, y, z, ...)

Tips:

如果遇到order by的列值存在null值，那么，A>null OR (A=null AND B>y)。这种写法在postgresql v9.4中是支持的。如果你的数据库不支持，可以将>null写成A is not null OR (A is null AND B>y)