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

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

二.实现方式：
根据需求要求根据用户和公司维度统计金额，涉及到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倍左右

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