SQL连接查询优化[姊妹篇.第五弹]

上篇的sql优化篇章，更多偏向于优化的思想概念，先前抛出的4个优化问题中，篇幅过长，只对前两个问题进行了解析。

 

接下来我们一起来谈谈sql的连接查询优化，更偏向于实际运用，并对如下两个问题进行探讨。篇幅过长，请耐心看完。

 

1.嵌套查询、HASH连接、排序合并连接、笛卡尔连接等怎样玩能达到最优？

 

2. IN 与 EXISTS 谁快谁慢？

 

嵌套循环(NESTED LOOPS)

 

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

 

这里我补充一下驱动表和被驱动表：理解驱动表和被驱动表的本质，需要理解顺序读取和随机读取的差异，内存适合随机读取，硬盘则顺序读取的效率比较好。

 

驱动表，作为外层循环，若进行一次IO将所有数据读取，则适合顺序读取，一次性批量的把数据读取出来，不考虑缓存情况下。

 

被驱动表，即里层循环，由于需要不断的拿外层循环传进来的每条记录去匹配，所以如果是适合随机读取的，那么效率就会比较高。如果表上有索引，实际上就意味着这个表是适合随机读取的。

 

 

1.嵌套循环可以快速返回两表关联的前几条数据，如果SQL中添加了HINT：FIRST_ROWS，在两表关联的时候，优化器更倾向于嵌套循环。

 

2.嵌套循环驱动表应该返回少量数据。如果驱动表返回了100万行，那么被驱动表 就会被扫描100万次。这个时候SQL会执行很久，被驱动表会被误认为热点表，被驱动表连接列的索引也会被误认为热点索引。

 

3.嵌套循环被驱动表必须走索引。如果嵌套循环被驱动表的连接列没包含在索引中，那么被驱动表就只能走全表扫描，而且是反复多次全表扫描。当被驱动表很大的 时候，SQL就执行不出结果。

 

4.嵌套循环被驱动表的连接列基数应该很高。如果被驱动表连接列的基数很低，那么被驱动表就不应该走索引，这样一来被驱动表就只能进行全表扫描了，但是被驱动表也不能走全表扫描。

 

 

5.两表关联返回少量数据才能走嵌套循环。前面提到，嵌套循环被驱动表必须走索引，如果两表关联，返回100万行数据，那么被驱动表走索引就会产生100万次回表。回表一般是单块读，这个时候SQL性能极差，所以两表关联返回少量数据才能走嵌套 循环。

 

 

两表关联走不走NL(嵌套循环)是看两个表关联之后返回的数据量多少?还是看驱动表返回的数据量多少？

 

如果两个表是1∶N关系，驱动表为1，被驱动表为N并且N很大，这时即使驱动表返回数据量很少，也不能走嵌套循环，因为两表关联之后返回的数据量会很多。

 

所以判断两表关联是否应该走NL应该直接查看两表关联之后返回的数据量，如果 两表关联之后返回的数据量少，可以走NL；返回的数据量多，应该走HASH连接。 

 

SELECT * FROM t1,t2 WHERE t1.id = t2.id; 如果t1有200条数据，t2有200万行数据，t1与t2是1∶N关系，N很低，应该怎么优化SQL?

 

 

因为t1与t2是1∶N关系，N很低，我们可以在b的连接列（id）上创建索引，让 t1与t2走嵌套循环（t1 nl t2），这样t2表会被扫描100次，但是每次扫描表的时候走的 是id列的索引（范围扫描）。

 

如果让t1和t2进行HASH连接，t2表会被全表扫描（因为没有过滤条件），需要查询表中100万行数据，而如果让t1和t2进行嵌套循环，t2表只需要 查询出表中最多几百行数据（100*N）。

 

 

一般情况下，一个小表与一个大表关联，我们可以考虑让小表NL大表，大表走连接列索引（如果大表有过滤条件，需要将过滤条件与连接列组合起来创建组合索引），从而避免大表被全表扫描。

 

HASH连接(HASH JOIN )

 

HASH连接的算法：两表等值关联，返回大量数据，将较小的表选为驱动表，将驱动表的“select列和join列”读入PGA中的work area，然后对驱动表的连接列进行hash 运算生成hash table，当驱动表的所有数据完全读入PGA中的work area之后，再读取被驱动表（被驱动表不需要读入PGA中的work area），对被驱动表的连接列也进行 hash运算，然后到PGA中的work area去探测hash table，找到数据就关联上，没找到 数据就没关联上。哈希连接只支持等值连接。

 

 

如何优化HASH连接？

 

因为HASH连接需要将驱动表的select列和join列放入PGA中，所以，我们 应该尽量避免书写select * from....语句，将需要的列放在select list中，这样可以减少驱动表对PGA的占用，避免驱动表被溢出到临时表空间，从而提升查询性能。

 

