《一個拖垮性能的過濾條件引發的SQL優化》要點：
本文介紹了一個拖垮性能的過濾條件引發的SQL優化，希望對您有用。如果有疑問，可以聯系我們。

作者介紹

黃浩：從業十年,始終專注于SQL.十年一劍,十年磨礪.3年通信行業,寫就近3萬條SQL；5年制造行業,遨游在ETL的浪潮；2年性能優化,厚積薄發自成一家.

在《SQL優化案例之五味雜陳》之后的若干天,開發人員來到我座位,不說話,只是端看著我,還似笑非笑.看著這詭異的一幕,從他不懷好意的神情中,隱隱感覺到一絲絲不祥之感.果真,又出現了性能問題.剎那間,我心里瘆得慌,因為當時我曾斷言,在經過對數據模型進行大刀闊斧的優化后,性能撐個一年兩年的是沒問題的.而現在還不到一個月的時間,就在開發人員癡癡的笑聲中被啪啪啪打臉了.

是福不是禍,是禍躲不過

我故作鎮定地與開發做了一番交談：

“是突然變慢了嗎?”

此時,我希望是執行計劃變化引發的性能問題.

“是的.”

開發人員的回答讓我稍稍輕松了下,但是他接下來的描述如同一盆冷水,又澆滅了我剛剛點燃的星星火苗

“這次是增加了活動流過濾條件,就變慢了.之前的條件還是蠻快的.”

……….哎,被赤裸裸地調戲了一番呀.

找開發人員拿到了SQL,如下：

這個SQL我是相當的熟悉了,根據開發人員的說法,只是比之前的SQL多了一個過濾條件：

AND (T1.TASKLOWIDS IN (18061000))

這個非常簡單的過濾條件居然會有如此大的魔力,將我千辛萬苦優化的SQL,輕而易舉地讓性能從2秒變成了90秒,不僅打回原形,還“變本加厲”了.面對如此赤裸裸的挑釁,也激發了我的應戰情緒.

沉著冷靜,從容不迫

在展開分析之前,結合之前的優化過程,我梳理了下思路：

1. 這次性能問題特征很明顯：由一個過濾條件引發的性能問題；
2. 增加一個過濾條件,正常情況下對性能的影響不會太大,但是可能會對執行計劃產生一系列影響,比如如果該過濾字段有索引,很可能會將之前的TABLE ACESS FULL變成INDEX RANGE SCAN,繼而,其與其他表的關聯方式會從之前的NESTED LOOP變成HASH JOIN.

因此,我初步判定這個條件過濾引發了執行計劃的變化,為了印證我的判定,我對比了執行計劃,如下：

我先來看下帶有TASK_FLOW_ID條件的執行計劃

簡單解讀如下：

1. 驅動表是SDS_DU_TF_RELEASE_T,該表的訪問方式是TABLE ACCESS BY INDEX ROWID,因為在該表上,分別在字段TASK_FLOW_ID和PROJECT_NUMBER上創建了索引,所以ORACLE優化器選擇了兩個索引BITMAP AND操作.需要注意的是,此時出現的索引SDS_SDS_DU_TF_RELEASE_TFID_I正是因為過濾條件AND (T1.TASKLOWIDS IN (18061000)) 引起的；
2. SQL中的主體表RP_PLAN_LOG_T的訪問方式是TABLE ACESS BY LOCAL INDEX ROWID,被訪問的索引是INX_OPERATETIME_PROJECTNUMBER,即過濾條件中PROJECT_NUMBER和OPERATE_TIME的字段組合索引.由于OPERATE_TIME命中的是多個分區,所以最終是PARTITION RANGE ITERATOR；
3. 結果1和結果2兩個集合通過DU_IID做了HASH JOIN.

接下來我們看看沒有TASK_FLOW_ID過濾條件的執行計劃：

1. 驅動表為SQL中的主體表RP_PLAN_LOG_T,訪問方式是TABLE ACESS BY LOCAL INDEX ROWID,被訪問的索引是INX_OPERATETIME_PROJECTNUMBER,即過濾條件中PROJECT_NUMBER和OPERATE_TIME的字段組合索引.由于OPERATE_TIME命中的是多個分區,所以最終是PARTITION RANGE ITERATOR；
2. SDS_DU_TF_RELEASE_T,該表的訪問方式是TABLE ACCESS BY INDEX ROWID；
3. 結果集1和結果集2通過DU_IID,進行了NESTED LOOPS關聯.

不比不知道,一比嚇一跳

通過上述對比,我們發現：

RP_PLAN_LOG_T的訪問方式是沒有變化的,前后都是：

1. 驅動表發生了變化,沒有TASK_FLOW_ID過濾條件時,驅動表為RP_PLAN_LOG_T表.而后變成了SDS_DU_TF_RELEASE_T
2. RP_PLAN_LOG_T與SDS_DU_TF_RELEASE_T的關聯方式也發生了變化,沒有TASK_FLOW_ID過濾條件時,關聯方式為NESTED LOOPS,而后變成了HASH JOIN

至此,我的心情有些失落.一開始,我是做了打一場大戰硬戰的準備,而這場戰斗才剛開始,就似乎要結束了.這個起初“山雨欲來風滿樓,劍拔弩張馬齊嘶”的性能問題突然變成了一個非常常見又平常的案例：由一個查詢條件引發了執行計劃變化,從而導致了性能問題.而此類問題的藥方也通用：干擾Oracle優化器.比如這次的方案,可以通過HINT,或者LEADING指定驅動表,或者NO_INDEX強制不使用TASK_FLOW_ID的索引,或者USE_NL指定關聯方式.

水落石未出,疑云層層來

