MySQL:如何进行更快的IP范围查询?GeoIP [英] MySQL: How to make a faster IP range query? GeoIP

问题描述

我有一个PHP/MySQL geo-ip脚本，该脚本使用一个用户的IP地址，将其转换为长整数，然后在IP范围表中搜索该用户IP所在的单个地理位置ID:

I have a PHP/MySQL geo-ip script which takes a user's IP address, converts it to long integer and searches through an IP range table for a single geographical location id of where the user's IP is located in:

$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秒钟才能完成，但是我希望有一种方法可以改进它，因为它是我网站上最慢的查询.

The "geoip" table contains 2.5M rows. Both the "range_begin" and "range_end" column are Unique Indexes. IP ranges don't seem to overlap. Sometimes this query takes about 1 second to complete, but I was hoping there was a way to improve it as it is the slowest query on my site.

谢谢

我将查询更改为:

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

我现在有一个唯一的综合索引(range_begin，range_end).我使用了"EXPLAIN"函数，看起来仍然可以搜索120万行:

I now have a UNIQUE Composite Index (range_begin, range_end). I used the "EXPLAIN" function and it looks like it still searches through 1.2M rows:

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

推荐答案

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

It's a very useful exercise to spend some time thinking about why a conventional index is useless in a scenario like this. Indeed if you can get the query to use the index you will find it will probably be slower than running a full table scan.

解释为什么要占用比此处可用空间更多的空间.有一个解决方案-将ipaddress数据库视为一维空间并使用空间索引.但是mysql空间索引只能在2维空间中工作-因此，您需要按照

Explaining why would take more space than available here. There is a solution - which is to treat the ipaddress database as a one dimensional space and use spatial indexing. But mysql spatial indexes only work in 2 dimensions - so you need to map the coordinate into a 2 dimensional space as described here

请注意，大于/极限方法(尽管比空间索引快)在您处理嵌套子网时会变得凌乱.

Note that the greater than / limit method, although faster than the spatial index becomes messy when you start dealing with nested sub-nets.

这篇关于MySQL:如何进行更快的IP范围查询?GeoIP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！