不懂业务的SQL优化方法

看着如下长长的SQL，对于其要实现的功能一无所知，开发人员需要根据领导的需要调整该SQL并导出产生的数据，但每次运行都要5分钟左右，对对于开发，调试和领导确认都不能接受，开发人员直接将此SQL转给我优化，由于此SQL并不写入应用且手工执行频率很高，于是最直接的方法就是先并行加快sql的执行速度
WITH T_MAST AS( 
SELECT 
        T3.BASE_YW
        ,T2.USER_ID
        ,T2.USER_NM   
        ,T2.BRNC_ID
        ,(SELECT SORG_NM FROM MCS_HQ.MA_SORG T WHERE T.SORG_ID = T2.BRNC_ID) BRNC_NM
        ,T2.OFFC_ID
        ,(SELECT SORG_NM FROM MCS_HQ.MA_SORG T WHERE T.SORG_ID = T2.OFFC_ID) OFFC_NM
        ,(SELECT CODE_NM FROM MCS_HQ.CD_CODE_LIST T WHERE T.CODE_DIV = 'USER_TP' AND T.LANG_CD = 'CN' AND T.CODE_CD = T2.USER_TP) USER_TP_NM
        ,COUNT(*)    LONG_CNT
from    MCS_HQ.HI_USER_MENU_ACCS_LOG T1
        ,MCS_HQ.MA_USER T2
        ,MCS_HQ.MA_BASE_YMD T3
WHERE   T1.MENU_ID IN ('M001164','M001126','M001125','M001124','M001123','M001163')
AND     T1.USER_ID = T2.USER_ID
AND     T1.ACCS_DT >= DATE '2014-12-29'
AND     TO_CHAR(T1.ACCS_DT,'yyyymmDD') = T3.BASE_YMD
AND     T2.USER_TP IN ('OFFC','SLMN','BRNC')  --指定用户
AND     T2.USER_USE_ST = 'ACTV'
AND     T2.USER_ID NOT IN ('chokyu','kimbk01') --('CHOKU','KIMBK01');
AND EXISTS(SELECT * FROM MCS_HQ.MA_USER_PROD A WHERE A.USER_PROD_REL_TP = 'MPRD' AND A.USE_YN = 'Y'
AND A.REL_TO_YMD = '99991231' AND A.PROD_CD IN ('GSM','LTE','CDMA','TABLET') AND A.USER_ID = T1.USER_ID)
AND     EXISTS(
            SELECT  *
            FROM    MCS_HQ.MA_USER_ROLE A
                    ,MCS_HQ.MA_ROLE_MENU B
            WHERE   A.USER_ID = T1.USER_ID
            AND     A.USE_YN = 'Y'
            AND     B.ROLE_ID = A.ROLE_ID
            AND     B.USE_YN = 'Y'
            AND     B.MENU_ID IN ('M001164','M001126','M001125','M001124','M001123','M001163')
        )
AND     T2.SUBS_ID = 'SCIC'
GROUP BY T3.BASE_YW
        ,T2.BRNC_ID
        ,T2.OFFC_ID
        ,T2.USER_ID
        ,T2.USER_NM
        ,T2.USER_TP
)
SELECT   MAX(BRNC_NM) BRNC_NM
        ,MAX(OFFC_NM) OFFC_NM
        ,USER_ID USER_ID
        ,MAX(USER_NM) USER_NM   
        ,USER_TP_NM
        ,sum(DECODE(BASE_YW,'201501',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201502',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201503',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201504',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201505',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201506',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201507',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201508',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201509',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201510',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201511',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201512',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201513',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201514',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201515',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201516',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201517',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201518',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201519',LONG_CNT)) W20_USER_CNT
        ,sum(DECODE(BASE_YW,'201520',LONG_CNT)) W20_LOGN_CNT
        ,sum(DECODE(BASE_YW,'201521',LONG_CNT)) W21_LOGN_CNT
        ,sum(DECODE(BASE_YW,'201522',LONG_CNT)) W22_LOGN_CNT
        ,sum(DECODE(BASE_YW,'201523',LONG_CNT)) W23_LOGN_CNT
        ,sum(DECODE(BASE_YW,'201524',LONG_CNT)) W24_LOGN_CNT
        ,sum(DECODE(BASE_YW,'201525',LONG_CNT)) W25_LOGN_CNT
