敞开生长之旅！这是我参与「日新计划 12 月更文应战」的第9天，点击查看活动概况
上篇的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。
