Oracle運(yùn)維案例之反序函數(shù)索引的使用

給大家分享一個(gè)我們分組遇到的sql優(yōu)化的案例，案例非本人所負(fù)責(zé)的數(shù)據(jù)庫，本人只是搬運(yùn)工。
這個(gè)案例發(fā)生在去年，發(fā)現(xiàn)原因是nets主機(jī)cpu上升，開發(fā)運(yùn)營找到了DA，隨后DA對其情況進(jìn)行了分析，最后定位到一條低效SQL展開分析。（關(guān)于DA，是平安集團(tuán)數(shù)據(jù)庫技術(shù)部對DBA的一個(gè)細(xì)分）

以下就是定位到的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;

首先，根據(jù)這條SQL的相關(guān)表（T_SOFTPHONE_CALLINFO）了解到以下信息。
1.這是一張電話呼入的信息表（這里場景做COUNT統(tǒng)計(jì)）
2.ANI和DANI傳入的變量是電話號碼，一個(gè)是座機(jī)號碼，一個(gè)是手機(jī)號碼
3.使用的HINT索引是時(shí)間字段（updated_date）常規(guī)B-TREE索引，執(zhí)行計(jì)劃也是走的此索引RANGE方式，這點(diǎn)沒有問題。

對上訴SQL有一定了解之后，DA首先收集了一下下歷史執(zhí)行的一些情況，結(jié)論如下：
通過對比最近幾個(gè)月的增長情況，發(fā)現(xiàn)雖然執(zhí)行計(jì)劃沒有改變，但是執(zhí)行頻率從原來每15分鐘1000次增加到大約60000次，單次邏輯讀的消耗也增長了數(shù)倍，隨著業(yè)務(wù)量和數(shù)據(jù)量的攀升，這種時(shí)間字段的索引方式越來越低效，成為一條隱患sql，在某個(gè)時(shí)間點(diǎn)問題就一下子就凸顯出來了，急需優(yōu)化改進(jìn)。
ps：此時(shí)nets的體量已接近30TB

對此，DA提出了一些意見和質(zhì)疑：
1、 第一、第二個(gè)參數(shù)都是手機(jī)號，而且從歷史來看都是輸入的完整的手機(jī)號碼，為啥要用like，能否直接改成等號？
2、 兩個(gè)電話字段都有單獨(dú)的索引，如果不用like，可以將以上SQL語句優(yōu)化一下，走對應(yīng)的電話號碼索引，改寫形式類似如下方式：

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左右，這是極大的效率提升。
但是之后，開發(fā)和運(yùn)營給了新的業(yè)務(wù)反饋，發(fā)現(xiàn)事情并沒有這么簡單了。
開發(fā)運(yùn)營：T_SOFTPHONE_CALLINFO中的電話號碼是從隨機(jī)數(shù)據(jù)中獲取的，可能包含有0等前綴，如果要統(tǒng)計(jì)到所有信息，無法直接使用等號，加0和不加0，與電話呼入所在地有關(guān)，外地加0，本地不加0，你在A地呼95511，可能沒加0；你在B地用相同的手機(jī)號呼95511，就可能加0。經(jīng)過DA的排查確實(shí)如此，手機(jī)號甚至有還有特殊取代符號的存在。
思考：
既然如此，看樣子，LIKE的方式無法改變了，字段前使用%會抑制索引的使用，這樣就無法用到對應(yīng)的索引，如何規(guī)避這個(gè)問題并且使用到高效的索引呢？通過自己的思考和同事的建議，結(jié)合目前的業(yè)務(wù)場景，給出了一個(gè)可靠的方案，就是創(chuàng)建一個(gè)函數(shù)索引，反序函數(shù)索引！

當(dāng)機(jī)立斷，在想到方法后立即進(jìn)行了測試和分析階段。
1.首先創(chuàng)建了兩個(gè)對應(yīng)字段的函數(shù)索引

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);

