SQL改写案例2

postgresql 并没有像 oracle 、dm 有这么丰富的 hint，在不改 sql 的情况下能干预执行计划。

如果想学好 postgresql、kingbase、MySQL  的sql 调优，sql 的等价改写是必须要掌握的技巧，今天分享一个案例，简单的标量子查询改左连接。

 

环境介绍：

 学生表 和 课程表，表结构、索引、表的数据量如上图。

这边我用的是 pg15 的版本，因为 pg15优化器性能的提升在pg12以及之前的版本非常大， 笔者有测试过不同场景的复杂 sql 。

 

慢sql以及执行计划如下：

explain analyze select sc.CLS_ID,
       sc.CLS_NAME,
       (select count(s.STU_ID) from STUDENT s where s.STU_CLASSID = sc.CLS_ID)
from STU_CLASS sc;

 

该sql的语义从2千万行的学生表统计出不同课程id的学员分别是多少，执行时间消耗了1m8s， 很慢。

但是这条sql 笔者在相同配置的 orace 11.2.0.4 环境中跑过，oracle 执行时间需要 1m51s。

这点可以看出 pg15 优化器性能提升是非常巨大的，甚至感觉可以吊打市面上除了 ORACLE 以外任何关系型数据库的sql优化器。(个人想法，有兴趣朋友可以测试下)

话说回来，这条sql 在不改写的情况下，加索引或者是使用 hint 都不好使，就需要左连接方式等价的方式改写这条sql。

-- 改写1、标量子查询改左连接：
explain analyze
select sc.CLS_ID,
       sc.CLS_NAME,
       count(s.stu_id)
       from stu_class sc left join student s on (sc.cls_id = s.stu_classid)
group by sc.CLS_ID, sc.CLS_NAME;

 

 

 

-- 改写2、使用分析函数 + 左连接改写
explain analyze
select distinct sc.CLS_ID,
                sc.CLS_NAME,
                count(s.stu_id) over (partition by sc.CLS_ID,sc.CLS_NAME) count_stu_id
from stu_class sc
         left join student s on (sc.cls_id = s.stu_classid);

可以看到改写1、标量子查询改左连接执行速度最快，原来sql 需要1分8秒的执行时间，下降到 7.7 s 的执行时间，已经是巨大的提升，Oracle中执行该 sql 需要 6s。

再看改写2、使用分析函数 + 左连接改写，虽然降低到 34s 的执行时间，这种改法在本案例中效果并不出众，但是也有缩减一半执行时间的提升效果。

注意：标量子查询等价改下，使用分析函数并不是最佳手段，这里演示这种改法只是为了扩展大家sql改写的思路，实际情况下，自连接查询使用分析函数等价改写的效果性能是最好的，因为表只访问了一次。

从上面的案例可以知道，生产环境中大部分标量子查询的sql都可以等价改写成左连接，这样程序运行起来性能会有比较大的提升。

 

通过以上sql案例改写的学习，给大家出个简单的改写题，大家有兴趣可以下去玩玩。

scott.emp表没有的话，可以百度，一大堆创建的语句。

select e1.empno,
       e1.ename,
       e1.sal,
       (select sum(sal)
        from EMP e2
        where e2.sal >= e1.sal - 100
          and e2.sal <= e1.sal) sum_sal
from emp e1;

改写要求（任意数据库改写都可以）：

改写方式1：要求上面 sql 改成左连接。

改写方式2：要求上面 sql 改成分析函数 + 左连接。

改写方式3：要求上面sql 只放问表emp一次，不使用表连接。

方式1、方式2 通过上面案例的学习，相信大家很快就能做出来，方式3的改写可能就需要思考一下，如果实在想不出来也可以联系我，告诉你答案，嘿嘿！

想交流 SQL调优、PLSQL、SQL等价改写、JAVA 的朋友可以联系我 18902234014（微信同号），相互学习，共同进步。