SQL优化三板斧:精简之道、驱动为王、集合为本

2017年Gdevops全球敏捷运维峰会-成都站(限时优惠),运维派作为本次峰会协办方,您可以点击这里了解详情

作者介绍

黄浩,现任职于中国惠普,从业十年,始终专注于SQL。在华为做项目的两年多,做过大大小小的SQL多达1500个。闲暇之余,喜欢将部分案例写成博客发表在华为内部数据库官方社区,反响强烈,已连续四个月蝉联该社区最佳博主。目前已开设专栏“优哉悠斋”,成为首个受邀社区“专家访谈”的外协人员。

公元2016年8月1日晚上,朋友圈流行着这样一个段子:特想摸清台风“妮妲”的威力有多大,一专业人士说:只须一句话就能让你深刻理解。遂追问,答曰:“就连华为都通知放假了?”感谢“妮妲”,让深圳这座高速运转的城市在星期二这天暂停了;感谢华为,让我这个来深10年,为生活奔波劳顿的人也能倚在窗前,眼观疾风骤雨之变,心游惊涛骇浪之中。

妮妲走了,SQL来了

8月3日,一同事转来一个SQL,我打开文件,发现整个代码多达347行。

SQL SQL 代码 代码

在DB中执行,时耗达到了4分多钟,再往下钻取,如同蜗牛一般,根本钻不动,14分钟过去了,还只钻取到了800行。

DB

由此该SQL的性能表现为“两慢”:首条返回慢、下钻提取慢。大多数情况,我们只会遇其一,要么快速返回出现性能瓶颈,要么全部提取出现性能瓶颈。这回好了,都齐全了。透过窗户,望着被“妮妲”肆意狂虐后叶颤枝乱的树木,心里不禁在想:服务器也被“妮妲”肆虐了?

此时,台风“妮妲”疯狂过后的温馨凉意,也没能让我心如止水,毕竟这个优化任务看起来有些棘手。

人生若只如初见

因为来者不善,而时间宽限,我也计划打持久战。在展开分析前,我对SQL中的表对象和数据量做了初步统计。如下:

SQL表对象

人生若只如初见,初见往往是美妙的,让人心旷神怡的。而与该SQL的初次交流,画面却是暗潮涌动杀机四伏:

  1. 动辄千万上亿的数据量,近40次对象访问,还不包括VIEW中的表对象。
  2. 从SQL代码上看,出现了聚合函数,因此可以断定是批量数据处理。

以上两点,按经验,能2分钟跑出来就不错了,现在是要求2~3S,看起来是一个不可完成的任务。

第一板斧:大刀阔斧

在初步分析中,ORDER_RELEASE和ORDER_RELEASE_REFNUM两个表是最抢眼的,数据量分别是千万级和亿级,访问次数更是惊人的达到了10次以上。好奇心我决定以这两个表为切入口,探究下是如何被访问的?

借助于NOTEPAD++编辑神器,很快定位到了这两个表的访问情况:

数据量

初步一看:

这两个表的访问基本上都是在子查询中,而且都是成对出现

仔细对比了子查询后,发现这些子查询可分A、B两类

A类子查询共有5个的代码都是完全一样的,如下:

代码

4、B类子查询共有3个的代码都是完全一样的,如下

代码

深入子查询内部,无论是A类子查询还是B类子查询,ORDER_RELEASER和ORDER_RELEASE_REFNUMO_REF的关联方式都是一样的,关联字段是ORDER_RELEASE_GID。此时,结合两个表的命名,按多年的经验,我猜想:

ORDER_RELEASE_GID为ORDER_RELEASE表的主键字段

ORDER_RELEASE_REFNUM与ORDER_RELEASE表存在主外键约束,字段就是ORDER_RELEASE_GID

为了验证我的假设,我VIEW了ORDER_RELEASE_REFNUM的表结构,如下:

VIEW了ORDER_RELEASE_REFNUM的表结构

果真如此。那么问题来了,即便如此,我们又能做什么呢?答案很简单,这两类子查询中,ORDER_RELEASE表可以被“砍掉”。等价的SQL如下:

A类:

等价的SQL

B类:

等价的SQL

再看看这个子查询的数据量:

查询数据量

