likes
comments
collection
share

关于多表分页业务逻辑处理

作者站长头像
站长
· 阅读数 9

前言

在平时我们写分页列表的时候，通常对于单表的分页，通过Mybatis分页插件结合Mysql的limit分页轻松完成单表的分页，但是也难免遇到多表的情况，多表需要对数据进行处理，合并、分组、排序、然后在进行分页查看，单纯的依靠分页插件和简单的limit的是无法实现的，也是相对于比较考验性能的一个接口，复杂的逻辑处理可能会对接口性能产生影响。

数据量大的情况下，对于分页性能的要求会更加高，如果分页查询不正确，会导致性能下降，严重直接会导致页面卡死，之前也是遇到过。

Mysql 单表分页

其实mysql的分页还是分很多种的，常见limit，还有子查询的方式进行limit、JOIN分页方式，这几种我都想大概说下，很多时候我可能对这种相对于简单的东西不太在乎，但是往往存在性能隐患。

limit

LIMIT * OFFSET *，用于返回select查询记录数，limit第一个参数来设定记录行的偏移量，也就是从第几行开始进行遍历拿数据，第二个参数来设定每次返回多少行数据，⚠️记录行的偏移量是从0开始的，不是1； limit m,n ，一般m是page−1)∗page-1)*page−1)∗pagesize ，当前页-1乘以每页条数；

//返回 6-15行的数据 ，需要尽量减少m的值
SELECT * FROM table LIMIT 5,10;


//返回前5条数据，limit 5 = limit 0,5 
SELECT * FROM table LIMIT 5

//每次查询获取上一页的最大id，无需扫描之前的数据 ，需要尽量减少m的值
select * from table where id > max_id order by id limit n;


//join 分页 通过关联子查询进行分页
SELECT * FROM `table` AS t1   
JOIN (SELECT id FROM `table` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2   
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

其实以上的分页sql都存在着一些性能问题，在大数据量的情况下，会导致越往后分页查询的速度越发的慢，而且在使用limit的时候，需要获取到offset（偏移量），根据size来获取数据，大概就是后面两种写法。

可优化的点：

  • 可以通过子查询建立索引，用索引字段进行查询，索引的效率要高很多
  • 通过书签记录记录上次数据位置，下次直接从该位置进行扫描数据，可以通主键id
  • 分页过深会导致，使用order by 索引失效

多表分页思路

最近开发需求，大概是这么一个业务场景，两张表需要合并查询，并且根据维度进行分组后，分页进行展示；

表结构大概是这样的，收入和支出表，需要根据用户分别展示，收入和支出的数据。

关于多表分页业务逻辑处理

我的基本思路是：

  • 第一步：union数据，构建虚拟表，进行分组、排序、并且进行分页； 因为要合并数据展示，所以无法讲两张表分别进行分页，必须合并后分组，进行分页；
Query query = DTO.getQuery();
//每次分页条数
Integer size = query.getSize();
//页数
Integer current = (query.getCurrent()-1)* size;

select CONCAT(a.name, ' ', a.batchNo) as nameBatch ,a.* from (SELECT
        *
        FROM
        table t1
        UNION
        SELECT
        *
        FROM
        table t2
        ) as a group by a.name,a.batchNo order by a.createTime desc   limit #{dto.current},#{dto.size}
  • 第二步：根据上一步已经进行分组，得到分页后的结果集，过滤出table1和table2表的关联字段，再去分别查这两张表，也就是table1 和table2
//过滤出，两张表都有的字段，也是分组维度所需要的字段
List<String> batchNos = appFarmerFeeItemVOS.stream().map(AppFarmerFeeItemVO::getBatchNo).collect(Collectors.toList());



//table 1 
SELECT
	* 
FROM
	table1 
WHERE
	batch_no IN < foreach collection = "dto.batchNos" INDEX = "index" item = "item" OPEN = "(" SEPARATOR = "," CLOSE = ")" > #{item}
	</ foreach >;
//table 2 
	SELECT
	* 
FROM
	table2 
WHERE
	batch_no IN < foreach collection = "dto.batchNos" INDEX = "index" item = "item" OPEN = "(" SEPARATOR = "," CLOSE = ")" > #{item}
  • 第三步：通过代码逻辑对两张表的结果集，根据第一步分组的维度，每一个分组，循环去组装需要的数据明细；

大概就是这样的一个数据结构

关于多表分页业务逻辑处理

总结

多表分页，需要合并之后构建虚拟表，进行分页； ⚠️union的时候需要注意上下表，列数必须相同，否则会出现mysql报错，union列数量不一致，无法进行合并； ⚠️ union上下表，字段的映射的顺序上下必须保持一致，且字段名必须相同，否则union后进行group by会出现分组维度出现问题； 如果还有知道其他对于多表分组更方便方法的小伙伴，可以在评论区留言，大家一起交流下；