本文转载自微信公众号「数据和云」, 作者于志军 . 转载本文请联系数据和云公众号.
SQL 优化过程中, 发现开发人员在写多表关联查询的时候, 对于谓词过滤条件的写法很随意, 写在 on 后面与 where 后面的情况均有, 这可能会导致没有理解清楚其真正的含义而无法得到期望的结果.
多表关联连接方式有 inner join,left join,right join,full join 四种, 下面通过实验来说明不同连接方式谓词放在 on 与 where 后的效果与影响.
初始化测试数据
- create table t1(id number(10),name varchar2(30),status varchar2(2));
- create table t2(id number(10),mobile varchar2(30));
- insert into t1 values(1,'a','1');
- insert into t1 values(2,'b','1');
- insert into t1 values(3,'c','1');
- insert into t1 values(4,'d','1');
- insert into t1 values(5,'e','1');
- insert into t1 values(6,'f','0');
- insert into t1 values(7,'g','0');
- insert into t1 values(8,'h','0');
- insert into t1 values(9,'i','0');
- insert into t1 values(10,'j','0');
- insert into t2 values(1,'12345');
- insert into t2 values(2,'23456');
- insert into t2 values(3,'34567');
- insert into t2 values(6,'67890');
- insert into t2 values(7,'78901');
- 1.Inner join
- SQL>select * from t1 inner join t2 on t1.id=t2.id and t1.status='1';
- ID NAME ST ID MOBILE
- 1 a 1 1 12345
- 2 b 1 2 23456
- 3 c 1 3 34567
- SQL> select * from t1 inner join t2 on t1.id=t2.id where t1.status='1';
- ID NAME ST ID MOBILE
- 1 a 1 1 12345
- 2 b 1 2 23456
- 3 c 1 3 34567
我们发现谓词 t1.status='1'放在 on 后与 where 后结果一样, 它们的执行计划相同, 说明 CBO 对这两种情况做了相同处理.
执行计划如下图所示:
Inner join 时谓词不管放在哪个位置, CBO 都先对 t1 表过滤, 再与 t2 表关联.
2.left join
(1) 左右表谓词过滤都放在 on 后面:
- SQL> select * from t1 left join t2 on t1.id=t2.id and t1.status='1' and t2.id<3;
- ID NAME ST ID MOBILE
- 1 a 1 1 12345
- 2 b 1 2 23456
- 3 c 1
- 8 h 0
- 5 e 1
- 9 i 0
- 10 j 0
- 7 g 0
- 6 f 0
- 4 d 1
执行计划如下:
从执行计划可以看出, t1.status='1'放在 on 后面, t1 表并没有对谓词 status 进行过滤, 结果集显示 t1 的全表数据. 这是由 left join 的特性决定的, 左表会显示全部数据. t2.id<3 是先对 t2 表进行过滤再进行连接, 而 t1.status='1'是作为连接条件存在, 对连接时产生的笛卡尔积数据做连接过滤.
(2) 左右表谓词过滤都放在 where 后面:
- SQL>select * from t1 left join t2 on t1.id=t2.id where t1.status='1' and t2.id<3;
- ID NAME ST ID MOBILE
- 1 a 1 1 12345
- 2 b 1 2 23456
从执行计划可以看出, 谓词放在 where 后面, 是先对表进行过滤, 然后再对过滤后的数据进行连接. 而且我们发现 t1 表上自动加上了 id<3 的过滤条件, 这是因为有 t1.id=t2.id 等值连接, 如果 t1 表上 id 列有索引, 性能就能看出差别来了. 注意连接方式变成了 hash join, 这是因为右表的谓词过滤条件写在 where 后面, CBO 会把左连接等价为内连接.
(3) 右表的谓词写在 on 后面, 左表的谓词写在 where 后面:
- SQL>select * from t1 left join t2 on t1.id=t2.id and t2.id<3
- where t1.status='1'; 2
- ID NAME ST ID MOBILE
- 1 a 1 1 12345
- 2 b 1 2 23456
- 5 e 1
- 4 d 1
- 3 c 1
当把对右表的过滤写在 on 后面, 先对两表进行过滤, 再进行 left join, 显示结果集与写在 where 后面是不同的, 连接方式还是左外连接, 显示 t1 过滤后的全部数据.
(4) 右表的谓词写在 where 后面, 左表的谓词写在 on 后面:
- SQL> select * from t1 left join t2 on t1.id=t2.id and t1.status='1' where t2.id<7;
- ID NAME ST ID MOBILE
- 1 a 1 1 12345
- 2 b 1 2 23456
- 3 c 1 3 34567
从执行计划看这种情况左连接转换为内连接, 左表的谓词条件写在哪个位置都一样. 而且因为 t2 表过滤后数据比 t1 表少, CBO 把 t2 表当成了驱动表.
接下来我们再看一个语句:
- SQL> select * from t1 left join t2 on t1.id=t2.id and t1.status='1'
- where t1.status='0' ;
- ID NAME ST ID MOBILE
- 8 h 0
- 6 f 0
- 9 i 0
- 10 j 0
- 7 g 0
从执行计划看出, 虽然 t2 表返回 0 行, 步骤 3 上的 filter 条件肯定不成立, 但有逻辑读消耗, 所以推断它依然进行了全表扫描, 所以这种语句对 t2 表的扫描是对资源的一种浪费, 没有意义. 或许你会觉得谁会这么无聊写这种 SQL, 但是在开发过程中, SQL 语句经常是各种过滤条件组合经过拼接而成, 因为返回结果是对的, 他们意识不到会出现这种问题, 在此说明此种情况主要是想说明一件事: 不要总想着用一个语句来解决所有的功能需求, 适当的拆分对性能的提升是很有必要的.
3.right join
右连接与左连接是相似的, 只不过是右表显示全部数据, 写在 on 后面谓词过滤对右表不起作用, 在此不再举例说明.
4.full join
全连接在应用中似乎很少碰到, 但是存在即合理, 只是自己没有遇到而已.
(1) 两个表的谓词都放在 on 的后面:
这种情况不会先对两个表过滤, 而是作为连接条件过滤, 符合连接就匹配上, 不符合的就把左右两表的数据都显示出来, 另一表的字段以空显示.
(2) 两个表的谓词都放在 where 后面:
这种情况 CBO 将其转换为内连接, 先过滤再关联.
(3) 左表谓词放在 on 后面, 右表放在 where 后面:
这种情况转换为右外连接, 但是也是先对两表过滤后再关联.
(4) 左表谓词放在 where 后面, 右表放在 on 后面:
这种情况转换为左外连接, 也是先对两表过滤后再关联.
总结
1. 对于内连接 inner join, 两个表的谓词条件放在 on 与 where 后面相同.
2. 对于 left join:
左表谓词放在 on 后不会对左表数据进行过滤, 依然显示左表全部数据, 放在 where 后面才会对左表进行过滤
右表谓词不管放在 on 后还是 where 后都会对右表先过滤再连接, 但是放在 where 后 left join 会转换为 inner join.
3. 对于外连接, 谓词条件放的位置不同, 结果集也不同, 可以根据自己的需求斟酌使用.
来源: http://database.51cto.com/art/202109/680467.htm