exists or IN UNION ALL 的优化

对应的执行计划及执行时间:

老虎刘注:

根据执行计划及文章中其他相关信息: t表大概168w记录,n表2400w记录, m表未知,可以认为跟n差不多,对结论没有影响.

接着往下看,原作者的第一次优化:用hint调了一个索引的使用, 耗时降到44秒.

老虎刘注:

   这里有人可能有疑问, 原SQL的逻辑读2874w, 调了索引之后逻辑读也高达1529w(降了不到50%), 为什么时间降了那么多?  

   这些因为第一次sql执行有大量的物理读(270w次), 而第二次执行时, 这些数据块都已经读到了内存, 物理读(physical reads)是0.  所以, 实际上这一步的优化只是提升了不到1倍, 如果数据不在缓存,执行时间会远高于44秒. 这里也可以看出, 这个库的存储IO能力比较差,应该是传统的机械磁盘.

继续分析:

原作者对这个提升不太满意(不到1倍的提升确实不能满意, 下次生产上再执行,数据不在缓存的可能性非常大,执行时间仍要1小时以上), 对SQL进行了改写,改写后的SQL是这个样子:

这个改写之后, SQL的执行效率就飞起来了:

老虎刘点评:

很多人看到这里可能就不明觉厉了. 我的第一感觉也是这样的. 认真分析一下改写后的SQL和执行计划, 就能发现事实并非如此.

根据上面执行计划显示, 其实oracle优化器把改写后的SQL做了查询转换,转换后的SQL应该是下面这个样子:

看到上面这个SQL, 估计很多人就能看出问题了, 这个sql的改写,只是结果上的等价, 逻辑上的等价差的比较多. 为什么这么说?

1. 首先, exists不能简单改成inner join,除非子查询关联字段有唯一约束;

2. 改写后出现了笛卡尔集(上图执行计划中标注3,下面的NL也相当于笛卡尔), 原来t与m, t与n 是两两关联, 改写后变成了t与m关联, 再与n做笛卡尔, t与n关联, 再与m做笛卡尔, 正常情况这是比较差的执行计划.

为什么看起来比较差(笛卡尔关联和全表扫描)的执行计划, 执行效率又那么好呢?  这是因为这个SQL使用的一组变量, 返回的结果集为空(最后执行计划我标注1和2的地方), 因为t与m关联后结果集为空, n表的全表扫描不需要了(标注4);  t与n关联后结果集为空, m表的全表扫描也不需要了(标注5).

如果t与m关联后结果集是100行, 那么n表就要做100次全表扫描(2400万记录的表); 如果t与n关联后结果集也是100行, 那么m表也要做100次全表扫描. 如果是这样, 你觉得这个SQL的执行时间还会是10毫秒吗?

综上分析, 这个SQL的改写效果只是在特定变量下的表现, 换一组变量, 效率可能要差很多很多. 

这个SQL的正确改写方法是什么呢? 下面是我给出的改写方法, 非常简单, 只需要把t表在两个子查询中的公共条件从exists里面提取出来就可以了:

有人可能会问, 这样的or exists会不会还是走filter的执行计划?  其实oracle从11g版本开始就可以对这个之前只能走filter的执行计划做查询转换, 使用的是一个叫unnest_disjunctive_subq的查询转换规则, 自动将sql转换成下面这个样子(下面其实是我的改写, 我没看10053, 根据执行计划反推的):

如果你用的是mysql或是postgresql的数据库, 遇到这样的sql, 如果不能像oracle优化器那样做查询转换, 也可以手工改成上面这个样子,在逻辑上是完全等价的. 

----------------------------

本例子是比较某条记录的情况，如果在进行ETL时需要比较T，M，N表中所有关联条件时，改写的方向

1. 使用union all 对OR 进行改写，这种也不是完全等价，union 或者union all都可能多一点或者少一点数据。

2. exists 里面有or的情况，真的可以用IN 来改写，注意NOT NULL的情况，使用IN 改写，查看执行计划如果看到HASH JOIN MERGE JOIN基本达到目的，Filter 或者NL join都要继续查看。

花絮, 看几个原帖的点评:

点评1: 下面这个点评来自同一个人.

"先分析一下表"(即收集表的统计信息) :这个可能是对SQL优化不太了解的人做调优的第一板斧,但明显这个SQL不适用; 

"子查询结果集小用in" : 这个说法要么不是搞oracle数据库的, 要么还在用oracle很古老的版本, 要么还在靠百度优化.

--这人说的没问题，IN 对空值处理方法不同，所以可能转发为join

点评2: 

对于下面这个点评, 我要多说几句, 能写成这个评论, 说明点评人对SQL优化的了解不多. 如果说一个sql可以不用改写, 通过hint或某些参数就能得到优化, 他这么说没问题, 这也是我在生产系统做优化的推荐做法. 但是这个SQL明显是只能通过改写才能解决性能问题, 给出这样的评论就只能暴露自己认知上的不足. 

 接下来我可能会点评另一篇专家文章, 一个可以用hint调整执行计划来优化的SQL, 专家做了很复杂的改写(改成了plsql), 这个也是不建议的.

预告: 接下来可能还有类似的点评文章跟大家分享, 如果大家感兴趣. 

优化是严谨的, 有原理可循的, 不能乱来.