SQL优化三板斧：精简之道、驱动为王、集合为本
ConNixon
8年前
<p>8月3日，一同事转来一个SQL，我打开文件，发现整个代码多达347行。</p> <p><img src="https://simg.open-open.com/show/cab58af22754260427fbe8b185fe1567.jpg"></p> <p><img src="https://simg.open-open.com/show/2579a5b66e11e5af1527cc88d6df32fe.jpg"></p> <p><img src="https://simg.open-open.com/show/0a0ce6d90d5f9891b729e249cbdad59d.jpg"> <img src="https://simg.open-open.com/show/74d2c62bf7508ac67deae4f5dc60c735.jpg"></p> <p>在DB中执行，时耗达到了4分多钟，再往下钻取，如同蜗牛一般，根本钻不动，14分钟过去了，还只钻取到了800行。</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/f68a80df3c609dffaff9c400a54c6e9f.jpg"></p> <p>由此该SQL的性能表现为“两慢”： <strong>首条返回慢、下钻提取慢。</strong> 大多数情况，我们只会遇其一，要么快速返回出现性能瓶颈，要么全部提取出现性能瓶颈。这回好了，都齐全了。透过窗户，望着被“妮妲”肆意狂虐后叶颤枝乱的树木，心里不禁在想： <strong>服务器也被“妮妲”肆虐了？</strong></p> <p>此时，台风“妮妲”疯狂过后的温馨凉意，也没能让我心如止水，毕竟这个优化任务看起来有些棘手。</p> <p><strong>人生若只如初见</strong></p> <p>因为来者不善，而时间宽限，我也计划打持久战。在展开分析前，我对SQL中的表对象和数据量做了初步统计。如下：</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/92739fb6ab6c0840b8755909341f2c86.png"></p> <p>人生若只如初见，初见往往是美妙的，让人心旷神怡的。而与该SQL的初次交流，画面却是暗潮涌动杀机四伏：</p> <ol> <li> <p>动辄 <strong>千万上亿</strong> 的数据量， <strong>近40次</strong> 对象访问，还不包括VIEW中的表对象。</p> </li> <li> <p>从SQL代码上看，出现了 <strong>聚合函数</strong> ，因此可以断定是 <strong>批量数据处理。</strong></p> </li> </ol> <p>以上两点，按经验，能2分钟跑出来就不错了，现在是要求2~3S，看起来是一个不可完成的任务。</p> <p><strong>第一板斧：大刀阔斧</strong></p> <p>在初步分析中，ORDER_RELEASE和ORDER_RELEASE_REFNUM两个表是最抢眼的，数据量分别是千万级和亿级，访问次数更是惊人的达到了10次以上。好奇心我决定以这两个表为切入口，探究下是如何被访问的？</p> <p>借助于NOTEPAD++编辑神器，很快定位到了这两个表的访问情况：</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/9aba76c4cdd24c67e2f3aa3bd85d6351.jpg"></p> <p>初步一看：</p> <ol> <li> <p>这两个表的访问基本上都是在 <strong>子查询</strong> 中，而且都是成对出现</p> </li> <li> <p>仔细对比了子查询后，发现这些子查询可分A、B两类</p> </li> <li> <p>A类子查询共有 <strong>5个的代码都是完全一样的</strong> ，如下：</p> </li> </ol> <p style="text-align: center;"><img src="https://simg.open-open.com/show/df80366ee2273a16198dc387b1fbe2a3.jpg"></p> <p>4、B类子查询共有 <strong>3个的代码都是完全一样的</strong> ，如下：</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/50d54b1bb8076dd4486bef3acce047f8.jpg"></p> <p>深入子查询内部，无论是A类子查询还是B类子查询，ORDER_RELEASE R和ORDER_RELEASE_REFNUM O_REF的关联方式都是一样的，关联字段是ORDER_RELEASE_GID。此时，结合两个表的命名，按多年的经验，我猜想：</p> <ol> <li> <p>ORDER_RELEASE_GID为ORDER_RELEASE表的 <strong>主键字段</strong></p> </li> <li> <p>ORDER_RELEASE_REFNUM与ORDER_RELEASE表存在 <strong>主外键约束</strong> ，字段就是ORDER_RELEASE_GID</p> </li> </ol> <p>为了验证我的假设，我VIEW了ORDER_RELEASE_REFNUM的表结构，如下：</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/fc3956ec298efaa59003831cf3ec881f.jpg"></p> <p>果真如此。那么问题来了，即便如此，我们又能做什么呢？答案很简单，这两类子查询中， <strong>ORDER_RELEASE表可以被“砍掉”</strong> 。等价的SQL如下：</p> <p>A类：</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/dec92b9e03194cd674c4a8bef213a211.jpg"></p> <p>B类：</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/e0aa77833d62c26cd16cc01709c24b5c.jpg"></p> <p>再看看这个子查询的数据量：</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/dccae15659e45851d9241e3024b821c7.jpg"></p> <p>只有 <strong>8千多条</strong> ，相对于千万上亿，已经是非常少的数据量了。</p> <p>结合上述分析结果，我对SQL做了如下调整：</p> <ol> <li> <p>将A、B类子查询用两个 <strong>with子查询</strong> 代替，这样就能 <strong>减少大表的访问次数；</strong></p> </li> <li> <p>在A、B类子查询中， <strong>将ORDER_RELEASE表“砍掉”</strong> ，减少表关联带来的IO开销；</p> </li> <li> <p>由于子查询的数据量非常小，将之前的IN子查询改写为INNER JOIN，这样就可以形成小结果集驱动大表的效果。</p> </li> </ol> <p>调整后的代码如下：</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/3c48b85bfafc12bca38570205110f596.jpg"> <img src="https://simg.open-open.com/show/672be37d9000110ecfdaf7fc824277e2.jpg"> <img src="https://simg.open-open.com/show/3622c0c48616508095fcbc9ecdeca918.jpg"></p> <p>对于这次的优化，我并没有抱什么希望，因为这仅仅是常规性的精简，还没有深入到代码内部。或者说， <strong>这还仅仅是规范性改写。</strong></p> <p>果真，执行仍然需要 <strong>耗时4分多钟</strong> ，但是，这次的精简并不是没有任何收益。因为当往下钻取时，速度非常快， <strong>钻取完6625条记录不到10S。</strong></p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/f214d9ade810adea80021f3522639744.jpg"></p> <p>不知不觉中，已到了下班时间。台风过后，残阳西下，不再燥热，难违暖意，别有一番韵味。</p> <p><strong>第二板斧：披荆斩棘</strong></p> <p>第二天一上班，就开始接着昨天的节奏继续优化。</p> <p>SQL的精简并没有为快速返回带来任何收益，我决定看下执行计划，尝试着从执行计划中得到更多的信息。果真，F5后看到的执行计划中，一个 <strong>VIEW的COST犹如“鹤立鸡群”</strong> ，特别的扎眼：</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/0cdd871c7b5724714a9d34ce3f6ba129.jpg"></p> <p>从执行计划看，Oracle对这个视图做了传统的处理，没有合并，也没有谓词推入。所以视图中的表基本上都是table access full。此时，突然想起在当时统计表对象的时候，记得只有一个视图，而在昨天在精简B类子查询的时候，也出现过一个视图。那这两个视图应该是同一个了。而昨天B类子查询的速度是非常快的。</p> <p>我赶紧将执行计划定位到了B类子查询，如下：</p> <p style="text-align: center;"><img src="http://static.open-open.com/lib/uploadImg/20161116/20161116155342_226.jpg"></p> <p>原来如此，在B类子查询中，该视图被 <strong>merge</strong> 了。</p> <p>受此启发，我也计划将主查询中的VIEW通过HINT进行MERGE，但是 <strong>HINT似乎并不生效</strong> ，始终都无法改变现有的执行计划。无奈之际，只有深入SQL，实地窥探这个VIEW到底“何德何能”，会让ORACLE优化器如此“死心塌地”的“维持原判”。</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/6b112c679ff1fa65341e6ea4f1ed7427.jpg"></p> <p>从上图中可以看出，该视图与A类子查询进行了关联，而事实上，B类子查询就是该视图与A类子查询关联的结果呀。怎么在这里又要 <strong>临时关联</strong> 呢？难道昨天做精简的时候还存在 <strong>漏网之鱼</strong> ？</p> <p>再看代码：</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/93c4caf16aa65697a28b3b80d8e6bcc0.jpg"></p> <p>原来这里需要获取该视图的两个字段，而在B类子查询中，我们只获取了SHIPMENT_GID一个字段。那 <strong>是否可以直接在B类子查询中加一个字段</strong> 呢？</p> <p>我们再来看看B类子查询的代码逻辑：</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/e6477537fcf2938fa4267ebd03ae6a3e.jpg"></p> <p>在这里，我们获取了SHIPMENT_GID字段，并对该字段通过DISTINCT去除了 <strong>重复值</strong> 。这样做的目的在于，在后面调用该子查询时，以该子查询为驱动表，驱动关联其他表对象。因为子查询的结果集很小，而被关联的表对象都是千万上亿级别的。</p> <p>很显然，如果我们在B类子查询中增加ORDER_RELEASE_GID字段，就会影响到SHIPMENT_GID的 <strong>唯一性</strong> ，这样，在后续的关联查询中，就不能直接用B类子查询驱动关联。这会直接破坏掉已经建立好的驱动关系。</p> <p>既然增加字段之路行不通，那就尝试着再 <strong>增加一个WITH子查询</strong> ，代码如下：</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/bba4d82ccd4422d1731a696537ad56b2.jpg"></p> <p>与此同时，对访问该视图的代码也进行了适应性的修改，修改后的脚本如下：</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/f330a68cc14142a908726e210ba04005.jpg"> <img src="https://simg.open-open.com/show/72da2ab0fd8998aa52ae5cf240a67f94.jpg"> <img src="https://simg.open-open.com/show/c26d8c2e2555a0783a1dc875ee9cb578.jpg"> <img src="https://simg.open-open.com/show/c6260316dc413fa17e43740470cb172b.jpg"></p> <p>再次执行，耗时2:28，虽然与秒级的性能要求相距甚远，但是至少性能提升了近50%，其意义并在于提升的效果，而在于证明了优化方向是正确的，即在 <strong>大表</strong> 林立群狼环视虎视眈眈的环境中，要快速准确的 <strong>定位出驱动表，需要明确将驱动表数据准备好。</strong></p> <p><strong>第三板斧：神工鬼斧</strong></p> <p>性能尚未达标，优化仍需继续。</p> <p>先看看执行计划：</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/951c4b6c0879366dd8b04b2939e3ba4f.jpg"></p> <p>从COST列，并没有看到成本特别高的操作。所以，我放弃了继续在执行计划上做文章，转而 <strong>深入分析SQL代码逻辑。</strong></p> <p>经过一番抽丝剥茧起承转合后，SQL的整体代码逻辑也呼之欲出，发现顶层的逻辑设计非常简单明了，就是三个 <strong>子查询的结果集内连接</strong> ，如下图所示：</p> <p style="text-align: center;"><img src="http://static.open-open.com/lib/uploadImg/20161116/20161116155346_764.jpg"></p> <p>接下来，我做了一件被人“鄙视”的小儿科的事，就是 <strong>分别执行了这三个子查询。</strong> 原本想着总会有一个慢的，我就重点优化慢的那个子查询。而结果却出人意表， <strong>三个子查询都是在2S左右</strong> 就能完成执行，而且数据量都在1万以内。那为何三个子查询 <strong>关联</strong> 在一起， <strong>性能会如此受影响呢？</strong> 要知道，如果是三个1万以内的表关联，即便是无任何索引，那也是秒出呀。</p> <p>那么问题出在哪里呢？没的说，肯定是执行计划并没有按我们预想的去执行这个SQL。此时，我也没有心思去仔细分析执行计划，而是直接祭出了第三板斧 通过with子查询的方式将ORDER_REL、SHP、REL三个子查询封装成结果集 ，改写后的SQL如下：</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/d7238cbdcf885663464082d2254c1fed.jpg"> <img src="https://simg.open-open.com/show/e95ff50c706fbde81aea24184a1b0313.jpg"> <img src="https://simg.open-open.com/show/d21c2f7d78116f4443c002705b57a0b8.jpg"> <img src="https://simg.open-open.com/show/daadb36b626d0a98721b795437968bbc.jpg"> <img src="https://simg.open-open.com/show/9c8a9beb67f1f42f1b9ca7982daa6940.jpg"></p> <p>再看执行计划：</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/20d050a0d4dc49a8b5db0e4fc27a249f.jpg"></p> <p>看起来与我们预期的效果一致了，而关键还是要看执行的效率。</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/bb0adcb2b74f02d2c2f3fa6556585f6a.jpg"></p> <p>3.5S，再往下钻取，也不到10s皇天不负有心人，终于可以画“句号”了。此时，已经是第三天上午，距离拿到原始SQL将近2天的时间了。台风“妮妲”早已销声匿迹，来也匆匆去也匆匆。你方唱罢我登场，立秋前的烧烤模式再次以胜利者的姿态，歇斯底里的“蒸烤”着这片大地。而躲在空调房的人类，也在尽情的透支着地球赐予的有限资源，最终会如同这个SQL一样，终有一天会引发灾难；而再去治理，再去挽救，需要花费更多的资源与精力。</p> <p><strong>后记</strong></p> <p>从4分钟到3.5S，从钻取卡顿到一泻千里，整整经历了近2天时间，耗时之长在以往的优化案例中实属少见。事实上，当一开始拿到这个SQL时，尤其是在了解到这个SQL及背后的数据环境时，我心里面是直打鼓的。可以说，是硬着头皮拿下了这个SQL，现在回想起来仍然后怕。然而，除了后怕，更多的是该案例优化过程中所体现出的SQL（优化）精髓： <strong>精简之道、驱动为王、集合为本。</strong></p> <p><strong>精简之道</strong></p> <p>大道至简、简单即高效、复杂的事情简单化等等这些我们喜闻乐见的生活常识，同样适用于SQL（优化）。记得SQL优化大师曾说过： <strong>不要让ORACLE做多余的事</strong> 。而对于ORACLE而言，多余的事情是什么呢？多余的表关联、重复的表访问、冗余的关联（过滤）条件、不必要的DISTINCT\ORDER BY\GROUP BY、曲折的访问路径。虽然ORACLE优化器引擎也在努力识别并消除这些“多余的事 ”（可参见博客，然而，在面对复杂的SQL时，ORACLE也往往束手无策。因此， <strong>SQL优化的首要之事就是精简SQL。</strong></p> <p><strong>驱动为王</strong></p> <p>有这样一句话：一头狮子领着一群羊，要胜过一头羊领着一群狮子。这就道出了“领头”的重要性，在ORACLE优化器中，就是 <strong>“驱动表”</strong> 。驱动表的意义有如木楔子，只有薄如纸片锐如刀刃的楔子，才能轻而易举的插入坚硬木桩中。如果给你一个圆头的木头，任凭你力气再大，也不能插入。这就要求驱动表的数据量要足够的少。尽管ORACLE优化器也在努力寻找合适的“领头”，而有的时候，ORACLE优化器 <strong>会被腰里别了杆枪的老鼠给骗了</strong> 。比如本案例中的A类子查询，起初是通过IN子查询进行过滤的，这就存在很大的性能风险。关于驱动表的优化案例有很多，后续会专题分享。</p> <p><strong>集合为本</strong></p> <p><strong>集合操作是二维关系数据库引擎在数据处理时的根本</strong> ，单表是一个集合，多表关联后的结果也是一个集合，视图、子查询的返回结果还是一个集合，整个SQL执行完后的结果仍然是一个集合。</p> <p>因此， <strong>一个高效的SQL一定有一个合理的集合运算结构。</strong> 根据业务需求，结合代码逻辑，有的时候需要将代码片通过子查询封装；而有的时候又需要将子查询合并到主查询中；有的时候需要将大集合根据业务逻辑切片成多个小的集合；有的时候又需要将若干个小的集合预先合并成大集合。总之，在进行SQL（优化）时，一定要有集合的概念，用集合的思维指导SQL（优化）。</p> <p> </p> <p>来自：http://dbaplus.cn/news-21-805-1.html</p> <p> </p>