oracle性能優化（項目中的一個sql優化的簡單記錄）

在項目中，寫的sql主要以查詢為主，但是數據量一大，就會突出sql性能優化的重要性。其實在數據量2000W以內，可以考慮索引，但超過2000W了，就要考慮分庫分表這些了。本文主要記錄在實際項目中，一個需要查詢很慢的sql的優化過程，如果有更好的方案，請在下麵留言交流。

很多文章都有關於sql優化的方法，這裡就不一一陳述了。如果有需要可以查看博客：https://blog.csdn.net/linhaiyun_ytdx/article/details/79101122

SELECT T.YHBH,
       (SELECT NAME FROM DIM_REGION WHERE CODE = SUBSTR(T.GDDWBM, 0, 4)) GDDWMC,
       (SELECT NAME FROM DIM_REGION WHERE CODE = T.GDDWBM) FJMC,
       T.DFNY,
       T.YHMC,
       T.YDDZ,
       (SELECT NAME FROM DIM_ELECTRICITY_TYPE WHERE CODE = T.YHLBDM) YDLBMC
  FROM (SELECT DISTINCT T.YHBH,
                        DECODE(T.GDDWBM,
                               NULL,
                               '0000',
                               DECODE(T.GDDWBM, '09', '0000', T.GDDWBM)) AS GDDWBM,
                        T.BBNY AS DFNY,
                        T.YHLBDM AS YHLBDM,
                        T.YHMC,
                        T2.YDDZ
          FROM V_TEMP_TABLE_JHCBHSTJ_HISTORY T, TMP_KH_YDKH T2
         WHERE T.YHBH = T2.YHBH(+)
           AND NOT EXISTS (SELECT 1
                  FROM DJHJSL_LSB_FZ_HISTORY B
                 WHERE B.BBNY = T.BBNY
                   AND B.YHBH = T.YHBH
                   AND B.GDDWBM = T.GDDWBM
                   AND B.YHLBDM = T.YHLBDM
                   AND B.ZDCBZHS <> '0')
) T 
WHERE SUBSTR(T.GDDWBM, 0, 4) = '0946' 
  AND T.DFNY = '201911'

這個是我的sql腳本。其實這個腳本一點都不複雜。其中V_TEMP_TABLE_JHCBHSTJ_HISTORY，DJHJSL_LSB_FZ_HISTORY每個月增加330萬，目前有1960多萬， TMP_KH_YDKH表有330多萬。DIM_REGION 和DIM_ELECTRICITY_TYPE 是兩個數據字典項表。

在沒有索引的情況下，這個腳本執行需要30s，看到執行過程，現在都是全表掃描的。接下來開始優化。

1.修改腳本的查詢，將外層的查詢條件放到裡面，減少數據量。

SELECT T.YHBH,
       (SELECT NAME FROM DIM_REGION WHERE CODE = SUBSTR(T.GDDWBM, 0, 4)) GDDWMC,
       (SELECT NAME FROM DIM_REGION WHERE CODE = T.GDDWBM) FJMC,
       T.DFNY,
       T.YHMC,
       T.YDDZ,
       (SELECT NAME FROM DIM_ELECTRICITY_TYPE WHERE CODE = T.YHLBDM) YDLBMC
  FROM (SELECT DISTINCT T.YHBH,
                        DECODE(T.GDDWBM,
                               NULL,
                               '0000',
                               DECODE(T.GDDWBM, '09', '0000', T.GDDWBM)) AS GDDWBM,
                        T.BBNY AS DFNY,
                        T.YHLBDM AS YHLBDM,
                        T.YHMC,
                        T2.YDDZ
          FROM V_TEMP_TABLE_JHCBHSTJ_HISTORY T, TMP_KH_YDKH T2
         WHERE T.YHBH = T2.YHBH(+)
           AND NOT EXISTS (SELECT 1
                  FROM DJHJSL_LSB_FZ_HISTORY B
                 WHERE B.BBNY = T.BBNY
                   AND B.YHBH = T.YHBH
                   AND B.GDDWBM = T.GDDWBM
                   AND B.YHLBDM = T.YHLBDM
                   AND B.ZDCBZHS <> '0')
            AND SUBSTR(T.GDDWBM, 0, 4) = '0946' 
            AND T.BBNY = '201911'
) T 

2.對三個表都建上索引

對V_TEMP_TABLE_JHCBHSTJ_HISTORY根據DFNY，SUBSTR(T.GDDWBM, 0, 4)建上聯合索引。

CREATE INDEX IDX_TMP_JHCBHSTJ_HISTORY_UNION ON V_TEMP_TABLE_JHCBHSTJ_HISTORY(BBNY,SUBSTR(GDDWBM, 0, 4));

對TMP_KH_YDKH表，使用了關聯，所以需要對yhbh建個索引

create index IDX_YHBH_KH on TMP_KH_YDKH (YHBH);

對於DJHJSL_LSB_FZ_HISTORY表，在not EXISTS裡面，會全表掃描這個表，現在對他建立聯合索引試試。

CREATE INDEX IDX_DJHJSL_FZ_HISTORY_UNION ON V_TEMP_TABLE_JHCBHSTJ_HISTORY(BBNY,YHBH,GDDWBM,YHLBDM);

查看oracle的執行計劃，建立聯合索引，並沒有讓這個表走索引，還是在全表掃描的，但是查詢已經提升到9s了。

接下來對分別對這四個欄位建立索引：

create index IDX_DJHJSL_FZ_HISTORY_BBNY on DJHJSL_LSB_FZ_HISTORY (BBNY);
create index IDX_DJHJSL_FZ_HISTORY_YHBH on DJHJSL_LSB_FZ_HISTORY (YHBH);
create index IDX_DJHJSL_FZ_HISTORY_GDDWBM on DJHJSL_LSB_FZ_HISTORY (GDDWBM);
create index IDX_DJHJSL_FZ_HISTORY_YHLBDM on DJHJSL_LSB_FZ_HISTORY (YHLBDM);

 從執行計劃來看，oracle只走了IDX_DJHJSL_FZ_HISTORY_BBNY這個索引，現在最快已經到1.95s了。

雖然現在已經滿足了查詢3s內的要求，但是考慮到以後，每個月的數據增長，數據量有5000萬，一億這樣的大數據量的時候還是會很慢。

其實我在正式環境測試的時候，NOT EXISTS 裡面的這個表，建立單個索引是沒有用的，建立聯合索引才會使這個表走索引，可能是因為電腦的cpu不同等因素影響的。

上面的優化方法當然不能滿足項目的需求，接下來結合業務進行優化。作為一個監控系統，數據是T+1的，不需要追求實時性，這些數據，都是使用etl抽取工具每天定時抽取的。而且每個月300萬數據，用戶只關註的只有幾千條。所以結合業務，我們在使用etl抽取完數據後，將用戶關註的數據插入到另一張表中，這樣，每個月只有幾千條數據，這樣的話，一年也才幾萬條數據，對oracle來說決定是零壓力的。

如果大家還有其他的方式優化，請在下方留言交流。