【云和恩墨大讲堂】复合索引与绑定变量

讲师简介

邓秋爽(小鱼)

云和恩墨专家,有超过5年超大型数据库专业服务经验,擅长oracle 数据库优化、SQL优化和troubleshooting

今晚的恩墨大讲堂将有我为大家分享SQL审核中的两个典型案例,如下:

1复合索引前导列选择 2绑定变量分享——某业务SQL性能问题分析

在介绍这两个case之前先对目前我们的SQL审核交付服务做一个简单说明,我们目前的SQL审核交付服务包括以下几个部分:

  • 待上线SQL评审,预估性能瓶颈——控制系统新上线SQL质量
  • 线上系统SQL性能监控、优化——持续优化系统线上SQL,降低系统资源消耗
  • 线上系统预警、故障诊断分析——及时对系统故障进行干预和处理,最快响应客户的请求
  • 数据库版本变更、优化器参数调整评估——重大数据库参数调整评估,数据库版本变更评估
  • 对业务需求、设计进行评审——对表、索引结构设计进行咨询——业务切合数据库设计,最有效的管控系统风险

SQL审核服务工作两年来,我们一直都是在做线上和待上线SQL代码的审核分析,通过专业的SQL审核团队和方法我们取得了一定的成效,系统上线变更均没有出现重大故障,得到了客户的认可。

言归正常,接下来我们来分享这两个case:

1、复合索引前导列选择

待上线SQL审核发现核心业务模块的某SQL是大表全表扫描

执行计划如下:

这里我们看到,有一个全表扫描,优化器评估的是 263k,

以下是表的统计信息:

我们看到有3.2亿的数据量。

可能说到优化全表扫描,大家都会想到要创建索引,那么这个索引如何创建呢?

在执行计划的第五部分谓词部分我截取如下:

这里有service ID 和enddate两个谓词,我们要通过对这两个谓词创建索引进行优化,我们看统计信息里面,enddate的不同值有1628520个,serviceID的不同值有402个,而表有3.2亿的数据量,是很大的。

那么如何创建索引?可能有以下三种方案:

1、 serviceID+enddate复合索引 2、 enddate+serviceID复合索引 3、 enddate的单列索引

我们在创建索引的时候有以下原则:

核心业务SQL要求SQL执行计划尽量最高效,而刚好这个SQL是个核心业务SQL,用户需要响应时间最可能最短,由于复合索引的效率一般是远优于单列索引的,所以enddate的单列这里不再考虑

再看serviceid、enddate两列如何创建复合索引

我们首先来看serviceID+enddate的复合索引

这个索引在索引范围扫描的过程中消耗的逻辑读是33,回表次数达到373。

接下来是enddate+serviceID的复合索引:

这个在索引范围扫描的过程消耗的逻辑读是873,回表次数是1213次。

为什么两者的索引范围扫描消耗的逻辑读相差这么大。我们来看看谓词部分。

前者serviceid+enddate复合索引索引范围扫描全部走access,access就是说我在索引范围扫描的时候,在扫描页块节点中是不存在多余的页块的,也就是扫描的每一个叶块都是满足SQL的谓词条件的。

enddate+serviceid复合索引索引范围扫描enddate字段走access、serviceid走filter,因为在非等值列作为索引前导时,索引范围扫描叶块找到满足enddate的条件后,在双向链表下做索引扫描并不能保证扫描的每一个叶块都是满足serviceid条件的,所以需要取出来之后再过滤一次,这样就多了一次filter的操作。

因此我们看到,两者的区别就在索引范围扫描这里。在第二种方式中,读取的页块里面存在无效的页块。

复合索引的前导的选择:

1.索引的前导尽量让更多的核心业务sql能够使用,这样能够避免再另外创建单独的单列索引 2.对于单独的核心业务的sql语句,要求该SQL的性能尽可能最优,对于同时出现等值和非等值条件(比如范围、like等)时复合索引应该尽量选择where条件中等值条件列作为索引的前导列。例如where条件中同时出现state=X and create_time>sysdate-1,此时应该创建state+create_time的复合索引,state字段作为索引的前导列。

