三种表连接方式比对

这里介绍三种表连接方式，分别为：嵌套循环Nested Loop Joins，哈希连接 Hash Joins，排序合并Sort Merge Joins

对于嵌套循环连接执行的方式：两表谓词过滤后的数据用双层循环的形式进行比较，选择出符合关联条件的行，外层循环为驱动表，内层循环为被驱动表，外层循环每有一条记录，就需要内层循环查找一次。

优化条件：1.两表连接列上建立索引

                  2.选择合适的驱动表（绝大部分情况小表做驱动表，此处一般为优化器自己选择）

选择小表为驱动表的原理:

两表连接每次的where条件关联只返回一条记录，存在2张表，一个10条记录，一个1000万条记录，若2表都存在连接字段索引

小表为驱动表的代价：10*(通过索引在大表查询一条记录的代价)，大概10*5

大表为驱动表的代价：1000万*(通过索引在小表查询一条记录的代价)，大概1000万*3

通过索引获取一条记录（取一条记录那在表里肯定就是一个块了，除非那行大于块的大小8k），10行的表代价通常在3  blocks，索引2块，表1块。1000万的表代价通常5 blocks，索引可能达到4块，表1块

hash连接的执行方式：也有驱动表，被驱动表的概念，选择驱动表所有符合条件的数据执行hash算法映射到内存，然后依次选择被驱动表的数据执行相同hash算法,看是否与内存中的数据匹配，仅限等值连接。

优化：1.驱动表要小，要能全部放在内存中

 排序合并连接的方式：  合并排序，顾名思义，就是通过将两个有序的序列合并为一个大的有序的序列的方式来实现排序。合并排序是一种典型的分治算法：首先将序列分为两部分，然后对每一部分进行循环递归的排序，然后逐个将结果进行合并。

假如有A、B两张表进行排序合并连接，ORACLE会首先将A表进行排序，形成一张临时的“表”C，然后将B进行排序，形成一张临时的“表”D，然后将C与D进行合并操作，返回结果集。就是两个行源分别按照关联字段排序以后，做关联。

优化： 1.连接列建立索引

            2.要有足够内存 （合并排序最大的优点是它的时间复杂度为O(nlgn)，这个是我们之前的选择排序和插入排序所达不到的。他还是一种稳定性排序，也就是相等的元素在序列中的相对位置在排序前后不会发生变化。他的唯一缺点是，需要利用额外的N的空间来进行排序。）

借鉴别人的总结：

访问次数|嵌套循环|驱动表返回几条，被驱动表访问多少次
              |—————————————————————————————————————— 
              |哈希连接|表都最多访问一次
              |--------------------------------------------------------------------------
              |排序合并|表都最多访问一次
---------------------------------------------------------------------------
适应场景|嵌套循环|小量数据的连接或小表大表连接
              |—————————————————————————————————————— 
              |哈希连接|大数据的连接或小表大表连接
              |--------------------------------------------------------------------------
              |排序合并|大数据的连接且有排序操作
---------------------------------------------------------------------------
排序差异|嵌套循环|无排序
              |-------------------------------------------------------------
              |哈希连接|无排序，但会消耗内存建立Hash表
              |--------------------------------------------------------------
              |排序合并|有排序
----------------------------------------------------------------
限制场景|嵌套循环|无任何限制，<>,>,<,like都可以用
              | ——————————————————————————
              |哈希连接|只能使用=
              | -----------------------------------------------------
              |排序合并|无任何限制，<>,>,<,like都可以用
----------------------------------------------------
索引使用|嵌套循环|驱动表的限制条件和被驱动表的连接条件上创建索引
              | ------------------------------------------------------------
              |哈希连接|索引列无要求，与单表情况无异
              | -----------------------------------------------------------
              |排序合并|索引可以消除排序
              ---------------------------------------------------------

需要注意连接时，要避免笛卡尔积，即避免字段应该是多对多的关系，即连接键尽量不能有重复值。

感谢以下文章作者，借鉴其中部分内容：

表的三种连接方式官方解释及个人理解_ITPUB博客