区间检索SQL性能优化方法

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

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-05-03

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏陈仁松博客

ASP.NET Core 'Microsoft.Win32.Registry' 错误修复

今天在发布Asp.net Core应用到Azure的时候出现错误InvalidOperationException: Cannot find compilati...

4878
来自专栏我和未来有约会

Silverlight第三方控件专题

这里我收集整理了目前网上silverlight第三方控件的专题,若果有所遗漏请告知我一下。 名称 简介 截图 telerik 商 RadC...

4075
来自专栏飞扬的花生

jsencrypt参数前端加密c#解密

      写程序时一般是通过form表单或者ajax方式将参数提交到服务器进行验证,如何防止提交的请求不被抓包后串改,虽然无法说绝对安全却给非法提交提高了难度...

3929
来自专栏张善友的专栏

LINQ via C# 系列文章

LINQ via C# Recently I am giving a series of talk on LINQ. the name “LINQ via C...

2675
来自专栏张善友的专栏

Miguel de Icaza 细说 Mix 07大会上的Silverlight和DLR

Mono之父Miguel de Icaza 详细报道微软Mix 07大会上的Silverlight和DLR ,上面还谈到了Mono and Silverligh...

2737
来自专栏ASP.NETCore

ASP.NET Core 整合Autofac和Castle实现自动AOP拦截

除了ASP.NETCore自带的IOC容器外,我们还可以使用其他成熟的DI框架,如Autofac,StructureMap等(笔者只用过Unity,Ninjec...

694
来自专栏Ceph对象存储方案

Luminous版本PG 分布调优

Luminous版本开始新增的balancer模块在PG分布优化方面效果非常明显,操作也非常简便,强烈推荐各位在集群上线之前进行这一操作,能够极大的提升整个集群...

3265
来自专栏闻道于事

js登录滑动验证,不滑动无法登陆

js的判断这里是根据滑块的位置进行判断,应该是用一个flag判断 <%@ page language="java" contentType="text/html...

7188
来自专栏pangguoming

Spring Boot集成JasperReports生成PDF文档

由于工作需要,要实现后端根据模板动态填充数据生成PDF文档,通过技术选型,使用Ireport5.6来设计模板,结合JasperReports5.6工具库来调用渲...

1.2K7
来自专栏张善友的专栏

Mix 10 上的asp.net mvc 2的相关Session

Beyond File | New Company: From Cheesy Sample to Social Platform Scott Hansel...

2627

扫码关注云+社区