mysql - 存储数以百万计的每日 IP 地址日志

Question

我们有一个点击跟踪系统，我可以跟踪每个请求的 IP 地址。

我们每天都有数百万次点击。

对于每个请求，在 MySQL 中将 IP 地址存储为 1 行

我们还需要每日统计前 10 个 IP 地址命中率。

这是我在使用 MySQL 时遇到的问题，但我们的问题是数据库越来越重，占用的空间也越来越大。

我在寻找可以有效存储此 IP 地址的良好“数据结构”吗？

目前，如果我选择好的数据结构，那么我的问题将得到解决，我将每一次点击都存储为一行

我不想运行复杂的查询，而是每天运行前 10 个，每个 IP 地址每周运行前 10 个。

并且必须节省存储空间

Answer 1

如果您不需要每个 ip 访问时间戳都精确到秒，您可以将每一天分成一系列时间段(可能每 10 或 5 分钟一个段)。每个日期时间段在时间段表中都有一个 id。然后，您可以为 ip 表中的每个唯一 IP 地址创建一个 id。

然后您有一个连接表，您可以在其中将 IP 地址(外键)与具有计数(无符号整数)的时间段(外键)相关联。因此，您的核心访问行数据现在减少为 2 个 ID 和 1 个无符号整数(最重要的是，没有字符串)。

所以当你收到一个IP的请求时，你判断当前的时间段，如果那个时间段不存在，就为新的时间段创建一行。如果当前时间段与IP没有关联，则创建一个新行，计数为1。如果该时间段和IP有一行，则增加计数。

通过这种方式规范化数据/表格并略微降低准确性，您实现了一种信息压缩形式。尝试使用时间段间隔来找到最佳权衡。例如。如果您不需要几分钟甚至几小时的查询粒度，您可以将时间段设置为一天。

更新:

是的，所以上面的所有内容都是压缩来自同一 IP 地址的多次点击。与唯一点击相比，这显然更有效，你获得的重复点击越多。如果您只关心独特的点击率，则完全无关紧要。

有多种方法可以将 IPv4 地址压缩为 unsigned int(32 位)。只是位移每个 a.b.c.d部分转换为字节 0xff000000 , 0x00ff0000 , 0x0000ff00 , 0x000000ff分别。

这样，每个 IP 使用 4 个字节而不是字符串；在这一点上存储外键是没有用的(无论如何至少需要 4 个字节)。因此，您可以只拥有一个包含字段的非规范化表:(IPv4 作为编码的无符号整数，日期时间/时间戳作为 4 字节整数)。您可能会用日期和计数替换日期时间，具体取决于多次点击对您是否重要。如果多次点击不算数，您真的可以使用 int 表示 IP 和 int 表示日期。

如果 day-granulartiy 是您需要的最低粒度，则还有一个更进一步的选择:您可以在每天结束时清除此 IP 数据库表，并将聚合查询的结果仅存储在数据库中。其余数据可以每天存档并从 IP 数据库表中删除。这意味着您的表只需要一个字段:编码的 IP 作为 unsigned int。在这一点上，问题就变成了每天构建大量独特的整数集。

您还可以将时间(或时间段)扁平化/非规范化为 int(或什至更小)，这取决于您想要记录时间的频率/粒度，以及您是否选择聚合/存档/清除定期 IP 数据库表。

另一种以压缩方式存储多个 IP 地址的方法是使用 trie数据结构，但是它不直接映射到数据库存储(与内存数据结构相比)。通过 SQL 存储树结构(例如 trie)的一种方法是使用 Materialized Path方法 - 但是，此方法无法实现良好的数据压缩，而且查询开销可能不值得。