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(微信同号),相互学习,共同进步。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