限制 SQL 表中包含的行数
这是一个表设计问题。我有一个存储 IP 地址的表。表中的数据查询量非常大。 IP 可以有不同的标志，例如“未阻止”、“暂时阻止”和“永久阻止”。 95% - 99% 的 IP 地址没有任何类型的阻止。
有没有办法限制表中的行数而不排除任何数据 - 同时将所有数据保留在同一个表中？
有人向我提出的建议是在其中一个字段中使用逗号分隔值（我假设使用未阻止的 IP 地址）。然而，我对这项技术一点也不熟悉。
This is a table design issue. I have a table that stores IP addresses. The data in the table is queried very heavily. The IPs can have different flags such as "unblocked", "temporarily blocked" and "permanently blocked". 95% - 99% of the IP addresses do not have any type of block on them.
Is there a way to limit the # of rows in the table without excluding any of the data - while keeping all of the data in the same table?
A suggestion that was made to me was to utilize comma delimited values in one of the fields (I presume with unblocked IP addresses). I am not at all familiar with this technique, however.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
IP 地址是字符串 URL，例如 http://www.Amazon.com，还是实际的点四边形符号？如果是后者，并且您这样做是为了提高性能，请考虑存储 IP 地址的 32 位整数表示，而不是点分四串表示。 （你使用的是IP4地址还是IP6地址？
xxx.xxx.xxx.xxx的字符串表示需要15个字节，一个32位整数只需要4个字节。为你的statusFlag添加一个字节，你就有一个只有5个字节宽的表其性能应该足以容纳所有可能的 IP4 地址（40 亿个）。
Are the IP Addresses string URLs, like http://www.Amazon.com, or are they actual dotted quad notation ? if they are the latter, and if you are doing this to try to improve performance, then consider storing the 32 bit integer represetation of the IP address instead of the dotted quad string representation. (Are you using IP4 addresses or IP6 addresses?
The string represetnation of xxx.xxx.xxx.xxx takes 15 bytes, a 32 bit integer takes only 4. Add a byte for your statusFlag, and you have a table that's only 5 bytes wide. This should be perfomant enough to have every possible IP4 address (4 Billion of them) in it.
我认为你应该有一个包含唯一 IP 地址列表的表。您还应该有另一项用于这些 IP 地址上的交易，例如（“解除阻止”、“暂时阻止”和“永久阻止”）。
I think you should have a table that has a list of unique ip addresses. You should also have another one for the transaction on those ip addresses like ("unblocked", "temporarily blocked" and "permanently blocked").
这张桌子上真的有性能问题吗？
这里的关键是索引；假设您基于 IP 或标志（或两者）进行索引，您应该能够快速查询所需的任何行，无论有多少行。
如果您关心性能但需要保留数据，则始终可以有两个表 - 一张用于带有标志的 IP，一张用于未标记的行。您需要一组过程/触发器来在标志状态更改时插入/删除行。
不要使用逗号分隔的东西。它使得查询单个 IP 变得更加费力（更不用说必须处理处理标志更改的相同问题......）
Do you actually have a performance problem on this table?
The key here is indexing; assuming you are indexing based on IP or on flags (or both) you should be able to quickly query any rows you need regardless of how many rows there are.
If you are concerned about performance but need to keep the data, you could always have two tables -- one for IPs with flags, one for unflagged rows. You'd need a set of procs/triggers for inserting/deleting rows as flag states change.
Don't use the comma-separate thing. It makes querying for individual IPs much more laborious (not to mention having to deal with the same problem of dealing with changes in flags...)
您的表中未提及的地址与未屏蔽的地址有什么区别？如果绝大多数地址都被解除阻止，那么也许您应该通过不在表中来表示这一点？
否则，如果您要存储 IPv4 地址（不是域名），请采纳 Charles Bretanas 的建议并将地址存储为原始整数。如果是这样，您还可以为网络掩码添加另一个 32 位整数，这样您就可以存储整个范围（即要阻止每个地址 10.0.0.0 - 10.255.255.255，您可以将地址 10.0.0.0 存储为一个整数，网络掩码 255.0.0.0作为另一个整数）。这可以极大地减少行数（取决于您的阻塞行为），但它也使得对特定地址的有效查询变得更加复杂。
相同的基本技术也可以应用于 IPv6 地址，只是它们更长。
What is the difference between an address that is not mentioned in your table and an unblocked address? If a large majority of addresses are unblocked, then maybe you should represent that by being absent from the table?
Otherwise, if you are storing IPv4 addresses (not domain names), take Charles Bretanas suggestion and store the adresses as raw integers. If so, you can also add another 32-bit integer for a netmask, so you can store entire ranges (i.e. to block every address 10.0.0.0 - 10.255.255.255, you store address 10.0.0.0 as one integer and netmask 255.0.0.0 as another integer). This can reduce the number of rows extremely much (depending on your blocking behaviour), but it also makes efficient querying for a specific address a bit more complicated.
The same basic techiques can be applied for IPv6 address also, except that they are longer.