million+记录的ip2location查询

Question

我订阅了一个ip2location服务，它为我提供了一个以下格式的SQL表

FromIP (int), ToIP(int), lat, long, city  etc etc 

我想解析我的访问日志并对所有用户进行分类，访问日志具有IP地址和用户IP(例如)

因此，我运行如下查询

select city from ip2location where FromIP <= $ipAsInt  AND ToIP => $ipAsInt

但是这对于我必须解析的million+记录来说太耗时了。我猜这一定是ip2location的一种非常常见的用法。有没有可能优化结构？

Answer 1

我发现最简单的方法是按IP的排序顺序查找当我查找一个IP时，我也选择了最后一个IP

像这样

select city，ToIP from ip2location where FromIP <= $ipAsInt和ToIP => $ipAsInt

现在，当我必须查找下一个地址时，如果下一个ip已经小于最后一个$ToIP，我就不会查询表

对于每1000次查找，我在这里只查询了大约80次。

Answer 2

您还可以像这样编写查询

select city,ToIP from ip2location where ToIP >= $ipAsInt order by ToIP limit 1

这将比以前快得多。

您还可以为ToIP列建立索引，以提高性能。

正如ip2location所建议的，您可以使用以下命令创建FromIP和ToIP的复合主键

ALTER TABLE ip2location ADD PRIMARY KEY(FromIP,ToIP);

Answer 3

事实证明，您可以比LIMIT 1做得更好！

在ip2location website here上，它显示了使用子查询是如何将的速度提高了几个数量级

SELECT * FROM ( SELECT * FROM ip2location_db3 WHERE ip_to >= INET_ATON('8.8.8.8') LIMIT 1 ) AS tmp WHERE ip_from <= INET_ATON('8.8.8.8')

在我的MacBook测试中，不同的IP地址花费了不同的时间。性能最差的IP地址具有以下性能：

在不带LIMIT 1

• 300ms的情况下使用BETWEEN的
• 14使用带LIMIT 1
• 2ms的BETWEEN使用子选择上面的

这是7000倍的速度！