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

《SQL优化三板斧：精简之道、驱动为王、集合为本》要点：
本文介绍了SQL优化三板斧：精简之道、驱动为王、集合为本，希望对您有用。如果有疑问，可以联系我们。

作者介绍

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

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

妮妲走了,SQL来了

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

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

由此该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的表结构,如下：

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

A类：

B类：

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

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

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

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

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

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

调整后的代码如下：

（编辑：ASP站长网）