2-严格区间检索最佳处理方法

这是《老虎刘谈Oracle性能优化》的第二篇文章,这篇文章给出的方法有点复杂,需要用函数来实现,后来在第49篇做了更新,这里做个整合。

原文+更新:

2013年,有朋友让我帮忙优化一个SQL:根据IP地址查询对应的国家/地区(根据号码查询归属地也属类似业务),这个就属于严格区间检索。

所谓严格区间,就是区间不重叠,给定一个值最多只匹配一个区间。

业务SQL代码如下:

Select country_code

From COUNTRY_IP_RANGE IP

WHERE

IP.Start_Ip1 <= ip_to_number1(:ip)

AND

IP.End_Ip1   >= ip_to_number1(:ip);

说明:

其中ip_to_number1是一个将ip地址转换成整数的函数。COUNTRY_IP_RANGE表记录数大概有12万条。存在一个start_ip1和end_ip1字段上的联合索引。SQL每次最多只返回一条记录。

当前的性能问题:

查询一个小IP(如:1.0.0.1)时,只需要几个buffer gets;查询一个较大的IP时(如:222.252.0.123),buffer gets要400多。

传统优化方法:

第一步、根据业务规则,增加一个rownum=1的谓词条件,SQL变成:

Select country_code

From COUNTRY_IP_RANGE IP

WHERE

IP.Start_Ip1 <= ip_to_number1(:ip)

AND

IP.End_Ip1   >= ip_to_number1(:ip)

and ROWNUM=1;

加了这个条件后,性能只有一点点的改善,每次的buffer gets会少一个

第二步、根据业务特点及索引默认扫描方式为升序扫描,改变索引扫描方式,使用索引降序扫描,用index_rs_desc的hint实现:

select /*+ INDEX_RS_DESC(ip  IDX_IP1) */

country_code

from COUNTRY_IP_RANGE IP

WHERE

IP.Start_Ip1 <= ip_to_number1(:ip)

AND

IP.End_Ip1   >= ip_to_number1(:ip)

And rownum=1;

其中IDX_IP1是start_ip1,end_ip1两字段联合索引。

做了这两步后,每次的buffer gets就只有3个了。

如果不用hint,可以通过改变联合索引的先后顺序也能实现相同优化效果,即联合索引的顺序是(end_ip1,start_ip1)

当时,优化到这一步就已经解决了朋友的大问题。

最近在整理这个案例的时候,发现还有个问题没有解决:在给定IP地址找不到对应区间的时候,仍需要大量的buffer gets。有外国优化大师给出的解决方案是通过plsql代码实现,需要创建一个函数。这个方案比较复杂,改动也比较大。

我给出了一个直接通过SQL就能完美解决上面问题的sql写法,代码如下:

改写SQL为:

SELECT

case when start_ip1<= :B1 then COUNTRY_CODE

else ‘no_match’ end

FROM

(SELECT COUNTRY_CODE, start_ip1,end_ip1

FROM COUNTRY_IP_RANGE

WHERE end_ip1 >= :B1 order by end_ip1

) WHERE ROWNUM = 1;

这个改写只需要配合 end_ip1 单字段索引即可。 这样,无论查询的IP地址是大是小,是否找得到对应区间,都只需要3个buffer,是最完美的解决方案。

发表评论

电子邮件地址不会被公开。 必填项已用*标注