给大家分享一个我们分组遇到的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万字段值,当取到这些极值的时候,光靠一个单值索引,效率必定很差,如图下:
Oracle运维案例之反序函数索引的使用

于是进入了新一轮的思考分析,如何整改能满足所有场景,是否能直接创建更高效的索引?刹那间发现这条sql使用了三个条件(updated_date,ani(dani), CREATED_BY),开发也提供思路说,在三个条件下过滤出来的数据并不会很多,这时候就有新的思路,能否创建一个复合索引呢,按选择性排列,是否会有惊人的效果?
话不多说,马上开启了新一轮的性能测试分析,通过几种组合的复合索引和单值索引测试,具体步骤不必多说了,请直接看下列测试数据:
Oracle运维案例之反序函数索引的使用

想必经过反复的性能分析测试和实验,结合上面的测试数据,大家已经知道哪种方式最好了。最后我们也采用了最适合这个场景的改造方案,又进行了一轮整改,监控了后面的几天运行情况,效果极佳,终于完全解决了所有的问题,皆大欢喜,觉得是个不错的案例,给大分享一下!

最后在给大家普及一下,可能有人同学会搞错反序索引和反序函数索引,这是不同的概念:
反向索引也是一种B树索引,但它物理上将按照列顺序保存的每个索引键值进行了反转。例如,索引键是20,用16进制存储这个标准B树索引键的两个字节是C1,15,那么反向索引存储的字节就是15,C1,目的主要是减少打散索引叶子块的争用,针对大并发插入场景比较实用,但弊端也比较明显,当使用范围查询时,由于数据分散在不同块内,性能也会有所降低。
函数索引是一种基于函数使用的索引,针对某些字段使用特殊函数时,如果需要使用索引可以建立相关的索引,这个案例场景中,我需要实现的是将数字进行完全颠倒(并非字节颠倒),概念有所不用,更多是站在查询效率和场景使用的角度,所以综合考虑更适用于反序函数的使用,并且建立相关反序函数的索引。

心得:
这一案例涉及的sql很简单,但要求DA具备扎实的基本功及良好的业务嗅觉。在数据库愈发智能、日常运维愈发简单的今天,DBA需与业务深度融合,根据业务特点进行sql优化及架构设计。