汇总表在数据统计业务场景中的作用

type

status

date

slug

summary

category

icon

password

tag

统计表

慢查询

索引设计

MySQL

解决方案

近期项目上有一个需要基于任务数据统计报表的需求，组内一个研发初步设计方案实现后，发现页面请求耗时过长，向我请教了几个问题，我觉得在很多场景下还挺有参考意义，做下记录。

首先需求是基于历史任务订单数据进行数据统计，自动为客户展示产能统计报表，要求可以按照天数、月份、年度进行切换；还有一个报表是自动基于任务数据计算当班任务的完成率分类（按照呼叫-完成分档，2分钟、2~5分钟、5分钟之上）。

该同学向我提出问题时说明了一下自己的思考方案，我首先说明下：由于产能统计一定是需要基于所有完成的任务数据进行，因此该同学的想法是查询出所有已完成的任务订单，在内存中进行分组（一开始该同学确实是这样做的，向我发出求助是因为查询太慢导致接口超时）；对于查询超时的问题他的思考方案是使用后台线程把所有的已完成数据异步查询并存储到Redis中，这样无论哪个实例接收到页面请求都能够基于共享内存进行统计。

针对该同学的解决方案思考，我给出了自己的方案考虑以及初始解决方案的弊端（实时查询数据并分组，数据量过大的情况下内存剧增甚至溢出；MySQL查询大量数据也会造成压力过大），当然这也是我在阅读MySQL相关书籍时得到的经验：首先我认为针对这种历史完成的订单数据进行分组统计的需求，可以通过设计统计表的方式来解决，这是因为基于历史数据的统计这种需求实际上是有非常鲜明的特征的，那就是历史订单是不会再发生变化的静态数据，基于历史性的流水数据进行统计，不需要也不推荐进行实时计算，那这种需求是非常适合利用统计表进行解决的；通过统计表的方案设计我认为至少可以包括几点好处：

统计数据可以通过查询汇总表的方式快速获取，不再需要查询所有历史完成数据统计，这样可以使得接口查询的速度提高，也就意味着用户体验更高；

其次我们的业务实际上最大的数据部分就是任务订单数据，该同学一开始因为查询速度的问题想要异步存储到共享缓存Redis中，我给出了两点不推荐的原因：首先历史数据的查询既然是基于某种业务特征分组的，那么查询速度慢需要正面解决，而不是通过异步读取到共享缓存处理，这种基于某些条件获取历史数据的统计数量的查询，应该通过分析底层MySQL的查询语句建立对应的查询索引，来提高查询的执行速度；其次这种历史流水数据是随着时间线性增长的，只要是向共享内存中存储数据，就需要考虑数据的存储容量，像本次案例中的需求显然是不适合存储的。

针对这类需求我的解决方案建议

查询历史数据时，根据任务日期建立索引并分批次查询，每批次需要通过LIMIT限制查询数据量；
后台通过异步线程加载并计算汇总数据，计算完成再将结果存储到汇总表中；
如果汇总表中的数据已经不再需要刷新（日期强相关），则设计结束标识，每次对已经结束的日期不再重复计算，避免不必要的查询；
针对当班数据可以利用每次计算时强行重新计算的方式

当然在我提出统计表的解决方案之后，该同学基于业务需求还提出了一点自己的疑问，那就是如果客户需要切换每天统计产能的固定班次的时间点，那统计表的数据就不准确了；针对这个疑问我也给出了自己的见解，首先历史统计数据本就是基于过去的班次时间统计的，从业务意义来说并不是不准确的数据；其次即使客户真的需要改变历史数据的统计时间，可以在设计统计表时考虑将统计时间进行记录，这样即使客户更换统计班次无非是后台重新基于新的班次时间异步计算即可，而数据的格式是统一的。