数码控科技猎奇Iphone动漫星座游戏电竞lolcosplay王者荣耀攻略allcnewsBLOGNEWSBLOGASKBLOGBLOGZSK全部技术问答问答技术问答it问答代码软件新闻开发博客电脑/网络手机/数码笔记本电脑互联网操作系统软件硬件编程开发360产品资源分享电脑知识文档中心IT全部全部分类全部分类技术牛文全部分类教程最新网页制作cms教程平面设计媒体动画操作系统网站运营网络安全服务器教程数据库工具网络安全软件教学vbscript正则表达式javascript批处理更多»编程更新教程更新游戏更新allitnewsJava新闻网络医疗信息化安全创业站长电商科技访谈域名会议专栏创业动态融资创投创业学院 / 产品经理创业公司人物访谈营销开发数据库服务器系统虚拟化云计算嵌入式移动开发作业作业1常见软件all电脑网络手机数码生活游戏体育运动明星影音休闲爱好文化艺术社会民生教育科学医疗健康金融管理情感社交地区其他电脑互联网软件硬件编程开发360相关产品手机平板其他电子产品摄影器材360硬件通讯智能设备购物时尚生活常识美容塑身服装服饰出行旅游交通汽车购房置业家居装修美食烹饪单机电脑游戏网页游戏电视游戏桌游棋牌游戏手机游戏小游戏掌机游戏客户端游戏集体游戏其他游戏体育赛事篮球足球其他运动球类运动赛车健身运动运动用品影视娱乐人物音乐动漫摄影摄像收藏宠物幽默搞笑起名花鸟鱼虫茶艺彩票星座占卜书画美术舞蹈小说图书器乐声乐小品相声戏剧戏曲手工艺品历史话题时事政治就业职场军事国防节日风俗法律法规宗教礼仪礼节自然灾害360维权社会人物升学入学人文社科外语资格考试公务员留学出国家庭教育学习方法语文物理生物工程学农业数学化学健康知识心理健康孕育早教内科外科妇产科儿科皮肤科五官科男科整形中医药品传染科其他疾病医院两性肿瘤科创业投资企业管理财务税务银行股票金融理财基金债券保险贸易商务文书国民经济爱情婚姻家庭烦恼北京上海重庆天津黑龙江吉林辽宁河北内蒙古山西陕西宁夏甘肃青海新疆西藏四川贵州云南河南湖北湖南山东江苏浙江安徽江西福建广东广西海南香港澳门台湾海外地区

如何利用分析函数改写范围判断自关联查询详解

来源:脚本之家  责任编辑:小易  

前言

最近碰到一个单条SQL运行效率不佳导致数据库整体运行负载较高的问题。

分析、定位数据库的主要负载是这条语句引起的过程相对简单,通过AWR报告就可以比较容易的完成定位,这里就不赘述了。

现在直接看一下这个导致性能问题的SQL语句,其对应的SQL REPORT统计如下:

Stat Name Statement Total Per Execution % Snap Total
Elapsed Time (ms) 363,741 363,740.78 8 .42
CPU Time (ms) 362,770 362,770.00 8 .81
Executions 1    
Buffer Gets 756 756.00 0.00
Disk Reads 0 0.00 0.00
Parse Calls 1 1.00 0.01
Rows 50,825 50,825.00  
User I/O Wait Time (ms) 0  
Cluster Wait Time (ms) 0    
Application Wait Time (ms) 0    
Concurrency Wait Time (ms) 0    
Invalidations 0    
Version Count 1    
Sharable Mem(KB) 28    

从SQL的性能指标上看,其单次执行需要6分钟左右,处理5万多条记录,逻辑度只有756,主要消耗时间在CPU上。而这里就存在疑点,逻辑读如此之低,而CPU时间花费又如此之高,那么这些CPU都消耗在哪里呢?当然这个问通过SQL的统计信息中是找不到答案的,我们下面关注SQL的执行计划:


Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
0 SELECT STATEMENT       1226 (100)  
1    SORT ORDER BY   49379 3375K 3888K 1226 (2) 00:00:05
2      HASH JOIN ANTI   49379 3375K 2272K 401 (3) 00:00:02
3        TABLE ACCESS FULL T_NUM 49379 1687K   88 (4) 00:00:01
4        TABLE ACCESS FULL T_NUM 49379 1687K   88 (4) 00:00:01

