Java 在成本资源控制下, 千万级别数据查询优化

2024-07-12
福建
本文字数：2382 字
阅读完需：约 8 分钟

一.需求背景需求:分页列表的查询功能 , 按第三方业务系统数据已有的设计 , 根据用户和公司维度统计金额, 并且按项目维度进行数据权限的控制：

二.实现方式：根据需求要求根据用户和公司维度统计金额，涉及到 6 个业务表,分别是[订单明细,订单批次,用户,项目,合同,公司] ,1 个[订单明细]关联 1 个[用户]1 个[订单批次]关联 N 个[订单明细] ,1 个[订单批次]关联 1 个[项目] ,1 个[项目] 关联 1 个[合同],1 个[合同]关联 1 个[公司]

当时[订单明细]的数据量:开发环境:400 万生产环境:5700 万

数据库配置: 4 核 8G

1.实现方式一, 使用 GROUP BY:

已创建对应的索引

1.1.分页查询 sql:

select    company.id as companyId,    c.cert_nofrom toll_order as torderjoin toll_batch tb on torder.batch_id = tb.idjoin customer c on c.id = torder.customer_idjoin project p on tb.project_id = p.idjoin project_contract pc on p.contract_id = pc.idjoin company company on company.id = pc.company_idwhere       torder.order_status = 'SUCCESS'AND tb.project_id IN (...)GROUP BY c.cert_no, company.idlimit 0,10
开发环境执行结果: 查询数据量: 10执行时间:   69s

复制代码

1.2.总记录数查询 sql:

SELECT count(1) from (select    company.id as companyId,    c.cert_nofrom toll_order as torderjoin toll_batch tb on torder.batch_id = tb.idjoin customer c on c.id = torder.customer_idjoin project p on tb.project_id = p.idjoin project_contract pc on p.contract_id = pc.idjoin company company on company.id = pc.company_idwhere       torder.order_status = 'SUCCESS'AND tb.project_id IN (...)GROUP BY c.cert_no, company.id) a
开发环境执行结果: 查询数据量:1,166,855执行时间:   108s

复制代码

因在开发环境还没达到生产环境的数据量, 执行效率就已经这么慢, 是接受不了的,估想另外的实现方式

2.实现方式二:

使用汇总表, 避免 group by 语句和减少联表

但使用汇总表 ,会遇到几个问题,如 1.因业务方没有维护[更新时间]字段,估需定时任务每天统计汇总,就不是实时数据,是 N+12.数据权限维度的改变,不可采用项目维度进行控制, 需调整到公司维度控制

跟产品沟通后, 不能接受第 2 点数据权限的问题,故此方案 pass。

3.实现方式三：

新增扩展表, 把需要的外键 id 放在扩展表,减少联表

3.1.全量初始化扩展表定时任务(只执行一次)

3.2.新增 toll_order 的扩展表, 把身份证,项目 id,公司 id 等字段记录到扩展表, 避免联表,列表分页查询改用扩展表.

3.3.使用 DTS 订阅数据变化发送 MQ,通过接受 MQ 增量更新扩展表的数据,并把该订阅的数据存储至重试表(用状态区分:处理完成, 处理失败)

3.4.重试定时任务: 查询处理失败的数据, 更新扩展表的数据, 更新重试表 (需保证同一 id 下的失败记录,按 MQ 时间戳升序排序,先处理第一个成功才执行后续的 , 否则执行顺序混乱会导致脏数据)

分页查询 sql:

SELECT cert_no,company_id    FROM toll_order_ext    where        order_status = 'SUCCESS'        AND project_id in (...)    GROUP BY cert_no, company_idlimit 0,10
预生产环境执行结果: 查询数据量: 10执行时间:   16s , 比方式一的69s快 4.3倍左右

复制代码

总记录数查询 sql:

select count(1) from (    SELECT cert_no,company_id    FROM toll_order_ext    where        order_status = 'SUCCESS'        AND project_id in (...)    GROUP BY cert_no, company_id) a执行结果: 查询数据量: 1,332,263执行时间:   20s,比方式一的108s快 5倍左右

复制代码

目前还是采用 Mybatis plus 的分页工具 , 开发环境与生产环境数据量差距较大, 还是存在潜在的性能问题:1.分页最后一页的查询: 开发环境耗时 20s2.总记录数的查询:开发环境耗时 20s

继续优化,方案如下:

分页查询:1.列表手动分页的查询语句调整,去除 group by 语句,改使用 distinct,然后再根据 cert_no,company_id 去 in 查询记录, 在代码层把 cert_no+company_id 作为 key 进行汇总金额, 分页 sql 如下:

SELECT DISTINCT cert_no,company_id    FROM toll_order_ext    where        order_status = 'SUCCESS'     AND project_id in (...)limit 0,10
执行结果: 查询数据量: 10第一页执行时间:   0.165s , 比上述group by分页语句的16s快 97倍左右最后一页执行时间: 15s, 比上述group by分页语句的20s快 1.3倍左右

复制代码

总记录数:1.列表接口手动分页, 不执行 count()语句的查询 2.新增一个查询总记录数和页数的接口: 通过当前登录人查询缓存获取总记录数, 有筛选条件则查询数据库 3.登录的时候, 触发查询总记录数,存储至缓存(先判断是否有该菜单权限和数据权限, 没有则不查询), 总记录数的查询不使用 group by 语句, 改使用 distinct , sql 如下:

SELECT count(distinct cert_no, company_Id)FROM toll_order_ext extwhere order_status = 'SUCCESS'AND ext.project_id in (....)
执行结果: 查询数据量:  1,332,263执行时间:   7.49s , 比上述group by语句的20s快 2.6倍左右

复制代码

至此,在现有成本资源的控制下, 目前是最佳处理方式, 若还有更好的不增加成本的方案,欢迎给出建议

伤感汤姆布利柏

关注

还未添加个人签名 2023-06-19 加入

还未添加个人简介

发布

暂无评论

创作场景

Java 在成本资源控制下, 千万级别数据查询优化

1.实现方式一, 使用 GROUP BY:

2.实现方式二:

3.实现方式三：

相关推荐

伤感汤姆布利柏

评论