535
技術社區[雲棲]
殺手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審核SQL審核將 SQL 質量審核和優化這項任務，從 DB 端提取到研發端，通過擅長 SQL 的開發 DBA 和開發團隊一起修正係統的 SQL，找出問題、修複問題，提升係統的健壯性和穩定性，從而保證整個係統的運維建設質量。
對於未上線係統，通過前期的SQL審核管控，將80%的SQL問題消滅在萌生階段，對於線上運行係統，發現和解決潛在的性能問題，可做到提前預防，防患於未然。
SQL審核，讓DBA由係統的急救醫生轉身成為係統的保健醫生
1、DBA參與應用代碼開發測試過程：給開發人員提供專業的數據庫開發及優化建議
2、優化前置：在應用代碼上線前根據業務需求設計高效的SQL、索引
3、控製變更風險：預先評估應用開發中表結構變更、SQL變更對運行中應用的影響，確定合適的變更窗口，變更方案。
SQL審核產品工具 - z3
雲和恩墨基於Oracle數據庫的SQL審核產品工具 - z3 ，通過內置的算法引擎，可定製的抓取數據庫中執行的SQL及其詳細數據，通過過濾分析，進行量化的積分趨勢展現，並將SQL問題高亮顯示，指導程序員的優化分析，並可以通過內置的工作流由DBA進行優化，變更管理同時被內置。
本文出自數據和雲公眾號，原文鏈接
最後更新：2017-07-17 17:03:26