干货 | SQL优化之SQL transformation

0、导读

本次我们邀请到韩国Infobridge的SQL优化专家郑老师，同时他也是知数堂的MySQL课程学员哟。在郑老师分享的《SQL优化之SQL transformation》中内容干货多多，教你在MySQL中如何进行SQL改写优化，实现数千倍的效率提升，小伙伴们快来围观吧。

提示：只看PPT的话，有很多干货信息并没体现，建议直接看视频，效果更佳。

1、资料发布

本次公开课的PPT、视频以及课中提到的相关学习资料均已上传到百度云盘，链接: https://pan.baidu.com/s/1sltrS2x。欢迎转存及转发（扫描下方二维码识别）

知数堂培训全新MySQL DBA课程和Python运维开发班持续招生中，扫描下方二维码加入QQ群 529671799 获得最新信息。

关于知数堂

http://zhishuedu.com

“知数堂培训”是由资深MySQL专家叶金荣、吴炳锡联合推出专业优质在线培训课程，当前主要有MySQL DBA实战优化和Python运维开发两个课程，是业内最有良心、最有品质的培训课程。

2、分享内容

小编备注：PPT中的SQL截图不够清晰，下面贴上SQL原文

SELECT sl.sku,sl.positionCode,ps.positionLev,

  SUM(CASE WHEN sk.skipQty > 0 THEN 0 ELSE CASE WHEN ps.positionLev = 3 

   THEN (IFNULL(sl.quantity,0)-IFNULL(ll.lockedQty,0)+IFNULL(fl.frozenQty,0)) ELSE 0 END END) jhqty,

  SUM(CASE WHEN sk.skipQty > 0 THEN 0 ELSE CASE WHEN ps.positionLev = 4 

           THEN (IFNULL(sl.quantity,0)-IFNULL(ll.lockedQty,0)+IFNULL(fl.frozenQty,0)) ELSE 0 END END) bhqty,

  SUM(CASE WHEN sk.skipQty > 0 THEN 0 ELSE CASE WHEN ps.positionLev = 5 

           THEN (IFNULL(sl.quantity,0)-IFNULL(ll.lockedQty,0)+IFNULL(fl.frozenQty,0)) ELSE 0 END END) byqty,

  SUM(CASE WHEN sk.skipQty > 0 THEN 0 ELSE CASE WHEN ps.positionLev = 6 

           THEN (IFNULL(sl.quantity,0)-IFNULL(ll.lockedQty,0)+IFNULL(fl.frozenQty,0)) ELSE 0 END END) ryqty

  FROM wms_si_stock sl

  JOIN wms_bd_product_information p ON p.sku=sl.sku

  JOIN wms_bd_positions ps ON ps.warehouseIdentity=sl.warehouseIdentity AND ps.positionCode=sl.positionCode

  LEFT JOIN (SELECT l.warehouseIdentity,l.sku,l.positionCode,l.isbox,l.boxNo,IFNULL(SUM(quantity),0) lockedQty

  FROM wms_si_locked l GROUP BY l.warehouseIdentity,l.sku,l.positionCode,l.isbox,l.boxNo) ll

  ON sl.warehouseIdentity=ll.warehouseIdentity AND sl.sku=ll.sku AND sl.positionCode=ll.positionCode AND

  sl.isbox=ll.isbox AND sl.boxNo=ll.boxNo

  LEFT JOIN (SELECT warehouseIdentity,sku,positionCode ,SUM(quantity) frozenQty FROM wms_so_frozen WHERE STATUS=1

  AND 0>quantity AND checkPlanId !='' AND checkPlanId IS NOT NULL GROUP BY warehouseIdentity,sku,positionCode) fl

  ON sl.warehouseIdentity=fl.warehouseIdentity AND sl.sku=fl.sku AND sl.positionCode=fl.positionCode

  LEFT JOIN (SELECT warehouseIdentity,sku,positionCode,boxNo,SUM(quantity) skipQty FROM wms_si_skiped WHERE STATUS=0

  and warehouseIdentity = '1'   

  GROUP BY warehouseIdentity,sku,positionCode,boxNo) sk 

        ON sk.warehouseIdentity=sl.warehouseIdentity 

        AND sk.sku=sl.sku 

    AND sk.positionCode=sl.positionCode AND sk.boxNo=sl.boxNo

  where 1=1   

  and sl.warehouseIdentity = '1'   

  AND ps.positionLev != 7

  GROUP BY sl.sku,sl.positionCode,ps.positionLev,sl.warehouseIdentity\G

修改前的执行计划：

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: ps

         type: ALL

possible_keys: idx_pos_pos,idx_pos_lev,idx_pos_posLev

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 108416

        Extra: Using where; Using temporary; Using filesort

*************************** 2. row ***************************

           id: 1

  select_type: PRIMARY

        table: sl

         type: ref

