将SQL优化做到极致 - 子查询优化

编辑手记:子查询是SQL中比较重要的一种语法,恰当地应用会很大程度上提高SQL的性能,若用的不得当,也可能会带来很多问题。因此子查询也是SQL比较难优化的部分。今天一起来学习最常见的几种优化子查询到方式。

作者简介:

韩锋

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

针对子查询,优化器支持了多种优化策略。Oracle查询转换功能主要有启发式(基于规则)查询转换以及基于Cost的查询转换两种,针对子查询主要有Subquery Unnest、Push Subquery等。查询转换的目的是转化为Join(包括Semi、Anti Join等),充分利用索引、Join技术等高效访问方式提高效率。如果子查询不能unnest(启发式),可以选择把子查询转换为Inline View(基于Cost);如果都不可以,那么子查询就会最后执行,可能会看到类似Filter的操作。

1.子查询转换

下面先通过一个示例看看。

//上面代码准备了必要的数据环境,并收集相关对象的统计信息

//默认情况下,是将上面的操作转换为表间关联方式执行

/*通过提示no_unnest,禁止了子查询解嵌套。一次采用了原始的方式执行,子查询部分的作用就是"FILTER"*/

2.子查询合并

子查询合并是指优化器不再单独为子查询生成执行计划,而是将子查询合并到主查询中,最终为合并后的结果生成一个最优的执行计划。可以通过参数_simple_view_merging或者提示MERGE/NO_MERGE来控制是否开启、关闭子查询合并。

根据子查询的复杂程度,子查询可分为简单子查询、复杂子查询

  • 所谓简单子查询,是指可以简单将子查询字段投影到外部的情况。对于这种情况,优化器采取的是启发式策略,即满足条件下就行合并。
  • 复杂子查询是指存在分组行数的情况。针对这种情况,优化器采取的是基于代价的策略,最终是否转换取决于成本。当然还有一些子查询是无法进行合并的。

下面通过几个示例看一下。

//这种方式下,并没有进行子查询合并。

下面强制看看效果

/*这里可以看到,没有再生成内联视图,子查询被合并了。那为什么默认没有进行子查询合并呢?从成本可见,显然不合并的成本更低*/

3.解嵌套子查询

解嵌套子查询是指在对存在嵌套子查询的复杂语句进行优化时,查询转换器会尝试将子查询展开,使得其中的表能与主查询中的表关联,从而获得更优的执行计划。部分子查询反嵌套属于启发式查询转换,部分属于基于代价的转换。

系统中存在一个参数来控制解嵌套子查询—_unnest_subquery。参数_unnest_subquery在8i中的默认设置是false,从9i开始其默认设置是true。然而9i在非嵌套时不考虑成本。只有在10g中才开始考虑两种不同选择的成本,并选取成本较低的方式。当从8i升级到9i时,可能想阻塞某些查询的非嵌套。利用子查询中的no_unnest提示可以完成这一点。在8i和9i中,如果star_transformation_enabled=true,则非嵌套时被禁用(即使用了提示)。在11g环境下还受优化器参数_optimizer_unnest_all_subqueries控制。此外,提示UNNEST/NO_UNNEST可以控制是否进行解嵌套。

下面我们通过几个示例看看解嵌套子查询。

1)IN/EXISTS转换为SEMI JOIN:

/*示例中的子查询引用表DEPT,最终转换为两个表的哈希半连接。也就是说,exists子句中的子查询被展开,其中的对象与主查询中的对象直接进行半关联操作*/

// IN的情况类似,如下:

2)IN/EXISTS转换为ANTI JOIN:

/*优化器将NOT EXISTS后的子查询做解嵌套,然后选择了哈希的反连接。这种转换属于基于代价的查询转换。*/

//下面看看NOT IN的情况

/*和NOT EXISTS类似,也选择了哈希连接,只不过是HASH JOIN ANTI NA。这里的NA,实际表示Null-Aware的意思,在11g及以后的版本中,Oracle增加了对空值敏感的反关联的支持*/

3)关联子查询的解嵌套

在对于关联子查询的解嵌套过程中,会将子查询构造出一个内联视图,并将内联视图与主查询的表进行关联。这个操作可以通过参数_unnest_subquery来控制。这种转换属于启发式查询转换。

/*在ID=2的步骤中生成了内联视图,然后跟外部表进行的哈希连接。下面尝试修改参数,看优化器如何处理*/

//这里转换成了嵌套循环的一种特列FILTER

4.子查询推进

子查询推进是一项对未能合并或者反嵌套的子查询优化的补充优化技术。这一技术是在9.2版本引入的。通常情况下,未能合并或者反嵌套的子查询的子计划会被放置在整个查询计划的最后执行,而子查询推进使得子查询能够提前被评估,使之可以出现在整体执行计划较早的步骤中,从而获得更优的执行计划。可以通过PUSH_SUBQ/NO_PUSH_SUBQ来控制。