FROM    T_MAST
GROUP BY BRNC_ID
        ,OFFC_ID
        ,USER_ID
        ,USER_TP_NM
ORDER BY
    BRNC_NM
    ,OFFC_NM
    ,USER_ID
    ,USER_TP_NM
要想尽快确认哪些表需要并行提高执行速度，先查看执行计划

SELECT STATEMENT, GOAL = FIRST_ROWS   48563 1 248  
 INDEX RANGE SCAN MCS_HQ UX_MA_SORG_3 1 1 16  
 INDEX RANGE SCAN MCS_HQ UX_MA_SORG_3 1 1 16  
 INDEX RANGE SCAN MCS_HQ UX_CD_CODE_LIST_1 2 1 34  
 SORT ORDER BY   48563 1 248  
  HASH GROUP BY   48563 1 248  
   VIEW MCS_HQ_READ  48561 1 248  
    HASH GROUP BY   48561 1 135  
     NESTED LOOPS SEMI   48560 1 135  
      NESTED LOOPS   48554 1 103  
       NESTED LOOPS   48552 1 56  
        HASH JOIN RIGHT SEMI   48551 1 40  
         VIEW SYS VW_SQ_1 481 37509 412599  
          HASH JOIN   481 37509 1912959  
           TABLE ACCESS FULL MCS_HQ MA_ROLE_MENU 58 145 3480 ("B"."MENU_ID"='M001123' OR "B"."MENU_ID"='M001124' OR "B"."MENU_ID"='M001125' OR "B"."MENU_ID"='M001126' OR "B"."MENU_ID"='M001163' OR "B"."MENU_ID"='M001164') AND "B"."USE_YN"='Y' 
           TABLE ACCESS FULL MCS_HQ MA_USER_ROLE 422 158708 4285116 "A"."USE_YN"='Y' AND "A"."USER_ID"'chokyu' AND "A"."USER_ID"'kimbk01' 
         INLIST ITERATOR       
          TABLE ACCESS BY INDEX ROWID MCS_HQ HI_USER_MENU_ACCS_LOG 48069 56598 1641342 "T1"."USER_ID"'chokyu' AND "T1"."USER_ID"'kimbk01' 
           INDEX RANGE SCAN MCS_HQ X_HI_USER_MENU_ACCS_LOG_1 658 57249   
        INDEX RANGE SCAN MCS_HQ UX_MA_BASE_YMD_1 1 1 16  
       TABLE ACCESS BY INDEX ROWID MCS_HQ MA_USER 2 1 47 "T2"."USER_USE_ST"='ACTV' AND ("T2"."USER_TP"='BRNC' OR "T2"."USER_TP"='OFFC' OR "T2"."USER_TP"='SLMN') AND "T2"."SUBS_ID"='SCIC' 
        INDEX UNIQUE SCAN MCS_HQ PK_MA_USER 1 1  "T2"."USER_ID"'chokyu' AND "T2"."USER_ID"'kimbk01' 
      INLIST ITERATOR       
       TABLE ACCESS BY INDEX ROWID MCS_HQ MA_USER_PROD 6 384534 12305088 "A"."USE_YN"='Y' AND "A"."REL_TO_YMD"='99991231' 
        INDEX UNIQUE SCAN MCS_HQ PK_MA_USER_PROD 5 1  "A"."USER_ID"'chokyu' AND "A"."USER_ID"'kimbk01'
从计划中不难看出MA_ROLE_MENU，MA_USER_ROLE先做了全表扫描，然后又和HI_USER_MENU_ACCS_LOG做了 HASH JOIN RIGHT SEMI 连接，且做连接时消耗的成本极高，于是设法
在这两部采用并行，提高执行效率，增加并行后的sql如下

