网站开发登录链接,wordpress二次元极简主题,重生做皇帝小说网站,免费心理咨询师24小时在线咨询报表需求背景
报表是一个很常见的需求#xff0c;在项目中后期往往会需要加多种维度的一些统计信息#xff0c;今天就来谈谈上线近10个月后的一次报表优化优化之路#xff08;从一天报表跑需要五分钟#xff0c;优化至秒级#xff09; 需求#xff1a;对代理商进行日统计…报表需求背景
报表是一个很常见的需求在项目中后期往往会需要加多种维度的一些统计信息今天就来谈谈上线近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查询 以及 内存循环对比且门店统计那块是嵌套循环查询订单的查询时间也有点长 带着这些思路去排查发现几个问题
每个代理都需要去查询一遍门店统计信息这里网络IO次数 总代理数量 若每次50ms * 几千emm怎么这么多…订单的查询某些代理耗时很高去看了下索引emm1 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(MN) 代理门店列表 与 有订单门店列表求交集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
