数据库with as 用法及with as 改写sql进行优化

最新推荐文章于 2025-04-16 16:18:31 发布

键盘上的五花肉

最新推荐文章于 2025-04-16 16:18:31 发布

阅读量2.1k

点赞数 1

分类专栏：数据库知识文章标签：数据库

本文链接：https://blog.youkuaiyun.com/qq_42902673/article/details/104031013

版权

数据库知识专栏收录该内容

10 篇文章

订阅专栏

with as 在oracle中用法：

WITH wa_tab_name AS (SELECT * FROM emp)
SELECT * FROM wa_tab_name;
#相当于在将emp表临时存放在了wa_tab_name中

再如：

WITH wa_tab_name AS (SELECT deptno,COUNT(*) AS cnt FROM emp GROUP BY deptno)
SELECT * FROM dept a JOIN wa_tab_name b 
ON a.deptno = b.deptno; 
#求每个部门多少人，先在wa_tab_name中计算出来临时存放，再进行关联

关于with as优化改写sql
本人在前段时间工作中，当天正好没啥事，看到身边同事在看一个多表关联的sql，我就闲来问了一句他干嘛呢？具体这个sql应用是这样的：
是一个用于前台展示的分页语句，用来展示每日跑批状态记录表。对应的后台数据库是postgresql数据库，一共4个表关联，还有1个表用于where过滤，每次在前台查询一次最少耗时2分30秒+。如果随着每个月数据量稍微增加，sql将执行更长时间。
sql大概如下（因为生产脚本，不变透露，所以我凭印象写个大概的样子）

SELECT *
  FROM (SELECT A.COL_NAME1,
               A.COL_NAME2,
               A.COL_NAME3,
               A.COL_NAME4,
               B.COL_NAME1,
               COUNT(C.COL_NAME1)
          FROM A --3000+数据量
          JOIN B --4000+数据量
            ON A.COL_NAME = B.COL_NAME
           AND A.BAT_NO = B.BAT_NO
          JOIN C --4000+数据量
            ON A.COL_NAME = C.COL_NAME
           AND A.BAT_NO = C.BAT_NO
          LEFT JOIN (SELECT D.BAT_NO, D.COL_NAME, COUNT(*)
                      FROM D --20000+数据量
                     GROUP BY D.BAT_NO, D.COL_NAME) D
            ON A.COL_NAME = D.COL_NAME
           AND A.BAT_NO = D.BAT_NO
         WHERE A.COL_NAME = '$date'
           AND A.BAT_NO = '$hostid'
           AND A.OBJECT_NAME || A.ID NOT IN
               (SELECT E.OBJECT_NAME || E.ID
                  FROM E --4000+数据量
                 WHERE E.COL_NAME = '$date'
                   AND E.BAT_NO = '$hostid')
         GROUP BY A.COL_NAME1,
                  A.COL_NAME2,
                  A.COL_NAME3,
                  A.COL_NAME4,
                  B.COL_NAME1) 
LIMIT '$end_no' OFFSET '$start_no' ;

因为是postgresql数据库，我之前也没有接触过啊，这执行计划打开我也不一定看的懂啊，怎么办？索性不看执行计划了，靠自己硬解析sql，定位问题出在了哪里，毕竟我写sql也好几年了，像有笛卡尔积之类的垃圾sql还是应该能直接通过sql看出来的。
首先我看各个表的数据量，看了最大的数据量才2w多，其余的都是4000条左右的数据，这对我一个数仓人员来说随便写sql都应该问题不大啊，为什么会有执行2分多钟的情况呢？（有可能是mpp数据库搞多了，所以才有这性能，但是我以前在oralce上开发时候这么点数据量也不会跑这么慢的）
数据量看着不大啊，那我在看看sql有没有标量子查询之类的啊，我仔细一看，这sql要是我写我也得这样写，唯一我要改的是where条件拼接哪里，（where条件多字段直接过滤就行，为啥子拼接呢，难道是这个地方因为拼接导致的？）我的改写如下：

