分享自研实现的多数据源(支持同DB不同表、跨DB表、内存数据、外部系统数据等)分页查询工具类实现原理及使用
思考:
提起分页查询,想必任何一个开发人员(不论是新手还是老手)都能快速编码实现,实现原理再简单不过,无非就是写一条SELECT查询的SQL语句,ORDER BY分页排序的字段, 再结合limit (页码-1),每页记录数,这样即可返回指定页码的分页记录,类似SQL如下所示:
1 | select * from table where 查询条件 order by id limit 100,100; -- 这里假设是第2页(limit 第1个值从0开始),每页100条 |
那如果是想将多张表的记录合并一起进行分页查询,我们又该如何实现呢?我估计稍微有点经验的开发人员可能会立马举一反三,想到了通过UNION 多张表的方式来实现分页查询,类似SQL如下所示:
1 | select * from |
这样实现有没有问题呢?我觉得如果是UNION的多张小表(即:数据量相对较小)那么这样实现成本最低最有效果,肯定是OK的,但如果UNION的多张表是一些大表(即:数据量相对较大,如:100W+)且有些表的查询条件中的查询字段不一定是有索引的,那么就会存在严重的查询性能问题,另外如果UNION的表过多,即使不都是大表也仍然存在查询性能问题,而且查询性能随着UNION的表的数量增加查询性能而降低,导致无法扩展。
这里有人可能会说,分页查询一般都是单表或JOIN多表的结果集,即使UNION多张表也不会太多,为何要考虑扩展?我只能说,一切皆有可能,谁也没有规定分页查询只能单表或限定在几张表内,如果产品经理提出需要将多个功能模块(对于开发人员来说:可能是多张表)的数据合并分页查询展示,那我们也必需实现,断然不能因为“实现不了 或 实现有难度 或 存在性能问题”就拒绝此类需求,因为产品经理提出的需求肯定有他的背景及业务价值,作为开发人员,且想做为一个优秀的开发人员,那么“有求必应”是必备的工作态度,豪不夸张的张,没有实现不了的产品需求,就看实现的成本(包含时间成本、人力成本、物质成本等)是否与产品需求的价值相匹配,如果成本与价值基本相符(或说投入与产出后的效果),那么即使再难实现也必定是可以实现的。扯得有点远了,还是回到上面所描述的关于多张表分页查询的问题,UNION多张表确实可以解决一些相对简单的多表分页的问题,但如果多张表的数据字段结构、记录数不相同(即:字段名不同、一对多、单行水平字段、垂直多行字段),甚至不仅仅是多张表,有可能是跨系统、跨DB的多张表或是动态计算的结果,这些情况下,UNION SQL的方式肯定是满足不了了,必需要有其它的解决办法,我认为最好的实现方式有两种:一种是想办法将多查询来源(因为不仅限于表)的记录全部汇总到一张总表上,然后使用传统的单表分页查询SQL即可(正如一开始所举例的SQL),另一种就是本文重点介绍的,支持多数据源分页查询工具类(MultiSourcePageQueryBuilder)
多数据源分页查询工具类(MultiSourcePageQueryBuilder)介绍
多数据源分页查询工具类(MultiSourcePageQueryBuilder)的使用前提条件是:多个查询来源(不仅限于表)必需是有顺序的,即:先查第1个来源,查完后再查下一个来源,依此类推,直至查完所有来源,分页结束,如:表1,表2,表3,先分页查表1,查完后再查表2,查完后最后查表3。
多数据源分页查询工具类(MultiSourcePageQueryBuilder)的使用效果:多个查询来源(不仅限于表)能够正常记录总页数,总记录数,能够支持正常连续分页,跳转分页,且只要不是最后1页,则每页的记录数均为设定的页大小(即:pageSize,满页),若上一个查询来源的记录数不足页大小则会自动切换下一个查询来源来补足1页大小的记录,否则最后1页才有可能剩余记录不足1页大小的情况(即:与传统单表分页查询效果一致),整体对前端,对用户无差异感知。
多数据源分页查询工具类(MultiSourcePageQueryBuilder)的实现原理与机制:
先通过汇总计算每个查询来源的总记录数,然后根据每个查询来源的总记录数精确计算出分页区间占比情况(即:pageRange),分页区间的关键信息有:开始区间页码、结束区间页码、所属查询来源、开始页实际记录数、结束页记录数(注意:结束页记录数是累加的,目的是便于计算下一个查询来源的分页区间),最后得出真实的总页数、总记录数;(对应代码的方法:getTotalCountResult),下面通过一个表格来展示分页区间的计算情况:
假设:pageSize:每页2条
如下每一单元格代表一行记录数,单元格中的数字表示分页数字,不同颜色区分不同的查询来源
分页查询时,根据前端用户选择的查询页码、查询来源(这个首次不传则为默认0,后面若跨查询来源则会由后端返回给前端,前端保存)、分页大小、分页区间(这个由后端计算后返回给前端保存)等入参信息(MultiSourcePagination),先由页码获得分页区间对象列表(不足1页跨多查询来源时会有多个查询来源,否则一般都只会命中一个分页区间),选择第1个分页区间对象,若这个分页区间的查询来源与当前请求的查询来源相同说明是正常的分页,则执行正常分页逻辑;若不相同时说明存在跳页情况,则再判断当前查询的页码是否为这个分页区间对应的的开始页码,若是说明无需分隔点,则仅需切换查询来源及设定查询来源的分页超始页码后执行正常分页逻辑,否则说明跳页且当前查询的页码在这个查询来源的第2页及后面的分页区间内(含最末页)或分页区间开始页存在跨多个查询来源(即:多个查询来源补足1页记录,如:表1占10条,表2占10条,页大小为20条),此时就需要先根据分页区间的开始页记录数及查询条件查出对应的补页记录信息,然后获取结果的最后一条记录作为这个查询来源的分页过滤条件(注意:若查询补页记录后的数据源与当前原请求的分页区间的数据源不相同时,则说明数据有变化(数据条数变少或没有,导致切换下一个查询来源),此时应重新汇总计算分页信息,以便再翻页时能准确查询到数据),最后执行正常分页逻辑(对应代码的方法:getPageQueryResult)
正常分页逻辑(对应代码的方法:doPageQueryFromMultiSource):根据请求的查询来源索引从已设置的多数据源分页查询回调方法列表中选择对应的分页查询回调方法引用,执行分页查询回调方法获得分页的结果,若结果记录满足页大小(即:实际记录数=页大小pageSize)则正常返回即可,否则需判断是否为最后一个查询来源,若是则说明已到最大页码,直接返回当前剩余记录即可,无需补充分页记录的情况,除外则说明查询的结果为空或记录数不满1页大小,需要跨查询来源进行补页查询(即:缺少几条记录就查多少记录补全1页大小,如:页大小20,表1查询出8条,不足1页还差12条,则切换查表2查询12条补全1页),注意可能存在跨多个查询来源才补全1页大小的情况,最后在返回分页结果时,需将补页记录的最后一条记录设置为查询来源的分页过滤条件(querySourceFilterStart)、当前请求页码设置为这个查询来源的分页起始页码(即:已占用的页码,querySourcePageStart)一并返回给前端即可。后续翻页时前端除了更改页码外还需将上述分页区间信息、分页过滤条件、分页起始页码等回传给后端,以避免每次都要后端重新计算 影响查询性能或因分页入参信息不全不准导致分页结果不正确的情况;
下面通过表格图来展示几种情况下的多数据源的分页情况
其中:pageLimitStart=(this.page【请求的页码】 - this.querySourcePageStart【起始页码】 - 1) * this.pageSize【页大小】;
第一种情况:无论是正常分页(即:连续分页)或是跳页分页(即:随机页码翻页)均不存在补页情况(即:同1页中包含多个查询来源的数据),最为简单,每个查询来源均正常分页查询即可(limit pageLimitStart,pageSize),跳页时仅需确认查询来源、分页起始页码即可;
第二种情况:无论是正常分页(即:连续分页)或是跳页分页(即:随机页码翻页)均需要补页情况,由于涉及补页的情况,故跳页时也分两种情况,如果在已执行过的查询来源的分页区间中进行跳页(情形1),那么仅需确定查询来源、分页起始页码即可,而如果从一个已执行过的查询来源跳到未执行过的查询来源(情形2),那么此时因为存在补页故必需先查询这个查询来源的分页区间起始页补页记录信息从而确定分隔过滤条件及分页起始页码;
第三种情况:与上面第二种情况一下,无论是正常分页(即:连续分页)或是跳页分页(即:随机页码翻页)均需要补页情况,但补页涉及多个查询来源;
总之:不论哪种情况,如果某个查询来源不足1页大小时,必需由另一个或多个查询来源的记录补全1页,一旦存在补页,那么补页的最后查询来源后面的页码记录均需要排除掉补页的记录(这也就是为什么跳页时,需要先查分页区间的起始页的补页记录并确认分隔点过滤条件的目的),即:需确认分隔过滤条件;
多数据源分页查询工具类(MultiSourcePageQueryBuilder)代码快速上手示例指南:
示例1:(这里采用的是纯内存模拟数据,其实也说明了支持不同类型的查询来源,不论是DB的表或内存中的集合对象 、甚至是调外部系统的接口,只要能符合分页的出入参字段即可,混合也是可以的)
1 |
|
示例2:(mybatis spring boot)
相关mapper xml的SQL定义片段:
1 | <!-- AMapper.xml sql:--> |
JAVA代码片段:
1 |
|
好了,最后就贴出MultiSourcePageQueryBuilder 源代码,其实理解了多数据源分页的原理后写代码还是很简单的。
1 | public class MultiSourcePageQueryBuilder<T,B> { |
提示:.NET语言也可以参考上述JAVA代码转为实现对应的C# 或VB.NET版本的多数据源分页查询工具类,个人觉得还是比较适用的,如果大家觉得也能帮助到你,可以点赞支持一下哈!
- 标题: 分享自研实现的多数据源(支持同DB不同表、跨DB表、内存数据、外部系统数据等)分页查询工具类实现原理及使用
- 作者: WenJun.Zuo
- 创建于 : 2024-02-24 12:05:10
- 更新于 : 2024-02-24 12:05:10
- 链接: https://www.zuowenjun.cn//2024/02/24/java-multi-source-page-query-builder/
- 版权声明: 本文章采用 CC BY-NC-SA 4.0 进行许可。