Oracle性能优化-子查询到特殊问题

编辑手记:前面我们介绍常用的子查询优化方法,但总有一些情况时在规律之外。谨慎处理方能不掉坑。

前文回顾:

性能优化之查询转换 - 子查询类 将SQL优化做到极致 - 子查询优化

作者简介:

韩锋

精通包括Oracle、MySQL、informix等多种关系型数据库,有丰富的数据库架构设计开发经验。就职于宜信。

1、空值问题

首先值得关注的问题是,在NOT IN子查询中,如果子查询列有空值存在,则整个查询都不会有结果。这可能是跟主观逻辑上感觉不同,但数据库就是这样处理的。因此,在开发过程中,需要注意这一点。看个例子吧。

SQL> select * from dual where 2 not in (select 1 from dual); D - X SQL> select * from dual where 2 not in (select 1 from dual union all select null from dual); no rows selected

显然,第二条语句在印象中应该会返回记录,但实际情况就是没有。

第二个值得关注的是,在11g之前,如果主表和子表的对应列未同时有NOT NULL约束,或都未加IS NOT NULL限制,则Oracle会走FILTER。11g有新的ANTI NA(NULL AWARE)优化,可以正常对子查询进行UNNEST。

注意此时的关联字段OBJECT_ID,是可为空的。示例模拟了11g以前的情况,此时走了最原始的FILTER

在确定子查询列object_id不会有NULL存在的情况下,又不想通过增加NOT NULL约束来优化,可以通过上面方式进行改写

在11g的默认情况下,走的就是ANTI NA(NA=NULL AWARE)

2、OR问题

对含有OR的Anti Join或Semi Join,注意有FILTER的情况。如果FILTER影响效率,可以通过改写为UNION、UNION ALL、AND等逻辑条件进行优化。优化的关键要看FILTER满足条件的次数。看下面的示例。

//上例中包含有OR条件的Semi Join,执行计划中使用了FILTER过滤,整个逻辑读消耗为69。

//下面通过改写,看看效果如何?

//将上面的OR连接修改为UNION,消除了FILTER。从成本或逻辑读等角度来看,整个逻辑读为30,较前面的69大大降低了

3、[NOT] IN/EXISTS问题

下面看两个关于[NOT] IN/EXISTS的问题。

1. IN/EXISTS

从原理来讲,IN操作是先进行子查询操作,再进行主查询操作。EXISTS操作是先进行主查询操作,再到子查询中进行过滤。

  • IN操作相当于对inner table执行一个带有distinct的子查询语句,然后得到的查询结果集再与outer table进行连接,当然连接的方式和索引的使用仍然等同于普通的两表连接。
  • EXISTS操作相当于对outer table进行全表扫描,用从中检索到的每一行与inner table做循环匹配输出相应的符合条件的结果,其主要开销是对outer table的全表扫描(full scan),而连接方式是nested loop方式。

当子查询表数据量巨大且索引情况不好(大量重复值等),则不宜使用产生对子查询的distinct检索而导致系统开支巨大的IN操作;反之当外部表数据量巨大(不受索引影响)而子查询表数据较少且索引良好时,不宜使用引起外部表全表扫描的EXISTS操作。如果限制性强的条件在子查询,一般建议使用IN操作。如果限制性强的条件在主查询,则使用EXISTS操作。

2. NOT IN/EXISTS

在子查询中,NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下,NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历)。

为了避免使用NOT IN,可以把它改写成外连接(Outer Joins)或NOT EXISTS。

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

原文发表时间:2017-02-21

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏desperate633

MySQL索引实现原理分析

目前大部分数据库系统及文件系统都采用 B-Tree(B 树)或其变种 B+Tree(B+树)作为索引结构。B+Tree 是数据库系统实现索引的首选数据结构。

653
来自专栏程序你好

30个MySQL千万级大数据SQL查询优化技巧详解

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

1173
来自专栏北京马哥教育

优化临时表使用,SQL语句性能提升100倍

【问题现象】 线上mysql数据库爆出一个慢查询,DBA观察发现,查询时服务器IO飙升,IO占用率达到100%, 执行时间长达7s左右。 SQL语句如下: SE...

2678
来自专栏Java帮帮-微信公众号-技术文章全总结

Java企业面试——数据库

数据库部分 数据表连接问题,左外连接、右外连接、内连接等 一、交叉连接(CROSS JOIN) 交叉连接(CROSS JOIN):有两种,显式的和隐式的,不...

2604
来自专栏IT技术精选文摘

MySQL-性能优化-索引和查询优化

701
来自专栏IT技术精选文摘

MySQL索引背后的数据结构及算法原理

874
来自专栏鹅厂少年的奇妙之旅

MySQL索引背后的数据结构及算法原理

本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题。特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MyS...

2423
来自专栏13blog.site

Spring+SpringMVC+MyBatis+easyUI整合优化篇(十三)数据层优化-表规范、索引优化

本文提要 最近写的几篇文章都是关于数据层优化方面的,这几天也在想还有哪些地方可以优化改进,结合日志和项目代码发现,关于数据层的优化,还是有几个方面可以继续修改的...

2948
来自专栏JavaQ

三分钟学习分布式ID方案

在分布式系统中,当数据库数据量达到一定量级的时候,需要进行数据拆分、分库分表操作,传统使用方式的数据库自有的自增特性产生的主键ID已不能满足拆分的需求,它只能保...

912
来自专栏Java3y

数据库两大神器【索引和锁】

所以说,如果我们写select * from user where username = 'Java3y'这样没有进行任何优化的sql语句,默认会这样做:

1300

扫码关注云+社区