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

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

 

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

 

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

 

2. IN  EXISTS 谁快谁慢?

 

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

嵌套循环(NESTED LOOPS)

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

 

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

 

《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大表,大表走连接列索引(如果大表有过滤条件,需要将过滤条件与连接列组合起来创建组合索引),从而避免大表被全表扫描。

 

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

HASH连接(HASH JOIN )

 

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

 

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

 

如何优化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数据,从而避免驱动表被溢出到临时表空间。

 

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

排序合并连接(SORT MERGE JOIN)

 

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

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

 

怎么优化排序合并连接?

 

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

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

笛卡尔连接(CARTESIAN JOIN)

 

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

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

标量子查询(SCALAR SUBQURY)

 

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

 

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

 

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

 

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

 

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

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

半连接与反连接  

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

 

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

 

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

 

需要注意的是,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的算法与标量子查询一模一样。

 

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

 

IN 与EXISTS 谁快谁慢?

 

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

 

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

 

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

    原文作者:桑小榆的坚定学说
    原文地址: https://www.cnblogs.com/ISangyu/p/16278482.html
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