热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
登录
注册
子查询写法与优化
技术分享/
文章详情
/
子查询写法与优化
PYZ
2024/05/31
1522
98
2
相关概念
- 相关子查询：当一个子查询含有一个外部引用时，它就与外部语句相关联，称这种子查询为相关子查询。
- 非相关子查询：理解为相关子查询的反义即可，非相关查询不受外层查询的影响。
- 标量子查询：标量子查询的结果一定只能返回一行一列记录，由于这个特性会见到标量子查询出现在 SQL 语句的各个部分，但是否使用得当需要具体分析。
- 子查询平坦化：将子查询展开为与上层查询非相关的手段，可以更加精确的对子查询的代价进行估算。
一、in子查询的等价写法
--创建测试表
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
原SQL：in子查询
select T1.owner,T1.object_name,T1.object_type,T1.created,T1.status
from T1
where T1.object_id in (select object_id from T2 where owner='SYS');
1 #NSET2: [3, 5937, 247]
2 #PRJT2: [3, 5937, 247]; exp_num(6), is_atom(FALSE)
3 #HASH LEFT SEMI JOIN2: [3, 5937, 247]; KEY_NUM(1); KEY(T1.OBJECT_ID=T2.OBJECT_ID) KEY_NULL_EQU(0)
4 #CSCN2: [1, 5937, 247]; INDEX33558563(T1)
5 #SLCT2: [1, 148, 78]; T2.OWNER = 'SYS'
6 #CSCN2: [1, 5939, 78]; INDEX33558564(T2)
exists改写，通常情况下in与exists的执行计划是一致的，效率上没有太大区别，但在一些复杂场景可能有区别需要具体分析
select T1.owner,T1.object_name,T1.object_type,T1.created,T1.status
from T1
where exists (select 1 from T2 where T1.object_id=T2.object_id and owner='SYS');
1 #NSET2: [3, 5937, 247]
2 #PRJT2: [3, 5937, 247]; exp_num(6), is_atom(FALSE)
3 #HASH LEFT SEMI JOIN2: [3, 5937, 247]; KEY_NUM(1); KEY(T1.OBJECT_ID=T2.OBJECT_ID) KEY_NULL_EQU(0)
4 #CSCN2: [1, 5937, 247]; INDEX33558563(T1)
5 #SLCT2: [1, 148, 78]; T2.OWNER = 'SYS'
6 #CSCN2: [1, 5939, 78]; INDEX33558564(T2)
内关联改写
select T1.owner,T1.object_name,T1.object_type,T1.created,T1.status
from T1
inner join (select distinct object_id from T2 where owner='SYS') t
on T1.object_id = t.object_id;
1 #NSET2: [4, 87, 313]
2 #PRJT2: [4, 87, 313]; exp_num(5), is_atom(FALSE)
3 #HASH2 INNER JOIN: [4, 87, 313]; KEY_NUM(1); KEY(T.OBJECT_ID=T1.OBJECT_ID) KEY_NULL_EQU(0)
4 #PRJT2: [1, 1, 78]; exp_num(1), is_atom(FALSE)
5 #DISTINCT: [1, 1, 78]
6 #SLCT2: [1, 148, 78]; T2.OWNER = 'SYS'
7 #CSCN2: [1, 5939, 78]; INDEX33558564(T2)
8 #CSCN2: [1, 5937, 235]; INDEX33558563(T1)
注意：
• 改写需要先去重再关联，而不是先关联再去重，关联字段需要去重才会与原子查询写法等价；
• 通过执行计划的对比可见in/exists写法使用的是"半连接SEMI JOIN"，"半"的意思是：不是子查询内所有的结果都参与关联，所以要去重；
• 如果t2.object_id有重复值，是半连接；
• 如果t2.object_id没有重复值，就是正常关联，可以直接改写为inner join t2；
通过Hint SEMI_GEN_CROSS 进行等价调整
SEMI_GEN_CROSS：优先采用半连接转换为等价的内连接，仅OPTIMIZER_MODE=1时有效，通过执行计划可见与改写为inner join的执行计划基本一致，唯一的区别在于使用hint时会先做projection投影后再去重
select /*+ semi_gen_cross */
T1.owner,T1.object_name,T1.object_type,T1.created,T1.status
from T1
where T1.object_id in (select object_id from T2 where owner='SYS');
1 #NSET2: [4, 87, 325]
2 #PRJT2: [4, 87, 325]; exp_num(6), is_atom(FALSE)
3 #HASH2 INNER JOIN: [4, 87, 325]; LKEY_UNIQUE KEY_NUM(1); KEY(DMTEMPVIEW_889211524.colname=T1.OBJECT_ID) KEY_NULL_EQU(0)
4 #DISTINCT: [1, 1, 78]
5 #PRJT2: [1, 148, 78]; exp_num(1), is_atom(FALSE)
6 #SLCT2: [1, 148, 78]; T2.OWNER = 'SYS'
7 #CSCN2: [1, 5939, 78]; INDEX33558564(T2)
8 #CSCN2: [1, 5937, 247]; INDEX33558563(T1)
二、子查询中的反关联 not in/not exists
not in与not exists区别：
• 如果not in子查询结果集为空，直接返回主查询结果集（包括关联字段为空的记录）
• 如果not in子查询关联字段有null值，整个查询结果集为空
• 如果not in非上述两种情况，主查询关联字段为null值的记录不会返回
• not exists 会忽略子查询是否为空，主查询关联字段为空的记录始终返回
not exists的等价改写，原SQL：
select t1.object_id,t1.object_name
from t1
where not exists
(select 1 from t2 where t1.object_id=t2.object_id)
等价的外关联写法：
select t1.object_id,t1.object_name
from t1
left join t2
on t1.object_id = t2.object_id
where t2.object_id is null;
--或
select t1.object_id,t1.object_name
from t1 ,t2
where t1.object_id=t2.object_id(+)
and t2.object_id is null;
改写原理：不需要去重，通过关联条件is null将关联到的记录丢弃，实现not exists
注意：
• not in/not exists不一定等价
• not exists是exists的反向；not in不是in的反向
• 主查询结果集大的情况一般没有太高效的执行计划，除非子查询先扫描，遇到了null记录
三、or与子查询的等价改写
or in/exists --小结果集
--原SQL：
select object_name,object_id,owner
from t1
where object_name='EMP'
or exists (select 1 from t2 where t1.object_id=t2.object_id and t2.object_name='T');
等价改写：小结果集的情况可以将or改写为union all，lnnvl函数作为第一个分支结果的去重，条件过滤性高，可对谓词条件分别创建索引优化
select object_name,object_id,owner
from t1
where object_name='EMP'
union all
select
object_name,object_id,owner
from t1
where exists (select 1 from t2 where t1.object_id=t2.object_id and t2.object_name='T')
and lnnvl(object_name='EMP');
or in/exists --大结果集
--原写法
select count(owner)
from t1
where object_name='EMP'
or exists (select 1 from t2 where t1.object_id=t2.object_id and t2.object_type='TABLE');
大结果集的情况下，可以通过left join改写使执行计划倾向于hash join
--left join改写：
select count(owner)
from t1
left join (select distinct object_id from t2 where t2.object_type='TABLE') v_t2
on t1.object_id=v_t2.object_id
where ( t1.object_name='EMP' or v_t2.object_id is not null);
四、标量子查询
标量子查询产生的性能问题
1、主查询过滤结果集时的效率，如果主查询的访问路径较差，例如全表扫描、错误的索引扫描，易产生性能问题；
2、主查询过滤结果集后返回的数据量较大（这里的数据量指的是连接列的唯一值），会导致标量部分多次重复查询，即使标量的访问路径为索引唯一扫描，也容易因为较多的查询次数产生性能问题；
3、标量部分的查询效率，如果标量部分的访问路径较差，易引起性能问题。
标量子查询常规优化方案
1、对于查询语句中的标量子查询，通常使用left join改写，如果标量部分与主表在连接列上为主键、外键关系时，也可以改写为inner join，进一步提升性能；
2、对于update语句中的标量子查询，通常使用merge语句改写；
3、在某些环境下不能改写时，可通过hint、索引手段优化标量部分的访问路径和连接方式；
示例1：
--原SQL
select
nvl((select sum(t2.object_id) from t2 where t2.owner=t1.owner and t2.object_type='TABLE'),0) as sumA,
nvl((select sum(t2.object_id) from t2 where t2.owner=t1.owner and t2.object_type='INDEX'),0) as sumB
from t1;
1 #NSET2: [24, 5941->5941, 60]
2 #PIPE2: [24, 5941->5941, 60]
3 #PIPE2: [20, 5941->5941, 60]
4 #PRJT2: [1, 5941->5941, 60]; exp_num(3), is_atom(FALSE)
5 #CSCN2: [1, 5941->5941, 60]; INDEX33558587(T1)
6 #SPL2: [19, 5941, 198]; key_num(2), spool_num(1), has_variable(0), sites(-)
7 #PRJT2: [19, 5941->5941, 198]; exp_num(3), is_atom(FALSE)
8 #HAGR2: [19, 5941->5941, 198]; grp_num(1), sfun_num(3), MEM_USED(1883KB), DISK_USED(0KB), distinct_flag[0,0,0]; slave_empty(0) keys(T1.ROWID)
9 #HASH RIGHT JOIN2: [3, 180871->14435786, 198]; key_num(1); col_num(3); MEM_USED(11950KB), DISK_USED(0KB) KEY(T2.OWNER=T1.OWNER)
10 #SLCT2: [1, 3075->3075, 138]; T2.OBJECT_TYPE = 'INDEX'
11 #CSCN2: [1, 5908->5908, 138]; INDEX33558478(T2)
12 #CSCN2: [1, 5941->5941, 60]; INDEX33558587(T1)
13 #SPL2: [3, 5941, 198]; key_num(2), spool_num(0), has_variable(0), sites(-)
14 #PRJT2: [3, 5941->5941, 198]; exp_num(3), is_atom(FALSE)
15 #HAGR2: [3, 5941->5941, 198]; grp_num(1), sfun_num(3), MEM_USED(1883KB), DISK_USED(0KB), distinct_flag[0,0,0]; slave_empty(0) keys(T1.ROWID)
16 #HASH RIGHT JOIN2: [2, 8234->286182, 198]; key_num(1); col_num(3); MEM_USED(11950KB), DISK_USED(0KB) KEY(T2.OWNER=T1.OWNER)
17 #SLCT2: [1, 140->140, 138]; T2.OBJECT_TYPE = 'TABLE'
18 #CSCN2: [1, 5908->5908, 138]; INDEX33558478(T2)
19 #CSCN2: [1, 5941->5941, 60]; INDEX33558587(T1)
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
95 logical reads
0 physical reads
0 redo size
195039 bytes sent to client
359 bytes received from client
2 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5941 rows processed
0 io wait time(ms)
1527 exec time(ms)
left join改写后
select
nvl(sumA,0) as sumA,
nvl(sumB,0) as sumB
from t1
left join (select owner,sum(t2.object_id) as sumA from t2 where t2.object_type='TABLE' group by owner) a
on t1.owner=a.owner
left join (select owner,sum(t2.object_id) as sumB from t2 where t2.object_type='INDEX' group by owner) b
on t1.owner=b.owner;
1 #NSET2: [7, 5941->5941, 300]
2 #PRJT2: [7, 5941->5941, 300]; exp_num(2), is_atom(FALSE)
3 #HASH RIGHT JOIN2: [7, 5941->5941, 300]; key_num(1); col_num(2); MEM_USED(16046KB), DISK_USED(0KB) KEY(B.OWNER=T1.OWNER)
4 #PRJT2: [1, 10->8, 126]; exp_num(2), is_atom(FALSE)
5 #HAGR2: [1, 10->8, 126]; grp_num(1), sfun_num(1), MEM_USED(1651KB), DISK_USED(0KB), distinct_flag[0]; slave_empty(0) keys(T2.OWNER)
6 #SLCT2: [1, 3075->3075, 126]; T2.OBJECT_TYPE = 'INDEX'
7 #CSCN2: [1, 5908->5908, 126]; INDEX33558478(T2)
8 #HASH RIGHT JOIN2: [3, 5941->5941, 174]; key_num(1); col_num(2); MEM_USED(16046KB), DISK_USED(0KB) KEY(A.OWNER=T1.OWNER)
9 #PRJT2: [1, 10->8, 126]; exp_num(2), is_atom(FALSE)
10 #HAGR2: [1, 10->8, 126]; grp_num(1), sfun_num(1), MEM_USED(1651KB), DISK_USED(0KB), distinct_flag[0]; slave_empty(0) keys(T2.OWNER)
11 #SLCT2: [1, 140->140, 126]; T2.OBJECT_TYPE = 'TABLE'
12 #CSCN2: [1, 5908->5908, 126]; INDEX33558478(T2)
13 #CSCN2: [1, 5941->5941, 48]; INDEX33558587(T1)
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
57 logical reads
0 physical reads
0 redo size
195039 bytes sent to client
447 bytes received from client
2 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5941 rows processed
0 io wait time(ms)
10 exec time(ms)
改写原理：原来的SQL含有两个标量子查询，因此主查询在输出结果时再将关联条件带入子查询中按条件过滤执行，改成外连接时也需要关联两次并分别按条件取值，对两次过滤结果先关联再返回，如上执行计划变为T1与T2的hash join，通过trace也可以清晰的发现left join改写后的逻辑读要小于标量子查询的逻辑读。
Hint ENABLE_RQ_TO_NONREF_SPL
相关查询表达式转化为非相关查询表达式，目的在于相关查询表达式的执行处理由之前的平坦化方式转化为一行一行处理，类似 ORACLE 的每行处理机制。
0：不启用该优化；
1：对查询项中出现的相关子查询表达式进行优化处理；
2：对查询项和 WHERE 表达式中出现的相关子查询表达式进行优化处理；
4：相关查询采用 SPL 方式去相关性后，可以作为单表过滤条件
支持使用上述有效值的组合值，如 3 表示同时进行 1 和 2 的优化
select /*+ ENABLE_RQ_TO_NONREF_SPL(1) */
nvl((select sum(t2.object_id) from t2 where t2.owner=t1.owner and t2.object_type='TABLE'),0) as sumA,
nvl((select sum(t2.object_id) from t2 where t2.owner=t1.owner and t2.object_type='INDEX'),0) as sumB
from t1;
1 #NSET2: [2, 5941->5941, 60]
2 #PIPE2: [2, 5941->5941, 60]
3 #PIPE2: [1, 5941->5941, 60]
4 #PRJT2: [1, 5941->5941, 60]; exp_num(3), is_atom(FALSE)
5 #CSCN2: [1, 5941->5941, 60]; INDEX33558587(T1)
6 #SPL2: [1, 1, 126]; key_num(1), spool_num(1), has_variable(1), sites(-)
7 #PRJT2: [1, 1->5941, 126]; exp_num(1), is_atom(TRUE)
8 #AAGR2: [1, 1->5941, 126]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
9 #SLCT2: [1, 307->14435781, 126]; (T2.OBJECT_TYPE = 'INDEX' AND T2.OWNER = var2)
10 #CSCN2: [1, 5908->35099428, 126]; INDEX33558478(T2)
11 #SPL2: [1, 1, 126]; key_num(1), spool_num(0), has_variable(1), sites(-)
12 #PRJT2: [1, 1->5941, 126]; exp_num(1), is_atom(TRUE)
13 #AAGR2: [1, 1->5941, 126]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
14 #SLCT2: [1, 14->286177, 126]; (T2.OBJECT_TYPE = 'TABLE' AND T2.OWNER = var1)
15 #CSCN2: [1, 5908->35099428, 126]; INDEX33558478(T2)
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
225777 logical reads
0 physical reads
0 redo size
195103 bytes sent to client
458 bytes received from client
3 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5941 rows processed
0 io wait time(ms)
8918 exec time(ms)
当ENABLE_RQ_TO_NONREF_SPL=1时：表示对查询项中出现的相关子查询表达式进行优化处理，可以发现执行计划中的has_var(1)，即T1主表的关联条件以变量的形式，依次往子查询中进行关联，类似嵌套循环的方式。
但此场景中trace里的逻辑读很高，这是因为T1主表的结果集太多，并且关联字段owner的NDV值太低，导致嵌套循环执行效率较差，因此如果主查询结果集较大与子查询嵌套循环次数太多，产生的代价要远高于做hash的代价，因此在调整参数ENABLE_RQ_TO_NONREF_SPL的值时一定要考虑适当的场景。
示例2：update中的标量子查询
更新10w左右数据
update t1
set t1.object_name =
(select t2.object_name
from t2
where t2.object_id = t1.object_id);
1 #UPDATE : [0, 0, 0]; table(T1), type(select)
2 #NTTS2: [4, 5941->99409, 90]; for_mdis(FALSE)
3 #PIPE2: [4, 5941->99409, 90]
4 #PRJT2: [1, 5941->99409, 90]; exp_num(3), is_atom(FALSE)
5 #CSCN2: [1, 5941->99409, 90]; INDEX33558587(T1)
6 #SPL2: [3, 5951, 168]; key_num(2), spool_num(0), has_variable(0), sites(-)
7 #PRJT2: [3, 5951->99409, 168]; exp_num(2), is_atom(FALSE)
8 #HASH2 INNER JOIN: [3, 5951->99409, 168]; KEY_NUM(1), MEM_USED(40622KB), DISK_USED(0KB) KEY(T2.OBJECT_ID=T1.OBJECT_ID) KEY_NULL_EQU(0)
9 #CSCN2: [1, 5908->99409, 78]; INDEX33558478(T2)
10 #CSCN2: [1, 5941->99409, 90]; INDEX33558587(T1)
Statistics
-----------------------------------------------------------------
99409 data pages changed
99976 undo pages changed
200749 logical reads
0 physical reads
19735389 redo size
76 bytes sent to client
165 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99409 rows processed
0 io wait time(ms)
708 exec time(ms)
merge into改写
merge into t1
using t2
on (t1.object_id = t2.object_id)
when matched then
update set t1.object_name = t2.object_name;
1 #UPDATE : [0, 0, 0]; table(T1), type(select)
2 #UFLT: [3, 5812, 168]; IS_TOP_1(FALSE)
3 #PRJT2: [3, 5812->99409, 168]; exp_num(3), is_atom(FALSE)
4 #HASH2 INNER JOIN: [3, 5812->99409, 168]; KEY_NUM(1), MEM_USED(40622KB), DISK_USED(0KB) KEY(T1.OBJECT_ID=T2.OBJECT_ID) KEY_NULL_EQU(0)
5 #CSCN2: [1, 5803->99409, 90]; INDEX33558587(T1)
6 #CSCN2: [1, 5908->99409, 78]; INDEX33558478(T2)
Statistics
-----------------------------------------------------------------
99409 data pages changed
99784 undo pages changed
200122 logical reads
0 physical reads
19728665 redo size
64 bytes sent to client
182 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99409 rows processed
0 io wait time(ms)
691 exec time(ms)
查看执行计划可以发现update+子查询时需要先扫描T1，has_variable(0)说明在做SPL时进行了平坦化处理，并没有将t1.object_id作为变量推入内层进行查找，T1与T2也是进行hash关联，merge into则省去了SPL的过程，直接进行hash关联。因此在简单场景下merge into与update+子查询的逻辑读和执行效率上相差不大，merge into效率略高于update子查询，但在一些复杂场景的大数据更新过程中，还是推荐使用merge into代替update。
评论
后发表回复
L_Jing-
彭老师牛啊！
发布于
2024/09/24 14:53
广州-老虎刘
这篇文章对于in/exists和not in/not exists的描述是非常到位的, 几种改写方法也是优化的常用套路, 跟oracle数据库是完全一样的, 只是在hint的使用上稍有不同. 标量子查询的改写还可以再优化一下, 减少一次关联; update 改merge , update写法会把关联不上的也改成null , 改成merge 后不会.
发布于
2024/09/08 17:28
作者
文章
阅读量
获赞
扫一扫
联系客服
联系客服
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1