微信号:OraNews

介绍:分享数据库技术、新闻与信息,尤其是和Oracle数据库相关的内容,文章内容来自原创、专栏作者投稿或读者投稿.

区间检索SQL性能优化方法

2017-05-03 07:05 刘永甫

编辑手记: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对此案例的帮助,这个案例对理解索引扫描方式很有帮助,有兴趣的朋友可以慢慢体会。


加入"云和恩墨大讲堂"微信群,参与讨论学习

搜索 盖国强(Eygle)微信号:eyygle,或者扫描下面二维码,备注:云和恩墨大讲堂,即可入群。每周与千人共享免费技术分享,与讲师在线讨论。


关注公众号,获得后续精彩分享

 
数据和云 更多文章 YH12:一篇文章读懂SCAN YH11:Oracle Flex ASM 知识库 YH4:Oracle Flex Clusters New in 12.2- 让每个PDB都拥有自己的 local undo表空间 YH3:一文全面了解Oracle RAC One Node
猜您喜欢 揭秘OpenAI:技术免费共享,Elon Musk解放人工智能的疯狂计划 “自由即奴役”的Google AMP 热点|云技术开启网络视频黄金时代 重点行业灾备建设特点和趋势-电力行业篇 【Linux】xargs命令用法详解