f*****u 发帖数: 17 | 1 最近碰到问题。希望能得到tips.
在工作中,需要对大量的IP进行IP匹配,查找出其相应的地区信息。收集了一个20多万
的IP信息库
(address)含有城市名city,将IP地址(表 information)和此IP信息库的城市名进行匹
配出城市
的名字(city)。使用如下的语句进行匹配:
proc sql;
select a.*, b.city
from information as a left join address as b
on a.ip_num between b.begin_num and end_num
quit;
此语句的匹配速度一直是我比较头疼的问题,因为几百万的IP信息需要若干个小时,
而在IP信息库
表表的begin_num和end_num上建立聚集索引后速度也没有很大的提升,如下为建立聚集
索引的语句:
create index idx_address_begin_num_end_num on
address(begin_num,end_num)
应该这里的人经常碰到这样的问题,以前的帖子没有怎么找到,希望得到指教!谢谢! |
j******o 发帖数: 127 | 2 没有实际经验,不知道。但我想是否可以将你的ip库按照ip分类,这样进行ip对比时就
不用搜索整个ip库,应该可以节省时间。 |
A*******s 发帖数: 3942 | 3 不懂。先用msglevel=i看看index有没有用 |
D******n 发帖数: 2836 | |
p********a 发帖数: 5352 | 5 这个算无法优化的CATISIAN JOIN吧,所以慢,你想想几百万X20万是什么数量级。不知
道你的ADDRESS能否做成FORMAT,然后直接用FORMAT语句可能快些。 |
l*********s 发帖数: 5409 | |
l***a 发帖数: 12410 | 7 i don't think hash work here. hash does exact match
【在 D******n 的大作中提到】![](/moin_static193/solenoid/img/up.png) : hash?
|
h***x 发帖数: 586 | 8 It would be helpful if you subset address dataset first.
Study how to find location (city) according to IP. There are lots of
information in google.
【在 f*****u 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 最近碰到问题。希望能得到tips. : 在工作中,需要对大量的IP进行IP匹配,查找出其相应的地区信息。收集了一个20多万 : 的IP信息库 : (address)含有城市名city,将IP地址(表 information)和此IP信息库的城市名进行匹 : 配出城市 : 的名字(city)。使用如下的语句进行匹配: : proc sql; : select a.*, b.city : from information as a left join address as b : on a.ip_num between b.begin_num and end_num
|
b*******r 发帖数: 152 | 9 vote for this.
【在 p********a 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 这个算无法优化的CATISIAN JOIN吧,所以慢,你想想几百万X20万是什么数量级。不知 : 道你的ADDRESS能否做成FORMAT,然后直接用FORMAT语句可能快些。
|
o****o 发帖数: 8077 | 10 hash tree might help given ip address's structure
but for OP's case, BM's format solution should be a quick one
【在 l***a 的大作中提到】![](/moin_static193/solenoid/img/up.png) : i don't think hash work here. hash does exact match
|
|
|
d*******o 发帖数: 493 | 11 楼主的Query execution plan 应该是
Select <-- Nested loop <-- index scan on [information] table
<-- table lookup on [address] table
Cost 主要在table lookup上。
解决办法是把你的lookup table[address] normalize
For example:
Chicago 205.1.1.1
Chicago 205.1.1.2
….
把ip做primary key,然后做one-to-many merge
这时候也可以用proc format或hash tree
以后的Query execution plan 应该是
Select <-- Nested loop <-- index scan on [information] table
<-- index scan on [address] table |
b********p 发帖数: 875 | |
f*****u 发帖数: 17 | 13 谢谢大家的建议!网上有个帖子说
PostgreSQL数据库上, 找到了IP4r这个包,这个包可以进行快速的IP匹配,几百万的
IP基本上在几分钟之内完成。
难道是个传说! |
A*******s 发帖数: 3942 | 14 还有一个土一点的办法是multi-thread,把你的information文件割成几块然后同时进
行匹配。 |
B*********L 发帖数: 700 | |
f*****u 发帖数: 17 | 16 谢谢!这个建议最实在。
把IP变成bigint,然后加index.
a1.a2.a3.a4
=a1*256*256*256+a2*256*256+a3*256+a4
index 其实也加了,可是效率还是不行。可能我没有找到好办法。
上面提到的分类的方式不太可能实现,没有具体的规律的。这个地址库进30万条,认真
分析了,几乎没有具体的规律。 |
p*******r 发帖数: 1951 | 17 没有做过ip匹配的项目,瞎说两句。
IP不是有四段吗。如果只通过一次proc sql比较找到对应城市,后台做的Catesian
product table 太大了。那可不可以分段来比较,先对应前两段,或前三段,再用第二
个proc sql来找剩下的。这样每一段的table size会小很多。 |