《由索引未被使用，看SQL開發規范落地》要點：
本文介紹了由索引未被使用，看SQL開發規范落地，希望對您有用。如果有疑問，可以聯系我們。

作者介紹

王科,新炬網絡架構師.Oracle OCM,10年以上運維管理經驗,擅長運維服務與運維工具的融合應用,在數據資產管理、云計算、大數據相關領域也均有一定的研究及實踐.

上次丁俊大師在社群上做了CBO優化器和坑爹案例的分享后,反響不是一般的強烈,但其中也有一部分同學表示太高大上了(我也是這樣覺得的),消化起來相當有難度,于是便有了本文.繞開復雜的CBO優化器不說,本文將幫你理清那些因為SQL語句編寫規范問題導致沒有充分利用索引來大幅提升效率的使用場景.

一、SQL無法走索引的情況及解決思路

因為數據庫優化器不夠智能,或者一些邏輯原因,導致SQL在比較適合走索引的情況下卻無法正確利用索引.這時候,除了給數據庫需要的統計信息之外,SQL語句本身還必須要給優化器足夠多的額外有效信息,幫助優化器能夠選擇更好的執行計劃.要讓優化器正確選擇需要的索引,要考慮兩點：

• 如何避免優化器的限制
• 根據業務數據特點改寫SQL語句

說明：這里說的走不了索引,是指走不了正常的RANGE SCAN,非(FAST) FULL INDEX SCAN.

SQL無法走索引常見的有如下8種情況：

1. 統計信息不準確
2. 索引列的值允許為NULL
3. 謂詞使用了不等于(<>, !=)
4. LIKE前通配或全通配的查詢
5. 索引列使用了函數、數學運算、其它表達式等
6. 使用了隱式類型轉換
7. 查詢轉換失敗
8. 其它語句邏輯原因

第一、二種情況在現實中比較常見,解決辦法也相對比較簡單,下面就不再作詳細展開了.

謂詞使用了不等于(<>,!=),走不了索引

解決方法：

1. 如果不等條件之外的值不多,而且是確定的,可以改為等值或IN查詢,比如status狀態字段一般值類別很少；
2. 如果不等條件之外的值很多,可以改為“> OR <”的形式,當然第2種方法包含了方法1.

舉個例子,先構建測試場景：

謂詞使用<>,無法利用索引：

將<>改寫為OR連接后,能夠正確使用索引,走OR擴展：

如果業務允許,改寫為下列語句也是走索引的,不再演示.

SELECT * FROM t?WHERE t.NAME IN (‘ORADB1′,’ORADB2′,’ORADB3’);

LIKE前通配或全通配的查詢,走不了索引

解決方法,有如下三種：

(1)根據業務需求,是否可以把前通配去掉

原來全通配,無法走索引：

把前通配去掉,改為后通配,可以正常使用索引：

(2)和此LIKE一樣的前通配或全通配的SQL有很多,此謂詞的LIKE變化不大?如果是,考慮建立函數索引,否則對于全通配問題最好辦法就是全文索引.

創建instr函數索引：

(3)如果只是前通配,可以使用reverse函數索引(不是翻轉鍵索引)

原始語句：
SELECT ?* FROM t WHERE t.NAME LIKE ‘%ORADB1’;

創建reverse函數索引,并改寫語句,注意查找值要倒序：

注意：如果通配查詢的是中文,要注意使用REVERSE翻轉條件值,因為REVERSE內部會按字節翻轉的,正確寫法如：

SELECT * FROM t WHERE REVERSE(t.name) LIKE REVERSE(‘數據’)||’%’;

否則查詢出來的數據不對,將可能影響到業務的正常運行.

索引列使用了函數、數學運算、其他表達式等,走不了索引

解決方法：去掉對索引列的相關運算,保持索引列純凈.

目前優化器對一些數學運算,還無法做很好的消除動作,所以對于索引列應該盡量保持純凈,否則可能無法用上正確的索引.

舉例：

把語句的條件改寫一下,將運算去掉：

以上例子只是簡單的數學運算,可能的運算還有和其他列運算,比如where ID+ext_col…

記住一個原則：盡量保持索引列純凈.

使用了隱式類型轉換,走不了索引

解決方法：必須避免隱式類型轉換,全部要求顯式類型轉換(非索引列),且避免對索引列進行類型轉換(有函數索引除外).如果類型不一致,不管是否發生自動類型轉換,謂詞的右值應該顯式轉換為與索引列保持一致(對于非索引列的運算也應該如此).

舉例：

從以上兩次查詢對比來看,第一次查詢發生了類型轉換,可以通過執行計劃中的謂詞信息獲知.通過分析發現,X因為是VARCHAR2,優先級比數值類型低,遇到數值類型,會TO_NUMBER隱式轉換,所以索引失效.第二次查詢,通過傳入與索引列類型一致的字符串后,得以解決.

查詢轉換失敗,走不了索引

查詢轉換是非常復雜的過程,ORACLE CBO的查詢轉換有好幾十種,比如CVM ：complex view merging ,SU:subquery unnest, JPPD:JOIN PREDICATE PUSH DOWN等(在10053文件里都可以看到).如果查詢轉換失敗,那么必將影響后續優化器的一些操作,比如JPPD中JOIN謂詞無法推入到視圖中,那么很可能視圖就無法走索引了.而且,查詢轉換有很多BUG,觸發BUG需要找到原因,比如設置隱含參數、fix control等,或者改寫SQL繞過BUG.如下例所示：

其中AB_XRTOFFREC_201703是UNION ALL查詢組成的視圖,這個查詢在10.2.0.4上很正常,升級到11.2.0.4后執行計劃顯示不走索引,性能非常差.