性能測試下來，在大多數(shù)場景下效率提升都非常明顯，原來平均幾十萬的消耗基本區(qū)間維持在到幾百，原來的走的是時(shí)間字段索引，現(xiàn)在走的是兩個(gè)電話號碼字段的反序函數(shù)索引，于是當(dāng)即開發(fā)就安排第一輪整改，期待有好的效果。
附執(zhí)行計(jì)劃類似如下:

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

但是，過了幾天之后，中間又發(fā)生了一些小插曲，開發(fā)反饋雖然大部分場景效率都是極高的，但仍有一小部分場景效率較差，帶入值后消耗較高，雖然那些值的場景可能不多，但也會偶爾出現(xiàn)。DA分析馬上想到了是否是數(shù)據(jù)出現(xiàn)傾斜的情況，才會導(dǎo)致少部分值效率差。
在猜測了情況后，馬上登陸系統(tǒng)去查看了一下著這張的數(shù)據(jù)傾斜情況，果不其然，有些值傾斜非常厲害，有一個(gè)800萬，還有很多100到200萬字段值，當(dāng)取到這些極值的時(shí)候，光靠一個(gè)單值索引，效率必定很差，如圖下：

于是進(jìn)入了新一輪的思考分析，如何整改能滿足所有場景，是否能直接創(chuàng)建更高效的索引？剎那間發(fā)現(xiàn)這條sql使用了三個(gè)條件（updated_date，ani（dani）, CREATED_BY），開發(fā)也提供思路說，在三個(gè)條件下過濾出來的數(shù)據(jù)并不會很多，這時(shí)候就有新的思路，能否創(chuàng)建一個(gè)復(fù)合索引呢，按選擇性排列，是否會有驚人的效果？
話不多說，馬上開啟了新一輪的性能測試分析，通過幾種組合的復(fù)合索引和單值索引測試，具體步驟不必多說了，請直接看下列測試數(shù)據(jù)：

想必經(jīng)過反復(fù)的性能分析測試和實(shí)驗(yàn)，結(jié)合上面的測試數(shù)據(jù)，大家已經(jīng)知道哪種方式最好了。最后我們也采用了最適合這個(gè)場景的改造方案，又進(jìn)行了一輪整改，監(jiān)控了后面的幾天運(yùn)行情況，效果極佳，終于完全解決了所有的問題，皆大歡喜，覺得是個(gè)不錯(cuò)的案例，給大分享一下！

最后在給大家普及一下，可能有人同學(xué)會搞錯(cuò)反序索引和反序函數(shù)索引，這是不同的概念：
反向索引也是一種B樹索引，但它物理上將按照列順序保存的每個(gè)索引鍵值進(jìn)行了反轉(zhuǎn)。例如，索引鍵是20，用16進(jìn)制存儲這個(gè)標(biāo)準(zhǔn)B樹索引鍵的兩個(gè)字節(jié)是C1，15，那么反向索引存儲的字節(jié)就是15，C1，目的主要是減少打散索引葉子塊的爭用，針對大并發(fā)插入場景比較實(shí)用，但弊端也比較明顯，當(dāng)使用范圍查詢時(shí)，由于數(shù)據(jù)分散在不同塊內(nèi)，性能也會有所降低。
函數(shù)索引是一種基于函數(shù)使用的索引，針對某些字段使用特殊函數(shù)時(shí)，如果需要使用索引可以建立相關(guān)的索引，這個(gè)案例場景中，我需要實(shí)現(xiàn)的是將數(shù)字進(jìn)行完全顛倒（并非字節(jié)顛倒），概念有所不用，更多是站在查詢效率和場景使用的角度，所以綜合考慮更適用于反序函數(shù)的使用，并且建立相關(guān)反序函數(shù)的索引。

心得：
這一案例涉及的sql很簡單，但要求DA具備扎實(shí)的基本功及良好的業(yè)務(wù)嗅覺。在數(shù)據(jù)庫愈發(fā)智能、日常運(yùn)維愈發(fā)簡單的今天，DBA需與業(yè)務(wù)深度融合，根據(jù)業(yè)務(wù)特點(diǎn)進(jìn)行sql優(yōu)化及架構(gòu)設(shè)計(jì)。