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 的大作中提到】
: 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 的大作中提到】
: 最近碰到问题。希望能得到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 的大作中提到】
: 这个算无法优化的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 的大作中提到】
: 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会小很多。 |