只有8千多条,相对于千万上亿,已经是非常少的数据量了。

结合上述分析结果,我对SQL做了如下调整:

将A、B类子查询用两个with子查询代替,这样就能减少大表的访问次数;

在A、B类子查询中,将ORDER_RELEASE表“砍掉”,减少表关联带来的IO开销;

由于子查询的数据量非常小,将之前的IN子查询改写为INNERJOIN,这样就可以形成小结果集驱动大表的效果。

调整后的代码如下:

代码 等价的SQL

对于这次的优化,我并没有抱什么希望,因为这仅仅是常规性的精简,还没有深入到代码内部。或者说,这还仅仅是规范性改写。

果真,执行仍然需要耗时4分多钟,但是,这次的精简并不是没有任何收益。因为当往下钻取时,速度非常快,钻取完6625条记录不到10S。

精简

第二板斧:披荆斩棘

第二天一上班,就开始接着昨天的节奏继续优化。

SQL的精简并没有为快速返回带来任何收益,我决定看下执行计划,尝试着从执行计划中得到更多的信息。果真,F5后看到的执行计划中,一个VIEW的COST犹如“鹤立鸡群”,特别的扎眼:

VIEW的COST

从执行计划看,Oracle对这个视图做了传统的处理,没有合并,也没有谓词推入。所以视图中的表基本上都是table access full。此时,突然想起在当时统计表对象的时候,记得只有一个视图,而在昨天在精简B类子查询的时候,也出现过一个视图。那这两个视图应该是同一个了。而昨天B类子查询的速度是非常快的。

我赶紧将执行计划定位到了B类子查询,如下:

B类子查询

原来如此,在B类子查询中,该视图被merge了。

受此启发,我也计划将主查询中的VIEW通过HINT进行MERGE,但是HINT似乎并不生效,始终都无法改变现有的执行计划。无奈之际,只有深入SQL,实地窥探这个VIEW到底“何德何能”,会让ORACLE优化器如此“死心塌地”的“维持原判”。

SQL

从上图中可以看出,该视图与A类子查询进行了关联,而事实上,B类子查询就是该视图与A类子查询关联的结果呀。怎么在这里又要临时关联呢?难道昨天做精简的时候还存在漏网之鱼

再看代码:

代码

原来这里需要获取该视图的两个字段,而在B类子查询中,我们只获取了SHIPMENT_GID一个字段。那是否可以直接在B类子查询中加一个字段呢?

我们再来看看B类子查询的代码逻辑:

22

在这里,我们获取了SHIPMENT_GID字段,并对该字段通过DISTINCT去除了重复值。这样做的目的在于,在后面调用该子查询时,以该子查询为驱动表,驱动关联其他表对象。因为子查询的结果集很小,而被关联的表对象都是千万上亿级别的。

很显然,如果我们在B类子查询中增加ORDER_RELEASE_GID字段,就会影响到SHIPMENT_GID的唯一性,这样,在后续的关联查询中,就不能直接用B类子查询驱动关联。这会直接破坏掉已经建立好的驱动关系。

既然增加字段之路行不通,那就尝试着再增加一个WITH子查询,代码如下:

23

与此同时,对访问该视图的代码也进行了适应性的修改,修改后的脚本如下:

24

 

脚本 脚本 脚本

再次执行,耗时2:28,虽然与秒级的性能要求相距甚远,但是至少性能提升了近50%,其意义并在于提升的效果,而在于证明了优化方向是正确的,即在大表林立群狼环视虎视眈眈的环境中,要快速准确的定位出驱动表,需要明确将驱动表数据准备好。

第三板斧:神工鬼斧

性能尚未达标,优化仍需继续。

先看看执行计划:

执行计划

从COST列,并没有看到成本特别高的操作。所以,我放弃了继续在执行计划上做文章,转而深入分析SQL代码逻辑。

经过一番抽丝剥茧起承转合后,SQL的整体代码逻辑也呼之欲出,发现顶层的逻辑设计非常简单明了,就是三个子查询的结果集内连接,如下图所示:

子查询结果

