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

相关文章

来自专栏带你撸出一手好代码

深入浅出数据库索引

前段时间,公司一个新上线的网站出现页面响应速度缓慢的问题, 一位负责这个项目的但并不是搞技术的妹子找到我,让我想办法提升网站的访问速度 ,因为已经有很多用户来投...

3544
来自专栏资深Tester

数据库使用经验分享

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

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

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

4105
来自专栏Python爬虫实战

MySQL从零开始:数据库简介

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

1012
来自专栏Java帮帮-微信公众号-技术文章全总结

数据库三大范式【面试+工作】

设计良好结构的数据库,可以有效减小数据冗余,减少增删改中出现的问题。深入理解数据库设计的三范式,对于设计“健壮的数据库“十分有必要。数据库三范式是设计数据库 时...

3544
来自专栏数据和云

如何编写更好的SQL查询:终极指南(上)

结构化查询语言(SQL)是数据挖掘分析行业不可或缺的一项技能,总的来说,学习这个技能是比较容易的。对于SQL来说,编写查询语句只是第一步,确保查询语句高效并且适...

2866
来自专栏数据和云

【云和恩墨大讲堂】Oracle线上嘉年华第二讲

编辑手记:Oracle线上嘉年华,正在持续分享中。本次的主题是系统割接中的SQL解析问题和结合业务的SQL优化改写技巧。 1 嘉宾介绍 小鱼(邓秋爽) 云和恩...

2856
来自专栏日常学python

教你用Python爬去QQ音乐评论

去年夏天,好像于我而言,重要的事就是毕业来临,从此踏上了搬砖之路,从学校到职场,之间身份的转变,让我又多了一份责任。当然还有一段感情经历,现在回头去看,只能说且...

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

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

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

2090
来自专栏Django Scrapy

关系型数据库和非关系型数据库

一 三范式 1,所有列必须为原子化列 2,设计时需要主键列 3,所有非主键列不能依靠传递与主键列发生关系(所有列与主键列发生的都是直接关系) 生活中的...

3285

扫码关注云+社区