记一次基于CBO的Oracle SQL调优

问题描述

由于数仓建设于10多年前，那会儿直接使用Oracle做存储。数据量变大后运算速度跟不上，改用DataStage进行存储过程运算。

原始SQL长这样【已脱敏】：

select*from 
    db1.tb1 as t1,
    db1.tb2 as t2,
    db1.tb3 as t3,
    db1.tb4 as t4,
    db1.tb5 as t5
where
    t1.col1 = t2.col1
    and t2.col2 = t3.col2
    and t3.col3 = t4.col3
    and t4.col4 = t5.col4
    and t1.col5 =1and t2.col5 =1and t3.col5 =1and t4.col5 =1andexists(select1from 
            db1.tb6 as t6
        where 
            t1.col6=t6.col6
            and(
                t5.col7 <> t6.col8 
                or t5.col9 <> t6.col10 
                ));

老一辈古人认知不足，具有严重的历史局限性【当然几年后回望今朝，也会嘲讽现在的自己认知肤浅，有很强的历史局限性】，并没有想象到这个SQL涉及的表10多年后数据量会如此庞大，达到千万行的级别【当然还有几十e行这种级别的表，Oracle还是很强大，换MySQL可能早就狗带了】。于是问题产生：几年前可以2小时跑完的DataStage任务，近一年居然8h不一定跑完，甚至出现跨多天的情况，在离线T+1中当然是不容许出现任务跨天的情况【毕竟还有后续任务等待调度】。于是必须考虑进行SQL调优。

问题分析

首先执行外层SQL：

selectcount(*)from 
    db1.tb1 as t1,
    db1.tb2 as t2,
    db1.tb3 as t3,
    db1.tb4 as t4,
    db1.tb5 as t5
where
    t1.col1 = t2.col1
    and t2.col2 = t3.col2
    and t3.col3 = t4.col3
    and t4.col4 = t5.col4
    and t1.col5 =1and t2.col5 =1and t3.col5 =1and t4.col5 =1;

数据量是2e行这个级别。Oracle还算给力，分分钟跑完了。显然外层SQL及数据量尚且处于比较正常的水平。

乍一看这个SQL也比较正常，并没有什么复杂函数运算。

执行如下SQL：

selectcount(1)from 
    db1.tb1 as t1,
    db1.tb6 as t6
where 
    t1.col6=t6.col6

数据量在8千万行这个级别。exists里的SQL并不能直接copy出来执行，于是稍作改动：

selectcount(1)from 
    db1.tb1 as t1,
    db1.tb5 as t5,
    db1.tb6 as t6
where 
    t1.col6=t6.col6
    and(
        t5.col7 <> t6.col8 
        or t5.col9 <> t6.col10 
    );

数据量在2e这个级别！！！显然问题出现在这里！！！

当外层SQL跑出2千万行数据时，会根据exists的子集做filter操作。可想而知，执行过程大概率是从外层SQL的子集中遍历数据，并与exists子集的数据做一次对比，也就是说，保守估计都会有2 0000 0000 X 2 0000 0000 = 4 0000 0000 0000 0000 = 4ee次遍历操作！！！exists这波操作直接搞出了笛卡尔积。

伟大的HBase也不过列数用百万为基本单位，行数用10e为基本单位。好家伙，这种数据量居然还有2/3的概率能运算出来【当然要跨天】！！！能写出这句SQL的神仙估计也没有想象到伟大的Oracle居然有这么大的潜力！！！换成MySQL不知道已经狗带过多少次了。。。

开始调优

虽然笔者当时初来乍到，完全不了解业务，当然也不清楚这个SQL的目的，但是很容易看出，应该是想在外层SQL的数据集中filter掉不符合条件的子集。虽然笔者在MySQL和Hive中并没有使用过这个exists，大概也能判断出这是个必要条件。故对SQL进行改写：

selectcount(*)from
    db1.tb1 as t1,
    db1.tb2 as t2,
    db1.tb3 as t3,
    db1.tb4 as t4,
    db1.tb5 as t5,
    db1.tb6 as t6
where 
    t1.col1 = t2.col1
    and t2.col2 = t3.col2
    and t3.col3 = t4.col3
    and t4.col4 = t5.col4
    and t1.col5 =1and t2.col5 =1and t3.col5 =1and t4.col5 =1and t1.col6 = t6.col6
    and(
        t5.col7 != t6.col8
        or t5.col9 != t6.col10
        );

先把用到的表都给做个笛卡尔积，在where中过滤脏数据并做关联，大部分条件是一致的。但t1是个大表，t6是个小表。那么t1、t2、t3、t4、t5、t6根据这些条件：

where 
    t1.col1 = t2.col1
    and t2.col2 = t3.col2
    and t3.col3 = t4.col3
    and t4.col4 = t5.col4
    and t1.col5 =1and t2.col5 =1and t3.col5 =1and t4.col5 =1and t1.col6 = t6.col6

做filter后的数据量也就在十e这个级别，t5和t6关联不上的情况其实不算很多【绝对不会出现万的级别】，那么在十e级别的数据集再次filter，显然是要比4ee次操作少了6个数量级的运算量，自然SQL的运行速度就得以显著提升。调优后Oracle SQL执行时间从跨多天锐减到分钟级。数据链中下游调度任务也不会出现像之前那样直接错一大串的情况。

总结归纳

之前做数据库开发的SQL Boy们其实只会根据业务需要写几句SQL，并不会考虑数据量、运算次数这类问题。在平缓过渡到大数据攻城狮或者ETL攻城狮、数仓攻城狮这类大部分工作还是只需要会SQL就能凑合着胜任的SQL Boy岗位后，并没有什么本质的改变。大数据的各种组件、思想正是为了解决传统方式解决不了的大数据问题才产生的，在SQL编写及调优时，CBO是个很值得考虑的方案。

高版本的Hive、Spark、Flink其实都有自带的CBO优化器，尤其是Spark3对数据倾斜及SQL调优做了大量优化，这些基于代价的优化器比起手动统计数据量和手动调优还是要先进很多。但是谓词下推之类的优化，有时候并不一定能自动优化，必要的时候还是需要手动优化。SQL虽然显著降低了数据开发的门槛，但是对优化性能并没有任何帮助，SQL也很难排查性能问题，调优有时候反倒是一件很头疼的事。

笔者更喜欢DSL方式，直接使用各种算子做操作，强制按照某些顺序执行运算步骤。Spark的RDD操作有时候比SQL性能强很多。不过大数据SQL化是潮流，成也SQL，败也SQL。