MySQL日志表月统计排重_【mysql】海量日志数据如何处理统计？

国士九颜

于 2021-02-28 08:33:44 发布

阅读量229

点赞数

文章标签： MySQL日志表月统计排重

本文链接：https://blog.csdn.net/weixin_29092031/article/details/114875744

版权

项目需要做一个dashboard图表网站，展示日志的相关统计信息。这个页面图表很多，一次性会加载出很多数据。

日志表有很多种，都是一些入侵攻击日志、恶意站点访问日志等等，需要统计出当前时间、过去24小时、过去一周被攻击主机个数、恶意站点数(这是其中两个需求)等等数据。

比如被攻击主机个数，需要查多张数据表，然后统计出这个数据。

日志存储在PostgreSQL里面，已经基于时间做了分表，但是每天的的日志量都在100W以上。

写入数据库的模式是随时从其他的系统中写入。

根据这个应用场景，如果设计这个后端统计呢？还请大神提供一点思路，谢谢。

回答

虽然是一个PostgreSQL的问题，但是打了各种数据库标签。那么我就从MongoDB和NoSQL的角度说说这个问题。因为一些情况不是特别清楚，基于自己的假设来回答，如果有和你情况不符的地方再提出来。

数据库的日常应用无非OLAP和OLTP两大类，你的应用是一个比较典型的OLAP应用。通常OLAP的特点是对时效性的要求不是非常高，对系统资源占用比较重。你没有提对时效性要求到底有多高，还有你们数据的写入模式是怎样的。每天某个时间批量导入？或是随时从其他系统写入？不管怎样，还是有一些通用的办法来应对的。以下是无论使用哪种数据库都可以做的一些事情：

预聚合

从你的描述来看这是个比较典型的时序数据，过去的数据是不会变的。所以可以在每天结束时把这一天的数据先聚合好，某年某月某日有多少次攻击多少次恶意访问之类。如果要查一段时间的，则可以把已经按天统计好的数据再聚合一次。比如一个月的就是30条数据再次聚合，这比30x100w=3000w条数据的聚合要轻松很多。如果你的统计粒度需要比天还小，那就要看具体小到什么程度。如果是精确到时，那我可能还是会考虑按小时预聚合，这样统计比如过去30天的数据，就会有30×24=720条数据，也在接受范围内。但是如果统计范围允许到年，则有365×24=8760，情况就不是很乐观了。当然如果需要精确到分钟，那又是更麻烦的事情。但即使这样，预聚合仍然能有效减少数据量从而降低运算所需的时间和资源。为了解决小粒度聚合的问题，实际应用中可能需要进行多个层次的预聚合。比如按月，按天，按时，按分分别聚合好，这样在需要某分钟到某分钟的数据时，可以把大粒度的范围通过月、天、时先消化掉，剩下的两头零碎部分再用时、分钟处理，这样最大程度上减小需要聚合的数据量。

索引优化

无论使用哪种数据库，索引优化都是很重要的步骤。按上述方法预聚合后，各种时间因素肯定都是需要在索引中的。如果在时间基础上还需要对某个主机或域名等筛选，则最好是有这些字段的联合索引。具体问题具体分析，这个还需要你根据自己的表结构和查询去优化。

读写分离

无论怎么优化，OLAP对资源的占用都是不能忽略的。如果你的数据是实时写入，聚合期间很容易受到I/O瓶颈的影响。所以最好是把接受数据和分析数据的结点分开。

安利时间

说说如果使用MongoDB还有哪些事情可以做。

分片。水平扩展是NoSQL的特色之一，理论上所需时间和结点数量成反比。而数据量的增长在分布式环境中也不是一个问题。

Tag Aware Sharding。MongoDB分片的特色，可以把旧数据自动归集到容量大，但是性能相对差的硬件上，这样让热数据始终保持在性能较好的机器上达到更好的效果。

天然的读写分离和高可用。复制集本身就可以实现读写分离和高可用。相信这两个特性对任何应用都是很有意义的。

最后还是要提醒一点，理论归理论，没有一个方案是完美的，实际应用时肯定还会遇到各种各样奇怪的问题。编程是一项创造性的工作，需要你自己在实践中不断寻找最优的解决方案，在实践中成长。

1、个人感觉按天分区比较好，为了提升性能，统计SQL不要直接查询父表，而是将子表进行 union 统计。

2、另一点是合理设计索引；