如果无法避免驱动表被溢出到临时表空间，我们可以将临时表空间创建在SSD上或者RAID 0上，加快临时数据的交换速度。

 

当PGA采用自动管理，单个进程的work area被限制在1G以内，如果PGA采用手动管理，单个进程的work area不能超过2GB。如果驱动表比较大，比如驱动表有 4GB，可以开启并行查询至少parallel(4)，将表拆分为至少4份，这样每个并行进程中 的work area能够容纳1GB数据，从而避免驱动表被溢出到临时表空间。

 

排序合并连接(SORT MERGE JOIN)

 

前文提到HASH连接主要用于处理两表等值关联返回大量数据。排序合并连接主要用于处理两表非等值关联，比如>，>=，<，<=，<>，但是不能用于instr、substr、like、regexp_like关联，instr、substr、like、regexp_like关联只能走嵌套循环。

排序合并连接的算法：两表关联，先对两个表根据连接列进行排序，将较小的表作为驱动表，然后从驱动表中取出连接列的值，到已经排好序的被驱动表中匹配数据，如果匹配上数据，就关联成功。驱动表返回多少行，被驱动表就要被匹配多少次，这个匹配的过程类似嵌套循环，但是嵌套循环是从被驱动表的索引中匹配数据，而排序合并连接是在内存中（PGA中的work area）匹配数据。 

 

怎么优化排序合并连接？

 

如果两表关联是等值关联，走的是排序合并连接，我们可以将表连接方式改为HASH连接。如果两表关联是非等值关联，比如>，>=，<，<=，<>，这时我们应该先从业务上入手，尝试将非等值关联改写为等值关联，因为非等值关联返回的结果 集“类似”于笛卡儿积，当两个表都比较大的时候，非等值关联返回的数据量相当大。如果没有办法将非等值关联改写为等值关联，我们可以考虑增加两表的限制条件，将两个表数据量缩小，最后可以考虑开启并行查询加快SQL执行速度。 

笛卡尔连接(CARTESIAN JOIN)

 

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

标量子查询(SCALAR SUBQURY)

 

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

 

标量子查询类似一个天然的嵌套循环，而且驱动表固定为主表。大家是否还记得：嵌套循环被驱动表的连接列必须包含在索引中。同理，标量子查询中子查询的表连接列也必须包含在索引中。

 

我们建议在工作中，尽量避免使用标量子查询，假如主表返回大量数据，主表的连接列基数很高，那么子查询中的表会被多次扫描，从而严重影响SQL性能。如果主表数据量小，或者主表的连接列基数很低，那么这个时候我们也可以使用标量子查询，但是记得要给子查询中表的连接列建立索引。

 

当SQL里面有标量子查询，我们可以将标量子查询等价改写为外连接，从而使它 们可以进行HASH连接。

 

为什么要将标量子查询改写为外连接而不是内连接呢？因为标量子查询是一个传值的过程，如果主表传值给子查询，子查询没有查询到数据，这个时候会显示NULL。如果将标量子查询改写为内连接，会丢失没有关联上的数据。

半连接：两表关联只返回一个表的数据就叫半连接。半连接一般就是指的in和exists。在 SQL优化实战中，半连接的优化是最为复杂的。in和exists一般情况下都可以进行等价改写。 

 

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

 

 

需要注意的是，not in里面如果有null，整个查询会返回空，而in里面有null，查询不受null影响。所以在将not exists等价改写为not in的时候，要注意null。一般情况下，如果反连接采用not in写法，我们需要在where条件中剔除null。

 

FILTER：如果子查询（in/exists/not in/not exists）没能展开（unnest），在执行计划中就会产生FILTER，FILTER类似嵌套循环，FILTER的算法与标量子查询一模一样。

 

 

IN 与EXISTS 谁快谁慢？

 

如果执行计划中没有产生FILTER，那么我们可以参考以下思路：in与exists是半连接，半连接也属于表连接，那么既然是表连接，我们需要关心两表的大小以及两表之间究竟走什么连接方式，还要控制两表的连接方式，才能随心所欲优化SQL，而不是去记什么时候in跑得快，什么时候exists跑得快。

 

SQL 语句的本质：标量子查询可以改写为外连接（需要注意表与表之间关系，去重），半连接可以改写为内连接（需要注意表与表之间关系，去重），反连接可以改写为外连接（不需要注意表与表之间关系，也不需要去重）。

 

SQL语句中几乎所有的子查询都能改写为表连接的方式，所以我们提出这个观点：SQL语句其本质就是表连接（内连接与外连接），以及表与表之间是几比几 关系再加上GROPU BY。