区间检索SQL优化--续

在2017-07-16的文章 区间检索SQL性能优化方法 中，我使用了创建function的方式，来解决匹配不到区间时的buffer gets过多的性能问题。最近在解决一个客户的多表关联区间检索问题时，又琢磨了一下，发现还是可以直接使用SQL，替代原来的function，SQL还真是博大精深啊。

客户现场类似COUNTRY_IP_RANGE表（保存区间信息）的记录数是5000万，有75万记录要做区间匹配，有一部分数据是匹配不到区间的（随机取20条有10条无法匹配），用普通写法，匹配不到区间时，查一条记录大概要耗时1秒多，这个速度是无法接受的；而优化写法不管是否能够匹配到区间，都只要0.29毫秒（处理完75万记录并将结果插入到表，一共3分35秒）。

普通写法（需要存在end_ip1 + start_ip1 联合索引）：

SELECT COUNTRY_CODE

FROM COUNTRY_IP_RANGE

WHERE end_ip1 >:B1 and start_ip1 <= :B1 and rownum=1;

优化写法，原function的替代SQL代码(需要存在end_ip1 单字段上的索引)：

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;

这个写法要比原来function的性能要好那么一丁点，主要优点是简洁了，不用写function那么麻烦。

注：

变量如果超出COUNTRY_IP_RANGE表end_ip1的最大范围，sql会返回"no rows selected"；最大范围内匹配不到区间，则返回"no_match"（这与普通写法不一样）,匹配到区间就显示对应的COUNTRY_CODE字段值。

两种方法性能对比测试：

如果你要自己动手验证一下，可以在qq群16778072下载COUNTRY_IP_RANGE表的dmp文件:COUNTRY_IP_RANGE.dmp.gz：

该表12万多记录，用exp导出并压缩（压缩后2M大小）。

导入方法：

1、unzip COUNTRY_IP_RANGE.dmp.gz

2、imp xxx/xxx file=COUNTRY_IP_RANGE.dmp full=y

如果能够匹配到区间，普通写法与优化写法消耗的buffer gets是一样的，我们这里主要比较匹配不到区间时的性能情况。

普通写法：

--大的ip， 查不到对应区间：

var B1 number;

exec :B1 := 3716446208;

SELECT COUNTRY_CODE

FROM COUNTRY_IP_RANGE

WHERE end_ip1 >:B1 and start_ip1 <= :B1 and rownum=1;

--2 consistent gets （查大的IP，如果查不到， buffer gets数不多）

--小的ip ，查不到对应区间

var B1 number;

exec :B1 := 16777472 ;

SELECT COUNTRY_CODE

FROM COUNTRY_IP_RANGE

WHERE end_ip1 >= :B1 and start_ip1 <= :B1 and rownum=1;

--408 consistent gets

这个buffer gets数比较大了，如果创建的是end_ip1单字段上的索引（优化写法只需要单字段），buffer gets数更是要暴增很多倍。

新的优化写法：

--大的ip ，查不到对应区间：

var B1 number;

exec :B1 := 3716446208;

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;

--3 consistent gets

--小的ip ，查不到对应区间

var B1 number;

exec :B1 := 16777472 ;

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;

--3 consistent gets

测试结果：

对于优化写法，不论大的IP还是小的IP，匹配不到区间都是3个buffer gets。而对于普通写法，匹配不到区间时，buffer gets会随着查询值(:B1)与最大值（max end_ip1）的远近有很大变化，查询值越小效率越差。

问：

优化写法中，有了end_ip1字段上的索引，而且索引是升序扫描的，为什么还要加order by？

答：

如果索引失效，在没有order by的情况下，全表扫描会随机选一条的end_ip1 >= :B1的记录，造成结果错误。索引失效时sql可以慢，但是不可以出错。