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來說決定是零壓力的，

-----------------------------------------------------我是分界線---------------------------------------------------------

2020年5月1日更新：前一天我有點空閑時間，想起來對這個sql再做一次優化（經過幾個月的增長，已經有了4000萬的資料，就算上面的那個腳本查詢還有有點慢），因為我們的表資料是按月插入的，客戶查詢也是按月查詢的，所以我就對 V_TEMP_TABLE_JHCBHSTJ_HISTORY，DJHJSL_LSB_FZ_HISTORY 這兩個月進行了按月磁區（串列磁區），

下面是執行腳本（我這里沒有建默認磁區，在專案中一定要建立默認磁區）：

-- Create table 分母
create TABLE JHCBHSTJ_HISTORY1
(
  BBNY      VARCHAR2(6),
  BBNYR     VARCHAR2(8),
  GDDWBM    VARCHAR2(20),
  YHLBDM    VARCHAR2(20),
  DYLBBM    VARCHAR2(20),
  YHBH      VARCHAR2(50),
  YHMC      VARCHAR2(200),
  DYJHCBKHS NUMBER(10)
)
partition by LIST(BBNY)
(
  partition P_JHCBHSTJ_HISTORY_201905 values  ('201905'),
  partition P_JHCBHSTJ_HISTORY_201906 values  ('201906'),
  partition P_JHCBHSTJ_HISTORY_201907 values  ('201907'),
  partition P_JHCBHSTJ_HISTORY_201908 values  ('201908'),
  partition P_JHCBHSTJ_HISTORY_201909 values  ('201909'),
  partition P_JHCBHSTJ_HISTORY_201910 values  ('201910'),
  partition P_JHCBHSTJ_HISTORY_201911 values  ('201911'),
  partition P_JHCBHSTJ_HISTORY_201912 values  ('201912'),
  
  partition P_JHCBHSTJ_HISTORY_202001 values  ('202001'),
  partition P_JHCBHSTJ_HISTORY_202002 values  ('202002'),
  partition P_JHCBHSTJ_HISTORY_202003 values  ('202003'),
  partition P_JHCBHSTJ_HISTORY_202004 values  ('202004'),
  partition P_JHCBHSTJ_HISTORY_202005 values  ('202005'),
  partition P_JHCBHSTJ_HISTORY_202006 values  ('202006'),
  partition P_JHCBHSTJ_HISTORY_202007 values  ('202007'),
  partition P_JHCBHSTJ_HISTORY_202008 values  ('202008'),
  partition P_JHCBHSTJ_HISTORY_202009 values  ('202009'),
  partition P_JHCBHSTJ_HISTORY_202010 values  ('202010'),
  partition P_JHCBHSTJ_HISTORY_202011 values  ('202011'),
  partition P_JHCBHSTJ_HISTORY_202012 values  ('202012'),
  
  partition P_JHCBHSTJ_HISTORY_202101 values  ('202101'),
  partition P_JHCBHSTJ_HISTORY_202102 values  ('202102'),
  partition P_JHCBHSTJ_HISTORY_202103 values  ('202103'),
  partition P_JHCBHSTJ_HISTORY_202104 values  ('202104'),
  partition P_JHCBHSTJ_HISTORY_202105 values  ('202105'),
  partition P_JHCBHSTJ_HISTORY_202106 values  ('202106'),
  partition P_JHCBHSTJ_HISTORY_202107 values  ('202107'),
  partition P_JHCBHSTJ_HISTORY_202108 values  ('202108'),
  partition P_JHCBHSTJ_HISTORY_202109 values  ('202109'),
  partition P_JHCBHSTJ_HISTORY_202110 values  ('202110'),
  partition P_JHCBHSTJ_HISTORY_202111 values  ('202111'),
  partition P_JHCBHSTJ_HISTORY_202112 values  ('202112')
);;
  
  
ALTER SESSION ENABLE PARALLEL DML;
--插入資料　(采用并發，依據服務器性能和核數而定)
INSERT /*+PARALLEL(JHCBHSTJ_HISTORY1,30)*/ INTO JHCBHSTJ_HISTORY1 
SELECT /*+PARALLEL(V_TEMP_TABLE_JHCBHSTJ_HISTORY,30)*/ * FROM V_TEMP_TABLE_JHCBHSTJ_HISTORY;
 
  COMMIT;