possible_keys: idx_stock_all,idx_st_skuP,idx_st_posCd

          key: idx_st_posCd

      key_len: 152

          ref: wms.ps.positionCode

         rows: 1

        Extra: Using where

*************************** 3. row ***************************

           id: 1

  select_type: PRIMARY

        table: p

         type: ref

possible_keys: idx_proif_sku

          key: idx_proif_sku

      key_len: 153

          ref: wms.sl.sku

         rows: 1

        Extra: Using index

*************************** 4. row ***************************

           id: 1

  select_type: PRIMARY

        table: <derived2>

         type: ref

possible_keys: <auto_key0>

          key: <auto_key0>

      key_len: 368

          ref: wms.ps.warehouseIdentity,wms.sl.sku,wms.ps.positionCode,wms.sl.isbox,wms.sl.boxNo

         rows: 10

        Extra: NULL

*************************** 5. row ***************************

           id: 1

  select_type: PRIMARY

        table: <derived3>

         type: ref

possible_keys: <auto_key0>

          key: <auto_key0>

      key_len: 305

          ref: wms.ps.warehouseIdentity,wms.sl.sku,wms.ps.positionCode

         rows: 10

        Extra: NULL

*************************** 6. row ***************************

           id: 1

  select_type: PRIMARY

        table: <derived4>

         type: ref

possible_keys: <auto_key0>

          key: <auto_key0>

      key_len: 367

          ref: wms.ps.warehouseIdentity,wms.sl.sku,wms.ps.positionCode,wms.sl.boxNo

         rows: 2

        Extra: NULL

*************************** 7. row ***************************

           id: 4

  select_type: DERIVED

        table: wms_si_skiped

         type: ref

possible_keys: idx_skip_sku,idx_skip_stat

          key: idx_skip_stat

      key_len: 2

          ref: const

         rows: 22

        Extra: Using where; Using temporary; Using filesort

*************************** 8. row ***************************

           id: 3

  select_type: DERIVED

        table: wms_so_frozen

         type: ALL

possible_keys: idx_fz_skuP

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 37532

        Extra: Using where; Using temporary; Using filesort

*************************** 9. row ***************************

           id: 2

  select_type: DERIVED

        table: l

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 15957

        Extra: Using temporary; Using filesort

9 rows in set (0.00 sec)

执行计划中应该关注哪些重点信息可以参考这篇文章：FAQ系列 | EXPLAIN执行计划中要重点关注哪些要素

修改后的SQL原文：

SELECT s.sku,s.positionCode,s.positionLev,

 SUM(CASE WHEN s.skipQty > 0 THEN 0 ELSE CASE WHEN s.positionLev = 3 

          THEN (IFNULL(s.quantity,0)-IFNULL(s.lockedQty,0)+IFNULL(s.frozenQty,0)) ELSE 0 END END) jhqty,

 SUM(CASE WHEN s.skipQty > 0 THEN 0 ELSE CASE WHEN s.positionLev = 4 

          THEN (IFNULL(s.quantity,0)-IFNULL(s.lockedQty,0)+IFNULL(s.frozenQty,0)) ELSE 0 END END) bhqty,

 SUM(CASE WHEN s.skipQty > 0 THEN 0 ELSE CASE WHEN s.positionLev = 5 

          THEN (IFNULL(s.quantity,0)-IFNULL(s.lockedQty,0)+IFNULL(s.frozenQty,0)) ELSE 0 END END) byqty,

 SUM(CASE WHEN s.skipQty > 0 THEN 0 ELSE CASE WHEN s.positionLev = 6 

          THEN (IFNULL(s.quantity,0)-IFNULL(s.lockedQty,0)+IFNULL(s.frozenQty,0)) ELSE 0 END END) ryqty

 from (  

 SELECT sl.sku,sl.positionCode,ps.positionLev,sl.warehouseIdentity,sl.quantity

 (SELECT IFNULL(SUM(quantity),0) 

 FROM wms_si_locked ll

 ON sl.sku=ll.sku AND sl.positionCode=ll.positionCode and  sl.warehouseIdentity=ll.warehouseIdentity   AND

 sl.isbox=ll.isbox AND sl.boxNo=ll.boxNo) lockedQty,

 (SELECT  SUM(f1.quantity) frozenQty 

 FROM wms_so_frozen fl WHERE fl.STATUS=1

 AND 0>fl.quantity AND fl.checkPlanId !='' AND fl.checkPlanId IS NOT NULL   

 and sl.sku=fl.sku AND sl.positionCode=fl.positionCode AND sl.warehouseIdentity=fl.warehouseIdentity )frozenQty,

 (SELECT SUM(sk.quantity)  FROM wms_si_skiped sk 

  WHERE sk.STATUS=0

 and sk.warehouseIdentity = '1'      and sk.warehouseIdentity=sl.warehouseIdentity AND sk.sku=sl.sku AND

 sk.positionCode=sl.positionCode AND sk.boxNo=sl.boxNo) skipQty

 FROM wms_si_stock sl

 JOIN wms_bd_product_information p ON p.sku=sl.sku

 JOIN wms_bd_positions ps ON ps.warehouseIdentity=sl.warehouseIdentity AND ps.positionCode=sl.positionCode 

 where 1=1   

 and sl.warehouseIdentity = '1'   

 AND ps.positionLev != 7

 ) s

 GROUP BY s.sku,s.positionCode,s.positionLev,s.warehouseIdentity;

