性能优化之查询转换 - 子查询类

作者简介

韩锋

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

子查询,是SQL中常见的一种写法。对于优化器来说,子查询是较难优化的部分。Oracle提供了多种方式,对子查询进行查询转换。

1

子查询推进

子查询推进(又称子查询推入)是指优化器将子查询提前进行评估,使得优化器可以更早地介入优化以获得更优质的执行计划。这个技术可以通过提示PUSH_SUBQ/NO_PUSH_SUBQ控制。下面通过一个示例看看结果。

SQL> create table t_users as select * from dba_users; //表已创建 SQL> create index idx_user_created on t_users(created); //索引已创建 SQL> create table t_objects as select * from dba_objects; //表已创建

执行以下语句:

SQL> select /*+ no_push_subq(@inv)*/ /*hf1*/ * from t_objects u where created > (select /*+ qb_name(inv)*/ max(created) from t_users );

执行计划如下:

注:在这个语句中,我们通过提示强制不使用子查询推进技术。由执行计划可见,执行是按照T_OBJECTS和T_USRES进行的一个索引的嵌套循环。

使用子查询推进:

SQL> select /*hf2*/ * from t_objects u where created > ( select /*+ qb_name(inv)*/ max(created) from t_users );

执行计划如下:

在这个示例中,Oracle使用了子查询推入技术,且可以在OutLine中看到PUSH_SUBQ字样。从执行计划可见,没有出现两表关联,提前处理了子查询,生成MAX CREATED,然后全表扫描T_OBJECTS进行条件过滤,显然这种方式效率更高。

2

子查询解嵌套、展开

子查询解嵌套是指优化器将子查询展开,和外部的查询进行关联、合并,从而得到更优的执行计划。可以通过UNNEST/NO_UNNEST提示控制是否进行解嵌套。采用这种技术通常可以提高执行效率,原因是如果不解嵌套,子查询往往是最后执行的,作为FILTER条件来过滤外部查询;而一旦展开,优化器就可以选择表关联等更高效的执行方式,以提高效率。下面通过几个示例说明各种解嵌套的形式。

先看第一个示例:

SQL> create table t_tables as select * from dba_tables; Table created. SQL> select * from t_objects o where exists(select /*+ qb_name(inv)*/ 1 from t_tables t where t.owner=o.owner and t.table_name=o.object_name);

执行计划如下:

在这个示例中,对EXISTS的子查询进行了解嵌套,然后选择了半连接(SEMI JOIN)的关联方式。

再来看一个示例。

SQL> select * from t_objects o where not exists (select /*+ qb_name(inv)*/ 1 from t_tables t where t.owner=o.owner and t.table_name=o.object_name);

执行计划如下:

在这个示例中,对NOT EXISTS的子查询进行了解嵌套,然后选择了反连接(ANTI JOIN)的关联方式。

3

子查询分解

子查询分解是由WITH创建的复杂查询语句并存储在临时表中,可按照与一般表相同的方式使用该临时表的功能。这种方式可以把一个复杂的查询分成很多简单的部分,并让优化器去决定是产生中间数据集还是构建该查询复杂的扩展形式并对其进行优化。这种方式的优点在于,使用WITH子句的子查询在复杂查询语句中只需要执行一次,但结果可以在同一个查询语句中被多次使用。缺点在于,这种方式不允许语句变形,所以无效的情况较多。

下面看一个示例。

SQL> with user_obj as (select owner,count(*) cnt from t_objects group by owner) select u.user_id,u.username,o.cnt from t_users u,user_obj o where u.username=o.owner;

子查询定义为user_obj,在执行计划中以一个视图的形式(ID=2的步骤)出现,并与T_USRES进行了哈希关联。

上述过程并没有生成临时表,可通过一个提示materialize强制优化器创建临时表。

SQL> with user_obj as (select --+ materialize owner,count(*) cnt from t_objects group by owner ) select u.user_id,u.username,o.cnt from t_users u,user_obj o where u.username=o.owner;

执行计划如下:

引入了materialize提示后,由ID=2步骤可见,系统生成了一个临时表SYS_TEMP_XXX,并由这个表在后面与T_USERS进行了关联查询。

4

子查询合并