SELECT *
FROM (SELECT A.COL_NAME1,
            A.COL_NAME2,
            A.COL_NAME3,
            A.COL_NAME4,
            B.COL_NAME1,
            COUNT(C.COL_NAME1)
       FROM A --3000+数据量
       JOIN B --4000+数据量
         ON A.COL_NAME = B.COL_NAME
        AND A.BAT_NO = B.BAT_NO
       JOIN C --4000+数据量
         ON A.COL_NAME = C.COL_NAME
        AND A.BAT_NO = C.BAT_NO
       LEFT JOIN (SELECT D.BAT_NO, D.COL_NAME, COUNT(*)
                   FROM D --20000+数据量
                  GROUP BY D.BAT_NO, D.COL_NAME) D
         ON A.COL_NAME = D.COL_NAME
        AND A.BAT_NO = D.BAT_NO
      WHERE A.COL_NAME = '$date'
        AND A.BAT_NO = '$hostid'
        AND (A.OBJECT_NAME , A.ID) NOT IN
            (SELECT E.OBJECT_NAME , E.ID
               FROM E --4000+数据量
              WHERE E.COL_NAME = '$date'
                AND E.BAT_NO = '$hostid')
      GROUP BY A.COL_NAME1,
               A.COL_NAME2,
               A.COL_NAME3,
               A.COL_NAME4,
               B.COL_NAME1) 
LIMIT '$end_no' OFFSET '$start_no' ;

拉到数据库跑了一下，结果耗时还是2分30秒多，那么问题就不在这里了啊。这只能拿出我的“笨办法”了，要不然搞不出问题原因那岂不是很没面子啊？我就将这个sql拆分，让其a先与b关联加上where 条件，跑了一下，很快，问题不在这。继续往下，a join b join c ，结果跑了一下还是很快；那我进行了单独查询d，

SELECT D.BAT_NO, D.COL_NAME, COUNT(*)
                   FROM D --20000+数据量
                  GROUP BY D.BAT_NO, D.COL_NAME;
                  #跑了一下，执行的很快呢--返回上千条数据

我又重新关联，结果就跑的很慢了。那么问题定位出来了，就是出在与d表关联时候了。单独跑d表的这个sql很快，但是执行起来表关联很慢，我想难道后台执行计划是嵌套循环，而且每返回1条数据就查一次这个表吗？
这如果是生产跑批脚本，我一般就把d表拆出来临时建立个临时表了，然后在下一步在执行关联，可是此时这个sql是前台直接调用的sql啊，没办法，我只能一个sql写出来。这时候想到用with as 可以生成临时表，就是不知道postgresql支持不支持。我先在数据库里测试了一下with as 语法支持，万幸支持此语法，那这就好办了，我将d表查询放在with as 中，这样这个表就只用查询一次了。试一下效果：

WITH WA_TAB_NAME AS
 (SELECT D.BAT_NO, D.COL_NAME, COUNT(*)
    FROM D --20000+数据量
   GROUP BY D.BAT_NO, D.COL_NAME)
SELECT *
  FROM (SELECT A.COL_NAME1,
               A.COL_NAME2,
               A.COL_NAME3,
               A.COL_NAME4,
               B.COL_NAME1,
               COUNT(C.COL_NAME1)
          FROM A --3000+数据量
          JOIN B --4000+数据量
            ON A.COL_NAME = B.COL_NAME
           AND A.BAT_NO = B.BAT_NO
          JOIN C --4000+数据量
            ON A.COL_NAME = C.COL_NAME
           AND A.BAT_NO = C.BAT_NO
          LEFT JOIN WA_TAB_NAME D
            ON A.COL_NAME = D.COL_NAME
           AND A.BAT_NO = D.BAT_NO
         WHERE A.COL_NAME = '$date'
           AND A.BAT_NO = '$hostid'
           AND (A.OBJECT_NAME, A.ID) NOT IN
               (SELECT E.OBJECT_NAME, E.ID
                  FROM E --4000+数据量
                 WHERE E.COL_NAME = '$date'
                   AND E.BAT_NO = '$hostid')
         GROUP BY A.COL_NAME1,
                  A.COL_NAME2,
                  A.COL_NAME3,
                  A.COL_NAME4,
                  B.COL_NAME1) LIMIT '$end_no' OFFSET '$start_no';

这次怀着忐忑的心情又跑了一下，因为如果还是执行不快有可能是分页的问题导致的，那分页语句优化更难了。这次测试了一下，刚一点执行，1秒多中出来了结果，我还以为我看花了，结果还是上次执行的呢，后来我又点了一次，确实1秒多一点。激动啊，这是我优化改写sql中效果最明显的一次了，赶紧告诉同事，同事亲测，直说“牛，厉害，佩服”。哈哈哈，我也就是蒙的啦，不过是真的开心啊，后来改到前台脚本中，在前台展示需要2秒出结果，不过已经很快了，在也不用向以前一样点了一下鼠标等半天了。

好了，这次就写到这了，马上过年了，有sql问题的小伙伴欢迎留言，一起学习探讨。祝各位鼠年大吉，身体健康，工作顺利。