关于更新20w+数据量的SQL性能优化

最新推荐文章于 2024-08-30 08:42:11 发布

Jane Chiu

最新推荐文章于 2024-08-30 08:42:11 发布

阅读量1.9k

点赞数 1

分类专栏：性能优化文章标签： SQL

本文链接：https://blog.csdn.net/zhao__fang/article/details/84334142

版权

性能优化专栏收录该内容

8 篇文章 1 订阅

订阅专栏

前段时间，有个同事找我，让我帮忙优化一下DML SQL语句，说是update更新很慢,一分钟值更新260条数据,但是里面有20W以上的数据需要更新。
原SQL(F5执行效率Cost 24099646913):

UPDATE jb_cost_estimation_customer e
   SET (total_income, income, customer_price) =
       (SELECT SUM(sc.cost) AS total_income,
               round(SUM(sc.cost / (1 + nvl(scr2.remark_text, 0) / 100)), 2) AS income,
               rf.shipment_refnum_value AS customer_price
          FROM jb_cost_estimation_customer ec ---
          LEFT JOIN shipment_cost_remark scr
            ON ec.shipment_gid = scr.remark_text
           AND scr.remark_qual_gid = 'JBL.COST_应付运单号'
          LEFT JOIN shipment_cost sc ---
            ON scr.shipment_cost_seqno = sc.shipment_cost_seqno
          LEFT JOIN shipment s
            ON s.shipment_gid = sc.shipment_gid
          LEFT JOIN shipment_refnum rf
            ON s.shipment_gid = rf.shipment_gid
           AND rf.shipment_refnum_qual_gid = 'JBL.单价'
          LEFT JOIN shipment_cost_remark scr2 --
            ON scr2.shipment_cost_seqno = scr.shipment_cost_seqno
           AND scr2.remark_qual_gid = 'JBL.COST_税率'
         WHERE ec.shipment_gid = e.shipment_gid
        --1=1
         GROUP BY rf.shipment_refnum_value, ec.shipment_gid);

其中Select语句的条数统计如下：

SELECT COUNT(1) --215253
  FROM (SELECT SUM(sc.cost) AS total_income,
               round(SUM(sc.cost / (1 + nvl(scr2.remark_text, 0) / 100)), 2) AS income,
               rf.shipment_refnum_value AS customer_price,
               ec.shipment_gid
          FROM jb_cost_estimation_customer ec ---
          LEFT JOIN shipment_cost_remark scr
            ON ec.shipment_gid = scr.remark_text
           AND scr.remark_qual_gid = 'JBL.COST_应付运单号'
          LEFT JOIN shipment_cost sc ---
            ON scr.shipment_cost_seqno = sc.shipment_cost_seqno
          LEFT JOIN shipment s
            ON s.shipment_gid = sc.shipment_gid
          LEFT JOIN shipment_refnum rf
            ON s.shipment_gid = rf.shipment_gid
           AND rf.shipment_refnum_qual_gid = 'JBL.单价'
          LEFT JOIN shipment_cost_remark scr2 --
            ON scr2.shipment_cost_seqno = scr.shipment_cost_seqno
           AND scr2.remark_qual_gid = 'JBL.COST_税率'
        --WHERE ec.shipment_gid = e.shipment_gid
        --1=1
         GROUP BY rf.shipment_refnum_value, ec.shipment_gid) e;

首先我根据表关联的逻辑适当调整SQL（F5执行效率Cost 8672729249，经调整Cost下降两位数）

UPDATE jb_cost_estimation_customer e
   SET (total_income, income, customer_price) =
       (SELECT SUM(sc.cost) AS total_income,
               round(SUM(sc.cost / (1 + nvl(scr2.remark_text, 0) / 100)), 2) AS income,
               rf.shipment_refnum_value AS customer_price
          FROM jb_cost_estimation_customer ec,
               shipment_cost_remark        scr,
               shipment_cost_remark        scr2,
               shipment_cost               sc,
               shipment_refnum             rf
         WHERE ec.shipment_gid = scr.remark_text
           AND scr.remark_qual_gid = 'JBL.COST_应付运单号'
           AND scr.shipment_cost_seqno = scr2.shipment_cost_seqno
           AND scr2.remark_qual_gid = 'JBL.COST_税率'
           AND scr.shipment_cost_seqno = sc.shipment_cost_seqno
           AND sc.shipment_gid = rf.shipment_gid
           AND rf.shipment_refnum_qual_gid = 'JBL.单价'
              ---------------------------------------------------------------------------
           AND ec.shipment_gid = e.shipment_gid
        --------------------------------------------------------------------
         GROUP BY rf.shipment_refnum_value, ec.shipment_gid);

方案1：使用Merge语句，其F5执行效率Cost 157068
在这里插入图片描述