執行長達2分多鐘的大型sql優化
SELECT T1.BASE_YW
,T1.BRNC_ID
,T1.OFFC_ID
,T1.MKT_MODL_ID
,T1.BASC_MODL
,T1.BI_CHNL_TP_02
,T1.BI_CHNL_TP_03
,T1.BRNC_ID_NM
,T1.OFFC_ID_NM
,T1.MKT_MODL_DI_NM
,T1.BASC_MODL_NM
,T1.BI_CHNL_TP_02_NM
,T1.BI_CHNL_TP_03_NM
,T2.SHOP_CNT
,T1.YW_01_SHOP_CNT
,T1.YW_02_SHOP_CNT
,T1.YW_03_SHOP_CNT
,T1.YW_04_SHOP_CNT
,T1.YW_05_SHOP_CNT
,T1.YW_06_SHOP_CNT
,0 GR_BI_CHNL_TP_02
,0 GR_BI_CHNL_TP_03
,0 GR_MKT_MODL_ID --ADD
,0 GR_BASC_MODL --ADD
,0 GR_OFFC_ID
,0 GR_BRNC_ID
FROM (
SELECT MAX(T1.BASE_YW) BASE_YW
,T1.BRNC_ID
,T1.OFFC_ID
,T1.MKT_MODL_ID
,T1.BASC_MODL
,T1.BI_CHNL_TP_02
,T1.BI_CHNL_TP_03
,MAX(T1.BRNC_ID_NM) BRNC_ID_NM
,MAX(T1.OFFC_ID_NM) OFFC_ID_NM
,MAX(T1.MKT_MODL_ID) MKT_MODL_DI_NM
,MAX(T1.BASC_MODL) BASC_MODL_NM
,MAX(T1.BI_CHNL_TP_02_NM) BI_CHNL_TP_02_NM
,MAX(T1.BI_CHNL_TP_03_NM) BI_CHNL_TP_03_NM
,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 1 THEN SHOP_ID END) YW_01_SHOP_CNT
,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 2 THEN SHOP_ID END) YW_02_SHOP_CNT
,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 3 THEN SHOP_ID END) YW_03_SHOP_CNT
,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 4 THEN SHOP_ID END) YW_04_SHOP_CNT
,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 5 THEN SHOP_ID END) YW_05_SHOP_CNT
,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 6 THEN SHOP_ID END) YW_06_SHOP_CNT
FROM T_MART T1
WHERE NVL(T1.PSI_VAL,0) >= 1
GROUP BY T1.BRNC_ID
,T1.OFFC_ID
,T1.MKT_MODL_ID
,T1.BASC_MODL
,T1.BI_CHNL_TP_02
,T1.BI_CHNL_TP_03
) T1
,T_SHOP_DIM T2
WHERE T1.BRNC_ID = T2.BRNC_ID
AND T1.OFFC_ID = T2.OFFC_ID
AND NVL(T1.BI_CHNL_TP_02,'-') = NVL(T2.BI_CHNL_TP_02,'-')
AND NVL(T1.BI_CHNL_TP_03,'-') = NVL(T2.BI_CHNL_TP_03,'-')
UNION ALL
SELECT DISTINCT T1.BASE_YW
,T1.BRNC_ID
,'-' OFFC_ID
,'-' MKT_MODL_ID
,'-' BASC_MODL
,T1.BI_CHNL_TP_02
,T1.BI_CHNL_TP_03
,T1.BRNC_ID_NM
,T1.OFFC_ID_NM
,T1.MKT_MODL_DI_NM
,T1.BASC_MODL_NM
,T1.BI_CHNL_TP_02_NM
,T1.BI_CHNL_TP_03_NM
,SUM(T2.SHOP_CNT)OVER(PARTITION BY T1.BASE_YW,T1.BRNC_ID,T1.BI_CHNL_TP_02,T1.BI_CHNL_TP_03)SHOP_CNT
,T1.YW_01_SHOP_CNT
,T1.YW_02_SHOP_CNT
,T1.YW_03_SHOP_CNT
,T1.YW_04_SHOP_CNT
,T1.YW_05_SHOP_CNT
,T1.YW_06_SHOP_CNT
,0 GR_BI_CHNL_TP_02
,0 GR_BI_CHNL_TP_03
,1 GR_MKT_MODL_ID --ADD
,1 GR_BASC_MODL --ADD
,1 GR_OFFC_ID
,0 GR_BRNC_ID
FROM (
SELECT MAX(T1.BASE_YW) BASE_YW
,T1.BRNC_ID
-- ,T1.OFFC_ID
--,T1.MKT_MODL_ID
--,T1.BASC_MODL
,T1.BI_CHNL_TP_02
,T1.BI_CHNL_TP_03
,MAX(T1.BRNC_ID_NM) BRNC_ID_NM
,'TTL' OFFC_ID_NM
,'TTL' MKT_MODL_DI_NM --ADD
,'TTL' BASC_MODL_NM --ADD
,MAX(T1.BI_CHNL_TP_02_NM) BI_CHNL_TP_02_NM
,MAX(T1.BI_CHNL_TP_03_NM) BI_CHNL_TP_03_NM
,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 1 THEN SHOP_ID END) YW_01_SHOP_CNT
,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 2 THEN SHOP_ID END) YW_02_SHOP_CNT
,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 3 THEN SHOP_ID END) YW_03_SHOP_CNT
,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 4 THEN SHOP_ID END) YW_04_SHOP_CNT
,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 5 THEN SHOP_ID END) YW_05_SHOP_CNT
,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 6 THEN SHOP_ID END) YW_06_SHOP_CNT
FROM T_MART T1
WHERE NVL(T1.PSI_VAL,0) >= 1
GROUP BY T1.BRNC_ID
--,T1.OFFC_ID
--,T1.MKT_MODL_ID
--,T1.BASC_MODL
,T1.BI_CHNL_TP_02
,T1.BI_CHNL_TP_03
) T1
,T_SHOP_DIM T2
WHERE T1.BRNC_ID = T2.BRNC_ID
-- AND T1.OFFC_ID = T2.OFFC_ID
AND NVL(T1.BI_CHNL_TP_02,'-') = NVL(T2.BI_CHNL_TP_02,'-')
AND NVL(T1.BI_CHNL_TP_03,'-') = NVL(T2.BI_CHNL_TP_03,'-')
UNION ALL
……
有些統計報表的sql編寫過於複雜，常常是如上的形式，多個union到一起，且不同欄位組合在一起分組計算，工作中遇到上面這樣的類似sql，由11個上面的select語句塊 union到一起，統計出2000條資料要2分鐘。這樣的語句不在少數而且有時會手工修改滿足更改的報表需求，為提高sql執行和維護sql效率，必然需要優化。
對於這類union很多的分組計算的sql常常想到用報表分析函式合併，這樣類似cube的報表函式要比union all的效率高，io少，因此首要優化的想法就是用更高效的報表分析函式合併這些union語句。但看過具體實際的例子會發現很多select都帶有自己的查詢條件，上面這段sql每個select還和另一個查詢集合關聯，且每次連線的條件都不一樣。這樣就要找到一種通用的方式才能合併這樣的sql
首先在分析sql的時候發現，sql中的select中都有distinct這樣的關鍵字，經與開發人員分析後改distinct的存在主要是為了分析函式求出的商店總數，而對於這個商店總數在 T_SHOP_DIM中是可以計算的，於是首先在T_SHOP_DIM計算商店總數
SELECT SUM(T1.SHOP_CNT) SHOP_CNT
FROM T_SHOP_DIM T1
GROUP BY (T1.BRNC_ID,T1.BI_CHNL_TP_02,T1.BI_CHNL_TP_03)
計算後在與T1表關聯，於是首先去掉distinct的排中方法。
繼續觀察分析sql，T_SHOP_DIM的計算也是根據不同情況分組計算的，這個情況和T1集合中的分組情況是一致的，而關聯條件也可以想辦法根據不同的分組情況動態的設定成相應值，比如OFFC_ID在本次分組中未加入group by list中，這時就可以設定該值為’-’ ，而T1集合中的OFFC_ID也可以設定成’-’,這樣在對應到本次分組連線時兩個欄位就能連線了。連線的形式固定了，就可以採用分組分析函式合併union all了，於是最終優化sql是
SELECT MAX(T1.BASE_YW) BASE_YW
,T1.BRNC_ID
,(CASE WHEN GROUPING(T1.OFFC_ID)=1 THEN '-' ELSE T1.OFFC_ID END) OFFC_ID--,'-' OFFC_ID-- ,T1.OFFC_ID
,(CASE WHEN GROUPING(T1.MKT_MODL_ID)=1 THEN '-' ELSE T1.MKT_MODL_ID END) MKT_MODL_ID--'-' MKT_MODL_ID --,T1.MKT_MODL_ID
,(CASE WHEN GROUPING(T1.BASC_MODL)=1 THEN '-' ELSE T1.BASC_MODL END) BASC_MODL--'-' BASC_MODL --,T1.BASC_MODL
,T1.BI_CHNL_TP_02
,T1.BI_CHNL_TP_03
,MAX(T1.BRNC_ID_NM) BRNC_ID_NM
,(CASE WHEN GROUPING(T1.OFFC_ID)=1 THEN 'TTL' ELSE MAX(T1.OFFC_ID_NM) END) OFFC_ID_NM--'TTL' OFFC_ID_NM
,(CASE WHEN GROUPING(T1.MKT_MODL_ID)=1 THEN 'TTL' ELSE MAX(T1.MKT_MODL_ID) END) MKT_MODL_DI_NM--'TTL' MKT_MODL_DI_NM --ADD
,(CASE WHEN GROUPING(T1.BASC_MODL)=1 THEN 'TTL' ELSE MAX(T1.BASC_MODL) END) BASC_MODL_NM--'TTL' BASC_MODL_NM --ADD
,MAX(T1.BI_CHNL_TP_02_NM) BI_CHNL_TP_02_NM
,MAX(T1.BI_CHNL_TP_03_NM) BI_CHNL_TP_03_NM
,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 1 THEN SHOP_ID END) YW_01_SHOP_CNT
,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 2 THEN SHOP_ID END) YW_02_SHOP_CNT
,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 3 THEN SHOP_ID END) YW_03_SHOP_CNT
,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 4 THEN SHOP_ID END) YW_04_SHOP_CNT
,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 5 THEN SHOP_ID END) YW_05_SHOP_CNT
,COUNT(DISTINCT CASE WHEN T1.YW_RNK = 6 THEN SHOP_ID END) YW_06_SHOP_CNT
,GROUPING_ID(T1.BRNC_ID) C1
,CASE WHEN MAX(T1.OFFC_ID) IS NULL THEN GROUPING_ID(T1.BRNC_ID) END C2
,GROUPING_ID(T1.BRNC_ID,T1.BI_CHNL_TP_02) C3
,GROUPING_ID(T1.BRNC_ID,T1.OFFC_ID,T1.BI_CHNL_TP_02,T1.BI_CHNL_TP_03) C4
,GROUPING(T1.BI_CHNL_TP_02) GR_BI_CHNL_TP_02
,GROUPING(T1.BI_CHNL_TP_03) GR_BI_CHNL_TP_03
,GROUPING(T1.MKT_MODL_ID) GR_MKT_MODL_ID --ADD
,GROUPING(T1.BASC_MODL) GR_BASC_MODL --ADD
,GROUPING(T1.OFFC_ID) GR_OFFC_ID
,GROUPING(T1.BRNC_ID) GR_BRNC_ID
FROM T_MART T1
WHERE NVL(T1.PSI_VAL,0) >= 1
GROUP BY GROUPING SETS( (T1.BRNC_ID,T1.BI_CHNL_TP_02,T1.BI_CHNL_TP_03)
,(T1.BRNC_ID,T1.OFFC_ID,T1.MKT_MODL_ID,T1.BASC_MODL,T1.BI_CHNL_TP_02,T1.BI_CHNL_TP_03)
)
) T1
,(SELECT (CASE WHEN GROUPING(T1.BRNC_ID) = 1 THEN '-' ELSE T1.BRNC_ID END) BRNC_ID
,(CASE WHEN GROUPING(T1.OFFC_ID) = 1 THEN '-' ELSE T1.OFFC_ID END) OFFC_ID
,(CASE WHEN GROUPING(BI_CHNL_TP_02) = 1 THEN '-' ELSE T1.BI_CHNL_TP_02 END) BI_CHNL_TP_02
,(CASE WHEN GROUPING(BI_CHNL_TP_03) = 1 THEN '-' ELSE T1.BI_CHNL_TP_03 END) BI_CHNL_TP_03
,SUM(T1.SHOP_CNT) SHOP_CNT
FROM T_SHOP_DIM T1
GROUP BY GROUPING SETS( (T1.BRNC_ID,T1.BI_CHNL_TP_02,T1.BI_CHNL_TP_03)
,(T1.BRNC_ID,T1.OFFC_ID,T1.BI_CHNL_TP_02,T1.BI_CHNL_TP_03)
)
) T2
WHERE T1.BRNC_ID = T2.BRNC_ID
AND T1.OFFC_ID = T2.OFFC_ID
AND NVL(T1.BI_CHNL_TP_02,'-') = NVL(T2.BI_CHNL_TP_02,'-')
AND NVL(T1.BI_CHNL_TP_03,'-') = NVL(T2.BI_CHNL_TP_03,'-')
這樣根據不同的統計情況，還可以在group by grouping set中新增其他統計情況，這樣核心的sql合併後再統計的效率是47s，因此採用合適的分組分析函式對sql的效率和維護都是非常有必要的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/750077/viewspace-1720372/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL優化案例-從執行計劃定位SQL問題（三）SQL優化
- 達夢SQL優化方法statSQL優化
- sql語句執行順序與效能優化（1）SQL優化
- SQL 執行 - 執行器最佳化SQL
- Java多執行緒程式設計—鎖優化Java執行緒程式設計優化
- 多執行緒筆記---鎖（Synchronized）的優化和種類執行緒筆記synchronized優化
- SQL執行內幕：從執行原理看調優的本質SQL
- 1 多執行緒的優缺點執行緒
- Oracle優化案例-從執行計劃定位SQL問題（三）Oracle優化SQL
- C#多執行緒下的調優C#執行緒
- 達夢SQL優化-回表BLKUP2SQL優化
- 達夢SQL優化利器-ET使用方法SQL優化
- Android優化幀動畫過程中的多執行緒模型思考Android優化動畫執行緒模型
- Oracle - 執行過的SQL、正在執行的SQL、消耗資源最多的SQLOracleSQL
- 5分鐘搞懂多執行緒安全問題執行緒
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- SQL優化案例-改變那些CBO無能為力的執行計劃（一）SQL優化
- 計算節點會對收到的SQL 語句做解析、優化、執行並SQL優化
- 什麼是多執行緒？Python多執行緒有什麼優勢?執行緒Python
- 資料庫查詢優化：使用explain分析sql語句執行效率資料庫優化AISQL
- python多執行緒的優缺點總結Python執行緒
- MySQL多執行緒併發調優MySql執行緒
- javascript執行緒及與執行緒有關的效能優化JavaScript執行緒優化
- 【實戰】利用多執行緒優化查詢百萬級資料執行緒優化
- Cookbook：優化 Vue 元件的執行時效能優化Vue元件
- Java 執行緒與同步的效能優化Java執行緒優化
- mysql一次執行多個SQL檔案MySql
- 疑問：mybatis如何自定義SQL執行時長MyBatisSQL
- SQL優化的方法論SQL優化
- SQL是如何執行的SQL
- sql優化之邏輯優化SQL優化
- 資料庫優化 - SQL優化資料庫優化SQL
- MySQL查詢優化之優化器工作流程以及優化的執行計劃生成MySql優化
- Oracle優化案例-view merge與coe_load_sql_profile固定執行計劃（十五）Oracle優化ViewSQL
- 不會看 Explain執行計劃，勸你簡歷別寫熟悉 SQL優化AISQL優化
- 不會看 Explain 執行計劃，勸你簡歷別寫熟悉 SQL 優化AISQL優化
- SQL SERVER優化SQLServer優化
- SQL優化指南SQL優化
- 【MySQL】MySQL的執行計劃及索引優化MySql索引優化