接下来,我做了一件被人“鄙视”的小儿科的事,就是分别执行了这三个子查询。原本想着总会有一个慢的,我就重点优化慢的那个子查询。而结果却出人意表,三个子查询都是在2S左右就能完成执行,而且数据量都在1万以内。那为何三个子查询关联在一起,性能会如此受影响呢?要知道,如果是三个1万以内的表关联,即便是无任何索引,那也是秒出呀。

那么问题出在哪里呢?没的说,肯定是执行计划并没有按我们预想的去执行这个SQL。此时,我也没有心思去仔细分析执行计划,而是直接祭出了第三板斧通过with子查询的方式将ORDER_REL、SHP、REL三个子查询封装成结果集,改写后的SQL如下:

SQL SQL SQL SQL SQL

再看执行计划:

执行计划

看起来与我们预期的效果一致了,而关键还是要看执行的效率。

执行效率

3.5S,再往下钻取,也不到10s皇天不负有心人,终于可以画“句号”了。此时,已经是第三天上午,距离拿到原始SQL将近2天的时间了。台风“妮妲”早已销声匿迹,来也匆匆去也匆匆。你方唱罢我登场,立秋前的烧烤模式再次以胜利者的姿态,歇斯底里的“蒸烤”着这片大地。而躲在空调房的人类,也在尽情的透支着地球赐予的有限资源,最终会如同这个SQL一样,终有一天会引发灾难;而再去治理,再去挽救,需要花费更多的资源与精力。

后记

从4分钟到3.5S,从钻取卡顿到一泻千里,整整经历了近2天时间,耗时之长在以往的优化案例中实属少见。事实上,当一开始拿到这个SQL时,尤其是在了解到这个SQL及背后的数据环境时,我心里面是直打鼓的。可以说,是硬着头皮拿下了这个SQL,现在回想起来仍然后怕。然而,除了后怕,更多的是该案例优化过程中所体现出的SQL(优化)精髓:精简之道、驱动为王、集合为本。

精简之道

大道至简、简单即高效、复杂的事情简单化等等这些我们喜闻乐见的生活常识,同样适用于SQL(优化)。记得SQL优化大师曾说过:不要让ORACLE做多余的事。而对于ORACLE而言,多余的事情是什么呢?多余的表关联、重复的表访问、冗余的关联(过滤)条件、不必要的DISTINCT\ORDERBY\GROUPBY、曲折的访问路径。虽然ORACLE优化器引擎也在努力识别并消除这些“多余的事”(可参见博客,然而,在面对复杂的SQL时,ORACLE也往往束手无策。因此,SQL优化的首要之事就是精简SQL。

驱动为王

有这样一句话:一头狮子领着一群羊,要胜过一头羊领着一群狮子。这就道出了“领头”的重要性,在ORACLE优化器中,就是“驱动表”。驱动表的意义有如木楔子,只有薄如纸片锐如刀刃的楔子,才能轻而易举的插入坚硬木桩中。如果给你一个圆头的木头,任凭你力气再大,也不能插入。这就要求驱动表的数据量要足够的少。尽管ORACLE优化器也在努力寻找合适的“领头”,而有的时候,ORACLE优化器会被腰里别了杆枪的老鼠给骗了。比如本案例中的A类子查询,起初是通过IN子查询进行过滤的,这就存在很大的性能风险。关于驱动表的优化案例有很多,后续会专题分享。

集合为本

集合操作是二维关系数据库引擎在数据处理时的根本,单表是一个集合,多表关联后的结果也是一个集合,视图、子查询的返回结果还是一个集合,整个SQL执行完后的结果仍然是一个集合。

因此,一个高效的SQL一定有一个合理的集合运算结构。根据业务需求,结合代码逻辑,有的时候需要将代码片通过子查询封装;而有的时候又需要将子查询合并到主查询中;有的时候需要将大集合根据业务逻辑切片成多个小的集合;有的时候又需要将若干个小的集合预先合并成大集合。总之,在进行SQL(优化)时,一定要有集合的概念,用集合的思维指导SQL(优化)。

文章出处:DBAplu社群(订阅号ID:dbaplus)

网友评论comments

发表评论

电子邮件地址不会被公开。 必填项已用*标注

暂无评论

Copyright © 2012-2017 YUNWEIPAI.COM - 运维派 - 粤ICP备14090526号-3
扫二维码
扫二维码
返回顶部