从执行计划看,Oracle选择了HASH JOIN ANTI,JOIN的两张表都是T_NUM,且都采用了全表扫描,并未选择索引。仅靠执行计划也只等得到上面的结论,至于为什么不选择索引,以及为什么执行时间过长,还需要进一步的分析。

将原SQL进行简单脱密改写后, SQL文本类似如下:

SELECT BEGIN, END, ROWID, LENGTH(BEGIN)
FROM T_NUM A
WHERE NOT EXISTS (
SELECT 1
FROM T_NUM B
WHERE B.BEGIN <= A.BEGIN
AND B.END >= A.END
AND B.ROWID != A.ROWID
AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN));

如果分析SQL语句,会发现这是一个自关联语句,在BEGIN字段长度相等的前提下,想要找到哪些不存在BEGIN比当前记录BEGIN小且END比当前记录END大的记录。

简单一点说,表中的记录表示的是由BEGIN开始到END截至的范围,那么当前想要获取的结果是找出哪些没有范围所包含的范围。需要注意的是,对于当前的SQL逻辑,如果存在两条范围完全相同的记录,那么最终这两条记录都会被舍弃。

业务的逻辑并不是特别复杂,但是要解决一条记录与其他记录进行比较,多半采用的方法是自关联,而在这个自关联中,既有大于等于又有小于等于,还有不等于,仅有的一个等于的关联条件,来自范围段BEGIN的长度的比较。

显而易见的是,如果是范围段本身的比较,其选择度一般还是不错的,但是如果只是比较其长度,那么无疑容易产生大量的重复,比如在这个例子中:

SQL> select length(begin), count(*) from t_num group by length(begin) order by 2 desc;

 

LENGTH(BEGIN) COUNT(*)

————- ———-

12  22096

11  9011

13  8999

14  8186

16   49

9   45

8   41

7   27

大量重复的数据出现在长度为11到14的范围上,在这种情况下,仅有的一个等值判断条件LENGTH(BEGIN)是非常低效的,这时一条记录根据这个等值条件会关联到近万条记录,设置关联到两万多条记录,显然大量的实践消耗在低效的连接过程中。

再来看一下具体的SQL语句,会发现几乎没有办法建立索引,因为LENGTH(BEGIN)的选择度非常查,而其他的条件都是不等查询,选择度也不会好,即使建立索引,强制执行选择索引,效率也不会好。

那么如果想要继续优化这个SQL,就只剩下一个办法,那就是SQL的改写。对于自关联查询而言,最佳的改写方法是利用分析函数,其强大的行级处理能力,可以在一次扫描过程中获得一条记录与其他记录的关系,从而消除了自关联的必要性。

SQL改写结果如下:

SELECT BEGIN, OLDEND END, LENGTH(BEGIN)
FROM (
SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN,
ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN
FROM
(
SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END
FROM T_NUM
)
)
WHERE RN = 1
AND CN = 1;

简单的说,内层的分析函数MAX用来根据BEGIN从小到大,END从大到小的条件,确定每个范围对应的最大的END的值。而外层的两个分析函数,COUNT用来去掉完全重复的记录,而ROW_NUMBER用来获取范围最大的记录(也就是没有被其他记录的范围所涵盖)。

改写后,这个SQL避免对自关联,也就不存在关联条件重复值过高的性能隐患了。在模拟环境中,性能对比如下:

SQL> SELECT BEGIN, END, ROWID, LENGTH(BEGIN)

2 FROM T_NUM A

3 WHERE NOT EXISTS (

4  SELECT 1

5  FROM T_NUM B

6  WHERE B.BEGIN <= A.BEGIN

7  AND B.END >= A.END

8  AND B.ROWID != A.ROWID

9  AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN))

10 ;

 

48344 rows selected.

 

Elapsed: 00:00:57.68

 

Execution Plan

———————————————————-

Plan hash value: 2540751655

 

————————————————————————————

| Id | Operation   | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time  |

————————————————————————————

| 0 | SELECT STATEMENT |  | 48454 | 1703K|  | 275 (1)| 00:00:04 |

