147
【雲和恩墨大講堂】複合索引與綁定變量
編輯手記：2016 Oracle 技術嘉年華活動已經結束，為了讓更多的技術愛好者有機會獲取大會幹貨資源，我們將不斷邀請演講嘉賓展開“2016 Oracle 線上嘉年華”。
鄧秋爽（小魚）
雲和恩墨專家，有超過5年超大型數據庫專業服務經驗，擅長oracle 數據庫優化、SQL優化和troubleshooting
今晚的恩墨大講堂將有我為大家分享SQL審核中的兩個典型案例，如下：
1複合索引前導列選擇
2綁定變量分享——某業務SQL性能問題分析
在介紹這兩個case之前先對目前我們的SQL審核交付服務做一個簡單說明，我們目前的SQL審核交付服務包括以下幾個部分：
-
待上線SQL評審，預估性能瓶頸——控製係統新上線SQL質量
-
線上係統SQL性能監控、優化——持續優化係統線上SQL，降低係統資源消耗
-
線上係統預警、故障診斷分析——及時對係統故障進行幹預和處理，最快響應客戶的請求
-
數據庫版本變更、優化器參數調整評估——重大數據庫參數調整評估，數據庫版本變更評估
-
對業務需求、設計進行評審——對表、索引結構設計進行谘詢——業務切合數據庫設計，最有效的管控係統風險
SQL審核服務工作兩年來，我們一直都是在做線上和待上線SQL代碼的審核分析，通過專業的SQL審核團隊和方法我們取得了一定的成效，係統上線變更均沒有出現重大故障，得到了客戶的認可。
言歸正常，接下來我們來分享這兩個case：
1、複合索引前導列選擇
待上線SQL審核發現核心業務模塊的某SQL是大表全表掃描
執行計劃如下：
這裏我們看到，有一個全表掃描，優化器評估的是 263k，
以下是表的統計信息：
我們看到有3.2億的數據量。
可能說到優化全表掃描，大家都會想到要創建索引，那麼這個索引如何創建呢？
在執行計劃的第五部分謂詞部分我截取如下：
這裏有service ID 和enddate兩個謂詞，我們要通過對這兩個謂詞創建索引進行優化，我們看統計信息裏麵，enddate的不同值有1628520個，serviceID的不同值有402個，而表有3.2億的數據量，是很大的。
那麼如何創建索引？可能有以下三種方案：
1、 serviceID+enddate複合索引
2、 enddate+serviceID複合索引
3、 enddate的單列索引
我們在創建索引的時候有以下原則：
核心業務SQL要求SQL執行計劃盡量最高效，而剛好這個SQL是個核心業務SQL，用戶需要響應時間最可能最短，由於複合索引的效率一般是遠優於單列索引的，所以enddate的單列這裏不再考慮
再看serviceid、enddate兩列如何創建複合索引
我們首先來看serviceID+enddate的複合索引
這個索引在索引範圍掃描的過程中消耗的邏輯讀是33，回表次數達到373。
接下來是enddate+serviceID的複合索引：
這個在索引範圍掃描的過程消耗的邏輯讀是873，回表次數是1213次。
為什麼兩者的索引範圍掃描消耗的邏輯讀相差這麼大。我們來看看謂詞部分。
前者serviceid+enddate複合索引索引範圍掃描全部走access，access就是說我在索引範圍掃描的時候，在掃描頁塊節點中是不存在多餘的頁塊的，也就是掃描的每一個葉塊都是滿足SQL的謂詞條件的。
enddate+serviceid複合索引索引範圍掃描enddate字段走access、serviceid走filter，因為在非等值列作為索引前導時，索引範圍掃描葉塊找到滿足enddate的條件後，在雙向鏈表下做索引掃描並不能保證掃描的每一個葉塊都是滿足serviceid條件的，所以需要取出來之後再過濾一次，這樣就多了一次filter的操作。
因此我們看到，兩者的區別就在索引範圍掃描這裏。在第二種方式中，讀取的頁塊裏麵存在無效的頁塊。
複合索引的前導的選擇：
1.索引的前導盡量讓更多的核心業務sql能夠使用，這樣能夠避免再另外創建單獨的單列索引
2.對於單獨的核心業務的sql語句，要求該SQL的性能盡可能最優，對於同時出現等值和非等值條件（比如範圍、like等）時複合索引應該盡量選擇where條件中等值條件列作為索引的前導列。例如where條件中同時出現state=X and create_time>sysdate-1，此時應該創建state+create_time的複合索引，state字段作為索引的前導列。
接下來跟大家分享一個跟綁定變量有關的內容
關於綁定變量的問題在OLTP係統中我們經常會遇到，因為在OLTP中存在很多SQL代碼都是搞並發的，所以我們希望在SQL語句中盡量能使用到綁定變量，綁定變量最重要的作用就是代碼重用
綁定變量主要作用：
用於代碼重用，降低係統的硬解析成本
綁定變量的弊端：
綁定變量窺視副作用、自適應遊標缺陷、影響執行計劃的評估
綁定變量如何使用：
結合綁定變量窺視、直方圖、字段的業務類型綜合考慮
接下來我們來看一個案例
執行計劃是全表掃描，cost計算為1433，Oracle版本12.1.0.2? 帶入具體值單次邏輯讀和物理讀需要4萬多，平均返回0行數據，屬於核心業務SQL（同業類類型的SQL還有一批）
在這條SQL中，不存在任何的分組或者聚合函數，返回0行數據走全表掃描多半不是最合理的執行計劃
我們來看它的執行計劃
下麵是這張表的統計信息
我們看到processcode的不同值隻有112個，number bucket有111，而表的數據量達到139萬，我們看到這一列上是有索引的，那這裏優化器是否應該走索引更高效了？
1、processcode是傾斜字段且已收集直方圖，查詢表數據得知processcode=‘UMCPMTELSTE_ZT’占表中數據量比例極少。
2、_optim_peek_user_binds被置為FALSE,窺視特性關閉。
3、索引沒有失效、無sql profile、hint等其他影響。
由於processcode=‘UMCPMTELSTE_ZT’占有表中數據比例極少，索引範圍掃描此時應該是最合理的執行計劃，優化器難道cost計算錯了？
添加hint/*+index(a IDX_INTERFACE_COMM_PROCESSCODE)*/比較cost成本
我們看到成本是2513，是遠遠大於之前的全表掃描的成本的。
CBO根據成本計算會選擇全表掃描的執行計劃，那麼如何優化了：
我想很多DBA都遇到過執行計劃異常的情況，我們采取最輕量級的解決方式，就是通過sql profile、sql plan baseline等綁定執行計劃。但是這三種都有一個弊端，如果SQL text的變化，比如列的增加或者減少，這時候sql profile、sql plan baseline就會失效。
第二種方式就是通過hint 固化代碼。但是這種方式也存在一個問題，我們剛剛提到processcode並不是一個“均配”字段，比如說A對應的值是極少數的，而B對應的數據很多。
那這種情況下我們如果把代碼固定的話，如果別的SQL傳入的是B，則對於這個業務的SQL執行計劃並不是最合理的。
還是回到SQL優化的問題，我們不能避開它，看執行計劃的時候，一定要注意謂詞的部分，我們發現無論是索引範圍掃描還是全表掃描，Oracle的優化器在評估的時候一定是先去評估processcode=：BIZID，評估該謂詞條件後通過查詢轉換來進一步校驗‘UMCPMTELSTE_ZT’=:BIZID是否合理
這裏我們得出在where條件中同時出現processcode=：BIZID和processcode=具體值（UMCPMTELSTE_ZT）的時候，優化器這裏是從最左邊開始考慮各個執行計劃的執行成本，然後通過查詢轉換在filter階段再去校驗‘UMCPMTELSTE_ZT’=:BIZID是否合理。
如果說優化器能優先考慮到processcode=‘UMCPMTELSTE_ZT’具體值，則CPU是否會自動選擇索引範圍掃描呢？
我們將具體值和綁定變量做個替換，where processcode=‘UMCPMTELSTE_ZT’and processcode=：BIZID，執行計劃如下：
Oracle在索引範圍掃描的時候會直接考慮processcode=‘UMCPMTELSTE_ZT’，然後再第二步的時候檢查具體值是否等於綁定變量，做一個邏輯校驗。
大家仔細看可能會發現，上麵的那段代碼事實上是不嚴謹的，processcode=：BIZID和processcode=具體值（UMCPMTELSTE_ZT）同時出現，而實際的綁定變量都傳入的等於前麵那個具體值，如果是為了校驗傳入變量是否和具體值一致也沒必要丟給數據庫去判斷，而開發人員這種寫法則導致了該SQL沒有選擇合適的索引，這種SQL 在評審階段都是需要我們關注的。
這裏我們也提一下個人認為使用綁定變量的標準供大家參考：
綁定變量 - 哪些列適合使用綁定變量
綁定變量窺視關閉、收集直方圖環境下：
1、建議使用綁定變量的列：對於流水號、訂單號、用戶ID、電話號碼、身份證
號碼、證件號碼等，這類列的特點是列的唯一性較好、列的不同值數量跟表的
數據量比較接近，而這類列涉及的sql語句往往存在高並發，寫成綁定變量會
大大減少數據庫的解析成本。
2、不建議使用綁定變量的列：能夠枚舉的列（比如狀態類型字段，這部分類型
字段往往不同值較少，一般即使上千萬的數據表中該列也隻有十多個、幾十個
的不同值), 即使不寫綁定變量也隻會產生極少數的sql_id，這部分的oracle
解析消耗微乎其微，並且這種類型的列有可能存在傾斜數據，而如果使用綁定
變量，則對於傾斜數據的評估並不準確，可能導致該列無法走索引範圍掃描、
或者影響表JOIN階段驅動表的選擇等。
文章轉自數據和雲公眾號，原文鏈接
最後更新：2017-07-18 20:36:31