WITH T_MAST AS( 
SELECT  /*+PARALLEL(T1,8)*/
        T3.BASE_YW
        ,T2.USER_ID
        ,T2.USER_NM   
        ,T2.BRNC_ID
        ,(SELECT SORG_NM FROM MCS_HQ.MA_SORG T WHERE T.SORG_ID = T2.BRNC_ID) BRNC_NM
        ,T2.OFFC_ID
        ,(SELECT SORG_NM FROM MCS_HQ.MA_SORG T WHERE T.SORG_ID = T2.OFFC_ID) OFFC_NM
        ,(SELECT CODE_NM FROM MCS_HQ.CD_CODE_LIST T WHERE T.CODE_DIV = 'USER_TP' AND T.LANG_CD = 'CN' AND T.CODE_CD = T2.USER_TP) USER_TP_NM
        ,COUNT(*)    LONG_CNT
from    MCS_HQ.HI_USER_MENU_ACCS_LOG T1
        ,MCS_HQ.MA_USER T2
        ,MCS_HQ.MA_BASE_YMD T3
WHERE   T1.MENU_ID IN ('M001164','M001126','M001125','M001124','M001123','M001163')
AND     T1.USER_ID = T2.USER_ID
AND     T1.ACCS_DT >= DATE '2014-12-29'
AND     TO_CHAR(T1.ACCS_DT,'yyyymmDD') = T3.BASE_YMD
AND     T2.USER_TP IN ('OFFC','SLMN','BRNC')  --三星用户
AND     T2.USER_USE_ST = 'ACTV'
AND     T2.USER_ID NOT IN ('chokyu','kimbk01') --('CHOKU','KIMBK01');
AND EXISTS(SELECT * FROM MCS_HQ.MA_USER_PROD A WHERE A.USER_PROD_REL_TP = 'MPRD' AND A.USE_YN = 'Y'
AND A.REL_TO_YMD = '99991231' AND A.PROD_CD IN ('GSM','LTE','CDMA','TABLET') AND A.USER_ID = T1.USER_ID)
AND     EXISTS(
            SELECT  /*+PARALLEL(A,8) PARALLEL(B,8)*/*
            FROM    MCS_HQ.MA_USER_ROLE A
                    ,MCS_HQ.MA_ROLE_MENU B
            WHERE   A.USER_ID = T1.USER_ID
            AND     A.USE_YN = 'Y'
            AND     B.ROLE_ID = A.ROLE_ID
            AND     B.USE_YN = 'Y'
            AND     B.MENU_ID IN ('M001164','M001126','M001125','M001124','M001123','M001163')
        )
AND     T2.SUBS_ID = 'SCIC'
GROUP BY T3.BASE_YW
        ,T2.BRNC_ID
        ,T2.OFFC_ID
        ,T2.USER_ID
        ,T2.USER_NM
        ,T2.USER_TP
)
SELECT   MAX(BRNC_NM) BRNC_NM
        ,MAX(OFFC_NM) OFFC_NM
        ,USER_ID USER_ID
        ,MAX(USER_NM) USER_NM   
        ,USER_TP_NM
        ,sum(DECODE(BASE_YW,'201501',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201502',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201503',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201504',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201505',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201506',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201507',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201508',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201509',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201510',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201511',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201512',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201513',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201514',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201515',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201516',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201517',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201518',LONG_CNT)) W20_USER_CNT
         ,sum(DECODE(BASE_YW,'201519',LONG_CNT)) W20_USER_CNT
        ,sum(DECODE(BASE_YW,'201520',LONG_CNT)) W20_LOGN_CNT
        ,sum(DECODE(BASE_YW,'201521',LONG_CNT)) W21_LOGN_CNT
        ,sum(DECODE(BASE_YW,'201522',LONG_CNT)) W22_LOGN_CNT
        ,sum(DECODE(BASE_YW,'201523',LONG_CNT)) W23_LOGN_CNT
        ,sum(DECODE(BASE_YW,'201524',LONG_CNT)) W24_LOGN_CNT
        ,sum(DECODE(BASE_YW,'201525',LONG_CNT)) W25_LOGN_CNT
FROM    T_MAST
GROUP BY BRNC_ID
        ,OFFC_ID
        ,USER_ID
        ,USER_TP_NM
ORDER BY
    BRNC_NM
    ,OFFC_NM
    ,USER_ID
    ,USER_TP_NM
这样增加hint后的sql运行速度在10s内完成，基本适应频率较高的手动执行和更改也能满足领导及时检查的需要

时间： 2024-10-28 15:52:44

不懂业务的SQL优化方法的相关文章

千万级的mysql数据库与sql优化方法

对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from t where num=0 3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使

浅谈SQL优化方法

一. 什么情况下需要优化SQL? 1.单个查询执行时间非常长 2.许多查询执行时间过长,但是有一个代表性的可以调整(使用绑定变量) 二.什么情况下会触发SQL慢? 1.数据库版本的升级改变了行为或有bug 2.统计信息不正确 3.数据变化 4.应用变化 三.什么地方会出错呢? 1.索引:没被创建.建了但无效.建了但没走索引 2.表连接的顺序错了 3.表连接的类型错了 4.谓词没有推进,视图没有合并 5.查询转化不当(COST) 6.其它问题 查看本栏目更多精彩内容:http://www.bian

EXPLAIN sql优化方法（2） Using temporary ; Using filesort

优化GROUP BY语句 默认情况下,MySQL对所有GROUP BY col1,col2...的字段进行排序.这与在查询中指定ORDER BY col1,col2...类似.因此,如果显式包括一个包含相同的列的ORDER BY子句,则对MySQL的实际执行性能没有什么影响. 如果查询包括GROUP BY 但用户想要避免排序结果的消耗,则可以指定ORDER By NULL禁止排序,例如: Java代码   explain select id, sum(moneys) from sales2 gr

EXPLAIN sql优化方法（1） 添加索引

添加索引优化器更高效率地执行语句  假设我们有两个数据表t1和t2,每个有1000行,包含的值从1到1000.下面的查询查找出两个表中值相同的数据行: Java代码   mysql> SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2;   +------+------+   | i1 | i2 |   +------+------+   | 1 | 1 |   | 2 | 2 |   | 3 | 3 |   | 4 | 4 |   | 5 |

EXPLAIN sql优化方法（3）DERIVED

派生表和视图的性能 从MySQL 4.1开始,它已经支持派生表.联机视图或者基本的FROM从句的子查询. 这些特性之间彼此相关,但是它们之间的性能比较如何呢? MySQL 5.0 中的派生表似乎和视图实现的方式不同,尽管我从合并的代码基数来看觉得在查询优化上应该是一样的. 派生表仍然以临时表的方式显式地处理,而且还是没有索引的临时表(因此最好不要像在例子中那样连接2个派生表) 需要考虑的另一方面是,派生表需要被显式处理,尽管只是执行 EXPLAIN 语句.因此如果在 FROM 字句中的 SELE

总结SQL执行进展优化方法_MsSql

在本文中,小编将与大家重点探讨SQL执行进展优化的方法. 聚集索引扫描 SELECT * FROM C_SY_Ownedstorm 聚集索引扫描比表扫描快 聚集索引扫描:发生于聚集表,也相当于全表扫描操作,但在针对聚集列的条件等操作时,效率会较好. 表扫描 SELECT * FROM #temp 表扫描:发生于堆表,并且没有可用的索引时,会发生表扫描,表示整个表扫描一次. 测试SQL CREATE TABLE t1(c1 INT, c2 VARCHAR (8000)); GO DECLARE @

踩坑CBO，解决那些坑爹的SQL优化问题

本文根据DBAplus社群第93期线上分享整理而成.   讲师介绍  丁俊 新炬网络首席性能优化专家 SQL审核产品经理   DBAplus社群联合发起人,<剑破冰山-Oracle开发艺术>副主编. Oracle ACEA,ITPUB开发版资深版主,十年电信行业从业经验.   本次分享大纲: CBO优化器存在哪些坑 CBO优化器坑的解决之道 加强SQL审核,将性能问题扼杀于襁褓之中 分享现场FAQ   CBO( Cost Based Optimizer)优化器是目前Oracle广泛使用的优化器

被埋没的SQL优化利器——Oracle SQL monitor

转载声明:本文为DBA+社群原创文章,转载必须连同本订阅号二维码全文转载,并注明作者名字及来源:DBA+社群(dbaplus).    据说,在Oracle企业版数据库中有一个免费的工具,乃SQL优化之利器,那就是Oracle SQL monitor.下面,由DBA+社群原创专家周俊,给大家科普一下这一被埋没的神器.   专家简介    周俊 DBA+社群原创专家   具有14年以上Oracle数据库技术支持经验,在IBM的7年间担任华东区非IBM logo产品技术支持团队team leader

详解MySQL 数据库优化方法

用analyze进行处理,定期进行处理 ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name[, tbl_name]... 对表进行定义分析analyze table table_name CHECK TABLE tb1_name[,tbl_name]...[option]...option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED} 定期对表进行优化 OPTIMIZE [LOCAL | NO_W