SQL优化案例-正确的使用索引（二）

阳光穿透心脏的1/2处 2022-05-24 10:35 200阅读 0赞

下面sql 30秒执行出结果，查看sql谓词中有like，我们知道谓词中有这样的语句是不走索引的（为了保护客户的隐私，表名和部分列已经重命名）。

SELECT /*+1*/
    CHECKNUM AS PINGZBSM,
    CHECKDATE,
    XXXMODE,
    XXXRESULT,
    (SELECT RESULT 
    FROM (select ID,to_char(WMSYS.WM_CONCAT(xxxnum||xxxtype||xxxmode||xxxresult)) RESULT 
           from OOOO_XXXCHECKLOG 
          WHERE CHECKDATE BETWEEN DATE'2018-05-04' AND DATE'2018-05-04' and xxxtype like '%PAR'
          GROUP BY ID
        ) b where b.id=a.id
    ) RESULT,
    CLERKNUM AS CHECKNUM
    FROM OOO_XXXECHECKLOG A;

![20180525150228103][]

逻辑读600多万。查看索引情况如下

![20180525150301488][]

表过滤返回数据量如下：

SQL> select count(*) from OOOO_XXXCHECKLOG;
    2799616
    select count(*) from OOOO_XXXCHECKLOG WHERE CHECKDATE BETWEEN DATE'2018-05-04' AND DATE'2018-05-04' and xxxtype like '%PAR';
     12856
    select count(*) from OOOO_XXXCHECKLOG WHERE CHECKDATE BETWEEN DATE'2018-05-04' AND DATE'2018-05-04';
    197984

通过查询上面返回数据可知，因为xxxtype不走索引，所以通过索引要回表197984次，如果走了索引只回表12856次。

下面我们建立REVERSE索引IDX\_ID\_TYPE\_RE

SELECT /*+OOOO_XXXCHECKLOG index(IDX_ID_TYPE_RE) 2*/
    CHECKNUM AS PINGZBSM,
    CHECKDATE,
    XXXMODE,
    XXXRESULT,
    (SELECT RESULT 
    FROM (select ID,to_char(WMSYS.WM_CONCAT(xxxnum||xxxtype||xxxmode||xxxresult)) RESULT 
           from OOOO_XXXCHECKLOG 
          WHERE CHECKDATE BETWEEN DATE'2018-05-04' AND DATE'2018-05-04' and REVERSE(xxxtype) like 'RAP%'
          GROUP BY ID
        ) b where b.id=a.id
    ) RESULT,
    CLERKNUM AS CHECKNUM
    FROM OOO_XXXECHECKLOG A;

查看执行计划如下，逻辑读将为300万，但是时间还是维持在18秒，根本原因在于这个索引因为标量子查询的问题被访问700万次导致。

![20180525150413720][]

下面我们改写sql如下

SELECT /*+ index(OOOO_XXXCHECKLOG IDX_ID_TYPE_RE) 3*/
    CHECKNUM AS PINGZBSM,
    CHECKDATE,
    XXXMODE,
    XXXRESULT,
    B.RESULT,
    CLERKNUM AS CHECKNUM
    FROM OOO_XXXECHECKLOG A
    left join (select ID,to_char(WMSYS.WM_CONCAT(xxxnum||xxxtype||xxxmode||xxxresult)) RESULT 
           from OOOO_XXXCHECKLOG 
          WHERE CHECKDATE BETWEEN DATE'2018-05-04' AND DATE'2018-05-04' and REVERSE(xxxtype) like 'RAP%'
          GROUP BY ID
        ) b on b.id=a.id;

![20180525150505717][]

执行计划中出现index\_skip\_scan。

下面我们创建如下索引:

create index idx_date_seal_re on OOOO_XXXCHECKLOG(CHECKDATE,REVERSE(xxxtype));

![20180525150712210][]

可以看到，逻辑读降到64424，50个物理读是因为刚刚创建索引的原因，sql也秒出。

**|  作者简介**

### 姚崇·沃趣科技高级数据库技术专家 ###

### 熟悉Oracle数据库内部机制，丰富的数据库及RAC集群层故障诊断、性能调优、OWI、数据库备份恢复及迁移经验。 ###

[20180525150228103]: /images/20220524/92f53231e2024cce8769a03d59bd7d5e.png
[20180525150301488]: /images/20220524/57cc98c4be374c24a2ec475ce47ddd11.png
[20180525150413720]: /images/20220524/5e35df78b9ed4159baecba18509bd7ea.png
[20180525150505717]: /images/20220524/66063ff26d664927bc5ee3cb0512456a.png
[20180525150712210]: /images/20220524/b776971555db4f53aecf4e5ccbb730d6.png