該案例的優化工作就這樣在大起大落中平淡收場了.然而,有兩個問題并沒有隨著優化結束而水落石出,其一是為何增加了一個過濾條件會引發執行計劃變化?其二是為何RP_PLAN_LOG_T做驅動表的性能會高?尤其是第二個問題,要知道,RP_PLAN_LOG_T通過PROJECT_NUMBER和OPERATE_TIME綜合過濾后,其數據量達到了百萬級,是數據量最大的結果集,這明顯有違小表驅動的基本原理.

剝開第一層疑云

我們先看看第一個問題,這個問題相對簡單.為了弄清這個問題,我們首先要看看SDS_DU_TF_RELEASE_T的模型結構,在該SQL中,關于這個表的關鍵字段有三個字段,分別是DU_IID、TASK_FLOW_ID、PROJECT_NUMBER.三者之間的關系如下：

從PROJECT_NUMBER—>TASK_FLOW_ID—>DU_IID,數據粒度越來越細,所以當TASK_FLOW_ID作為了過濾條件,Oracle就認為可以過濾掉大量的數據,而且TASK_FLOW_ID上又存在索引,從而認定可以作為驅動表.

剝開第二層疑云

現在重點看看第二個問題：為何RP_PLAN_LOG_T做驅動表的性能會高?

帶著這個疑問,為了便于說明,我們簡化下這個SQL,砍掉枝枝葉葉,只保留RP_PLAN_LOG_T這個“孤家寡人”,同時我們也略作改動,即將ORDER BY的字段由OPERATE_TIME修改為CDESCRIPTOIN.如下：

其中RP_PLAN_LOG_T的表結構如下：

表的索引如下：

執行計劃如下：

索引還是那個索引,表還是那個表,只是SORT ORDER BY STOPKEY不見了,成本降低了,執行效率達到了毫秒級.

辯論時刻

這里,有一個大寫的疑問：明明是ORDER BY OPERATE_TIME,為何在執行計劃里面沒有SORT ORDER BY STOPKEY步驟了?難道是Oracle優化器的BUG?此時,你會不會因為發現了Oracle的BUG而歡呼雀躍?很遺憾的告訴你,這并非Oracle的BUG,反而是Oracle優化器的高明之處.

索引的特性之一就是有序,我們先通過OPERATE_TIME字段上的索引獲取到了有序的OPERATE_TIME(及其對應的ROWID),以此為基礎,通過TABLE ACCESS BY LOCAL INDEX ROWID獲取其它字段信息,這樣得到的結果集自然是已經按照OPERATE_TIME排好序的有序結果：

請問,這還需要“教條”般的再次排序嗎?

除了大寫的疑問外,還有一個小寫的疑問：不考慮排序,同樣的查詢條件,同樣的索引掃描,為何成本差異如此之大?在無SORT的情況下,INDEX RANGE SCAN的COST值為11,而如果進行了SORT,COST值為1910.

 

難道是SORT會影響到INDEX RANGE SCAN的成本?事實上ORACLE引擎是先執行INDEX RANGE SCAN,再執行SORT,也只能是：INDEX RANGE SCAN的結果集會影響到SORT的成本,因為INDEX RANGE SCAN的結果集越大,SORT的成本會越高.

那么,這里面到底發生了什么呢?還得要從根本說起：在正常情況下,我們如果想要獲取前N條數據,就必須要按照既定字段排序,那就意味著我們首先要獲取到全部的數據；但是,如果我們拿到的是已經按照既定字段排好序的數據,那么就可以直接獲取前N條數據,而無需獲取全部數據.這就是同樣是INDEX RANGE SCAN,而COST相距甚遠的玄妙所在.

這個猜想也是可以在執行計劃中得到印證：就是INDEX RANGE SCAN這步操作的實際返回ROWS,如下：

看到這里,你是否會有些小激動?因為你發現：在排序字段上創建一個索引,就能將分頁時排序產生的性能開銷幻滅于無形.其實并非絕對.為了印證,我們繼續以上述案例為例舉證.

在RP_PLAN_LOG_T表中,字段PLAN_LOG_ID的值由序列號填充,并且在上面創建了UNIQUE INDEX：

現在,我們將ORDER BY的字段由OPERATE_TIME修改為PLAN_LOG_ID,我們來看看執行計劃：

嘿,還真如我們所料：利用了索引數據有序的特性,COST也相當得低.

是真實的性能呢?通過SQL*MONITOR,我們發現耗時竟達66S.

其中IO等待耗時54S,為何?原來這個執行計劃實際加載了45M的數據量,這個就是全表的數據量.

由此可見,理想是豐滿的,而現實卻一地排骨.利用索引數據有序的特性做分頁排序,是要講究緣分的,可遇而不可求.必須要滿足如下兩個條件：

1. 排序字段上必須要建有(前綴)索引；
2. 在多表關聯的SQL中,排序字段所在表,必須為執行計劃中的驅動表

否則,反而事與愿違適得其反.

化腐朽為神奇,以四兩撥千斤

至此,為何RP_PLAN_LOG_T做驅動表的性能會高?這個問題就迎刃而解了.

我們再次通過SQL*MONITOR來回顧下執行計劃：

表面上,我們看到的是通過PROJECT_NUMBER和OPERATE_TIME過濾后的結果集多大170萬,而事實上,Oracle優化器巧妙的利用了OPERATE_TIME索引字段的排序：

1. 只獲取了15條記錄,用這15條記錄來驅動,即便千萬級集合,也會是彈指一揮間；
2. 省卻了龐大結果集排序的開銷,SORT的COST灰飛煙滅

文章來自微信公眾號：DBAplus社群

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