//默认情况下,就是用子查询推进技术。

对比一下,我们看看强制不使用的情况

/*对比上面,对了一步FILTER。这里使用了嵌套循环,每一个EMP表的记录,都对应一次子查询的查询,获得MAX值*/

5.子查询分解

所谓子查询分解,是指由WITH创建的复杂查询语句存储在临时表中,按照与一般表相同的方式使用该临时表的功能。从概念上来看它与嵌套视图比较类似,但各自有其优缺点。优点在于子查询如果被多次引用,使用嵌套视图就需要被执行多次,尤其在海量数据中满足条件的结果非常少得情况下,两者差别很明显。

  • 使用WITH子查询的优点就在于其复杂查询语句只需要执行一次,但结果可以在同一个查询语句中被多次使用。
  • 缺点是使用WITH子查询,由于不允许执行查询语句变形,所以无效的情况也比较多。尤其是WITH中的查询语句所创建的临时表无法拥有索引,当其查询结果的数据量比较大的时候,很可能会影响执行效率。

下面通过一个是示例看看。

/*从上面可以看出,在WITH中有两个子查询语句,但只创建了一个临时表,这是因为WITH中的第二个子查询使用的是第一个子查询的执行结果。在这种情况下,逻辑上只允许创建一个临时表,没有必要再次创建。在处理WITH临时表时,如果临时表可以被优先执行而且可以缩减连接之前的数据量,就可以采用嵌套循环连接,否则必须使用哈希连接*/

6.子查询缓存

针对某些子查询操作,优化器可以将子查询的结果进行缓存,避免重复读取。这一特性在FILTER型的子查询或标量子查询中都能观察到。看一个示例。

/*注意Id=3步骤的Start=3(emp表中的deptno有3个不同的值,这里就重复执行3次)。这体现了Cache技术,标量子查询中也有类似的Cache技术。*/

子查询相关文章:

Oracle性能优化之查询转换类-自查询

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

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

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Linyb极客之路

分布式系统唯一ID生成方案汇总

系统唯一ID是我们在设计一个系统的时候常常会遇见的问题,也常常为这个问题而纠结。生成ID的方法有很多,适应不同的场景、需求以及性能要求。所以有些比较复杂的系统会...

752
来自专栏H2Cloud

C++ FFLIB 之FFDB: 使用 Mysql&Sqlite 实现CRUD

摘要: C++ 操作DB真心不是太省心的事,一方面C++操作DB的接口大部分都使用C API,如Mysql、Sqlite 提供的API。尽管其C API文档已经...

3315
来自专栏owent

给客户端写得LRU缓存

由于我们的客户端的元素和资源比较多,cocos框架的各种库质量参差不齐,导致了有些地方加载速度实在很慢。并且没有一个统一的内存管理机制导致了整个内存占用不太好控...

721
来自专栏文渊之博

SQL优化技巧--远程连接对象引起的CTE性能问题

背景    最近SSIS的开发过程中遇到几个问题。其中使用CTE时,遇到一个远程连接对象,结果导致严重的性能问题,为了应急我就修改了代码。   之前我写了一篇介...

1647
来自专栏沃趣科技

oracle 查询转换初探

Oracle‍‍查询转换初探 作者‍:邱大龙‍‍ 概述 Oracle查询转换器的作用是把原始sql重写为语义相同的语句,目的是为了获得更高效的sql。 ...

3505
来自专栏性能与架构

为什么SQL优化中建议用UNION来代替OR

在SQL优化相关资料中,通常可以看到一个建议:用UNION来代替OR 举例 采用 OR 语句: SELECT * FROM a, b WHERE a.p...

34310
来自专栏java进阶架构师

「mysql优化专题」你们要的多表查询优化来啦!请查收(4)

相信这内连接,左连接什么的大家都比较熟悉了,当然还有左外连接什么的,基本用不上我就不贴出来了。这图只是让大家回忆一下,各种连接查询。 然后要告诉大家的是,需要根...

392
来自专栏性能与架构

Redis部分重同步的实现思路

部分重同步功能由以下三个部分构成: (1)master和slave的复制偏移量 (2)master的复制积压缓冲区 (3)服务器的运行ID(run ID) 复制...

2553
来自专栏豆包的专栏

共享内存无锁队列的实现

共享内存无锁队列是老调重弹了,相关的实现网上都能找到很多。但看了公司内外的很多实现,都有不少的问题,于是自己做了重新实现。主要是考虑了一些异常情况加强健壮性,并...

1.7K2
来自专栏电光石火

mybatis获取update的id

平常我门都是更新数据,用更新的条件再查询一次,得到更新的记录。这样我门就进行了两次数据库操作,链接了两次数据库。增加了接口的处理事件,因为链接数据库是很耗时...

3016

扫描关注云+社区