对范围进行快速 SQL 查询
我有一个 IP 范围表以及拥有该 IP 范围的各自城市和国家/地区。 IP 表示为 bigint（请参阅下面的公式将 IP 转换为 bigint），我正在执行一个选择来拉回正在使用网站的城市/国家。
将 IP 转换为数字的方程式是
Bigint = (octet1 * (2^24)) + (octet2 * (2^16)) + (octet3 * (2^8)) + Octet 4
我的选择如下所示。无论哪种情况，当我有很多 IP 地址 (200) 需要查找时，where 子句可能会变得很长，并且需要几分钟才能返回结果。我确实将 startipnum 和 endipnum 索引为聚集索引。该表有 625 万行数据（IP 范围和其中的国家/地区）。有没有更好的方法返回数据？
SELECT [startIpNum] ,[endIpNum],[country],[region],[city],[postal_code]
,[latitude],[longitude],[metroCode],[areacode]
FROM [GeoLocationView]
WHERE
(30072067 between startipNum and endipnum)
OR
(30072069 between startipNum and endipnum)
或者
SELECT [startIpNum] ,[endIpNum],[country],[region],[city],[postal_code]
,[latitude],[longitude],[metroCode],[areacode]
FROM [GeoLocationView]
WHERE
(startipNum <= 30072067 and 30072067 <= endipnum) or
(startipNum <= 30072069 and 30072069 <= endipnum)
I have a table of IP Ranges and their respective city and country that owns that IP Range. The IPs are represented as bigint (See equation below for turning IP into bigint) and I am doing a select to pull back what cities/countries that are using a website.
Equation for converting IP to number is
Bigint = (octet1 * (2^24)) + (octet2 * (2^16)) + (octet3 * (2^8)) + Octet 4
My selects are looking like the following below. In either case, when I have a lot of IP addresses (200) to look up, the where clause can get very long and the results take minutes to return. I do have the startipnum and endipnum indexed as a clustered index. The table has 6.25 million rows of data (IP ranges and countries in it). Is there a better way to return the data?
SELECT [startIpNum] ,[endIpNum],[country],[region],[city],[postal_code]
,[latitude],[longitude],[metroCode],[areacode]
FROM [GeoLocationView]
WHERE
(30072067 between startipNum and endipnum)
OR
(30072069 between startipNum and endipnum)
Or
SELECT [startIpNum] ,[endIpNum],[country],[region],[city],[postal_code]
,[latitude],[longitude],[metroCode],[areacode]
FROM [GeoLocationView]
WHERE
(startipNum <= 30072067 and 30072067 <= endipnum) or
(startipNum <= 30072069 and 30072069 <= endipnum)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
尝试使用带有 IP 列表的表变量（或临时表）可能会很有趣，并加入到查找表中
Might be interesting to try using a table variable (or temp table) with the list of IPs, joined to your lookup table
当您必须匹配大量值时，OR 子句通常比临时表上的 JOIN 慢。
When you have to match a lot of values, an OR clause is often slower than a JOIN on a temporary table.
您的集群会有所帮助。
我会选择
x > ？ order by xwhere 子句可以是来自单独表的子查询，如下所示：
但是您必须维护此 key_table ...
当它尝试优化您的查询时 - 表查找比尾部更好地为 RDMS 引擎服务钥匙数
Your cluster will help.
I would select
x > ? order by xthe where clause could be a subselect from a separate table like this:
but you have to maintain this key_table ...
When it tries to optimize your query - table lookups serve the RDMS engine better, than a tail of keys