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

相关文章

来自专栏文渊之博

数据仓库中如何使用索引

数据仓库的索引是个棘手的问题。如果索引太多,数据插入很快但是查询响应就会很慢。如果太多索引,数据导入就很慢并且数据存储空间更大,但是查询响应更快。数据库中索引的...

2307
来自专栏L宝宝聊IT

T-SQL应用实例

1664
来自专栏资深Tester

数据库使用经验分享

2285
来自专栏Java职业技术分享

Java纯干货——poi技术读取Excel数据到MySQL

这篇blog是介绍java中的poi技术读取Excel数据,然后保存到MySQL数据中。

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

持续近7个小时的索引扫描的查询优化分析 (r5笔记第44天)

昨天客户的DBA反映有一个数据抽取的任务持续了很长时间最后超时退出了,让我看看有什么地方可以调优一下。 找到了对应的日志,发现在一个大表抽取的时候,抽取持续了将...

4145
来自专栏枕边书

见招拆招-PostgreSQL中文全文索引效率优化

前言 上文 使用PostgreSQL进行中文全文检索 中我使用 PostgreSQL 搭建完成了一套中文全文检索系统,对数据库配置和分词都进行了优化,基本的查询...

5828
来自专栏葡萄城控件技术团队

使用正则表达式判断合法的电话号码

最近碰到了这样一个需求,提交标单的时候判断用户(国内)是否在输入框输入了正常的手机或者电话座机号码。 手机号码格式比较固定,无非是13x xxxx xxxx或者...

2939
来自专栏数据和云

你真的会用索引吗?来看看COUNT(*)到底能有多快

作者简介 ? 案例说明 一个大表的COUNT,究竟能有多快?除类似物化视图的做法,我们所能做到的极限能有多快?这不是一个真实的案例,而是根据笔者在网上发的一篇帖...

3826
来自专栏杂烩

Mycat基准测试报告 原

好久没上OSC,上面安排测下Mycat,于是申请服务器,花了两个周做出这个东西,供以借鉴。

2351
来自专栏Python爬虫实战

MySQL从零开始:数据库简介

从字面意思理解,首先数据库是一个存放东西的库,里面存的东西是数据。以下解释来自百度百科:

1412

扫码关注云+社区

领取腾讯云代金券