MySQL:如何进行更快的IP范围查询？GeoIP

Question

我有一个PHP/MySQL geo脚本，它接受用户的IP地址，将其转换为长整数，并在IP范围表中搜索用户IP所在位置的单一地理位置id：

$iplong = ip2long($_SERVER['REMOTE_ADDR']);

SELECT id FROM geoip
 WHERE ".$iplong." BETWEEN range_begin AND range_end
 ORDER BY range_begin DESC LIMIT 1

"geoip“表包含250万行。"range_begin“和"range_end”列都是唯一的索引。IP范围似乎不重叠。有时这个查询大约需要1秒才能完成，但我希望有一种方法来改进它，因为它是我站点上最慢的查询。

谢谢

编辑:我将查询更改为：

SELECT * FROM geoip
 WHERE range_begin <= ".$iplong." AND range_end >= ".$iplong."
 ORDER BY range_begin DESC LIMIT 1

我现在有一个独特的综合指数(range_begin，range_end)。我使用了“解释”函数，它看起来仍然在120万行中搜索：

id: 1
select_type: Simple
table: geoip
type: range
possible_keys: range_begin
key: range_begin
key_len: 8
ref: NULL
rows: 1282026
Extra: Using Index Condition

Answer 1

花一些时间思考为什么常规索引在这种情况下是无用的，这是一个非常有用的练习。实际上，如果您能够让查询使用索引，您会发现它可能比运行完整的表扫描要慢。

解释为什么会占用比这里更多的空间。有一种解决方案--将ipaddress数据库看作一个一维空间，并使用空间索引。但是mysql空间索引只在二维中工作所以您需要像描述的这里那样将坐标映射到一个二维空间中。

请注意，虽然大于/极限方法比空间索引更快，但在开始处理嵌套子网时会变得混乱。

Answer 2

我正在处理一个类似的问题，在这个问题中，我必须搜索一个拥有大约400万IP范围的数据库，并找到一个很好的解决方案，将扫描的行数从400万减少到大约5行(取决于IP)：

这个SQL语句是：

SELECT id FROM geoip WHERE $iplong BETWEEN range_begin AND range_end 

转化为：

SELECT id FROM geoip WHERE range_begin <= $iplong AND range_end >= $iplong 

问题是，MySQL用“range_begin <= $iplong”检索所有行，然后需要扫描“range_end >= $iplong”。这个第一个和条件(range_begin <= $iplong)检索了大约200万行，所有这些都需要检查是否range_end匹配。

但是，可以通过添加一个和条件来显着地简化这一点：

SELECT id FROM geoip WHERE range_begin <= $iplong AND range_begin >= $iplong-65535 AND range_end >= $iplong 

声明

range_begin <= $iplong AND range_begin >= $iplong-65535

仅检索range_begin在$iplong-65535和$iplong之间的条目。在我的例子中，这减少了从4个Mio检索的行数。脚本运行时从多分钟降到了几秒钟。

备注65535：对于我的表，range_begin和range_end之间的最大距离，即(Range_End_Begin)对我的所有行都是<= 65535。如果IP范围较大，则必须增加65535，如果IP范围较小，则可以减少此常数。如果这个常量太大(例如40亿)，您将不会节省任何查询时间。

对于这个查询，您只需要在range_begin上建立一个索引。

Answer 3

在我看数据之前，这是我逃避的更简单的方法。

首先，跑

SELECT * FROM Ip2location WHERE ip_from <= $IPAddress ORDER BY ip_from DESC LIMIT 1

这将返回最接近的IP，该IP等于或小于您正在搜索的值，因为您返回的是DB中最高的单个值。

接下来，在返回整行时，只需确保ip_to大于或等于IP，就可以确保IP在此范围内。

如果IP不属于该范围(如果ip_to较少)，这意味着该IP没有记录。

简单而快速执行！