寻找在MySQL中存储IP以获得更好性能的方法。

Question

我们公司这周在进行一场热烈的辩论。我们正在创建一个数据库来存储代理信息，就大部分而言，我们已经制定了模式，但还需要决定如何存储IP地址。一方希望使用4个小整数，每个八位组（octet）一个；另一方则希望使用1个大整数，INET_ATON。

由于这些表将会非常庞大，因此性能至关重要。我处于中间地带，因为在我的世界中，我通常使用 MS SQL 和 4 个小整数。我对存储 IP 地址的这种体积没有足够的经验。

我们将使用 Perl 和 Python 脚本访问数据库，将数据进一步归一化到其他几个表中，以便进行交流、处理等等。

我相信这个社区中有一些人已经做过类似于我们正在做的事情，我很想听听他们的经验和哪个路线是最好的：用1个大整数或4个小整数来存储IP地址。

编辑 - 我们担心的之一就是空间问题，这个数据库将会很庞大，例如每天有 5 亿条记录。因此，我们正在权衡空间问题以及性能问题。

编辑二 - 一些对话已经转向我们将要存储的数据量……这不是我的问题。问题在于哪种方法更适合存储 IP 地址，以及为什么。正如我在评论中所说，我们为一家大型财富 50 强公司工作。我们的日志文件包含用户的使用数据。这些数据反过来将用于安全上下文中的一些指标和驱动几个安全工具。

- OhioDude

---

@OhioDude：目前，Google每天大约有2亿次点击量。我认为为应对每天5亿次点击量做准备恰恰是所谓的“过早优化”。 - Quassnoi

是的，但是一个网页的访问并不会在日志文件中产生一行记录。网站上的每个图像和资源都有自己在代理日志中的记录。 - OhioDude

即使每个页面有100个图像/CSS/JavaScript文件（不考虑这些资源往往被浏览器大量缓存），您仍然与Google相差一到两个数量级。存储IP地址将是您较小的问题之一... - Benjamin Wohlwend

@SpoonMeiser：每天处理500M行数据，大约每秒6K行，只要批处理得当，这并不是不可能的。 - Quassnoi

相信我，关于数据量的问题我们已经考虑过了。想象一下，所有来自财富50强企业的代理日志都汇聚到同一个地方。我们获取的日志是一天前的。一旦我们获得它们，我们会使用一些awk技巧来清理和尽可能规范化它们。然后使用perl将其写入数据库。 - OhioDude

Answer 1

我建议先考虑您将运行哪种类型的查询以决定采用哪种格式。

只有在需要提取或比较单个八位组时，您才需要考虑将它们拆分为单独的字段。

否则，将其存储为4字节整数。这也有额外的好处，可以使用MySQL内置的INET_ATON()和INET_NTOA()函数。

性能 vs 空间

存储：

如果您仅支持IPv4地址，则MySQL中的数据类型可以是UNSIGNED INT，仅使用4字节的存储空间。

要存储各个八位组，您只需要使用UNSIGNED TINYINT数据类型，而不是使用每个1字节存储的SMALLINTS数据类型。

两种方法都可能使用类似的存储空间，对于某些开销来说，使用单独的字段可能略微多一些存储空间。

更多信息：

• 数字类型概述
• 整数类型（精确值）- INTEGER，INT，SMALLINT，TINYINT，MEDIUMINT，BIGINT

性能：

使用单个字段将产生更好的性能，这是一个比较而不是四个。您提到只对整个IP地址运行查询，因此无需保留八位组的单独字段。使用MySQL的INET_*函数将一次进行文本和整数表示之间的转换以进行比较。

- Andre Miller

---

我们的大多数查询将提取IP地址的聚合信息。例如，这个远程站点有多少个不同的IP地址访问过？或者在这8小时内，这个特定的主机访问了多少个站点？ - OhioDude

如果您将其存储为4字节整数，请确保不在其他任何地方硬编码该假设。为什么？IPv6。确保您有明确的升级路径。 - Schwern

Answer 2

MySQL中的BIGINT类型占用8个字节。

为了存储IPv4地址，使用UNSIGNED INT就足够了，我认为这应该是你要使用的。

我无法想象一个情景，其中4个八位组比单个INT表现更好，而后者更方便。

还要注意，如果你要执行像这样的查询：

SELECT  *
FROM    ips
WHERE   ? BETWEEN start_ip AND end_ip

如果您的表中有start_ip和end_ip这两列，那么使用这些查询的性能会很差。

这些查询用于确定指定的IP是否在子网范围内（通常是为了禁止它）。

为了使这些查询高效，您应该将整个范围存储为具有SPATIAL索引的LineString对象，并像这样查询：

SELECT  *
FROM    ips
WHERE   MBRContains(?, ip_range)

查看我的博客文章，了解如何执行此操作的更多详细信息：

• 禁止IP地址

- Quassnoi

Answer 3

使用PostgreSQL，有一个本地数据类型可以实现。

更严肃地说，我会选择“一个32位整数”的方法。只有当四个八位组被视为一个整体时，IP地址才有意义，因此在数据库中没有必要将八位组存储在不同的列中。您会使用三个（或更多）不同的字段存储电话号码吗？

- Greg Hewgill

---

更换关系型数据库不是一项轻松的任务。我喜欢pgsql，但仅因为一个数据类型而选择一个数据库管理系统并不能完全合理。 - J-16 SDiZ

很想使用Postgres，但是对于这个项目，我们必须使用MySQL。 - OhioDude

Answer 4

为了同时支持IPv4和IPv6，使用VARBINARY（16），IPv4始终为BINARY（4），IPv6始终为BINARY（16），因此VARBINARY（16）似乎是支持两者的最有效方法。要将它们从普通可读格式转换为二进制格式，请使用INET6_ATON（'127.0.0.1'），要反转该过程，请使用INET6_NTOA（binary）。

- hanshenrik

Answer 5

我认为将其分成单独的字段并不是特别明智的做法 - 就像将邮政编码分成几个部分或将电话号码拆分一样。

如果您想要有关各个部分的具体信息，这可能很有用，但是我认为没有使用32位整数的真正理由。

- Rich Bradshaw

---

可能更多是一条注释。 - Menotdan

Answer 6

将IP地址和整数之间高效地进行转换（可能对你有用）：（PERL）

sub ip2dec {
    my @octs = split /\./,shift;
    return ($octs[0] << 24) + ($octs[1] << 16) + ($octs[2] << 8) + $octs[3];
}

sub dec2ip {
    my $number = shift;
    my $first_oct = $number >> 24;
    my $reverse_1_ = $number - ($first_oct << 24);
    my $secon_oct = $reverse_1_ >> 16;
    my $reverse_2_ = $reverse_1_ - ($secon_oct << 16);
    my $third_oct = $reverse_2_ >> 8;
    my $fourt_oct = $reverse_2_ - ($third_oct << 8);
    return "$first_oct.$secon_oct.$third_oct.$fourt_oct";
}

- user105033

---

我没有给你点踩，但是如果他的数据库已经有这个功能，用Perl做这件事可能有点多余。 - Andre Miller

Answer 7

虽然这是一个旧的帖子，但为了读者的利益，请考虑使用ip2long。它将IP地址转换为整数。

基本上，当存储到数据库时，您将使用ip2long进行转换，然后在从数据库检索时使用long2ip进行转换。数据库中的字段类型将为INT，因此与将IP地址存储为字符串相比，您将节省空间并获得更好的性能。

- Roger