在10g中的執行計劃：

在11g中的錯誤執行計劃：

通過收集統計信息都無效,將優化器降級到10.2.0.4即有效.很顯然,這是引入了BUG或者新的限制.一旦遇到這種是BUG或限制導致的,可以通過10053跟蹤文件或者SQLT來進行分析.對于這條語句無法走JPPD查詢轉換,在10053中就可以找到原因：

然后在MOS中查看得知是BUG:9380298,默認開關關閉.

 

ORACLE針對這樣的查詢,為了防止遇到笛卡爾積,默認把修復BUG的補丁關閉了.顯然通過設置_fix_control參數打開9380298 fix即可.

語句邏輯問題,導致優化器選擇不了索引

舉一個典型的例子,先準備測試表,并在其上創建一個組合索引：

查詢需求：查找創建時間是2013年的,并且最后ddl時間比創建時間大1天以上的對象.

這個索引是組合索引,上面的語句對前導列進行了運行,也不符合走index skip scan的條件,所以,走FULL TABLE SCAN.那么是否可以通過邏輯改寫走索引呢,基于保持索引列純凈的原則,將create_date移到右邊,語句如下：

改寫后發現,還是沒有走索引,因為Oracle認為前導列右邊的created不固定,無法從指定索引處查找.通過分析得知,Oracle謂詞傳遞有一定限制,create_date+1無法做謂詞傳遞給last_ddl_time.再次改寫：

此時Oracle知道將謂詞傳遞給last_ddl_time了,T.LAST_DDL_TIME>=TO_DATE(‘ 2013-01-01 00:00:00’, ‘syyyy-mm-dd?hh24:mi:ss’).當然,也可以手動謂詞傳遞,last_ddl_time肯定大于等于DATE’2013-1-2′：

還未完,我們繼續往下看：

如果查詢條件中無t.created>=DATE’2013-1-1’,即如下面語句：

–由此兩數據比較可知,應該走索引更佳.因為沒有其他過濾條件,可以考慮建立函數索引：

SQL> CREATE INDEX idx1_t_object ON t_objects(last_ddl_time-created);

–注意收集直方圖,因為分布不均

SQL> exec dbms_stats.gather_table_stats(ownname => USER,tabname => ‘t_objects’,estimate_percent => 100,method_opt => ‘for all indexed columns’,cascade => TRUE);

當然,對于兩個都是范圍的查詢,這里只能通過一個列來輪詢索引,先做access,再做filter.

SQL語句的邏輯改寫很重要,往往通過邏輯改寫就能改變SQL的執行計劃,從不好的計劃到好的計劃,比如semi join,anti join與or,往往走FILTER導致執行計劃較差,這時候就需要通過邏輯等價改寫.邏輯等價改寫往往需要掌握一些集合的知識,比如NOT (A AND B)==NOT A OR NOT B,NOT (A OR B)==NOT A AND NOT B等.

總結

SQL有索引而不走索引的情況還有很多,比如在DBLINK查詢中,可能走不了索引,這時候需要通過driving_site hint或者遠程庫建立視圖等方式解決等,需要綜合從語法語義、索引選擇性、索引訪問特點等多方面進行分析.

二、如何將SQL開發規范落地

上面說到的問題,說到底都是不遵守數據庫開發規范的問題.說到數據庫開發規范,估計很多企業都有制定對應的規范及要求,但說到落地執行情況,這個就比較困難了.如果企業在意旨上是期望開發人員去學懂規范,然后學以致用,就有點太理想化了.于是,為了保證開發人員真的是按照數據庫開發規范來編寫代碼,很多企業就在應用上線前增加了一道SQL上線審核的工序.

說到SQL上線審核,關鍵要解決三個問題：

1、如何在上線的應用版本中發現新增的SQL語句；

2、新增SQL存在哪些問題,如何快速準確的定位；

3、對于問題SQL,如何快速提供優化方案.

這三個問題,是一環扣一環的,解決不了前面的問題,就無從解決后面的問題.然而,應用系統SQL眾多,如果單靠人工,難度是很大的,專家資源投入就更不說了,顯然不能滿足當今IT系統高速發展的需要.

這里跟大家分享我們在這方面的一些實踐和成果.通過結合多年的運維和優化經驗,我們自主研發了SQL審核工具,不僅可以自動化完成SQL上線審核,還可以做到SQL的性能監控和自動優化,達到SQL全生命周期管理的效果.對于SQL上線審核,我們將開發規范規則化后落到SQL審核平臺,內置了4個維度、200多種常見的審查規則,還支持靈活的按需添加規則.同時,審查的不只是SQL語句本身,還包括了對表的模型設計、索引的構建.

 

在應用新版本上線前,通過SQL審核平臺,自動分析出版本的新增SQL,基于以上規則對新增SQL進行審查,并自動提供優化建議,可生成可視化的報表和詳細報告.不管是DBA還是開發人員都可以基于此平臺,對問題進行確認和解決,實現系統優化前移、提升應用版本質量的目標.

 

三、總結

本文主要和大家分享了SQL無法走索引的一些常見情景及解決方法,當然,SQL的規范化使用是十分重要的,SQL的優化也不僅僅局限于索引的優化.所以,只有平時多積累,結合理論多實踐,遇到問題時才能運籌帷幄,對癥下藥、藥到病除.另外,企業在IT建設中要重視開發規范的落地執行,必要時使用合適的工具,在加速IT環境建設效率的同時,還能兼顧到IT系統的建設質量,做到兩不誤.

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

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