--替換之前的表
RENAME V_TEMP_TABLE_JHCBHSTJ_HISTORY TO JHCBHSTJ_HISTORY_BAK;
RENAME JHCBHSTJ_HISTORY1 TO V_TEMP_TABLE_JHCBHSTJ_HISTORY;

-- Create table  分子
create table DJHJSL_LSB_FZ_HISTORY_1
(
  BBNY    VARCHAR2(6),
  BBNYR   VARCHAR2(8),
  GDDWBM  VARCHAR2(20),
  YHLBDM  VARCHAR2(20),
  DYLBBM  VARCHAR2(20),
  YHBH    VARCHAR2(50),
  YHMC    VARCHAR2(200),
  ZDCBZHS NUMBER(10)
)
partition by LIST(BBNY)
(
  partition P_DJHJSL_LSB_FZ_HISTORY_201905 values  ('201905'),
  partition P_DJHJSL_LSB_FZ_HISTORY_201906 values  ('201906'),
  partition P_DJHJSL_LSB_FZ_HISTORY_201907 values  ('201907'),
  partition P_DJHJSL_LSB_FZ_HISTORY_201908 values  ('201908'),
  partition P_DJHJSL_LSB_FZ_HISTORY_201909 values  ('201909'),
  partition P_DJHJSL_LSB_FZ_HISTORY_201910 values  ('201910'),
  partition P_DJHJSL_LSB_FZ_HISTORY_201911 values  ('201911'),
  partition P_DJHJSL_LSB_FZ_HISTORY_201912 values  ('201912'),
  
  partition P_DJHJSL_LSB_FZ_HISTORY_202001 values  ('202001'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202002 values  ('202002'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202003 values  ('202003'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202004 values  ('202004'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202005 values  ('202005'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202006 values  ('202006'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202007 values  ('202007'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202008 values  ('202008'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202009 values  ('202009'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202010 values  ('202010'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202011 values  ('202011'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202012 values  ('202012'),
  
  partition P_DJHJSL_LSB_FZ_HISTORY_202101 values  ('202101'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202102 values  ('202102'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202103 values  ('202103'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202104 values  ('202104'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202105 values  ('202105'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202106 values  ('202106'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202107 values  ('202107'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202108 values  ('202108'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202109 values  ('202109'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202110 values  ('202110'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202111 values  ('202111'),
  partition P_DJHJSL_LSB_FZ_HISTORY_202112 values  ('202112')
);;

ALTER SESSION ENABLE PARALLEL DML;
--插入資料　(采用并發，依據服務器性能和核數而定)
INSERT /*+PARALLEL(DJHJSL_LSB_FZ_HISTORY_1,30)*/ INTO DJHJSL_LSB_FZ_HISTORY_1 
SELECT /*+PARALLEL(DJHJSL_LSB_FZ_HISTORY,30)*/ * FROM DJHJSL_LSB_FZ_HISTORY;
 
  COMMIT;
  
-- Create/Recreate indexes 
create index IDX_DJHJSL_FZ_HISTORY_UNION1 on DJHJSL_LSB_FZ_HISTORY_1 (BBNY, YHBH, GDDWBM, YHLBDM);


--替換之前的表
RENAME DJHJSL_LSB_FZ_HISTORY TO DJHJSL_LSB_FZ_HISTORY_bak;
RENAME DJHJSL_LSB_FZ_HISTORY_1 TO DJHJSL_LSB_FZ_HISTORY;

同時在兩個表插入完成之后，對兩個表收集了執行資訊：

--收集執行資訊
 EXEC DBMS_STATS.gather_table_stats(user,'V_TEMP_TABLE_JHCBHSTJ_HISTORY',cascade=>true);
 
 --收集執行資訊
EXEC DBMS_STATS.gather_table_stats(user,'DJHJSL_LSB_FZ_HISTORY',cascade=>true);

這樣我在執行查詢的時候，下面的圖可以看到效果，性能提升還是很大的，

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/39971.html

標籤：大數據

上一篇：pb讀寫txt 檔案的做法

下一篇：bayaim——聽課筆記_01.Docker基礎應用 10課.txt