根据我的IP显示我的国家，mysql优化 [英] Showing my country based on my IP, mysql optimized
问题描述
我从 http://www.wipmania.com/en/下载了WIPmania的worldip表格。 base / - 该表有3个字段和大约79k行：
I've downloaded WIPmania's worldip table from http://www.wipmania.com/en/base/ -- the table has 3 fields and around 79k rows:
- startip //示例：3363110912
- endip //示例：3363112063
- country //示例：AR（阿根廷）
所以，假设我在阿根廷，我的IP地址是：200.117.248.17
So, lets suppose i'm in Argentina and my IP address is: 200.117.248.17
1）我用此函数将我的ip转换为long
1) I use this function to convert my ip to long
function ip_address_to_number($ip) {
if(!$ip) {
return false;
} else {
$ip = split('\.',$ip);
return($ip[0]*16777216 + $ip[1]*65536 + $ip[2]*256 + $ip[3]);
}
}
2）我搜索通过匹配长转换后的IP来获取正确的国家/地区代码：
2) I search for the proper country code by matching the long converted ip:
$sql = 'SELECT * FROM worldip WHERE '.ip_address_to_number($_SERVER['REMOTE_ADDR']).' BETWEEN startip AND endip';
相当于：
SELECT country FROM worldip WHERE 3363174417 BETWEEN startip AND endip （基准：显示行0 - 0（总计1，查询花费0.2109秒））
which is equivalent to: SELECT country FROM worldip WHERE 3363174417 BETWEEN startip AND endip (benchmark: Showing rows 0 - 0 (1 total, Query took 0.2109 sec))
如果另一群阿根廷人也打开网站并且他们都有这些IP地址怎么办：
What if another bunch of argentinian guys also open the website and they all have these ip addresses:
- 200.117.248.17
- 200.117.233.10
- 200.117.241.88
- 200.117.159.24
- 200.117.248.17
- 200.117.233.10
- 200.117.241.88
- 200.117.159.24
因为我正在缓存所有sql查询;而不是匹配数据库中的每个IP查询，是否更好（和正确）只是通过修改这样的函数匹配IP的2个第一部分？
Since i'm caching all the sql queries; instead of matching EACH of the ip queries in the database, would it be better (and right) just to match the 2 first sections of the ip by modifying the function like this?
function ip_address_to_number($ip) {
if(!$ip) {
return false;
} else {
$ip = split('\.',$ip);
return($ip[0]*16777216 + $ip[1]*65536);
}
}
（注意第3和第4个分割值IP已被删除）。
(notice that the 3rd and 4th splitted values of the IP have been removed).
这样就可以代替查询这4个值：
That way instead of querying these 4 values:
- 3363174417
- 3363170570
- 3363172696
- 3363151640
- 3363174417
- 3363170570
- 3363172696
- 3363151640
...所有我要查询的是：3363110912（这是200.117。 0.0 转换为长）。
...all i have to query is: 3363110912 (which is 200.117.0.0 converted to long).
这是对的吗？还有其他任何想要优化这个过程的想法吗？
Is this right? any other ideas to optimize this process?
推荐答案
你绝对必须使用WIPmania吗？如果没有，Maxmind提供开源解决方案： http://www.maxmind.com/app/geolitecountry 。它的优点是它是一个二进制文件，并且有一个PHP扩展（你必须编译它并安装它）。在几个项目中使用它，查找速度非常快。你可以在这里获得PCL扩展：
http://pecl.php.net/package/ geoip
Do you absolutely have to use WIPmania? if not, Maxmind offers an open source solution: http://www.maxmind.com/app/geolitecountry. The advantage is that it's a binary file, and there's a PHP extension (you'd have to compile it ans install it). Used it on a couple of projects, the lookups are blazing fast. You can get the PCL extension here: http://pecl.php.net/package/geoip
这篇关于根据我的IP显示我的国家，mysql优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
