当前位置：首页 > article >正文

SQL优化（四）执行计划之表连接

article 2024/10/12 8:11:46

表连接方式

表连接方式就是各种JOIN的方式，一般大表连接时最容易出性能问题。

表连接和表的访问方式紧密相关。

驱动表和被驱动表

两表关联肯定要先进行取数，第一个取数的表是驱动表，其次是被驱动表。

以嵌套循环关联为例：

驱动表是嵌套循环中的外层表。在执行计划中，靠近NESTED LOOPS的表/索引为驱动表。

驱动表应该返回少量数据。返回的数据量越少对被驱动表扫描次数越少。

被驱动表是嵌套循环中的内层表。

被驱动表的连接列基数应该很高并且连接列必须在包含在索引中（走索引）。如果被驱动表连接列的基数很低，被驱动表就不会走索引而是进行全表扫描，如果全表扫描次数过多就会严重影响性能。

被驱动表走索引应该走INDEX UNIQUE SCAN或者INDEX RANGE SCAN，不应该走INDEX FULL SCAN，INDEX SKIP SCAN，和INDEX FAST FULL SCAN，这三个都是相当于扫描表全部行数。

1.NESTED LOOPS

嵌套循环：类似于先查询表A转化为游标，遍历游标，里边套一层select表B，将A的某些列的值传给B作为B的查询条件，B查询到的数据和A游标的当前行匹配。外层的A表是驱动表，内层的B表是被驱动表。

具体算法：驱动表返回一行数据，通过连接列传值给被驱动表，驱动表返回多少行，被驱动表就要被扫描多少次。

适用场景：两表关联返回少量数据，驱动表少量，被驱动表也是少量。

如果两表关联返回的数据量多且是等值连接，应该适用HASH JOIN。

当两表使用外连接（left/right join）进行关联，如果使用嵌套循环连接，那么这时驱动表会被固定为主表（left/right前面的表）。

2.HASH JOIN

哈希连接：适用于两表等值关联返回大量数据。

原理：也区分驱动表和被驱动表。将较小的表选为驱动表，将驱动表的“select的列和join的列”读入PGA中，然后对驱动表的连接列进行hash运算生成hash table，当驱动表的所有数据完全读入PGA后，再读取被驱动表（被驱动表不需要读入PGA中，只需要扫描一次），对被驱动表的连接列也进行hash运算，然后到PGA中去探测hash table，找到数据就关联上，没找到数据就没关联上。

哈希连接需要消耗PGA空间，当驱动表太大、PGA不能完全容纳驱动表时，驱动表就会溢出到临时表空间，产生磁盘哈希连接，这时哈希连接性能会严重下降，如果表非常大有上亿条数据，建议进行拆分。

使用哈希连接尽量减少select的列数，少占用PGA。

哈希连接只支持等值连接。

OLTP环境一般是高并发小事务居多，此类SQL返回结果很少，SQL执行计划多以嵌套循环为主，因此OLTP环境SGA设置较大，PGA设置较小（嵌套循环不消耗PGA）。

OLAP环境多数SQL都是大规模的ETL，此类SQL返回结果集很多，SQL执行计划通常以HASH连接为主，往往要消耗大量PGA，所以OLAP系统PGA设置较大。

3.SORT MERGE JOIN

排序合并连接：主要用于处理两表非等值关联且数据量较大的情况（不适合嵌套循环），比如>，>=，<，<=，<>，但不能用于instr、substr、like、regexp_like等函数关联，instr、substr、like、regexp_like关联只能走嵌套循环。

原理：先对两个表根据连接列进行排序（排序在PGA中进行），将较小的表A中取出连接列的值，到已经排好序的另一个表B中匹配数据，如果匹配上数据，就关联成功。B表匹配次数取决于A表返回的行数，匹配过程类似嵌套循环，不一样的是嵌套循环不需要排序。此外嵌套循环是从被驱动表的索引中匹配数据，而排序合并连接是在内存中（PGA中）匹配数据。

非等值关联尽量在业务上转化为等值关联，使用hash join连接，这种方式用的较少。

4.CARTESIAN JOIN

笛卡尔连接：两个表关联没有连接条件的时候会产生笛卡儿积，这种表连接方式就叫笛卡儿连接。笛卡儿连接会返回两个表行数的乘积，例如一个表10行，另一个表20行，则会返回200行。

笛卡儿连接会对两表中其中一个表进行排序。

注意：在多表关联的时候，两个表没有直接关联条件，但是优化器错误地把某个表返回的Rows算为1行（注意必须是1行），这个时候也可能发生笛卡儿连接。

只有某表真的返回一行数据和其他表进行关联，这时使用笛卡尔积没有问题，其他情况下都强烈建议增加关联条件。

5.SCALAR SUBQUERY

标量子查询：当一个子查询介于select与from之间，这种子查询就叫标量子查询。

标量子查询类似一个嵌套循环，而且驱动表固定为主表。可以改写为外连接（left join）。

写法：select (select b.code from B b where b.id=a.bid) as code from A a

可以改写为：select b.code from A a left join B b on a.bid=b.id

如果主表的连接列是外键，那么可以改为内连接。

6.SEMI JOIN

半连接：两表关联只返回一个表的数据就叫半连接。半连接一般就是指的in和exists。

例如：表A和表B关系是N:1，

select * from A a where a.bid in(select id from B b)

select * from A a where exists(select 1 from B b where a.bid=b.id)

改写：select a.* from A a join B b on a.bid=b.id

注意：如果半连接中主表和子表（子查询中的表）属于1:N的关系，在改写为内连接的时候，需要对子查询的表进行GROUP BY去重。这个时候半连接性能高于内连接。

如果半连接中主表和子表（子查询中的表）属于N:1的关系，在改写为内连接的时候，不需要去重。这个时候半连接与内连接性能一样。

如果半连接中主表和子表（子查询中的表）属于N:N的关系，可以先对子查询去重，将子表转换为1的关系，然后再进行关联。

7.ANTI JOIN

反连接：两表关联只返回主表的数据，而且只返回主表与子表没关联上的数据，这种连接就叫反连接。反连接一般就是指的not in和not exists。

注意：在Oracle中，not in里面如果有null，整个查询会返回空，in里面有null不受null影响。

所以在将not exists等价改写为not in的时候，要注意剔除null（非空键不用担心）。

例如：表A和表B关系是N:1

select * from A a where a.bid not in(select id from B b) （这里id是B的主键，所以不用判断 id is not null，如果是一般列则需要判断）

select * from A a where not exists(select 1 from B b where a.bid=b.id)

改写：select a.* from A a left join B b on a.bid=b.id where b.id is null