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

不念不忘少年蓝@ 2022-04-06 09:47 462阅读 0赞

摘要： 编辑手记：子查询是SQL中比较重要的一种语法，恰当地应用会很大程度上提高SQL的性能，若用的不得当，也可能会带来很多问题。因此子查询也是SQL比较难优化的部分。今天一起来学习最常见的几种优化子查询到方式。  
编辑手记：子查询是SQL中比较重要的一种语法，恰当地应用会很大程度上提高SQL的性能，若用的不得当，也可能会带来很多问题。因此子查询也是SQL比较难优化的部分。今天一起来学习最常见的几种优化子查询到方式。  
作者简介：  
韩锋  
640?wx\_fmt=jpeg&wxfrom=5&wx\_lazy=1  
精通包括Oracle、MySQL、informix等多种关系型数据库，有丰富的数据库架构设计开发经验。就职于宜信。  
针对子查询，优化器支持了多种优化策略。Oracle查询转换功能主要有启发式（基于规则）查询转换以及基于Cost的查询转换两种，针对子查询主要有Subquery Unnest、Push Subquery等。查询转换的目的是转化为Join（包括Semi、Anti Join等），充分利用索引、Join技术等高效访问方式提高效率。如果子查询不能unnest（启发式），可以选择把子查询转换为Inline View（基于Cost）；如果都不可以，那么子查询就会最后执行，可能会看到类似Filter的操作。  
1.子查询转换  
下面先通过一个示例看看。  
640?wx\_fmt=png&wxfrom=5&wx\_lazy=1  
//上面代码准备了必要的数据环境，并收集相关对象的统计信息  
640?wx\_fmt=png&wxfrom=5&wx\_lazy=1  
//默认情况下，是将上面的操作转换为表间关联方式执行  
640?wx\_fmt=png&wxfrom=5&wx\_lazy=1  
/\*通过提示no\_unnest，禁止了子查询解嵌套。一次采用了原始的方式执行，子查询部分的作用就是"FILTER"\*/  
2.子查询合并  
子查询合并是指优化器不再单独为子查询生成执行计划，而是将子查询合并到主查询中，最终为合并后的结果生成一个最优的执行计划。可以通过参数\_simple\_view\_merging或者提示MERGE/NO\_MERGE来控制是否开启、关闭子查询合并。  
根据子查询的复杂程度，子查询可分为简单子查询、复杂子查询。  
所谓简单子查询，是指可以简单将子查询字段投影到外部的情况。对于这种情况，优化器采取的是启发式策略，即满足条件下就行合并。  
复杂子查询是指存在分组行数的情况。针对这种情况，优化器采取的是基于代价的策略，最终是否转换取决于成本。当然还有一些子查询是无法进行合并的。  
下面通过几个示例看一下。  
640?wx\_fmt=png&wxfrom=5&wx\_lazy=1  
//这种方式下，并没有进行子查询合并。  
下面强制看看效果  
640?wx\_fmt=png&wxfrom=5&wx\_lazy=1  
/\*这里可以看到，没有再生成内联视图，子查询被合并了。那为什么默认没有进行子查询合并呢？从成本可见，显然不合并的成本更低\*/  
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：  
640?wx\_fmt=png&wxfrom=5&wx\_lazy=1  
/\*示例中的子查询引用表DEPT，最终转换为两个表的哈希半连接。也就是说，exists子句中的子查询被展开，其中的对象与主查询中的对象直接进行半关联操作\*/  
// IN的情况类似，如下：  
640?wx\_fmt=png&wxfrom=5&wx\_lazy=1  
2）IN/EXISTS转换为ANTI JOIN：  
640?wx\_fmt=png&wxfrom=5&wx\_lazy=1  
/\*优化器将NOT EXISTS后的子查询做解嵌套，然后选择了哈希的反连接。这种转换属于基于代价的查询转换。\*/  
//下面看看NOT IN的情况  
640?wx\_fmt=png&wxfrom=5&wx\_lazy=1  
/\*和NOT EXISTS类似，也选择了哈希连接，只不过是HASH JOIN ANTI NA。这里的NA，实际表示Null-Aware的意思，在11g及以后的版本中，Oracle增加了对空值敏感的反关联的支持\*/  
3）关联子查询的解嵌套  
在对于关联子查询的解嵌套过程中，会将子查询构造出一个内联视图，并将内联视图与主查询的表进行关联。这个操作可以通过参数\_unnest\_subquery来控制。这种转换属于启发式查询转换。  
640?wx\_fmt=png&wxfrom=5&wx\_lazy=1  
/\*在ID=2的步骤中生成了内联视图，然后跟外部表进行的哈希连接。下面尝试修改参数，看优化器如何处理\*/  
640?wx\_fmt=png&wxfrom=5&wx\_lazy=1  
//这里转换成了嵌套循环的一种特列FILTER  
4.子查询推进  
子查询推进是一项对未能合并或者反嵌套的子查询优化的补充优化技术。这一技术是在9.2版本引入的。通常情况下，未能合并或者反嵌套的子查询的子计划会被放置在整个查询计划的最后执行，而子查询推进使得子查询能够提前被评估，使之可以出现在整体执行计划较早的步骤中，从而获得更优的执行计划。可以通过PUSH\_SUBQ/NO\_PUSH\_SUBQ来控制。  
640?wx\_fmt=png&wxfrom=5&wx\_lazy=1  
//默认情况下，就是用子查询推进技术。  
对比一下，我们看看强制不使用的情况  
640?wx\_fmt=png&wxfrom=5&wx\_lazy=1  
/\*对比上面，对了一步FILTER。这里使用了嵌套循环，每一个EMP表的记录，都对应一次子查询的查询，获得MAX值\*/  
5.子查询分解  
所谓子查询分解，是指由WITH创建的复杂查询语句存储在临时表中，按照与一般表相同的方式使用该临时表的功能。从概念上来看它与嵌套视图比较类似，但各自有其优缺点。优点在于子查询如果被多次引用，使用嵌套视图就需要被执行多次，尤其在海量数据中满足条件的结果非常少得情况下，两者差别很明显。  
使用WITH子查询的优点就在于其复杂查询语句只需要执行一次，但结果可以在同一个查询语句中被多次使用。  
缺点是使用WITH子查询，由于不允许执行查询语句变形，所以无效的情况也比较多。尤其是WITH中的查询语句所创建的临时表无法拥有索引，当其查询结果的数据量比较大的时候，很可能会影响执行效率。  
下面通过一个是示例看看。  
640?wx\_fmt=png&wxfrom=5&wx\_lazy=1  
/\*从上面可以看出，在WITH中有两个子查询语句，但只创建了一个临时表，这是因为WITH中的第二个子查询使用的是第一个子查询的执行结果。在这种情况下，逻辑上只允许创建一个临时表，没有必要再次创建。在处理WITH临时表时，如果临时表可以被优先执行而且可以缩减连接之前的数据量，就可以采用嵌套循环连接，否则必须使用哈希连接\*/  
6.子查询缓存  
针对某些子查询操作，优化器可以将子查询的结果进行缓存，避免重复读取。这一特性在FILTER型的子查询或标量子查询中都能观察到。看一个示例。  
640?wx\_fmt=png&wxfrom=5&wx\_lazy=1  
/\*注意Id=3步骤的Start=3(emp表中的deptno有3个不同的值，这里就重复执行3次)。这体现了Cache技术，标量子查询中也有类似的Cache技术。\*/  
子查询相关文章：  
Oracle性能优化之查询转换类-自查询