105-跟专家学习SQL优化-1

网上有很多SQL优化的案例, 我本人对这方面特别感兴趣,今天就带着大家一起来学习一下专家是如何优化SQL的.

这是一个2020年某个人公众号上的一个案例, 我当时是在手机上看的, 大致看了一下, 虽然逻辑上不是那么严谨,但是最终结果是正确的, 效果也是相当的炸裂(把一个sql执行时间从2小时39分优化到10毫秒),当时就简单点评了一句改写的逻辑不够严谨.

前几天在网上闲逛时又看到了作者发布在墨天轮上的这个案例,这次是在电脑上看的,有些信息看的比较清晰, 就深入的分析了一下这个案例, 于是就有了新的发现.

优化前的SQL是这个样子的(做了一点简化,去掉了一些无关紧要的干扰信息,看起来更清晰):

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

老虎刘注:

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

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

老虎刘注:

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

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

有很多小白到现在还没有明白这个道理, 前几天还有人在群里说, 我把substr函数改成了left函数(mysql数据库), 快了0.03秒(用substr 0.12秒, 改用left 0.09秒) . 能做出这样的改写, 而且还有提升, 估计此人会把这个函数当成一个优化点, 不懂的人看了, 可能也会认为left 函数比substr函数高效. 看了我上面的解释,估计你们能够明白, 快了那0.03秒不是因为改变了什么函数, 而是第一次执行的时候, 数据做了缓存, 第二次执行从内存读, 自然要比从磁盘读快.

继续分析:

原作者对这个提升不太满意(不到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优化器那样做查询转换, 也可以手工改成上面这个样子,在逻辑上是完全等价的.

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

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

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

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

点评2:

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

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

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

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

(完)