Oracle，MySQL，PG三大库JOIN算法及其优化
三大库JOIN算法对比：
oracle的nested loops semi/anti包括Filter,隐式的Filter如标量子查询，UPDATE SET里的相关子查询，都有subquery cache,内部构建hash表，
1024个buckets,如果外表返回的行join key有序且重复值多，可以大幅度减少子查询执行次数来提高效率。
而MySQL只有非相关子查询且没有unnest走Filter的，然后子查询走materialization的才有subquery cache。
pg的子查询没有subquery cache,只有普通nested loops join才有memorize（也是cache),如果像标量子查询要利用到cache提高效率，
可以使用lateral join,也可以改成普通join，但是必须走nested loops才行。
在PG里要杜绝使用not in子查询，往往是性能杀手，加约束或is not null也搞不定，mysql能搞定
##pg里只要not in子查询都不能unnest subquery,如果子查询查询的数据小于work_mem,就走hashed SubPlan 1，内部实际上也有个hash表，效率要高点
子查询查询的数据量大于work_mem,走(NOT (SubPlan 1))，没有hash了，效率低
not in子查询不能unnest的，比如MySQL和pg的，改成not exists,left join+where inner.id is null,效率大部分时候要好很多。
##pg相关子查询加limit自动消除可unnest，mysql也是，但是oracle不行,oracle rownum消除查询重写
oracle里相关子查询画蛇添足式加rownum=1,rownum<...子查询不能unnest,走FILTER,往往就是性能杀手了
##oracle 12c partial join evaluation重写优化
只查询关联表里某表记录，为了避免中间结果集暴增而distinct，最好使用semi join（anti join的left join+inner.key is null
只查询不满足的不会导致结果集增加），针对使用普通join只查询某个表记录，有distinct，count(distinct)，sum(distinct)，avg(distinct)，
min，max的，oracle 12C有partial join evaluation优化，自动将join转为semi join避免中间结果集暴增还要剔重造成的性能问题
（为了等价可以在distinct，但是结果集少很多，当然，如果业务语义不需要最好去掉），pg，MySQL就得手段改写了，
当看到只查询部分表数据还要剔重的就要想到这种优化方式。
##最后注意一点
相关子查询和非相关子查询大部分时候能够相互转换成semi/anti join,但是相关子查询要稍微复杂点，里面有复杂结构，比如集合操作、
聚合分组（有意义的）、rownum之类的可能不能unnest,这时候就不等价了，所以，有时候写成非相关子查询可能多条路,当然，
pg和oracle非相关子查询可以转为普通join,而MySQL非相关子查询有复杂结构比如聚合操作，也不能unnest不能转为join,弱于pg和oracle。
网上那些啥in改成exists的，是非常古老的优化器，比如MySQL5.7，MySQL8的in能够自动转为semi join了，有first match。
决策清单：
能否 unnest
能 → 进入 Semi/Anti 算子，享受 First-Match。Semi join有时候CBO会将子查询改为view,然后剔重去相关转为普通join
否 → 只能 FILTER，同样短路但是NOT IN有子查询不能走索引的问题。
选算法
外表返回行极少 + 内表索引好 → Nested-Loops Semi/Anti。
外表返回行多 / 无索引 → Hash Semi/Anti（内存够）或 Sort-Merge（索引能消除排序）。
First-Match 行为
Semi：内表第一条匹配立即返回外表行，停止继续扫内表。
Anti：内表第一条匹配立即丢弃外表行，停止继续扫内表。
手工加 rownum/limit 的副作用
Oracle：阻断 unnest，除非用 /*+ UNNEST */ 强转。
PG/MySQL：仍可 unnest，但可能让优化器误判行数 → 不建议。
防坑
Anti-Join 遇到 NULL 会全表失配 → 给关联列加 NOT NULL / IS NOT NULL。但是PG遇到NOT IN就只能FILTER，要注意
Hash 碰撞严重 → 从业务上增加高选择度列到 Join Key。
内存不足 → 控制 select 列宽，按需查找，调大 PGA/work_mem/join_buffer_size。
###semi join
--pg 走nl semi/anti 只能主表驱动
explain select/*+leading((s1 s2)) nestloop(s1 s2)*/ * from s1 where exists(select 1 from s2 where s2.id<1000 and s1.id1=s2.id1);
-----------------------------------------------------------------------
Nested Loop Semi Join (cost=0.29..8264.06 rows=106 width=46)
-> Seq Scan on s1 (cost=0.00..194.00 rows=10000 width=46)
-> Index Scan using idx2_s2 on s2 (cost=0.29..0.81 rows=1 width=4)
Index Cond: (id1 = s1.id1)
Filter: (id < 1000)
##nl semi join强制从表驱动，则转为view再做普通join,如果子查询表的key不是唯一键，则需要剔重HashAggregate
explain select/*+leading((s2 s1)) nestloop(s1 s2)*/ * from s1 where exists(select 1 from s2 where s2.id<1000 and s1.id1=s2.id1);
QUERY PLAN
---------------------------------------------------------------------------------
Nested Loop (cost=47.78..885.34 rows=106 width=46)
-> HashAggregate (cost=47.49..58.09 rows=1060 width=4)
Group Key: s2.id1
-> Index Scan using idx_s2 on s2 (cost=0.29..44.84 rows=1060 width=4)
Index Cond: (id < 1000)
-> Index Scan using idx2_s1 on s1 (cost=0.29..0.77 rows=1 width=46)
Index Cond: (id1 = s2.id1)
(7 rows)
##普通hash join,pg的probe table是驱动表，实际上是先做hash join的，pg按照探测顺序来认为probe table是驱动表
explain select/*+leading((s1 s2)) hashjoin(s1 s2)*/ * from s1 where exists(select 1 from s2 where s2.id<1000 and s1.id1=s2.id1);
---------------------------------------------------------------------------------
Hash Semi Join (cost=58.09..279.52 rows=106 width=46)
Hash Cond: (s1.id1 = s2.id1)
-> Seq Scan on s1 (cost=0.00..194.00 rows=10000 width=46)
-> Hash (cost=44.84..44.84 rows=1060 width=4)
-> Index Scan using idx_s2 on s2 (cost=0.29..44.84 rows=1060 width=4)
Index Cond: (id < 1000)
(6 rows)
##semi join应该也有hash join right semi,不过这里没有模拟出来
explain select/*+leading((s2 s1)) hashjoin(s1 s2)*/ * from s1 where exists(select 1 from s2 where s2.id<1000 and s1.id1=s2.id1);
QUERY PLAN
---------------------------------------------------------------------------------
Hash Join (cost=366.49..382.13 rows=106 width=46)
Hash Cond: (s2.id1 = s1.id1)
-> HashAggregate (cost=47.49..58.09 rows=1060 width=4)
Group Key: s2.id1
-> Index Scan using idx_s2 on s2 (cost=0.29..44.84 rows=1060 width=4)
Index Cond: (id < 1000)
-> Hash (cost=194.00..194.00 rows=10000 width=46)
-> Seq Scan on s1 (cost=0.00..194.00 rows=10000 width=46)
(8 rows)
##merge join没有right semi
explain select/*+leading((s1 s2)) mergejoin(s1 s2)*/ * from s1 where exists(select 1 from s2 where s2.id<1000 and s1.id1=s2.id1);
QUERY PLAN
---------------------------------------------------------------------------------
Merge Semi Join (cost=98.43..491.99 rows=106 width=46)
Merge Cond: (s1.id1 = s2.id1)
-> Index Scan using idx2_s1 on s1 (cost=0.29..367.29 rows=10000 width=46)
-> Sort (cost=98.11..100.76 rows=1060 width=4)
Sort Key: s2.id1
-> Index Scan using idx_s2 on s2 (cost=0.29..44.84 rows=1060 width=4)
Index Cond: (id < 1000)
(7 rows)
##没有merge join没有right semi join,有right anti ，oracle没有merge join right anti
explain select/*+leading((s2 s1)) mergejoin(s1 s2)*/ * from s1 where exists(select 1 from s2 where s2.id<1000 and s1.id1=s2.id1);
QUERY PLAN
---------------------------------------------------------------------------------------
Merge Join (cost=969.75..1021.34 rows=106 width=46)
Merge Cond: (s2.id1 = s1.id1)
-> Sort (cost=111.36..114.01 rows=1060 width=4)
Sort Key: s2.id1
-> HashAggregate (cost=47.49..58.09 rows=1060 width=4)
Group Key: s2.id1
-> Index Scan using idx_s2 on s2 (cost=0.29..44.84 rows=1060 width=4)
Index Cond: (id < 1000)
-> Sort (cost=858.39..883.39 rows=10000 width=46)
Sort Key: s1.id1
-> Seq Scan on s1 (cost=0.00..194.00 rows=10000 width=46)
(11 rows)
##anti join,nested loops必须主表驱动
explain select/*+leading((s1 s2)) nestloop(s1 s2)*/ * from s1 where not exists(select 1 from s2 where s2.id<1000 and s1.id1=s2.id1);
QUERY PLAN
------------------------------------------------------------------------
Nested Loop Anti Join (cost=0.29..8264.06 rows=9894 width=46)
-> Seq Scan on s1 (cost=0.00..194.00 rows=10000 width=46)
-> Index Scan using idx2_s2 on s2 (cost=0.29..0.81 rows=1 width=4)
Index Cond: (id1 = s1.id1)
Filter: (id < 1000)
(5 rows)
##nested loops anti join还要子查询表驱动，hints是无效的，必须主表驱动
explain select/*+leading((s2 s1)) nestloop(s1 s2)*/ * from s1 where not exists(select 1 from s2 where s2.id<1000 and s1.id1=s2.id1);
QUERY PLAN
---------------------------------------------------------------------------------
Nested Loop Anti Join (cost=10000000000.29..10000008264.06 rows=9894 width=46)
-> Seq Scan on s1 (cost=0.00..194.00 rows=10000 width=46)
-> Index Scan using idx2_s2 on s2 (cost=0.29..0.81 rows=1 width=4)
Index Cond: (id1 = s1.id1)
Filter: (id < 1000)
(5 rows)
##普通的hash anti join
explain select/*+leading((s1 s2)) hashjoin(s1 s2)*/ * from s1 where not exists(select 1 from s2 where s2.id<1000 and s1.id1=s2.id1);
QUERY PLAN
---------------------------------------------------------------------------------
Hash Anti Join (cost=58.09..377.40 rows=9894 width=46)
Hash Cond: (s1.id1 = s2.id1)
-> Seq Scan on s1 (cost=0.00..194.00 rows=10000 width=46)
-> Hash (cost=44.84..44.84 rows=1060 width=4)
-> Index Scan using idx_s2 on s2 (cost=0.29..44.84 rows=1060 width=4)
Index Cond: (id < 1000)
(6 rows)
##支持Hash Right Anti Join
explain select/*+leading((s2 s1)) hashjoin(s1 s2)*/ * from s1 where not exists(select 1 from s2 where s2.id<1000 and s1.id1=s2.id1);
QUERY PLAN
---------------------------------------------------------------------------
Hash Right Anti Join (cost=319.29..368.88 rows=9894 width=46)
Hash Cond: (s2.id1 = s1.id1)
-> Index Scan using idx_s2 on s2 (cost=0.29..44.84 rows=1060 width=4)
Index Cond: (id < 1000)
-> Hash (cost=194.00..194.00 rows=10000 width=46)
-> Seq Scan on s1 (cost=0.00..194.00 rows=10000 width=46)
(6 rows)
##普通的merge anti join
explain select/*+leading((s1 s2)) mergejoin(s1 s2)*/ * from s1 where not exists(select 1 from s2 where s2.id<1000 and s1.id1=s2.id1);
QUERY PLAN
---------------------------------------------------------------------------------
Merge Anti Join (cost=98.39..491.99 rows=9894 width=46)
Merge Cond: (s1.id1 = s2.id1)
-> Index Scan using idx2_s1 on s1 (cost=0.29..367.29 rows=10000 width=46)
-> Sort (cost=98.11..100.76 rows=1060 width=4)
Sort Key: s2.id1
-> Index Scan using idx_s2 on s2 (cost=0.29..44.84 rows=1060 width=4)
Index Cond: (id < 1000)
(7 rows)
##支持 Merge Right Anti Join，这个oracle不支持
explain select/*+leading((s2 s1)) mergejoin(s1 s2)*/ * from s1 where not exists(select 1 from s2 where s2.id<1000 and s1.id1=s2.id1);
QUERY PLAN
-------------------------------------------------------------------------------
Merge Right Anti Join (cost=0.58..779.21 rows=9894 width=46)
Merge Cond: (s2.id1 = s1.id1)
-> Index Scan using idx2_s2 on s2 (cost=0.29..3817.29 rows=1060 width=4)
Filter: (id < 1000)
-> Index Scan using idx2_s1 on s1 (cost=0.29..367.29 rows=10000 width=46)
(5 rows)
########################在PG里要杜绝使用not in子查询，往往是性能杀手，加约束或is not null也搞不定，mysql能搞定
##pg里只要not in子查询都不能unnest subquery,如果子查询查询的数据小于work_mem,就走hashed SubPlan 1，内部实际上也有个hash表，效率要高点
explain select * from s1 where s1.id1 not in (select s2.id1 from s2 where s2.id<1000);
QUERY PLAN
-----------------------------------------------------------------------------
Seq Scan on s1 (cost=47.49..266.49 rows=5000 width=46)
Filter: (NOT (hashed SubPlan 1))
SubPlan 1
-> Index Scan using idx_s2 on s2 (cost=0.29..44.84 rows=1060 width=4)
Index Cond: (id < 1000)
(5 rows)
##将work_mem设成最小的64KB，子查询条件是s2.id<5000，子查询查询的数据量大于work_mem,走(NOT (SubPlan 1))，没有hash了，效率低
test=# set work_mem=64;
SET
test=# explain select * from s1 where s1.id1 not in (select s2.id1 from s2 where s2.id<5000);
QUERY PLAN
------------------------------------------------------------------------------------
Seq Scan on s1 (cost=0.29..1310844.29 rows=5000 width=46)
Filter: (NOT (SubPlan 1))
SubPlan 1
-> Materialize (cost=0.29..249.60 rows=5125 width=4)
-> Index Scan using idx_s2 on s2 (cost=0.29..202.98 rows=5125 width=4)
Index Cond: (id < 5000)
(6 rows)
##pg相关子查询加limit自动消除，mysql也是，但是oracle不行
explain select * from s1 where not exists(select 1 from s2 where s2.id<1000 and s1.id1=s2.id1 limit 10);
QUERY PLAN
---------------------------------------------------------------------------------
Hash Anti Join (cost=58.09..377.40 rows=9894 width=46)
Hash Cond: (s1.id1 = s2.id1)
-> Seq Scan on s1 (cost=0.00..194.00 rows=10000 width=46)
-> Hash (cost=44.84..44.84 rows=1060 width=4)
-> Index Scan using idx_s2 on s2 (cost=0.29..44.84 rows=1060 width=4)
Index Cond: (id < 1000)
(6 rows)
##oracle
##这里没有做semi join,可以做，因为这里用内表做驱动，oracle没有merge join right semi/anti,所以hints无效，但是leading有效
##semi join转为view然后做普通join,leading有效
select/*+leading(t2@inner) use_merge(t1,t2@inner)*/ * from t1 where exists (select/*+qb_name(inner)*/ 1 from t2 where t1.object_id=t2.object_id);
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2288102283
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999 | 1327K| | 2989 (1)| 00:00:01 |
| 1 | MERGE JOIN | | 9999 | 1327K| | 2989 (1)| 00:00:01 |
| 2 | SORT UNIQUE | | 9999 | 39996 | | 7 (0)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| IDX_T2_OBJECT_ID | 9999 | 39996 | | 7 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 73745 | 9506K| 26M| 2979 (1)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T1 | 73745 | 9506K| | 798 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
3 - SEL$5F35D591 / T2@INNER
U - use_merge(t1,t2@inner)
##使用merge_sj
select * from t1 where exists (select/*+merge_sj*/ 1 from t2 where t1.object_id=t2.object_id);
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1646611724
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999 | 1327K| 2039 (1)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 9999 | 1327K| 2039 (1)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 73745 | 9506K| 2031 (1)| 00:00:01 |
| 3 | INDEX FULL SCAN | IDX_T1_OBJECT_ID | 73743 | | 333 (1)| 00:00:01 |
|* 4 | SORT UNIQUE | | 9999 | 39996 | 9 (23)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN | IDX_T2_OBJECT_ID | 9999 | 39996 | 7 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
##merge anti join,只能主表驱动，没有merge join right semi/anti,所以leading也无效
select/*+leading(t2@inner) use_merge(t1,t2@inner)*/ * from t1 where not exists (select/*+qb_name(inner)*/ 1 from t2 where t1.object_id=t2.object_id);
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1334991640
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 64709 | 8594K| | 2987 (1)| 00:00:01 |
| 1 | MERGE JOIN ANTI | | 64709 | 8594K| | 2987 (1)| 00:00:01 |
| 2 | SORT JOIN | | 73745 | 9506K| 26M| 2979 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T1 | 73745 | 9506K| | 798 (1)| 00:00:01 |
|* 4 | SORT UNIQUE | | 9999 | 39996 | | 9 (23)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN| IDX_T2_OBJECT_ID | 9999 | 39996 | | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------
1 - SEL$5F35D591
U - leading(t2@inner)
3 - SEL$5F35D591 / T1@SEL$1
U - use_merge(t1,t2@inner)
##使用hash_sj+swap_join_inputs模拟HASH JOIN RIGHT SEMI
select/*+ swap_join_inputs(t2@inner) */ * from t1 where exists (select/*+qb_name(inner) hash_sj*/ 1 from t2 where t1.object_id=t2.object_id);
Execution Plan
----------------------------------------------------------
Plan hash value: 3621394761
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999 | 1327K| 806 (1)| 00:00:01 |
|* 1 | HASH JOIN RIGHT SEMI | | 9999 | 1327K| 806 (1)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| IDX_T2_OBJECT_ID | 9999 | 39996 | 7 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T1 | 73745 | 9506K| 798 (1)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
##使用swap_join_inputs+hash_aj模拟HASH JOIN RIGHT ANTI
select/*+ swap_join_inputs(t2@inner) */ * from t1 where not exists (select/*+qb_name(inner) hash_aj*/ 1 from t2 where t1.object_id=t2.object_id);
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3091616910
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 64709 | 8594K| 806 (1)| 00:00:01 |
|* 1 | HASH JOIN RIGHT ANTI | | 64709 | 8594K| 806 (1)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| IDX_T2_OBJECT_ID | 9999 | 39996 | 7 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T1 | 73745 | 9506K| 798 (1)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
### oracle里相关子查询画蛇添足式加rownum=1,rownum<...子查询不能unnest,走FILTER,往往就是性能杀手了
select * from t1 where not exists (select 1 from t2 where t1.object_id=t2.object_id and rownum<10);
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3454536103
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 73744 | 9506K| 63659 (1)| 00:00:03 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T1 | 73745 | 9506K| 798 (1)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
|* 4 | INDEX RANGE SCAN| IDX_T2_OBJECT_ID | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "T2" "T2" WHERE ROWNUM<10 AND
"T2"."OBJECT_ID"=:B1))
3 - filter(ROWNUM<10)
4 - access("T2"."OBJECT_ID"=:B1)
##强制not in走Filter,虽然t2.object_id有索引，但是走不了，如果是in子查询走FILTER则可以走
为什么不能走，因为外表t1传入每个object_id值，都是按照<>来查的，找到才返回，而且这种运算，子查询存在NULL就没有结果了
而如果not in能够unnest,是转为=查找的，当然可以走索引,比如nested loops join anti。
select * from t1 where t1.object_id not in (select/*+no_unnest*/ t2.object_id from t2);
Execution Plan
----------------------------------------------------------
Plan hash value: 895956251
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 73744 | 9506K| 4293K (1)| 00:02:48 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T1 | 73745 | 9506K| 798 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 4 | 68 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T2" "T2"
WHERE LNNVL("T2"."OBJECT_ID"<>:B1)))
3 - filter(LNNVL("T2"."OBJECT_ID"<>:B1))
##IN子查询+FILTER可以走索引
select * from t1 where t1.object_id in (select/*+no_unnest*/ t2.object_id from t2);
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3868275382
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 132 | 63659 (1)| 00:00:03 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T1 | 73745 | 9506K| 798 (1)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T2_OBJECT_ID | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T2" "T2" WHERE
"T2"."OBJECT_ID"=:B1))
3 - access("T2"."OBJECT_ID"=:B1)
============标量子查询
-- pg相关的标量子查询无法unnest,无法自动改写为外连接，子查询可能被驱动多次，也没有subquery cache,标量子查询被驱动多次
-- 相关标量子查询没有subplan hash
-- mysql可以通过set_var(optimizer_switch='subquery_to_derived=on'),支持count
-- oracle只支持单个min,max,不支持count
explain analyze
select a.id,(select max(b.name) from sub_t b where a.id=b.id) max_name
from main_t a;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on main_t a (cost=0.00..8340.50 rows=1000 width=36) (actual time=0.294..3.231 rows=1000 loops=1)
SubPlan 1
-> Aggregate (cost=8.31..8.32 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1000)
-> Index Scan using idx_sub_t on sub_t b (cost=0.29..8.31 rows=1 width=3) (actual time=0.002..0.002 rows=1 loops=1000)
Index Cond: (id = a.id)
Planning Time: 0.184 ms
Execution Time: 3.313 ms
(7 rows)
-- 相关标量子查询没有索引被驱动多次，则是性能杀手
drop index idx_sub_t;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Seq Scan on main_t a (cost=0.00..1698030.50 rows=1000 width=36) (actual time=6.363..6030.703 rows=1000 loops=1)
SubPlan 1
-> Aggregate (cost=1698.00..1698.01 rows=1 width=32) (actual time=6.026..6.026 rows=1 loops=1000)
-> Seq Scan on sub_t b (cost=0.00..1698.00 rows=1 width=3) (actual time=0.033..6.019 rows=1 loops=1000)
Filter: (a.id = id)
Rows Removed by Filter: 99999
Planning Time: 0.174 ms
Execution Time: 6031.154 ms
(8 rows)
--
-- 普通子查询没有subquery cache,如果子查询的key有重复，则可以使用lateral join来改写子查询，又能使用subquery cache(memoize),又能不产生重复值来提高效率
-- -- 使用lateral join,直接子查询前面加lateral或者用join lateral,支持left/right join lateral
---- 如果写join lateral则需要加on条件，可以是on true,不使用join lateral,直接子查询前加个latral,当作表，则不用on条件，可以加where条件
explain analyze
select a.* from main_t a,lateral(select 1 from sub_t b where a.name = b.name limit 1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.30..46.82 rows=1000 width=7) (actual time=0.043..0.548 rows=1000 loops=1)
-> Seq Scan on main_t a (cost=0.00..18.00 rows=1000 width=7) (actual time=0.015..0.089 rows=1000 loops=1)
-> Memoize (cost=0.30..0.36 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1000)
Cache Key: a.name
Cache Mode: binary
Hits: 990 Misses: 10 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Subquery Scan on unnamed_subquery (cost=0.29..0.35 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=10)
-> Limit (cost=0.29..0.34 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=10)
-> Index Only Scan using idx_sub_t on sub_t b (cost=0.29..45.79 rows=1000 width=4) (actual time=0.007..0.007 rows=1 loops=10)
Index Cond: (name = a.name)
Heap Fetches: 0
Planning Time: 0.129 ms
Execution Time: 0.600 ms
(13 rows)
-- 使用join lateral要加 on条件，可以以on true
select a.* from main_t a join lateral(select 1 from sub_t b where a.name = b.name limit 1) on true;
--- 标量子查询也可以用lateral优化
-- pg的标量子查询和普通子查询一样，没有subquery cache功能，和mysql差不多，MySQL只有非相关子查询不能unnest才有cache
explain analyze
select a.*,(select count(*) cnt from sub_t b where a.name=b.name) cnt
from main_t a;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on main_t a (cost=0.00..48319.98 rows=1000 width=15) (actual time=0.155..117.436 rows=1000 loops=1)
SubPlan 1
-> Aggregate (cost=48.29..48.30 rows=1 width=8) (actual time=0.116..0.116 rows=1 loops=1000)
-> Index Only Scan using idx_sub_t on sub_t b (cost=0.29..45.79 rows=1000 width=0) (actual time=0.005..0.075 rows=1000 loops=1000)
Index Cond: (name = a.name)
Heap Fetches: 0
Planning Time: 0.103 ms
Execution Time: 117.547 ms
(8 rows)
-- pg的lateral也有cache功能，因为普通nested loops join有memorize，类似cache
--如果外表join key重复值多，可以用来代替标量子查询提高效率,使用lateral,子查询执行次数从1000到10，效率从117ms到1.7ms，提升100倍。
select count(*),count(distinct name) from main_t;
count | count
-------+-------
1000 | 10
(1 row)
explain analyze
select * from main_t a, lateral(select count(*) cnt from sub_t b where a.name=b.name) as p;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=48.30..573.83 rows=1000 width=15) (actual time=0.155..1.696 rows=1000 loops=1)
-> Seq Scan on main_t a (cost=0.00..18.00 rows=1000 width=7) (actual time=0.014..0.090 rows=1000 loops=1)
-> Memoize (cost=48.30..48.31 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1000)
Cache Key: a.name
Cache Mode: binary
Hits: 990 Misses: 10 Evictions: 0 Overflows: 0 Memory Usage: 2kB
-> Aggregate (cost=48.29..48.30 rows=1 width=8) (actual time=0.120..0.120 rows=1 loops=10)
-> Index Only Scan using idx_sub_t on sub_t b (cost=0.29..45.79 rows=1000 width=0) (actual time=0.007..0.073 rows=1000 loops=10)
Index Cond: (name = a.name)
Heap Fetches: 0
Planning Time: 0.123 ms
Execution Time: 1.758 ms
(12 rows)
--MySQL的lateral没有cache功能，mysql只有子查询不能unnest且非相关子查询走materialize,MySQL还是执行1000次
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join (cost=2842.44 rows=1000) (actual time=1.004..706.793 rows=1000 loops=1)
-> Invalidate materialized tables (row from a) (cost=101.00 rows=1000) (actual time=0.043..1.437 rows=1000 loops=1)
-> Table scan on a (cost=101.00 rows=1000) (actual time=0.043..1.361 rows=1000 loops=1)
-> Table scan on p (cost=223.98..223.98 rows=1) (actual time=0.705..0.705 rows=1 loops=1000)
-> Materialize (invalidate on row from a) (cost=221.47..221.47 rows=1) (actual time=0.704..0.704 rows=1 loops=1000)
-> Aggregate: count(0) (cost=221.37 rows=1) (actual time=0.703..0.703 rows=1 loops=1000)
-> Covering index lookup on b using idx_sub_t (name=a.`name`) (cost=116.24 rows=1051) (actual time=0.004..0.652 rows=1000 loops=1000)
1 row in set, 1 warning (0.71 sec)
##oracle 12c partial join evaluation重写优化
只查询关联表里某表记录，为了避免中间结果集暴增而distinct，最好使用semi join（anti join的left join+inner.key is null只查询不满足的不会导致结果集增加），针对使用普通join只查询某个表记录，有distinct，count(distinct)，sum(distinct)，avg(distinct)，min，max的，oracle 12C有partial join evaluation优化，自动将join转为semi join避免中间结果集暴增还要剔重造成的性能问题（为了等价可以在distinct，但是结果集少很多，当然，如果业务语义不需要最好去掉），pg，MySQL就得手段改写了，当看到只查询部分表数据还要剔重的就要想到这种优化方式。
11G:
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------- SQL_ID 2xjj9jybqja87, child number 1 ------------------------------------- select distinct deptno,dname from dept join emp using(deptno) Plan hash value: 2962452962 ------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 4 | 129 | | 1 | HASH UNIQUE | | 1 | 15068 | 4 | 129 | |* 2 | HASH JOIN | | 1 | 15068 | 14014 | 129 | | 3 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 | 7 | | 4 | TABLE ACCESS FULL| EMP | 1 | 15068 | 14014 | 122 | ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
要读取所有的emp
12C:
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 2xjj9jybqja87, child number 0
-------------------------------------
select distinct deptno,dname from dept join emp using(deptno)
Plan hash value: 1629510749
-------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 | 14 |
| 1 | HASH UNIQUE | | 1 | 4 | 4 | 14 |
|* 2 | HASH JOIN SEMI | | 1 | 4 | 4 | 14 |
| 3 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 | 7 |
| 4 | TABLE ACCESS FULL| EMP | 1 | 15068 | 388 | 7 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
虽然EMP还是全表扫描，但是实际读取行数少很多，因为转为HASH JOIN SEMI
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7655508/viewspace-3117889/ ，如需转载，请注明出处，否则将追究法律责任。
