SQL优化案例——反关联改写
问题SQL如下：
涉及到表的数据量：
select count(*)from T; --3002042
select count(*)from T1; --3906248
select count(*)from T2; --4702
select count(*)from T3; --2241705
问题分析
先看执行计划，SQL主体部分为 T1 派生表内部关联去重之后，整体与 T3 进行的 LEFT JOIN，并且关联后 WHERE 条件为右表 T3 的关联字段为空，按T3.ALERT_ID IS NULL 条件进行过滤，SQL逻辑很简单，本质上其实就是 NOT EXISTS，但三张百万表先关联后过滤，执行耗时需要30分钟以上，业务执行时直接超时报错 (T^T)
在对SQL简化后，将 T1 的结果集取 1w 条插入一张临时表来模拟原 SQL 的 HASH 连接，查看 trace执行计划和 ET 结果，发现 10000 条数据与 T3 的 HASH 关联产生了近 8 亿的中间结果集，主要耗时都在 T1 与 T3 的 HASH 关联，而原 SQL的 T1 派生表的数据量有93w，依此估算中间结果集将会更大。
那么这里为什么会产生这么大的中间结果？
首先需要了解 HASH JOIN 的计算过程，哈希连接分为两个阶段：生成阶段和探测阶段
生成阶段：优化器需要根据统计信息将数据量较小的表中的每行数据经过散列函数进行计算，计算后的结果都放到不同的HASH桶中；
探测阶段：对另一个数据量大的表，同样针对每一行进行散列函数计算，确定其所对应的HASH桶，在针对这行和对应HASH桶中的每一行进行匹配，如果匹配则返回对应的行。
举个栗子：
--创建表 T1 和 T2，并插入数据，T1 10w，T2 100w
create table t1(id varchar2(60),name varchar2(100));
create table t2(id varchar2(60),name varchar2(100));
insert into t1 select dbms_random.string('l',2),dbms_random.string('l',2) from dual connect by rownum <=100000;
commit;
insert into t2 select dbms_random.string('l',2),dbms_random.string('l',2) from dual connect by rownum <=1000000;
commit;
--统计信息
call dbms_stats.gather_table_stats('SYSDBA','T1',null,100,false,'for all columns size auto');
call dbms_stats.gather_table_stats('SYSDBA','T2',null,100,false,'for all columns size auto');
模拟此问题SQL，可以看到 T1(10w) 与 T2(100w) 也是先关联后过滤出右表 t2.id is null，其中第4步的 HASH JOIN 也产生了160000713条行记录，执行计划中的最大耗时也是在HLO2，即使最终过滤结果为0 执行时间也需要62秒。
SQL> select t1.*
from t1
left join t2
on t1.id=t2.id
where t2.id is null;
1 #NSET2: [5546, 4000000->0, 96]
2 #PRJT2: [5546, 4000000->0, 96]; exp_num(2), is_atom(FALSE)
3 #SLCT2: [5546, 4000000->0, 96]; T2.ID IS NULL
4 #HASH LEFT JOIN2: [212, 160000000->160000713, 96]; key_num(1); col_num(3); partition_keys_num(0); mix(0); MEM_USED(40639KB), DISK_USED(0KB) KEY(T1.ID=T2.ID)
5 #CSCN2: [12, 100000->100000, 96]; INDEX33558554(T1); btr_scan(1)
6 #CSCN2: [114, 1000000->1000000, 48]; INDEX33558555(T2); btr_scan(1)
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
1030 logical reads
0 physical reads
0 redo size
176 bytes sent to client
149 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
0 io wait time(ms)
62588 exec time(ms)
已用时间: 00:01:02.588.
SQL> et(4208);
OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
----- -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- -------------------- -------------------- ----------------- --------------- --------------------
PRJT2 1 0% 7 2 2 0 0 0 0 NULL NULL 0
DLCK 4 0% 6 0 2 0 0 0 0 NULL NULL 0
NSET2 45 0% 5 1 2 0 0 0 0 NULL NULL 0
CSCN2 11174 0.02% 4 5 335 0 0 0 0 NULL NULL 0
CSCN2 118432 0.19% 3 6 3335 0 0 0 0 NULL NULL 0
SLCT2 803843 1.29% 2 3 535018 0 0 0 0 NULL NULL 0
HLO2 61376056 98.50% 1 4 538687 40639 0 625 0 NULL NULL 100000
根据问题SQL定义可以写出查询语句，理论上也能大概估算出 HASH JOIN 的输出行数。
SQL> with
a as (select id ,count(*) acnt from t1 group by id),
b as (select id ,count(*) bcnt from t2 group by id)
select sum(a.acnt * b.bcnt) as join_row_count
from a,b
where a.id = b.id;
行号 JOIN_ROW_COUNT
---------- --------------------
1 160000713
因此 HASH JOIN 所产生的中间结果大小与表关联字段上面的数据分布是有很大关系，那么针对这种情况如何进行优化？
改写 NOT EXISTS
--改写后
SQL> select t1.*
from t1
where not exists (select 1 from t2 where t1.id=t2.id);
1 #NSET2: [213, 1->0, 108]
2 #PRJT2: [213, 1->0, 108]; exp_num(3), is_atom(FALSE)
3 #HASH LEFT SEMI JOIN2: [213, 1->0, 108]; (ANTI),KEY_NUM(1); MEM_USED(40639KB), DISK_USED(0KB) KEY(T1.ID=T2.ID) KEY_NULL_EQU(0)
4 #CSCN2: [12, 100000->100000, 108]; INDEX33558554(T1); btr_scan(1)
5 #CSCN2: [114, 1000000->1000000, 48]; INDEX33558555(T2); btr_scan(1)
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
1014 logical reads
0 physical reads
0 redo size
176 bytes sent to client
142 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
0 io wait time(ms)
90 exec time(ms)
通过将 left join + 右表关联字段 is null 改写为 not exists 来让执行计划直接通过 ANTI JOIN 来取反，不需要进行先关联后过滤，以此来消除 HASH 连接中产生过多的结果集来达到最优效果，改写后仅需90ms。
优化方案
改写SQL
通过改写SQL避免了先关联后过滤，并且调整 HASH 相关参数扩大了一些内存，最终优化后从执行30分钟以上，到 60s 左右执行完成，应用业务也不再报错。
执行计划
总结
1、LEFT JOIN + IS NULL: 通过 LEFT JOIN，获取了 T1 表中的所有行，T2 中不存在的行记录会置为 NULL，再通过过滤 T2.ID IS NULL 来确定哪些行在 T2 表中没有匹配。
2、NOT EXISTS: 直接检查 T2 表中是否存在与 T1 表当前 ID 匹配的行。如果不存在，则输出该行。
这两种方法都有效地实现了相同的目标，即查找 T1 表在 T2 表中没有对应记录的行，因此这两种写法在逻辑上是等价的，但在生成执行计划的过程中第一种方式却需要先关联后过滤，HASH关联产生了大量的中间结果集，导致出现性能问题。
作者
文章
阅读量
获赞
联系客服
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1