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

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

以下就是定位到的sql：

SELECT /*+ index(c IDX_CALLINFO_UPDATED_DATE) */
COUNT(1)
  FROM T_SOFTPHONE_CALLINFO C
WHERE 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">=SYSDATE@!-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 processed
Execution 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">=SYSDATE@!-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優化及架構設計。