|* 1 | HASH JOIN ANTI |  | 48454 | 1703K| 1424K| 275 (1)| 00:00:04 |

| 2 | TABLE ACCESS FULL| T_NUM | 48454 | 851K|  | 68 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T_NUM | 48454 | 851K|  | 68 (0)| 00:00:01 |

————————————————————————————

 

Predicate Information (identified by operation id):

—————————————————

 

1 – access(LENGTH(TO_CHAR(“B”.”BEGIN”))=LENGTH(TO_CHAR(“A”.”BEGIN”)))

filter(“B”.”BEGIN”<=”A”.”BEGIN” AND “B”.”END”>=”A”.”END” AND

“B”.ROWID<>”A”.ROWID)

 

 

Statistics

———————————————————-

0 recursive calls

0 db block gets

404 consistent gets

0 physical reads

0 redo size

2315794 bytes sent via SQL*Net to client

35966 bytes received via SQL*Net from client

3224 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

48344 rows processed

 

SQL> SELECT BEGIN, OLDEND END, LENGTH(BEGIN)

2 FROM (

3  SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN,

4    ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN

5  FROM

6  (

7    SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END

8    FROM T_NUM

9  )

10 )

11 WHERE RN = 1

12 AND CN = 1;

 

48344 rows selected.

 

Elapsed: 00:00:00.72

 

Execution Plan

———————————————————-

Plan hash value: 1546715670

 

——————————————————————————————

| Id | Operation    | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time  |

——————————————————————————————

| 0 | SELECT STATEMENT   |  | 48454 | 2460K|  | 800 (1)| 00:00:10 |

|* 1 | VIEW     |  | 48454 | 2460K|  | 800 (1)| 00:00:10 |

|* 2 | WINDOW SORT PUSHED RANK|  | 48454 | 1845K| 2480K| 800 (1)| 00:00:10 |

| 3 | WINDOW BUFFER   |  | 48454 | 1845K|  | 800 (1)| 00:00:10 |

| 4 |  VIEW     |  | 48454 | 1845K|  | 311 (1)| 00:00:04 |

| 5 |  WINDOW SORT   |  | 48454 | 662K| 1152K| 311 (1)| 00:00:04 |

| 6 |  TABLE ACCESS FULL | T_NUM | 48454 | 662K|  | 68 (0)| 00:00:01 |

——————————————————————————————

 

Predicate Information (identified by operation id):

—————————————————

 

1 – filter(“RN”=1 AND “CN”=1)

2 – filter(ROW_NUMBER() OVER ( PARTITION BY LENGTH(TO_CHAR(“BEGIN”)),”END”

ORDER BY “BEGIN”)<=1)

 

 

Statistics

———————————————————-

0 recursive calls

0 db block gets

202 consistent gets

0 physical reads

0 redo size

1493879 bytes sent via SQL*Net to client

35966 bytes received via SQL*Net from client

3224 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

48344 rows processed

原SQL运行时间接近1分钟,而改写后的SQL语句只需要0.72秒,执行时间变为原本的1/80,逻辑读减少一半。

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对脚本之家的支持。

您可能感兴趣的文章:


  • 本文相关:
  • mysql中三种关联查询方式的简单比较
  • mysql中的跨库关联查询方法
  • 浅谈mysql中多表不关联查询的实现方法
  • mysql中表子查询与关联子查询的基础学习教程
  • 实例讲解java的mybatis框架对mysql中数据的关联查询
  • mybatis实践之动态sql及关联查询
  • mysql关联子查询的一种优化方法分析
  • yii2实现跨mysql数据库关联查询排序功能代码
  • mysql一对多关联查询分页错误问题的解决方法
  • mysql与oracle 差异比较之六触发器
  • spring集成mybatis完整实例(分享)
  • sql server 里的错误处理(try catch)
  • 主键与聚集索引
  • sql注入之必备的基础知识
  • hadoop 2.x伪分布式环境搭建详细步骤
  • 恢复 sql 被注入后的数据代码
  • 将sqlite3中数据导入到mysql中的实战教程
  • ado.net数据库访问技术
  • sql大量数据查询的优化及非用like不可时的处理方案
  • 免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved