SQL优化案例-正确的使用索引(二)

下面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;

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

表过滤返回数据量如下:

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万次导致。

下面我们改写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;

执行计划中出现index_skip_scan。 

下面我们创建如下索引:

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

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

|  作者简介

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

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

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏用户画像

sql server 实验5.2 触发器的建立与使用

一、实验目的                                                     

682
来自专栏developerHaoz 的安卓之旅

知乎 Matisse 源码解析,带你探究高效图片选择库的秘密

可以看到 Matisse 的可拓展性是非常强的,不仅可以自定义我们需要的主题,而且还可以按照需求来过滤出我们想要的文件,除此之外,Matisse 采用了建造者模...

1061
来自专栏刘望舒

Android架构(一)MVP全解析

前言 关于架构的文章,博主很早就想写了,虽说最近比较流行MVVM,但是MVP以及MVC也没有过时之说,最主要还是要根据业务来选择合适的架构。当然现在写MVP的文...

17810
来自专栏小白安全

过安全狗waf注入技巧

 过安全狗waf注入 前提web页面存在注入漏洞 测试开始本地搭建环境 环境   安全狗(APACE版)V4.0 防护规则全开 ...

2885
来自专栏「3306 Pai」社区

《那些年,我在乙方的日子 -- 神谕篇NO1》

某个夏日的午后,窗外知了在大声鸣叫。而我却在睡梦中跟基友一起吃鸡,正准备抢空投时 。手机突然铃声响起,惊醒后一看是领导电话,一下子回到了现实中。心想又得去公司吃...

672
来自专栏编程

ContentProvider使用方法详解

是四大组件之一,其本质上是一个标准化的数据管道,它屏蔽了底层的数据管理和服务等细节,以标准化的方式在 应用间共享数据。用户可以灵活实现所封装的数据存储以及增删改...

19510
来自专栏杨建荣的学习笔记

数据清理的遗留问题处理(r6笔记第87天)

最近处理一个遗留问题,感觉手动修复真是让人抓狂,所以花了点力气写了一个半自动的脚本,总算从这个繁琐的工作中解放出来了。 问题的背景如下图所示。 存在一个很大的统...

2533
来自专栏程序员互动联盟

android apk 防止反编译技术第四篇-对抗JD-GUI

又到周末一个人侘在家里无事可干,这就是程序员的悲哀啊。好了我们利用周末的时间继续介绍android apk防止反编译技术的另一种方法。前三篇我们讲了加壳技术、运...

3296
来自专栏V站

PHP丨实现禁止国外IP访问WP博客

你只需将以上代码加入到主题中的function.php即可。刷新页面试试效果, 如果主题中出现那么恭喜你,封禁成功了!! 其中,wp_redirect( ‘ht...

1626
来自专栏web编程技术分享

【Java框架型项目从入门到装逼】第六节 - 用ajax请求后台数据

2737

扫码关注云+社区