在语义等价的前提下,如果多个子查询产生的结果集相同,则优化器可以使用这种技术将多个子查询合并为一个子查询。这样的好处在于减少多次扫描产生的开销。可以通过NO_COALESCE_SQ/COALESCE_SQ提示来控制。下面看个示例:

select /*+ qb_name(mn)*/ t.* from t_tables t where exists (select /*+ qb_name(sub1)*/ 1 from t_tablespaces ts where t.tablespace_name=ts.tablespace_name and ts.block_size=8) and exists (select /*+ qb_name(sub2)*/ 1 from t_tablespaces ts where t.tablespace_name=ts.tablespace_name);

执行计划如下:

在这个查询中,外部对T_TABLES表的查询要同时满足SUB1和SUB2两个子查询,而SUB1在语义上又是SUB2的子集,因此优化器将两个子查询进行了合并(只进行一次对T_TABLESPACES表的扫描),然后与外部表T_TABLES进行半连接。

那么如果语义不等价又会怎么样呢?

select /*+ qb_name(mn)*/ t.* from t_tables t where exists (select /*+ qb_name(sub1)*/ 1 from t_tablespaces ts where t.tablespace_name=ts.tablespace_name and ts.block_size=8) and exists (select /*+ qb_name(sub2)*/ 1 from t_tablespaces ts where t.tablespace_name=ts.tablespace_name and ts.block_size=16);

执行计划如下:

在这个查询语句中,外部查询要满足两个子查询—SUB1和SUB2,但两者条件不同,不能简单合并。因此在执行计划中,分别对两者进行了扫描(直观感觉就是对T_TABLESPACES进行了两次扫描),然后再做关联查询。

5

子查询实体化

子查询实体化是指在上面WITH定义的查询中,将查询结果写入一张临时表中,后续的查询直接利用临时表中的数据。可以通过MATERIALIZE提示来控制。下面看个示例。

SQL> with v as (select /*+ MATERIALIZE */ * from t_users where username='SYS') select count(*) from v;

执行计划如下:

在ID=2的步骤中生成了一张临时表SYS_TEMP_xxx,并且这个临时表在后面会被直接使用。如果去掉提示会怎样呢?

SQL> with v as (select * from t_users where username='SYS') select count(*) from v;

执行计划如下:

此时不再生成临时表,直接解嵌套执行。

-----the end

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

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

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Golang语言社区

二十种实战调优MySQL性能优化的经验

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事...

442
来自专栏大数据

Zzreal的大数据笔记-SparkDay04

Spark SQL SparkSQL的前身是Shark,它抛弃原有Shark的代码,汲取了Shark的一些优点,如内存列存储(In-Memory Columna...

1829
来自专栏大数据

大数据干货系列(五)-Hive总结

Hive总结 一、本质 Hive基于一个统一的查询分析层,通过SQL语句的方式对HDFS上的数据进行查 询、统计和分析。 二、四大特点** • Hive本身不存...

2119
来自专栏禁心尽力

一次浴火重生的MySQL优化(EXPLAIN命令详解)

一直对SQL优化的技能心存无限的向往,之前面试的时候有很多面试官都会来一句,你会优化吗?我说我不太会,这时可能很多人就会有点儿说法了,比如会说不要使用通配符*...

1835
来自专栏光变

MySQL编程规范

641
来自专栏Kevin-ZhangCG

数据库索引

索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中,索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中,索引也允许数据库...

400
来自专栏Java3y

数据库面试题(开发者必看)

数据库常见面试题(开发者篇) ? ? 这里写图片描述 什么是存储过程?有哪些优缺点? 什么是存储过程?有哪些优缺点? 存储过程就像我们编程语言中的函数一样,封装...

3825
来自专栏北京马哥教育

数据库基础知识:数据库中的约束和三大范式

? 一.数据库中的范式: 范式, 英文名称是 Normal Form,它是英国人 E.F.Codd(关系数据库的老祖宗)在上个世纪70年代提出关系数据库模型后...

2777
来自专栏数据之美

一例 Hive join 优化实战

由于 hive 与传统关系型数据库面对的业务场景及底层技术架构都有着很大差异,因此,传统数据库领域的一些技能放到 Hive 中可能已不再适用。关于 hive ...

2538
来自专栏13blog.site

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

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

2958

扫码关注云+社区