使用Seekmethod做分页时offsetpredicate的注意事项
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中的方向对应起来。规则:
翻页方向 | order by方向 | where中的seek predicate操作符 |
下一页 | ASC | > |
DESC | < | |
上一页 | ASC | < |
DESC | > |
如果你的结果集顺序是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>nullOR (A=nullAND B>y)。这种写法在postgresql v9.4中是支持的。如果你的数据库不支持,可以将>null写成A is not null OR (A is null AND B>y)
文章标题:使用Seekmethod做分页时offsetpredicate的注意事项
分享URL:http://scyanting.com/article/pedjcd.html