php - 每天存储数百万条记录并可出于统计目的进行分组的数据的最佳方法是什么？

Question

我正在开发一个用于营销事件的自定义跟踪工具。该工具位于广告和着陆页之间。它负责保存用户的所有数据，例如用户代理中的信息、IP、登陆页面的点击次数以及用户 IP 的地理编码数据(国家/地区、ISP 等)。

目前我遇到一些设计问题:

• 这些广告系列的流量非常非常高，因此每天可能会插入数百万行。该系统可以有多个用户，因此我无法将所有这些数据存储在一张表上，因为这会变得一团糟。也许我可以将数据拆分到更多表中，每个用户一张表，但我不确定这个解决方案。
• 数据保存过程必须尽快完成(几毫秒)，因此我认为 NodeJS 在这方面比 PHP 更好。特别是在速度和服务器资源方面。我不希望服务器因内存不足而崩溃。
• 出于统计目的，我需要对这些数据进行分组。例如，对于访问我的着陆页的每个用户，我都有一行，但我需要对这些数据进行分组，以显示该特定着陆页上的展示次数。因此，对于如此大量的行，所有这些查询都需要尽可能快地执行。
• 我需要对 IP 地址进行地理编码，因此我需要准确的信息，例如国家/地区、ISP、连接类型等，但如果我调用 API 服务，这可能会减慢数据保存过程。而且这必须实时完成，不能稍后完成。

保存过程后，系统应该重定向到登陆页面。时间对于不失去任何可能的领先优势很重要。

基本上，我正在寻找以下方面的最佳解决方案:

• 有效管理非常大的数据库
• 在尽可能短的时间内(毫秒)保存用户的数据
• 如果可能，请在尽可能短的时间内对 IP 进行地理编码，而不会阻止执行
• 优化用于生成统计信息的架构和查询

你有什么建议吗？提前致谢。

Answer 1

每个用户一个表，情况更糟；不要这样做。

每天数百万行——每秒数十行，也许数百行？这可能需要某种形式的“暂存”——收集多行，然后批量插入它们。在进一步讨论之前，请详细说明数据流:单个客户端与多个客户端。 UI 与批处理。暂定创建表。等等

统计 - 计划创建和增量维护“汇总表”。

您是否正在尝试将用户 IP 地址映射到国家/地区？这是一个单独的问题，已得到解答。

“必须”“实时”“毫秒”。面对现实，你将不得不做出一些权衡。

更多信息:转到http://mysql.rjweb.org/ ;从那里，请参阅有关数据仓库技术的三个博客。

如何按天存储

InnoDB 按主键顺序存储数据。因此，要获得一天中彼此相邻的所有行，必须以日期时间开始 PK。对于大型数据库，可以通过允许查询按顺序扫描数据来显着改进某些查询，从而最大限度地减少磁盘 I/O。

如果您已经有id AUTO_INCRMENT(并且如果您仍然需要它)，请执行以下操作:

PRIMARY KEY(datetime, id),  -- to get clustering, and be UNIQUE
INDEX(id)  -- to keep AUTO_INCREMENT happy

如果您有一年的数据，并且这些数据无法放入 RAM，那么此技术对于小时间范围非常有效。但如果你的时间范围大于缓存，你将受到 I/O 速度的影响。

使用不断变化的数据维护汇总表

这也许是可能的；我需要更好地理解数据和变化。

无论缓存、调整和其他优化如何，您都无法在亚秒级时间内扫描一百万行。您可以使用汇总表更快地获取所需的数据。

缩小数据

• 如果 INT(4 字节)就足够了，就不要使用 BIGINT(8 字节)；如果 MEDIUMINT(3 个字节)可以，则不要使用 INT。等等
• 在适当的情况下使用UNSIGNED。
• 规范重复的字符串。

较小的数据将使其更易于缓存，因此当您必须访问磁盘时运行速度更快。