编辑手记：RWP（Real World Performance）团队是全球最优秀的性能优化团队，他们的目标在于系统性能千倍的提升。感谢刘永甫专家的授权，他从RWP团队转入售后，多年专注于性能优化。我们将会拣选他在职业生涯中一些经典的优化案例跟大家分享。
作者简介
刘永甫
性能优化专家，IT行业18年有余，大部分时间从事oracle数据库相关工作。2013年入职 oracle 深圳研发中心RWP部门，专门从事数据库优化相关工作。2014年底转入售后部门SSC，专注于oracle数据库性能优化，主要为金融、通信等行业的大型数据库做性能分析与优化。个人微信公众号：sql_tigerliu.
几年前，有朋友让我帮忙优化一个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多。
优化方法
1、首先根据业务规则，增加一个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会少一个。
2、根据业务特点及索引默认扫描方式为升序扫描，改变索引扫描方式，使用索引降序扫描，用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。但是光靠SQL本身已经无能为力。
最终的优化方法，通过plsql解决，创建下面的函数：
CREATE OR REPLACE function
get_ip_area(v_ip varchar2 ) return varchar2
IS
v_start_ip1 NUMBER;
v_COUNTRY_CODE varchar2(30);
BEGIN
select COUNTRY_CODE, start_ip1
INTO v_COUNTRY_CODE, v_start_ip1
from
(SELECT COUNTRY_CODE, start_ip1
FROM COUNTRY_IP_RANGE
WHERE
end_ip1 >= ip_to_number1 (v_ip)
order by end_ip1 )
where ROWNUM = 1;
if v_start_ip1 <= ip_to_number1(v_ip)
then
return v_COUNTRY_CODE;
else return 'N/A';
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'N/A';
END get_ip_area;
/
使用方法：
select get_ip_area('78.138.30.176') from dual;
使用了函数后，性能问题就彻底解决了！
这个优化案例在最近一期的SSC 技术通讯（优化专刊）中有刊出，感谢RWP同事Cary Dong对此案例的帮助，这个案例对理解索引扫描方式很有帮助，有兴趣的朋友可以慢慢体会。
区间检索SQL性能优化方法
https://mp.weixin.qq.com/s/xO4eEmM2xt9YNRtwfvsDgg
