一.需求背景
需求:分页列表的查询功能 , 按第三方业务系统数据已有的设计 , 根据用户和公司维度统计金额, 并且按项目维度进行数据权限的控制:
二.实现方式:
根据需求要求根据用户和公司维度统计金额,涉及到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_no from toll_order as torder join toll_batch tb on torder.batch_id = tb.id join customer c on c.id = torder.customer_id join project p on tb.project_id = p.id join project_contract pc on p.contract_id = pc.id join company company on company.id = pc.company_id where torder.order_status = 'SUCCESS' AND tb.project_id IN (...) GROUP BY c.cert_no, company.id limit 0,10 开发环境执行结果: 查询数据量: 10 执行时间: 69s
1.2.总记录数查询sql:
SELECT count(1) from ( select company.id as companyId, c.cert_no from toll_order as torder join toll_batch tb on torder.batch_id = tb.id join customer c on c.id = torder.customer_id join project p on tb.project_id = p.id join project_contract pc on p.contract_id = pc.id join company company on company.id = pc.company_id where 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+1
2.数据权限维度的改变,不可采用项目维度进行控制, 需调整到公司维度控制
跟产品沟通后, 不能接受第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_id limit 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.分页最后一页的查询: 开发环境耗时20s
2.总记录数的查询:开发环境耗时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 ext where order_status = 'SUCCESS' AND ext.project_id in (....) 执行结果: 查询数据量: 1,332,263 执行时间: 7.49s , 比上述group by语句的20s快 2.6倍左右
至此,在现有成本资源的控制下, 目前是最佳处理方式, 若还有更好的不增加成本的方案,欢迎给出建议
相关推荐
前端的世界总是在不断变化,作为开发者,我们需要保持好奇心和学习热情,不断探索新的技术,只有这样,我们才能在这个快速发展的时代中立于不败之地。低代码也是一个值得我们深入探索的领域,让我们拭目以待,它将给前端世界带来怎样的变革。
介绍一款程序员都应该知道的软件JNPF快速开发平台,很多人都尝试用过它,它是功能的集大成者,任何信息化系统都可以基于它开发出来。
JNPF可以实现应用从创建、配置、开发、测试到发布、运维、升级等完整生命周期的管理。减少了传统应用程序的代码编写量,通过图形化、可视化的界面,以拖放组件的方式,即可快速生成应用程序的产品,大幅降低了开发企业管理类软件的难度。
希望这篇文章对你有所帮助~