修改前SQL原文：

SELECT 

  slo.acid ,

  COALESCE(

    ROUND(

      SUM(

        CASE

          WHEN DATE_FORMAT(slo.ctime, '%Y-%m-%d') = DATE_FORMAT( DATE_SUB('2017-02-21', INTERVAL 1 DAY),'%Y-%m-%d') 

          THEN slo.amunt 

        END

      ),2),0 ) AS beforeDayMoney,

  COALESCE(

    ROUND(

      SUM(

        CASE

          WHEN slo.ctime >= '2017-02-21' AND slo.ctime <= '2017-02-22' 

          THEN slo.amunt 

        END

      ),2),0) AS toDayMoney,

  COUNT(

    CASE

      WHEN slo.ctime >= '2017-02-21' AND slo.ctime <= '2017-02-22' 

      THEN slo.oid 

    END ) AS toDayCount,

  COALESCE(

    ROUND(

      SUM(

        CASE

          WHEN DATE_FORMAT(slo.ctime, '%Y-%m') = DATE_FORMAT('2017-02-21', '%Y-%m') AND slo.ctime <= '2017-02-22' 

          THEN slo.amunt 

        END ),2 ), 0) AS memMoney,

  COUNT(

    CASE

      WHEN DATE_FORMAT(slo.ctime, '%Y-%m') = DATE_FORMAT('2017-02-21', '%Y-%m') AND slo.ctime <= '2017-02-22' 

      THEN slo.oid 

    END ) AS memCount 

FROM

  table1 slo 

WHERE slo.FLAG = 0  AND slo.STATUS = '4' 

GROUP BY slo.acid;

修改后SQL原文：

SELECT 

  slo.acid ,

  COALESCE(

    ROUND(

      SUM(

        CASE

          WHEN DATE_FORMAT(slo.ctime, '%Y-%m-%d') = DATE_FORMAT( DATE_SUB('2017-02-21', INTERVAL 1 DAY),'%Y-%m-%d') 

          THEN slo.amunt 

        END

      ),2),0 ) AS beforeDayMoney,

  COALESCE(

    ROUND(

      SUM(

        CASE

          WHEN slo.ctime >= '2017-02-21' AND slo.ctime <= '2017-02-22' 

          THEN slo.amunt 

        END

      ),2),0) AS toDayMoney,

  COUNT(

    CASE

      WHEN slo.ctime >= '2017-02-21' AND slo.ctime <= '2017-02-22' 

      THEN slo.oid 

    END ) AS toDayCount,

  COALESCE(

    ROUND(

      SUM(

        CASE

          WHEN DATE_FORMAT(slo.ctime, '%Y-%m') = DATE_FORMAT('2017-02-21', '%Y-%m') AND slo.ctime <= '2017-02-22' 

          THEN slo.amunt 

        END ),2 ), 0) AS memMoney,

  COUNT(

    CASE

      WHEN DATE_FORMAT(slo.ctime, '%Y-%m') = DATE_FORMAT('2017-02-21', '%Y-%m') AND slo.ctime <= '2017-02-22' 

      THEN slo.oid 

    END ) AS memCount 

FROM   table1 slo 

WHERE slo.FLAG = 0  AND slo.STATUS = '4' 

and slo.ctime >= '2017-02-21' AND slo.ctime <= '2017-02-22'

GROUP BY slo.acid;

最后再次感谢大家对知数堂培训的支持和关注！

参考：

FAQ系列 | EXPLAIN执行计划中要重点关注哪些要素

[MySQL优化案例]系列 — 分页优化

比较全面的MySQL优化参考（上篇）

比较全面的MySQL优化参考（下篇）

MySQL DBA面试全揭秘

近期公开课发布

干货分享《如何系统的学好Python》

公开课发布《MySQL Group Replication思想》

公开课发布《MySQL体系结构及常见高可用架构》

找到MySQL服务器发生SWAP罪魁祸首

DBA如何不用和开发撕逼直接做SQL优化？

云数据库实现原理和海量运维方法

大型网站架构及优化

不再加原创

喜欢就转发

打赏可勾搭