日报表定时任务优化历程

报表需求背景

报表是一个很常见的需求，在项目中后期往往会需要加多种维度的一些统计信息，今天就来谈谈上线近10个月后的一次报表优化优化之路（从一天报表跑需要五分钟，优化至秒级）
需求：对代理商进行日统计
统计数据：门店数量、设备总数、当日订单数/金额/退款/收益、门店七日新增数、30日0订单门店数量
前置约束：未明确标明指定主库操作 以及 事务，则默认代表走 从库 以及 默认事务

先来看看这一版的流程：

// 以下所有查询/统计 均为从MySQL中获取按天 开始 循环（任务调度时可指定日期补偿重跑，防止后续定时任务中断，默认跑昨日数据）1. 获取所有代理商（大几千个）代理商列表 循环开始2. 门店统计2.1    获取代理名下所有门店列表2.2    查询代理近三十天内有订单的门店ID，对比门店列表 得到：30日0订单门店数量2.3    获取代理名下七日新增门店3. 设备总数统计4. 订单统计4.1    统计代理昨日订单数/订单金额/退款（订单/收益 均是千万级表）4.2    统计代理昨日收益代理商列表 循环结束5. 新开事务 且 指定主库5.1    清理对应日期的统计数据5.2    对统计数据进行分批提交（mybatis拼接SQL，千条为一个批次，防止后续当日统计数据过多，导致SQL长度超限）5.3    事务提交
按天 结束 循环

以上流程跑当日耗时大约在4-5分钟，乍一看其实并不慢，但此时距离上线已有九月有余，乍一算这个任务得跑20+小时
不管了，能跑就行，先上线再优化
…
after a long time
午夜惊醒，这玩意得优化哇，这也太不好用了
-_- 还债的时刻到了
…

第二版

思考：报表任务里都是一些MySQL查询 以及 内存循环对比，且门店统计那块是嵌套循环查询，订单的查询时间也有点长
带着这些思路去排查，发现几个问题：

1. 每个代理都需要去查询一遍门店统计信息，这里网络IO次数 = 总代理数量
   若每次50ms * 几千，emm，怎么这么多…
2. 订单的查询某些代理耗时很高，去看了下索引，emm，1 2 3 4 …8 9 10个索引
   了解到MySQL8.0是基于成本模型来生成执行计划的，那么有可能是索引不完全匹配 或 执行计划偏移，下面贴一下SQL与表当前索引

# 订单统计SQL
SELECTcount( * ) orderTotal,sum( pay_amount ) AS orderAmount,sum( refund_amount ) AS refundTotal
FROMorder 
WHEREagent_id = #{groupId}AND pay_rev_time BETWEEN #{startDate} and #{endDate}    # 这个时间可能会有跨度# 贴下部分索引
uk_order_no            `order_no` ASC
idx_agent_id            `agent_id` ASC
idx_pay_rev_time    `pay_rev_time` ASC
idex_emp            `empower_time` ASC

发现问题，那么就开始一个个尝试改造优化下：

问题一流程优化

1. 分组查询所有代理 门店总数
2. 分组查询所有代理 7 日新增门店数
3. 分组查询所有代理 名下门店总数
4. 分组查询所有代理 近三十天内有订单的门店ID
5. 分组查询所有代理 设备总数
6. 分组查询所有代理 昨日收益金额
按天 开始 循环（任务调度时可指定日期补偿重跑，防止后续定时任务中断，默认跑昨日数据）7. 获取所有的代理代理商列表 循环开始8. 门店统计8.1    内存中 获取代理名下所有门店列表（时间复杂度O(1)）8.2    内存中 查询代理近三十天内有订单的门店ID，对比门店列表 得到：30日0订单门店数量（时间复杂度O(1)）8.3    内存中 获取代理名下七日新增门店（时间复杂度O(M+N) 代理门店列表 与 有订单门店列表求交集）9. 订单统计9.1    MySQL 统计代理昨日订单数/订单金额/退款9.2    内存中 统计代理昨日收益（时间复杂度O(1)）10. 内存中 获取设备总数统计（时间复杂度O(1)）11. 新开事务 且 指定主库11.1    清理对应日期的统计数据11.2    对统计数据进行分批提交（mybatis拼接SQL，千条为一个批次，防止后续当日统计数据过多，导致SQL长度超限）11.3    事务提交代理商列表 循环结束
按天 结束 循环

至此重跑，发现统计一天的数据已经达到秒级，这里给到一段真实执行时间

问题二SQL优化

看到这里就会有小伙伴有疑问了，为什么上面 9.1流程 中不采用预先一次性统计所有代理数据呢？
这里是为了引出第二个优化方向，不然这不就结束了嘛~~~

修改后打补丁继续执行，又又又失败了…

# 回顾上面的 订单统计SQL，有两个条件，分别是：agent_id、pay_rev_time
# 而这两个字段也分别有自己的独立索引，分别是：idx_agent_id、idx_pay_rev_time# 那么对于优化器就大概以下几个策略来进行查询：
#     1. 根据 idx_pay_rev_time索引来找到一段时间内数据，然后再根据agent_id 筛选出最终的结果
#     2. 根据 agent_id索引来找到具体代理商的数据，然后再根据pay_rev_time 筛选出最终的结果
#     3. 全表 扫# 在业务中，使用上述几种方式去查询都将不是最优解，而 agent_id、pay_rev_time又是此SQL的必填条件，
# 此时可以为他们创建一个联合索引：ALTER TABLE order ADD INDEX idx_agentid_paytime (agent_id,pay_rev_time);
# 并且在SQL上强制使用此索引，防止执行计划偏移SELECTcount( * ) orderTotal,sum( pay_amount ) AS orderAmount,sum( refund_amount ) AS refundTotal
FROMorder force index(idx_agentid_paytime)
WHEREagent_id = #{groupId}AND pay_rev_time BETWEEN #{startDate} and #{endDate}

后记

问题一流程优化解释

此解题思路实际上是避免了循环查询MySQL，以 一次慢查询 来 优化后续的 多次快查询。

但事无绝对，在某些情景下，一次统计的慢查询可能会令系统负载很高，甚至影响到实时业务，那么保持现状：多次快查询 可能会更优。

少量多次 与 一次解决，需要根据业务以及系统现状来衡量，有时候快并不是唯一的追求

参考资料

https://dev.mysql.com/doc/refman/8.0/en/cost-model.html
https://www.cnblogs.com/wcwen1990/p/6656611.html