接下来跟大家分享一个跟绑定变量有关的内容

关于绑定变量的问题在OLTP系统中我们经常会遇到,因为在OLTP中存在很多SQL代码都是搞并发的,所以我们希望在SQL语句中尽量能使用到绑定变量,绑定变量最重要的作用就是代码重用

  • 绑定变量主要作用:

用于代码重用,降低系统的硬解析成本

  • 绑定变量的弊端:

绑定变量窥视副作用、自适应游标缺陷、影响执行计划的评估

  • 绑定变量如何使用:

结合绑定变量窥视、直方图、字段的业务类型综合考虑

接下来我们来看一个案例

执行计划是全表扫描,cost计算为1433,Oracle版本12.1.0.2? 带入具体值单次逻辑读和物理读需要4万多,平均返回0行数据,属于核心业务SQL(同业类类型的SQL还有一批)

在这条SQL中,不存在任何的分组或者聚合函数,返回0行数据走全表扫描多半不是最合理的执行计划

我们来看它的执行计划

下面是这张表的统计信息

我们看到processcode的不同值只有112个,number bucket有111,而表的数据量达到139万,我们看到这一列上是有索引的,那这里优化器是否应该走索引更高效了?

1、processcode是倾斜字段且已收集直方图,查询表数据得知processcode=‘UMCPMTELSTE_ZT’占表中数据量比例极少。 2、_optim_peek_user_binds被置为FALSE,窥视特性关闭。 3、索引没有失效、无sql profile、hint等其他影响。 由于processcode=‘UMCPMTELSTE_ZT’占有表中数据比例极少,索引范围扫描此时应该是最合理的执行计划,优化器难道cost计算错了?

添加hint/*+index(a IDX_INTERFACE_COMM_PROCESSCODE)*/比较cost成本

我们看到成本是2513,是远远大于之前的全表扫描的成本的。

CBO根据成本计算会选择全表扫描的执行计划,那么如何优化了:

我想很多DBA都遇到过执行计划异常的情况,我们采取最轻量级的解决方式,就是通过sql profile、sql plan baseline等绑定执行计划。但是这三种都有一个弊端,如果SQL text的变化,比如列的增加或者减少,这时候sql profile、sql plan baseline就会失效。

第二种方式就是通过hint 固化代码。但是这种方式也存在一个问题,我们刚刚提到processcode并不是一个“均配”字段,比如说A对应的值是极少数的,而B对应的数据很多。

那这种情况下我们如果把代码固定的话,如果别的SQL传入的是B,则对于这个业务的SQL执行计划并不是最合理的。

还是回到SQL优化的问题,我们不能避开它,看执行计划的时候,一定要注意谓词的部分,我们发现无论是索引范围扫描还是全表扫描,Oracle的优化器在评估的时候一定是先去评估processcode=:BIZID,评估该谓词条件后通过查询转换来进一步校验‘UMCPMTELSTE_ZT’=:BIZID是否合理

这里我们得出在where条件中同时出现processcode=:BIZID和processcode=具体值(UMCPMTELSTE_ZT)的时候,优化器这里是从最左边开始考虑各个执行计划的执行成本,然后通过查询转换在filter阶段再去校验‘UMCPMTELSTE_ZT’=:BIZID是否合理。

如果说优化器能优先考虑到processcode=‘UMCPMTELSTE_ZT’具体值,则CPU是否会自动选择索引范围扫描呢?

我们将具体值和绑定变量做个替换,where processcode=‘UMCPMTELSTE_ZT’and processcode=:BIZID,执行计划如下:

Oracle在索引范围扫描的时候会直接考虑processcode=‘UMCPMTELSTE_ZT’,然后再第二步的时候检查具体值是否等于绑定变量,做一个逻辑校验。

