如何存储和搜索IP地址

Question

我有四个IP地址来源，想要将它们存储到SQL Server中，并且允许根据起始国家代码分类的范围在排除列表中进行标记。

为此，我有两个表：

IPAddressRange CountryCode 需要知道的是，如果将数据返回给客户端并缓存以进行快速查询，则存储返回的数据以查询特定IP地址的最佳方法是什么。我想知道提供的IP地址是否在列表中。

之所以将列表存在数据库中是为了方便存储。

我希望缓存然后在客户端上使用数据的原因是听说在基于trie结构的搜索IP地址更快。因此，我认为需要从db获取列表，将其存储在缓存中，以一种非常快速的结构进行搜索。

请提供以下帮助：A）存储地址的SQL结构和B）搜索IP地址的代码。

我知道一个代码项目解决方案，其中包含用于搜索的代码算法，但不确定如何将其与存储方面结合使用。

理想情况下，无需使用第三方库。代码必须在我们自己的服务器上执行。

- Coolcoder

Answer 1

我已经按照你描述的方式按国家进行了筛选。

然而，经过一番尝试后，我发现使用SQL无法以高效的方式完成。这就是为什么像这个（我正在使用的）IP数据库提供二进制数据库的原因，因为它针对此类数据进行了优化，速度要快得多。

他们甚至明确表示：

请注意，针对导入到SQL数据库中的CSV数据进行的查询可能需要几秒钟时间。如果性能是一个问题，那么二进制格式会快得多，并且可以处理数千次查找每秒。

此外，他们甚至提供了代码来查询此数据库。

我在一个中等流量的生产网站上使用它，每个请求都进行了过滤，没有性能问题。

- mausch

---

我们有点限制，只能使用官方原始数据源。 - Coolcoder

请查看MaxMind所说的内容：“请注意，对导入到SQL数据库中的CSV数据进行的查询可能需要几秒钟的时间。如果性能是一个问题，二进制格式会快得多，并且可以处理数千个查询每秒”。 - Mauricio Scheffer

有一篇关于使用原始数据和算法的代码项目文章，每秒产生约500K次搜索。这很好，我不需要依赖第三方公司。然而，我需要最佳的方法来存储数据以将其提取到缓存中以使用此算法。 - Coolcoder

也许可以更改算法，使用缓存数据而不是直接加载原始文件。 - Coolcoder

我们甚至不能使用MaxMind的解决方案，甚至也不能使用IptoCountry的努力。 - Coolcoder

Answer 2

假设您的IP地址是IPV4，您可以将它们存储在整数字段中。创建两个字段，一个用于范围的下限，另一个用于上限。然后确保这些字段已索引。在搜索值时，只需搜索值大于或等于下限且小于或等于上限的位置。在尝试编写更复杂的程序之前，我建议您先尝试使用类似这样简单的方法进行实验，因为这并不会显著提高结果速度。

- Kibbee

---

我并不相信每个页面请求（假设1-5K次/秒）都进行一次数据库调用对于性能表现而言会比一个缓存中的IP排除列表加上一个更精细的IP查找算法要好。我只是不知道最佳实践是什么。 - Coolcoder

Answer 3

IPv4地址可以存储为四字节无符号整数（在C#中为uint）。IPv6地址可以是八字节无符号整数（在C#中为ulong）。在SQL中创建适当宽度的列，然后检索并将其存储在变量中。然后使用简单的整数运算来检查您想要的范围，假设这些范围实际上是连续的。

更复杂的解决方案是创建一个IPAddress类，使您可以访问更熟悉的点分十进制结构，但在底层，它会执行与此处完全相同的操作。

- JS Bangs

---

IPv6地址需要16个字节，而不是8个。 - Alnitak

Answer 4

我从未尝试过这个，所以请带着怀疑态度看待我的答案，但是我认为字典树并不是你想要的，除非你打算存储每个要阻止的IP（而不是范围或子网/掩码）。 我认为B树更适合，这种情况下，只需使用常规数据库（许多数据库都使用B树或同样好的数据结构实现）。 我会将IP的4个字节中的每个字节存储在单独的列中，以帮助通过A/B/C子网进行搜索，并且“不关心”的值等于NULL，但没有理由不能将其存储为单个32位整数列并计算数字以确定它应该落入哪个范围（在这种情况下，存储屏蔽值可能会稍微棘手一些）。

- rmeador

---

现在已经没有A/B/C子网这样的东西了。 - Alnitak

Answer 5

IPv6地址可以是一个八字节的无符号整数（在C#中为ulong）。

IPv6地址是128位（16字节），而不是建议的8位。 我现在正在处理这个IP范围的问题。

我打算尝试填充或十六进制字符串，然后进行<和>比较。

- Verdant

Answer 6

对于IPv4，通常情况下DBA会建议使用4个tinyint字段，但由于你正在处理范围，这更适合之前提供的整数存储解决方案。在这种情况下，您将为该范围存储起始IP地址和结束IP地址。然后比较就变得非常简单。

- K. Brian Kelley

Answer 7

如果您将IPv4起始地址存储在正确的数据类型中，就可以高效地完成它。Varchar（或其他字符串类型）不正确-您需要使用int。

对于IPv4，请将IP号码存储为足够大的无符号整数，然后以INET_ATON格式存储（这很容易生成；我不确定如何在C#中实现，但并不困难）。

然后，您可以通过安排数据库进行范围扫描来轻松高效地查找IP地址所属的范围。

通过使用LIMIT（或MSSQL中的SELECT TOP 1），一旦找到记录，您就可以让它停止。

SELECT TOP 1 networkidorwhatever, IPNumber, IPNumberUpperBoundOrWhateverYouCallIt 
FROM networks 
WHERE IPNumber <= IPNUMBERTOQUERY ORDER BY IPNumber DESC 

应该找到小于等于IP地址的最高编号网络号，然后进行简单的检查以确定该IP地址是否在其中。

只要在IPNumber上有常规索引，它就应该是有效的。

对于IPv6，类型不同，但原理相同。

- MarkR