最佳化 SQL 查詢，減少'Not in' 運行時間

導讀	在 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#23:##12：TAETPLE ID 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變更對運行中應用的影響，確定合適的變更窗口，變更方案。

以上是最佳化 SQL 查詢，減少'Not in' 運行時間的詳細內容。更多資訊請關注PHP中文網其他相關文章！