区间检索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 条评论
登录 后参与评论

相关文章

来自专栏SAP最佳业务实践

想学FM系列(21)-SAP FM模块:派生规则推导策略(4)-派生规则推导步骤-功能模块

4.1.6 功能模块 在推导步骤过程中调用一个功能函数,将源数据字段值传入功能函数的参数值,再把功能函数执行后的结果数据值传出到目标字段。 在选择步骤类型为功能...

47610
来自专栏desperate633

第一课 了解sql准备样例表sql初探

首先阅读这本书,必须先了解这本书里sql语句所操作的数据库表。 也就是书附录中的样例表,深入理解这个样例表不仅可以帮助我们设计好的数据库结构,也能打下基础,更...

712
来自专栏liuchengxu

用 Go 构建一个区块链 -- Part 2: 工作量证明

翻译的系列文章我已经放到了 GitHub 上:blockchain-tutorial,后续如有更新都会在 GitHub 上,可能就不在这里同步了。如果想直接运行...

501
来自专栏斑斓

利用聚合概念指导MongoDB的Schema设计

在我们的项目中,为了能够保存分析报表以及用户设置的报表查询条件,我们将这些信息视为报表元数据存储在MongoDB中。要存储的元数据包括:

612
来自专栏数据科学与人工智能

【SQL技能】浅谈数据分析中的SQL

很久没写东西了,正好群里有童鞋最近要换工作,提到有关数据库方面的问题,个人认为,做数据分析的并没有必要把数据库开发之类的弄懂,你只需要从相应的数据库中调用你需...

2545
来自专栏性能与架构

将 Redis 作为图数据库

1. 简介 Redis 在 4.0 中正式支持了Module模块系统,使其可以进行丰富的扩展 图数据库的应用越来越广泛,RedisGraph 就是一个 Redi...

3336
来自专栏数据和云

郑保卫 - 索引优化策略及实战

本文中将要介绍的索引战略方案是以尽可能少的索引来满足尽可能多的数据读取类型的索引构建方法。这个策略方案要求在构建索引时,尽可能多地搜集当前正在使用的未来将要出...

2775
来自专栏喔家ArchiSelf

IOT语义交互性之词汇定义

只是限于能力有限,却始终没有什么建树。如今,区块链的出现可能会解决可信的问题。接下来闲暇的时候,需要好好学习一下。安全对于任何互联网应用都是及其重要。无所不在指...

834
来自专栏Hadoop数据仓库

HAWQ取代传统数仓实践(八)——维度表技术之角色扮演维度

        单个物理维度可以被事实表多次引用,每个引用连接逻辑上存在差异的角色维度。例如,事实表可以有多个日期,每个日期通过外键引用不同的日期维度,原则上每...

19810
来自专栏杨建荣的学习笔记

自动化平台开发小结(四)

今天对备份恢复和元数据的功能点进行了改进,突然发现需要做的事情远比想象的要多。 技术方面,目前Django的框架使用开始有一些需求的瓶颈了,因为有些需求从业务的...

3145

扫描关注云+社区