通过IP定位区域的SQL优化思路(r10笔记第10天)

昨天中午吃饭的时候,突然手机收到一条报警信息,提示数据库的负载突然提高了。对于一个高配,稳定,核心的系统来说,出现这么一个报警会立刻引起关注。 连接到环境之后,发现在问题发生时间段快照中资源消耗较大的SQL情况如下:

可以看到有两个语句的执行频率还是比较高,整体占用了绝大多数的CPU资源。 对应的前两个SQL语句如下: SQL_FULLTEXT ---------------------------------------------------------------------------------------------------- SELECT IP_ID,COUNTRY,PROVINCE,CAPITAL FROM SWD_IP2COUNTY WHERE STRIPTOINT(:B1 ) BETWEEN IP2NUM_LEFT_LINE AND IP2NUM_RIGHT_LINE 看起来语句很简单,第一感觉是不是全表扫描导致的,也就潜意识中感觉是不是没有相关的索引。 但是查看表结构信息,发现在IP2NUM_LEFT_LINE和IP2NUM_RIGHT_LINE有两个索引,索引最初的猜想不成立。 然后开始看这个语句的细节,发现这个语句还真是有点意思,之前还没见过这种写法。 首先是里面看起来是是存在一个函数STRIPTOINT处理绑定变量,然后有一个过滤条件between xxx and xxx,两端的可是字段,不是具体的数值。 如果函数STRIPTOINT处理某个字段的数值,那么就是一种使用不规范的情况,肯定会走索引,但是这里的情况又有些特殊。 看到这个逻辑,我是有些懵了,咱也不能随便猜。看看表结构。 COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH NULLABLE ---------- ------------------------------ --------------- ----------- ---------- 1 IP_ID NUMBER(10,0) 22 N 2 IP_LEFT_LINE VARCHAR2(15) 15 N 3 IP_RIGHT_LINE VARCHAR2(15) 15 N 4 IP2NUM_LEFT_LINE NUMBER(10,0) 22 N 5 IP2NUM_RIGHT_LINE NUMBER(10,0) 22 N 6 COUNTRY VARCHAR2(20) 20 Y 7 PROVINCE VARCHAR2(20) 20 Y 8 CAPITAL VARCHAR2(20) 20 Y 可以看出是和IP相关的,而且里面存在一些地理区域的信息。 函数STRIPTOINT是对传入的IP做过滤,比如传入一个IP 10.1.3.5 ,函数会把这个IP按照".”进行分隔,从第一部分开始到第四部分,会逐步拉开数据的梯度。 比如传入一个IP,就会转换为一个较大的数值,尽可能保证不会重复。 SQL> select STRIPTOINT('124.115.229.74') from dual; STRIPTOINT('124.115.229.74') ---------------------------- 208797012 所以这个SQL语句就类似下面的形式。 SELECT IP_ID,COUNTRY,PROVINCE,CAPITAL FROM tlbb.SWD_IP2COUNTY WHERE 208797012 BETWEEN IP2NUM_LEFT_LINE AND IP2NUM_RIGHT_LINE 这样理解起来还真是有点费劲,我们继续介绍一些相关的业务情况,这些也是我根据数据猜出来的,后面和开发的同学聊,和我想的是一样的。 比如数据是这样的形式:

 IP_LEFT_LINE    IP_RIGHT_LINE   IP2NUM_LEFT_LINE IP2NUM_RIGHT_LINE COUNTRY    PROVINCE   CAPITAL
----- --------------- --------------- ---------------- ----------------- ---------- ---------- -------
  5.34.184.0      5.34.191.255            86161408          86163455  中国       北京       北京  
  5.34.192.0      5.34.223.255            86163456          86171647  中国             河北       石家庄

比如IP 5.34.184.0~5.34.191.255 这个区间代表的是北京地区,5.34.192.0 ~ 5.34.223.255 这个区间代表的是河北石家庄。那么传入一个IP就开始映射得到一个数值,通过这个范围区间来找到对应的地区。 这样一个语句,明白了需求,好像还是有点道理。 来看看awr的SQL报告怎么说吧。 Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 2397 01-Sep-16 13:00:52 567 1.9 End Snap: 2398 01-Sep-16 13:30:54 566 1.9 Elapsed: 30.03 (mins) DB Time: 138.02 (mins) 可以看到在短期内,数据库的负载还是比较高。 而SQL的执行统计信息如下:

如果想得到更多的详细的信息,使用诊断事件也是不错的选择,这也是11g的一个功能。 比如对某一条SQL开启sql_trace,可以使用如下的方式。 开启: alter system set events 'sql_trace [sql: 63f970ck8r3kc] level 12'; 关闭: alter system set events 'sql_trace [sql: 63f970ck8r3kc] off'; 而如果有大量的会话频繁调用,那还是不建议使用的,会生成大量的trace文件,目前的情况就是如此,需要谨慎使用。 而我们怎么去分析这个SQL呢,来做一个10053事件吧。 ALTER SESSION SET EVENTS='10053 trace name context forever, level 1'; SELECT IP_ID,COUNTRY,PROVINCE,CAPITAL FROM test.SWD_IP2COUNTY WHERE 208797012 BETWEEN IP2NUM_LEFT_LINE AND IP2NUM_RIGHT_LINE; ALTER SESSION SET EVENTS '10053 trace name context off'; 这个时候trace的信息可以看到,内部做了查询转换,会把原有的语句转换为下面的形式: Final query after transformations:******* UNPARSED QUERY IS ******* SELECT "SWD_IP2COUNTY"."IP_ID" "IP_ID","SWD_IP2COUNTY"."COUNTRY" "COUNTRY","SWD_IP2COUNTY"."PROVINCE" "PROVINCE","SWD_IP2C OUNTY"."CAPITAL" "CAPITAL" FROM "TEST"."SWD_IP2COUNTY" "SWD_IP2COUNTY" WHERE "SWD_IP2COUNTY"."IP2NUM_LEFT_LINE"<=208797012 AND "SWD_IP2COUNTY"."IP2NUM_RIGHT_LINE">=208797012 对于执行路径和表信息的统计信息如下: Access path analysis for SWD_IP2COUNTY *************************************** SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for SWD_IP2COUNTY[SWD_IP2COUNTY] Column (#4): NewDensity:0.000006, OldDensity:0.000006 BktCnt:254, PopBktCnt:0, PopValCnt:0, NDV:163088 Column (#4): IP2NUM_LEFT_LINE( AvgLen: 7 NDV: 163088 Nulls: 0 Density: 0.000006 Min: 16910592 Max: 4261412864 Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255 Column (#5): NewDensity:0.000006, OldDensity:0.000006 BktCnt:254, PopBktCnt:0, PopValCnt:0, NDV:163246 Column (#5): IP2NUM_RIGHT_LINE( AvgLen: 7 NDV: 163246 Nulls: 0 Density: 0.000006 Min: 16912383 Max: 4278190079 Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255 Table: SWD_IP2COUNTY Alias: SWD_IP2COUNTY Card: Original: 163246.000000 Rounded: 1114 Computed: 1114.40 Non Adjusted: 1114.40 Access Path: TableScan Cost: 444.56 Resp: 444.56 Degree: 0 Cost_io: 443.00 Cost_cpu: 57439773 Resp_io: 443.00 Resp_cpu: 57439773 Access Path: index (RangeScan) Index: IND_IP2NUM_LEFT_LINE resc_io: 15.00 resc_cpu: 713197 ix_sel: 0.006874 ix_sel_with_filters: 0.006874 Cost: 15.02 Resp: 15.02 Degree: 1 Access Path: index (RangeScan) Index: IND_IP2NUM_RIGHT_LINE resc_io: 1902.00 resc_cpu: 101091934 ix_sel: 0.993126 ix_sel_with_filters: 0.993126 Cost: 1904.74 Resp: 1904.74 Degree: 1 ****** finished trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: IND_IP2NUM_LEFT_LINE Cost: 15.02 Degree: 1 Resp: 15.02 Card: 1114.40 Bytes: 0 最大的发现是就是这个语句的逻辑结合自己的分析终于清晰了,但是怎么优化还没想好。为什么性能如此差。 我用查询转换后的语句,直接在客户端执行,查看执行计划。 SQL> SELECT "SWD_IP2COUNTY"."IP_ID" "IP_ID","SWD_IP2COUNTY"."COUNTRY" "COUNTRY","SWD_IP2COUNTY"."PROVINCE" "PROVINCE","SWD_IP2COUNTY"."CAPITAL" "CAPITAL" FROM "TEST"."SWD_IP2COUNTY" "SWD_IP2COUNTY" WHERE "SWD_IP2COUNTY"."IP2NUM_LEFT_LINE"<=3030728598 AND "SWD_IP2COUNTY"."IP2NUM_RIGHT_LINE">=3030728598 ; 发现这个时候CBO是分析得出需要走全表扫描的。

Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1530 consistent gets 。。。 1 rows processed 这样就彻底明白了,这里走索引扫描的选择度本身就很低,其实效果和走全表差距也不大,这样一来也就容易理解上面的统计信息中大量的buffer gets的原因了。 这个语句如何优化呢,问题还是摆在我们面前,根据和开发同学的沟通,对这个需求有了更多的一些了解,而且根据IP来确定范围,是不会出现多个区域的情况,也就是说,这个结果集要不是1条,要么是没有映射到的。 所以语句可以简单优化一下,只需要添加rownum=1即可。 SELECT "SWD_IP2COUNTY"."IP_ID" "IP_ID","SWD_IP2COUNTY"."COUNTRY" "COUNTRY","SWD_IP2COUNTY"."PROVINCE" "PROVINCE","SWD_IP2COUNTY"."CAPITAL" "CAPITAL" FROM "TEST"."SWD_IP2COUNTY" "SWD_IP2COUNTY" WHERE "SWD_IP2COUNTY"."IP2NUM_LEFT_LINE"<=3030728598 AND "SWD_IP2COUNTY"."IP2NUM_RIGHT_LINE">=3030728598 and rownum=1 ; 这个时候走了索引,而且一致性读大大降低。

Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 。。。 1 rows processed 这个语句在实际环境中的改进效果如何呢,我抓取了对比的数据,发现性能提升了50%。

执行计划如下:

这种优化方式还是值得借鉴的。而且在这个基础上可以考虑对索引进行优化,也就是建立符合索引,根据数值的分布情况(存在》=,《=的数值区间),可以考虑对索引进行排序 比如:(IP2NUM_LEFT_LINE desc , IP2NUM_RIGHT_LINE asc)

这一点上还得感谢兔子的建议,看了下老白总结的案例就是这样的改进,都是套路,哈哈。

对于性能的比对情况如下,DB time有了很多改进。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2016-09-02

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏java达人

SQL索引优化

序言 数据库的优化方法有很多种,在应用层来说,主要是基于索引的优化。本次秘笈根据实际的工作经验,在研发原来已有的方法的基础上,进行了一些扩充,总结了基于索引的S...

1918
来自专栏机器学习从入门到成神

关于SQL数据库中的范式

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/sinat_35512245/articl...

821
来自专栏数据和云

性能为王:SQL标量子查询的优化案例分析

黄廷忠(网名:认真就输) 云和恩墨技术专家 个人博客:http://www.htz.pw/ 本篇整理内容是黄廷忠在“云和恩墨大讲堂”微信分享中的讲解案例,S...

3685
来自专栏GreenLeaves

SQL练习之不反复执行相同的计算

下面是Demo所需要的代码: CREATE TABLE Fee ( Income decimal(10,1), overhead decimal(10,1) )...

2028
来自专栏Python数据科学

【SQL刷题系列】:leetcode183 Customers Who Never Order

假设一个网站包含两个表: Customers和Orders。写出一个SQL查询语句找出所有没有任何订单的顾客。

982
来自专栏Python数据科学

【SQL刷题系列】:leetcode178 Rank Scores

编写一个 SQL查询来对分数排名。如果两个分数相同,那么两个分数应该有同样的排名。但也请注意,如果平分,那么下一个名次应该是下一个连续的整数值。换句话说,名次之...

1752
来自专栏数据和云

网友过招老杨:Gauss和Poincare数学问题的另类解法

大家应该还记得前几天我们的一篇文章:用SQL解一道有趣的数学题:Gauss和Poincare ,错过的朋友请先回顾。感谢网友的反馈,发来新的解法一则。 如网友所...

2784
来自专栏李蔚蓬的专栏

RFID课程前置——SQL巩固练习

-- 5) 输出所有数据的拨号流水,并且在最后一行添加总呼叫时长。 -- 记录呼叫员编号、对方号码、通话时长 --... -- 汇总[市内号码总时长][长...

1214
来自专栏鸿的学习笔记

Stream和Table的联系

传统的Table领域和目前的Stream领域看似横亘着不可跨越的鸿沟,类似于经典力学和量子力学,但Stream和Table的依然存在着相关性。在研究这个理论之前...

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

简单对比MySQL和Oracle中的一个sql解析细节 (r5笔记第40天)

SQL的语法解析器是一个很强大的内置工具集,里面会涉及到很多的编译原理的相关知识,语法分析,词法分析。。一大堆看起来很理论的东东,不过看起来枯燥之余,它们的价值...

3508

扫码关注云+社区