Oracle營運案例之反序函數索引的使用

標籤：Oracle   Sql最佳化   

給大家分享一個我們分組遇到的sql最佳化的案例，案例非本人所負責的資料庫，本人只是搬運工。
這個案例發生在去年，發現原因是nets主機cpu上升，開發運營找到了DA，隨後DA對其情況進行了分析，最後定位到一條低效SQL展開分析。（關於DA，是平安集團資料庫技術部對DBA的一個細分）

以下就是定位到的sql：

SELECT /*+ index(c IDX_CALLINFO_UPDATED_DATE) */COUNT(1)  FROM T_SOFTPHONE_CALLINFO CWHERE C.updated_date >= sysdate - 1 / 48   and (C.ANI like ‘%‘ || :1 or C.DANI like ‘%‘ || :2)   and C.CREATED_BY = :3;

首先，根據這條SQL的相關表（T_SOFTPHONE_CALLINFO）瞭解到以下資訊。
1.這是一張電話呼入的資訊表（這裡情境做COUNT統計）
2.ANI和DANI傳入的變數是電話號碼，一個是有線電話號碼，一個是手機號碼
3.使用的HINT索引是時間欄位（updated_date）常規B-TREE索引，執行計畫也是走的此索引RANGE方式，這點沒有問題。

對上訴SQL有一定瞭解之後，DA首先收集了一下下曆史執行的一些情況，結論如下：
通過對比最近幾個月的增長情況，發現雖然執行計畫沒有改變，但是執行頻率從原來每15分鐘1000次增加到大約60000次，單次邏輯讀的消耗也增長了數倍，隨著業務量和資料量的攀升，這種時間欄位的索引方式越來越低效，成為一條隱患sql，在某個時間點問題就一下子就凸顯出來了，急需最佳化改進。
ps：此時nets的體量已接近30TB

對此，DA提出了一些意見和質疑：
1、 第一、第二個參數都是手機號，而且從曆史來看都是輸入的完整的手機號碼，為啥要用like，能否直接改成等號？
2、 兩個電話欄位都有單獨的索引，如果不用like，可以將以上SQL語句最佳化一下，走對應的電話號碼索引，改寫形式類似如下方式：

SELECT (SELECT /*+ index(c IDX_SOFTPHONE_CALLINFO_ANI) */         COUNT(1)          FROM T_SOFTPHONE_CALLINFO C         WHERE C.updated_date >= sysdate - 1 / 48           and (C.ANI = ‘159******22‘)           and C.CREATED_BY = ‘*******880‘) +       (SELECT /*+ index(c IDX_SOFTPHONE_CALLINFO_DANI) */         COUNT(1)          FROM T_SOFTPHONE_CALLINFO C         WHERE C.updated_date >= sysdate - 1 / 48           and (C.DANI = ‘159******22‘)           and C.CREATED_BY = ‘******880‘)         FROM DUAL;

通過這種改寫方式，原來的平均邏輯讀從3萬可以降低到200左右，這是極大的效率提升。
但是之後，開發和運營給了新的業務反饋，發現事情並沒有這麼簡單了。
開發運營：T_SOFTPHONE_CALLINFO中的電話號碼是從隨機資料中擷取的，可能包含有0等首碼，如果要統計到所有資訊，無法直接使用等號，加0和不加0，與電話呼入所在地有關，外地加0，本地不加0，你在A地呼95511，可能沒加0；你在B地用相同的手機號呼95511，就可能加0。經過DA的排查確實如此，手機號甚至有還有特殊取代符號的存在。
思考：
既然如此，看樣子，LIKE的方式無法改變了，欄位前使用%會抑制索引的使用，這樣就無法用到對應的索引，如何規避這個問題並且使用到高效的索引呢？通過自己的思考和同事的建議，結合目前的業務情境，給出了一個可靠的方案，就是建立一個函數索引，反序函數索引！

當機立斷，在想到方法後立即進行了測試和分析階段。
1.首先建立了兩個對應欄位的函數索引

Create index NETS2DATA.IDX_SOFTPHONE_CI_ANI_REV on NETS2DATA.T_SOFTPHONE_CALLINFO(reverse(ani)) tablespace NETS2DATA parallel 8 ;Createindex NETS2DATA.IDX_SOFTPHONE_CI_DANI_REV on NETS2DATA.T_SOFTPHONE_CALLINFO(reverse(dani)) tablespace NETS2DATA parallel 8;

2.改寫了sql

SELECT COUNT(1)FROM (SELECT /*+index(c IDX_SOFTPHONE_CI_ANI_REV) */        C.CALLINFO_ID         FROM T_SOFTPHONE_CALLINFO C        WHERE C.updated_date >= sysdate - 1 / 48          and (reverse(C.ANI) like :1 || ‘%‘)          and C.CREATED_BY = :2       UNION ALL       SELECT /*+index(c IDX_SOFTPHONE_CI_DANI_REV) */        C.CALLINFO_ID         FROM T_SOFTPHONE_CALLINFO C        WHERE C.updated_date >= sysdate - 1 / 48          and (reverse(C.DANI) like :3 || ‘%‘)          and C.CREATED_BY = :4);

效能測試下來，在大多數情境下效率提升都非常明顯，原來平均幾十萬的消耗基本區間維持在到幾百，原來的走的是時間欄位索引，現在走的是兩個電話號碼欄位的反序函數索引，於是當即開發就安排第一輪整改，期待有好的效果。
附執行計畫類似如下:

Execution Plan----------------------------------------------------------Plan hash value: 1437385812----------------------------------------------------------------------------------------------------------| Id | Operation                   | Name                      | Rows | Bytes | Cost (%CPU)| Time    |----------------------------------------------------------------------------------------------------------|   0 |SELECT STATEMENT             |                           |    1 |    17 |    6   (0)| 00:00:01 ||   1 | SORT AGGREGATE             |                           |    1 |    17 |            |          ||* 2 |   TABLE ACCESS BY INDEX ROWID| T_SOFTPHONE_CALLINFO      |    1 |    17 |    6   (0)| 00:00:01 ||* 3 |    INDEX RANGE SCAN          | IDX_CALLINFO_UPDATED_DATE |    2 |       |    4   (0)| 00:00:01 |----------------------------------------------------------------------------------------------------------Predicate Information (identified byoperation id):---------------------------------------------------   2 - filter("ANI" LIKE ‘%152******96‘AND "ANI" IS NOT NULL)   3 -access("C"."UPDATED_DATE">[email protected]!-1)Statistics----------------------------------------------------------          8 recursive calls          0 db block gets    291086 consistent gets          0 physical reads          0 redo size       515 bytes sent via SQL*Net to client       492 bytes received via SQL*Net from client          2 SQL*Net roundtrips to/from client          0 sorts (memory)          0 sorts (disk)rows processedExecution Plan----------------------------------------------------------Plan hash value: 3534627589------------------------------------------------------------------------------------------------------| Id | Operation                   | Name                  | Rows | Bytes | Cost (%CPU)| Time    |------------------------------------------------------------------------------------------------------|   0 |SELECT STATEMENT             |                       |    1 |    17 |   831K (1)| 02:46:18 ||   1 | SORT AGGREGATE             |                      |    1 |    17 |            |          ||* 2 |   TABLE ACCESS BY INDEX ROWID| T_SOFTPHONE_CALLINFO |    1 |    17 |   831K (1)| 02:46:18 ||* 3 |    INDEX RANGE SCAN          | IDX_SOFTPHONE_ANI_ANT | 4989K|       | 14254   (1)| 00:02:52 |------------------------------------------------------------------------------------------------------Predicate Information (identified byoperation id):---------------------------------------------------   2 -filter("C"."UPDATED_DATE">[email protected]!-1)   3 - access(REVERSE("ANI") LIKE‘69******251%‘)      filter(REVERSE("ANI") LIKE ‘69******251%‘)Statistics----------------------------------------------------------          8 recursive calls          0 db block gets          137 consistent gets          0 physical reads          0 redo size       515 bytes sent via SQL*Net to client       492 bytes received via SQL*Net from client          2 SQL*Net roundtrips to/from client          0 sorts (memory)          0 sorts (disk)rows processed

但是，過了幾天之後，中間又發生了一些小插曲，開發反饋雖然大部分情境效率都是極高的，但仍有一小部分情境效率較差，帶入值後消耗較高，雖然那些值的情境可能不多，但也會偶爾出現。DA分析馬上想到了是否是資料出現傾斜的情況，才會導致少部分值效率差。
在猜測了情況後，馬上登陸系統去查看了一下著這張的資料扭曲情況，果不其然，有些值傾斜非常厲害，有一個800萬，還有很多100到200萬欄位值，當取到這些極值的時候，光靠一個單值索引，效率必定很差，下：

於是進入了新一輪的思考分析，如何整改能滿足所有情境，是否能直接建立更高效的索引？刹那間發現這條sql使用了三個條件（updated_date，ani（dani）, CREATED_BY），開發也提供思路說，在三個條件下過濾出來的資料並不會很多，這時候就有新的思路，能否建立一個複合索引呢，按選擇性排列，是否會有驚人的效果？
話不多說，馬上開啟了新一輪的效能測試分析，通過幾種組合的複合索引和單值索引測試，具體步驟不必多說了，請直接看下列測試資料：

想必經過反覆的效能分析測試和實驗，結合上面的測試資料，大家已經知道哪種方式最好了。最後我們也採用了最適合這個情境的改造方案，又進行了一輪整改，監控了後面的幾天運行情況，效果極佳，終於完全解決了所有的問題，皆大歡喜，覺得是個不錯的案例，給大分享一下！

最後在給大家普及一下，可能有人同學會搞錯反序索引和反序函數索引，這是不同的概念：
反向索引也是一種B樹索引，但它物理上將按照列順序儲存的每個索引索引值進行了反轉。例如，索引鍵是20，用16進位儲存這個標準B樹索引鍵的兩個位元組是C1，15，那麼反向索引儲存的位元組就是15，C1，目的主要是減少打散索引葉子塊的爭用，針對大並發插入情境比較實用，但弊端也比較明顯，當使用範圍查詢時，由於資料分散在不同塊內，效能也會有所降低。
函數索引是一種基於函數使用的索引，針對某些欄位使用特殊函數時，如果需要使用索引可以建立相關的索引，這個案例情境中，我需要實現的是將數字進行完全顛倒（並非位元組顛倒），概念有所不用，更多是站在查詢效率和情境使用的角度，所以綜合考慮更適用於反序函數的使用，並且建立相關反序函數的索引。

心得：
這一案例涉及的sql很簡單，但要求DA具備紮實的基本功及良好的業務嗅覺。在資料庫愈發智能、日常營運愈發簡單的今天，DBA需與業務深度融合，根據業務特點進行sql最佳化及架構設計。

Oracle營運案例之反序函數索引的使用