《殺手SQL：一條關于 ‘Not in’ SQL 的優化案例》要點：
本文介紹了殺手SQL：一條關于 ‘Not in’ SQL 的優化案例，希望對您有用。如果有疑問，可以聯系我們。

編輯手記：在 DBA 所優化的數據庫環境中,絕大多數性能問題其實是由于 SQL 編寫不當導致的.SQL 的世界無奇不有,今天我們一起見識一條讓你絕對想吐血的殺手SQL.

某保險客戶,ETL 耗時數個小時,我們做了sql report發現壓力主要在其中一個SQL上.

單次執行時間：5788(秒)

單次邏輯讀：10億(塊)

單次返回行數:21萬(行)

我們首先看SQL語句,因為比較長,此處只節選部分的

查看其執行計劃：

我們主要關注一下從7到16行：發現存在兩次全表掃描.中間做了一次filter.

多年的經驗告訴我,兩個全表掃組成的Filter ,問題很嚴重, 因為涉及數據逐條處理. 而這個執行計劃里,被驅動表還是全表掃.

Not In/In 操作有時候的確會產生 Filter操作,在11g之前的版本,要把not in 語句轉換成反連接,not in條件的列必須有Not null 屬性,或者語句中帶入了not null的限制,否則只能采用Filter,逐條過濾.

我們舉例說明一下：

SQL1：CREATE TABLE?T_OBJ AS SELECT OBJECT_ID,OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OWNER != ‘SEROL’;SQL2：CREATE TABLET_TABLE?AS SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER!=’SEROL’;

查看T_OBJ的屬性：

發現有在三列上都沒有not null的限制.

我們此時偽裝成10G的優化器.

SQL> alter session set optimizer_features_enable=”10.2.0.5″;

執行以下SQL：

SQL> set autotracetrace exp

SQL> SELECT * FROM T_TABLE WHERE TABLE_NAME NOT IN(SELECT OBJECT_NAME FROM T_OBJ);

此時查看執行計劃,我們發現走的是filter：

但在11g版本中,優化器可以自動把Not in操作從昂貴的Filter轉換成Null-Aware-Anti-Join.

若加個Not null 條件或者欄位屬性設為not null

SQL> alter table T_OBJ modify(OBJECT_NAME NOT NULL);

再次執行相同語句：

SQL> SELECT * FROM T_TABLE? WHERE TABLE_NAME

NOT IN(SELECT OBJECT_NAME FROM T_OBJ

WHEREOBJECT_NAME IS NOT NULL);

再次查看執行計劃：

此時我們發現,在執行計劃中,走了hash join anti.

并且,在11g里面,允許not in列沒有not null 限制也可以轉換Anti-Join.

SQL> alter session set optimizer_features_enable=”11.2.0.4″;

SQL> alter table T_OBJ modify(OBJECT_NAME NULL);

SQ>? SELECT * FROM T_TABLE? WHERE TABLE_NAME

NOT IN (SELECTOBJECT_NAMEFROM T_OBJ);

查看執行計劃：

我們看到,此時在沒有非空限制的情況下,也走了hash join anti.

這個特性, 可通過優化器參數控制.

SQL>alter session set “_optimizer_null_aware_antijoin”=FALSE;

再次執行以上語句并查看執行計劃：

SQL>? SELECT * FROM T_TABLE? WHERE TABLE_NAME

NOT IN (SELECTOBJECT_NAMEFROM T_OBJ);

發現仍然走的是hash join anti.

經過驗證,不是這個參數設置問題

Not in 的邏輯,就是結果集之間的互斥,其實有多種改寫的方式,比如：

— Not exists

— Outer Join + is null

— Minus

not in與以上三種寫法的區別是：not in 是會排斥空值.

我們嘗試改寫.

接下來正當你以為會發生奇跡的時候,語句報錯了!

為什么會報錯呢?

如果我們把該語句轉換為not in的方式：

根據not in的邏輯,此時在fee_code前應該加上’A.’,當然這也是沒有問題的,但是,再次看這條語句就會變成：

由于TMP_APP_xxx_PREM A 中并沒有FEE_CODE字段, 所以,Not in 無法自動改成Null Aware ANTI JOIN.

所以,至此答案揭曉,竟然是寫錯了?!我猜中了這開頭,卻沒有猜中這結局.

但在本案例中,由于SQL語句中沒有顯式寫出表明,導致在前期分析過程中一直沒有發現這個錯誤.

你是不是也很無語,其實我更想問的是,你是不是也經常寫出殺手SQL呢,但沒關系,你有病我有藥啊.(無辜臉,不要打我)

我們都知道,在 DBA 所優化的數據庫環境中,絕大多數性能問題其實是由于 SQL 編寫不當導致的.

對于未上線系統,通過前期的SQL審核管控,將80%的SQL問題消滅在萌生階段,對于線上運行系統,發現和解決潛在的性能問題,可做到提前預防,防患于未然.

SQL審核,讓DBA由系統的急救醫生轉身成為系統的保健醫生

1、DBA參與應用代碼開發測試過程：給開發人員提供專業的數據庫開發及優化建議

2、優化前置：在應用代碼上線前根據業務需求設計高效的SQL、索引

3、控制變更風險：預先評估應用開發中表結構變更、SQL變更對運行中應用的影響,確定合適的變更窗口,變更方案.

原文來自微信公眾號：數據和云

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/3736.html