大家仔细看可能会发现,上面的那段代码事实上是不严谨的,processcode=:BIZID和processcode=具体值(UMCPMTELSTE_ZT)同时出现,而实际的绑定变量都传入的等于前面那个具体值,如果是为了校验传入变量是否和具体值一致也没必要丢给数据库去判断,而开发人员这种写法则导致了该SQL没有选择合适的索引,这种SQL 在评审阶段都是需要我们关注的。

这里我们也提一下个人认为使用绑定变量的标准供大家参考:

绑定变量 - 哪些列适合使用绑定变量

绑定变量窥视关闭、收集直方图环境下:

1、建议使用绑定变量的列:对于流水号、订单号、用户ID、电话号码、身份证 号码、证件号码等,这类列的特点是列的唯一性较好、列的不同值数量跟表的 数据量比较接近,而这类列涉及的sql语句往往存在高并发,写成绑定变量会 大大减少数据库的解析成本。 2、不建议使用绑定变量的列:能够枚举的列(比如状态类型字段,这部分类型 字段往往不同值较少,一般即使上千万的数据表中该列也只有十多个、几十个 的不同值), 即使不写绑定变量也只会产生极少数的sql_id,这部分的oracle 解析消耗微乎其微,并且这种类型的列有可能存在倾斜数据,而如果使用绑定 变量,则对于倾斜数据的评估并不准确,可能导致该列无法走索引范围扫描、 或者影响表JOIN阶段驱动表的选择等。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-12-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Golang语言社区

终于遇到goroutine死锁的BUG了

今天测试用Go语言写的角色服务器,发现在模拟大量客户端获取角色列表的时候会卡住,但是服务器程序的CPU占用率为零。分析并经过代码检查确认是goroutine死锁...

38315
来自专栏PHP技术

MyISAM InnoDB 区别

 MyISAM 和 InnoDB 讲解   InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定。基本的...

3375
来自专栏数据和云

从商用到开源:15个维度,全面剖析DB2与MySQL数据库的差异

编辑手记 MySQL是目前最流行的开源数据库,由于其部署方便,运维简单,被广泛用于互联网的各个领域。随着整体IT架构的变更,传统的金融,电信业务,也逐渐走上从商...

3857
来自专栏技术博文

MyISAM InnoDB 区别

MyISAM 和 InnoDB 讲解   InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定。基本的差...

3207
来自专栏WindCoder

网易MySQL微专业学习笔记(十一)-MySQL业务优化与设计

这个系列属于个人学习网易云课堂MySQL数据库工程师微专业的相关课程过程中的笔记,本篇为其“MySQL业务优化与设计”中的MySQL数据类型相关笔记。

381
来自专栏乐沙弥的世界

Buffer cache 的调整与优化(一)

Buffer Cache是SGA的重要组成部分,主要用于缓存数据块,其大小也直接影响系统的性能。当Buffer Cache过小的时候,将会造成更多的

673
来自专栏技术专栏

慕课网高并发实战(二)-并发基础

左图为最简单的高速缓存的配置,数据的读取和存储都经过高速缓存,CPU核心与高速缓存有一条特殊的快速通道;主存与高速缓存都连在系统总线上(BUS)这条总线还用于其...

973
来自专栏JAVA烂猪皮

Java 内存泄漏分析和对内存设置

为了判断 Java 中是否有内存泄漏,我们首先必须了解 Java 是如何管理内存的。下面我们先给出一个简单的内存泄漏的例子,在这个例子中我们循环申请 Objec...

752

所有您需要了解的关于Elasticsearch 5.0:索引管理

我们看到两种主要的Elasticsearch索引使用模式 - 全局索引和滚动索引。多年来,Elasticsearch增加了一些功能,可以极大地改善这些模式的工作...

2823
来自专栏数据和云

SQL查询提速秘诀,避免锁死数据库的数据库代码

由于数据库领域仍相对不成熟,每个平台上的 SQL 开发人员都在苦苦挣扎,一次又一次犯同样的错误。当然,数据库厂商在取得一些进展,并继续在竭力处理较重大的问题。

1143

扫码关注云+社区