将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 条评论
登录 后参与评论

相关文章

来自专栏逸鹏说道

SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句

先看常用的一种表结构设计方式: ? 那么可能会遇到一种典型的查询方式,主子表关联,查询子表中的某些(或者全部)Key点对应的Value,横向显示(也即以行的方式...

2609
来自专栏张善友的专栏

Net Framework 2.0 事务处理

事务 处理事务是构建许多业务逻辑的一个重要方面。 .NET Framework 2.0 中的事务 在 .NET Framework 2...

1678
来自专栏Java3y

Hibernate【inverse和cascade属性】知识要点

Inverse属性 Inverse属性:表示控制权是否转移.. true:控制权已转移【当前一方没有控制权】 false:控制权没有转移【当前一方有控制权】 I...

2764
来自专栏程序员的SOD蜜

TOP语句放到表值函数外,效率异常低下的原因分析

SQLSERVER的表值函数是SQLSERVER 2005以来的新特性,由于它使用比较方便,就像一个单独的表一样,在我们的系统中大量使用。有一个获取客户数据的S...

1939
来自专栏lgp20151222

session.save()返回值问题

一次会话状态中,持久化对象经历以下三种状态: 1 transient:对象不与数据库中任意数据相关联。(也就是主键上面没有值) 2 persistent:对象与...

1021
来自专栏Jimoer

java设计模式之代理模式

代理模式 代理模式是常见设计模式的一种,代理模式的定义是:为其他对象提供一种代理以控制对这个对象的访问。 在某些情况下,一个对象不适合或者不能直接引用另一个对象...

3035
来自专栏散尽浮华

Mysql的二进制日志binlog的模式说明

binlog模式总共可分为以下三种:row,statement,mixed 1.Row 日志中会记录成每一行数据被修改的形式,然后在slave端再对相同的数据进...

1815
来自专栏coder修行路

Go基础之--操作Mysql(一)

关于标准库database/sql database/sql是golang的标准库之一,它提供了一系列接口方法,用于访问关系数据库。它并不会提供数据库特有的方法...

3219
来自专栏xingoo, 一个梦想做发明家的程序员

【Hibernate那点事儿】—— Hibernate知识总结

前言: 上一篇简单的讲解了下Hibernate的基础知识。这里对Hibernate比较重要的一些知识点,进行总结和归纳。 总结的知识点: 1 关于...

1858
来自专栏Java面试笔试题

Session的save()、update()、merge()、lock()、saveOrUpdate()和persist()方法分别是做什么的?有什么区别?

Hibernate的对象有三种状态:瞬时态(transient)、持久态(persistent)和游离态(detached),如第135题中的图所示。瞬时态的实...

763

扫码关注云+社区