mysql - 对单表大量数据进行报表汇总有什么高效的方法

问题描述

我有一个表记录了系统中所有功能的使用日志，精确到秒，我们简称为秒表。现在有需求是，分别制作对应的功能使用次数汇总，年，月，日，3个维度的报表。这个秒表1个月大概会增加400W条数据。

我的做法是，每一个月手动执行一次汇总方法。1.先把秒表汇总出日表2.再从日表汇总出月表3.再从月表汇总出年表4.然后删除秒表的数据，只保留最近7天的

这个方案有几个弊端，1.就是第一步，从秒表汇总出日表的时候，由于数据量太大，一个groupby一般都要卡很长时间，有时候还会假死。2.就是第四步，为了避免第一步数据量太大，所以才在汇总后删除用过的数据。但是这样删除之后，就等于丢失了实时数据。

所以。。。想问问大神们平时是怎么处理这些情况的，有什么更好的方案？

问题解答

1、你的第一步可以按照小时groupby 这样时间不会太长2、可以将秒表分表处理，即1周一个表，或者1天一个表，这样也可以解决这些问题。

把第一步“先把秒表汇总出日表”分散到每天执行前一天的数据，写个脚本，crontab定时每天执行一次。另外这种log型的数据，可以试试mongodb

数据产生时就已经做好报表数据的路过。把数据扔给elasticsearch试试速度。400w的数据而已，对mysql还是小意思的。另外，我推荐PostgreSQL

梳理sql，减少慢查询。通过多次查询来组合数据优化数据结构，操作+时间应该是一个索引(或者是时间+操作)。其实简而言之就是减少复杂sql，尽量通过其他方法去弥补。

大体是几个思路：1、将每月汇总的工作，分解到每一天，这样出月度最终结果的时间会比较短。2、为避免秒表的数据一直增大，可以考虑按照月度进行表分区，每月将上个月的数据移动到另外一个历史表，保持秒表的大小可控。另外一种方法是每月的数据进入结构相同、名称不同的表，从方案上就规避了秒表过大的问题。