SQL中几种常用的表连接方式详解本文转载请联系数据仓库宝宝库公众号。数据库性能优化最重要的部分是SQL优化。SQL优化的关键离不开三点：表连接方式、访问路径和执行顺序。本文重点介绍几种常见的连接方式。对于多表关联查询，查询优化器的执行步骤如下。1）访问路径：查询语句中涉及到多个对象，每个对象数据的检索方式可以根据成本决定，是选择全表扫描还是索引访问等。2）连接方式：连接方式结果集之间，主要包括嵌套循环、哈希连接、排序合并连接等。优化器对结果集之间连接方式的判断尤为重要，因为判断结果将直接影响SQL的执行效率。3）关联顺序：当关联对象超过2个时，先选择两个对象关联得到的结果集，再与第三个结果集关联。下面我们重点介绍几种常见的连接方式。01嵌套循环连接图1为嵌套循环连接示意图。图1嵌套循环连接示意图嵌套循环查询过程如下。1）两个表关联，优化器会先确定驱动表，也就是外表（outertable），另一个是被驱动表，也就是内表（innertable）。一般情况下，优化器会定义少量数据作为驱动表。在执行计划中，驱动表在最上层，从动表在下层。2）驱动表确定后，从中提取一行有效数据，在驱动表（内表）中查找匹配提取有效数据。3）返回数据给客户端。从上面的步骤我们可以看出，驱动表返回的行数直接影响到被驱动表的访问次数。比如驱动表根据过滤条件最终返回10行有效数据，每返回一行都会将值传给驱动表。匹配表，驱动表一共需要迭代10次。示例代码如下：SQL>SELECT/*+USE_NL(ed)*/e.first_name,e.last_name,e.salary,d.department_nameFROMhr.employeese,hr.departmentsdWHEREd.department_nameIN('Marketing','Sales')ANDe.department_id=d.department_id;SQL>select*fromtable(dbms_xplan.DISPLAY_CURSOR(null,null,'ALLSTATSLAST'));SQL_ID3nsqhdh150bx5,childnumber0----------------------------------SELECT/*+USE_NL(ed)*/e.first_name,e.last_name,e.salary,d.department_nameFROMhr.employeese,hr.departmentsdWHEREd.department_nameIN('营销','销售')ANDe.department_id=d.department_idPlanhashvalue:2968905875--------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|--------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||36|00:00:00.01|23||1|NESTEDLOOPS||1|19|36|00:00:00.01|23||*2|TABLEACCESSFULL|部门|1|2|2|00:00:00.01|8||*3|TABLEACCESSFULL|员工|2|10|36|00:00:00.01|15|----------------------------------------------------------------------------------从上面的示例代码可以看出，DEPARTMENTS是driver表，Starts为1，表示只访问一次，返回2行有效数据（A-Rows为实际返回的行数），EMPLOYEES为被驱动表，Starts为2，表示students学过C++编程的人应该访问两次请记住，C++中的嵌套循环有点类似于以下循环：#includeintmain(){inti,j;for(i=1;i<100;i++){for(j=1;j<=100;j++)if(!(i%j))break;if(j>(i/j))printf("%d\n",i);}return0;}j的循环次数取决于i的取值范围，我们可以将i视为驱动表，将j视为从动表。嵌套循环连接性能主要受以下几点限制。为驱动表返回的行数。从动表的访问方式：如果从动表的连接列的基数小，选择性差，会导致全表扫描的访问方式，其效率会变得很低。所以我们建议连接列有索引，而且基数大，选择性高。.driver表过滤后会返回少量数据。被驱动表的关联字段需要有索引（连接列基数大或选择性高）。两表关联后会返回少量数据。适用于OLTP系统。Tips如果优化器选择了错误的连接方式，那么我们可以使用提示（hint）强制使用嵌套循环的连接方式：“/*+USE_NL(TABLE1,TABLE2)LEADING(TABLE1)*/”，其中TABLE1和TABLE2是关联表的别名，LEADING(TABLE1)用于指定TABLE1为驱动表。02哈希连接图2为哈希连接示意图。图2 hashjoin示意图嵌套循环join适用于两张表关联后返回少量数据的情况，那么返回大量数据时应该使用哪种join方式呢？答案是使用hashjoin。hashjoin的查询过程如下。1）两个表之间的等价关联。2)优化器使用一个数据量较小的表作为驱动表，在PGA的SQL工作区中根据驱动表的连接列构造哈希表。3）读取大表，散列连接列（检查散列表，找到连接的行）。4）返回数据给客户端。从以上步骤可以看出，通过hash值匹配的方式更适合两张表的等价关联。示例代码如下：SQL>SELECT/*+USE_HASH(ol)*/o.customer_id,l.unit_price*l.quantity2FROMoe.orderso,oe.order_itemsl3WHEREl.order_id=o.order_id;SQL>select*fromtable(dbms_xplan.DISPLAY_CURSOR(null,null,'ALLSTATSLAST'));SQL_IDcu980xxpu0mmq,childnumber0------------------------------------SELECT/*+USE_HASH(ol)*/o.customer_id,l.unit_price*l.quantityFROMoe.orderso,oe.order_itemslWHEREl.order_id=o.order_idPlanhashvalue:864676608--------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|Reads|OMem|1Mem|Used-Mem|--------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||665|00:00:00.04|57|5|||||*1|HASHJOIN||1|665|665|00:00:00.04|57|5|1888K|1888K|1531K(0)||2|TABLEACCESSFULL|ORDERS|1|105|105|00:00:00.04|6|5|||||3|TABLEACCESSFULL|ORDER_ITEMS|1|665|665|00:00:00.01|51|0||||-------------------------------------------------------------------------------------------------------从上面的示例代码我们可以看出，ORDERS为驱动表，Starts为1，表示访问一次，返回105行有效数据（A-Rows为实际返回行数），ORDER_ITEMS为驱动表，Starts也为1，表示它只被访问一次。其中，OMem，1Mem为执行所需的PGA评估值，Used-Mem为实际执行时PGA中SQL工作区消耗的内存（即磁盘交换次数），当驱动表较大时，PGA的SQL工作区不能完全容纳，会溢出到临时表空间产生磁盘交互，影响性能。Hashjoin性能主要受以下两点限制。等效连接。当PGASQL工作区较小，驱动表为大表时，容易出现性能问题。当同时满足以下条件时，hashjoin方法将非常有用。两表等价关联后返回大量数据。与嵌套循环连接不同，散列连接在连接驱动表的字段时不需要索引。提示同样，我们也可以使用提示来强制使用散列连接：“/*+USE_HASH(TABLE1,TABLE2)LEADING(TABLE1)*/”。03排序合并连接图3为排序合并连接示意图。图3sort-mergejoin示意图Hashjoin适用于两表等价关联后返回大量数据的情况，那么对于非等价关联返回大量数据的情况应该使用哪种连接方式数据量？答案是排序合并连接。当同时满足以下条件时，sort-mergejoin的性能优于hashjoin。两个表之间的非等价关联（>、>=、<、<=、<>）。数据源本身是有序的。不需要额外的排序。sort-mergejoin模式没有驱动表的概念，join查询过程如下。1）两张表根据关联的列进行排序。2）在内存中进行合并处理。从上面的实现步骤我们可以看出，由于匹配的对象是join列的排序后的值，所以sortmergejoin方式更适用于两个表不是等价关联的情况。示例代码如下：SQL>SELECTo.customer_id,l.unit_price*l.quantityFROMoe.orderso,oe.order_itemslWHEREl.order_id>o.order_id;32233rowsselected..SQL>select*fromtable(dbms_xplan.DISPLAY_CURSOR(null,null,'ALLSTATSLAST'));SQL_IDajyppymnhwfyf,childnumber1--------------------------------SELECTo.customer_id,l.unit_price*l.quantityFROMoe.orderso,oe.order_itemslWHEREl.order_id>o.order_idPlanhashvalue:2696431709----------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|--------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||32233|00:00:00.10|21|||||1|MERGEJOIN||1|34580|32233|00:00:00.10|21||||2|SORTJOIN||1|105|105|00:00:00.01|4|11264|11264|10240(0)||3|TABLEACCESSFULL|ORDERS|1|105|105|00:00:00.01|4|||||*4|SORTJOIN||105|665|32233|00:00:00.05|17|59392|59392|53248(0)||5|TABLEACCESSFULL|ORDER_ITEMS|1|665|665|00:00:00.01|17||||---------------------------------------------------------------------------------------------------------从上例所示的执行计划可以看出，ORDERS表StartswithID=3为1，说明访问过一次，返回105行有效数据（A-Rows是实际返回的行数），ORDER_ITEMS表的Starts为1，说明只访问了一次，而ID=4的SORTJOIN表的Starts为105，说明匹配了105次在内存中执行。其中，OMem和1Mem是用于执行排序操作所需的PGA评估值，Used-Mem是实际执行时PGA中SQL工作区消耗的内存（即磁盘交换次数）。从上面的步骤可以看出，由于比较对象是两张表的连接列order_id，所以需要先对各自的连接列进行排序（ID=2和ID=4），然后再进行匹配。如果此时连接列上有索引，则索引返回的数据是有序的，此时不需要额外的排序操作。Tips同样，我们也可以使用提示来强制选择排序合并连接：“/*+USE_MERGE(TABLE1,TABLE2)*/”。04Cartesianjoin当一个或多个表在没有任何连接条件的情况下连接时，数据库将使用Cartesianjoin。优化器将一个数据源的每一行与另一个数据源的每一行连接起来，以创建两组数据的笛卡尔积。示例代码如下：SQL>SELECTo.customer_id,l.unit_price*l.quantityFROMoe.orderso,oe.order_itemsl;69825rowsselected.SQL>select*fromtable(dbms_xplan.DISPLAY_CURSOR(null,null,'ALLSTATSLAST'));SQL_IDd3xygy88uqzny,childnumber0---------------------------------------SELECTo.customer_id,l.unit_price*l.quantityFROMoe.orderso,oe.order_itemslPlanhashvalue:2616129901------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|Buffers|OMem|1Mem|Used-Mem|--------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||125|||||1|MERGEJOINCARTESIAN||1|69825|125|||||2|TABLEACCESSFULL|ORDERS|1|105|108|||||3|BUFFERSORT||105|665|17|27648|27648|24576(0)||4|TABLEBACCESSFULL|ORDER_ITEMS|1|665|17||||----------------------------------------------------------------------来自上面的执行计划我们可以看到先对order_items表进行排序，然后再对两张表进行笛卡尔积运算。由于没有过滤条件，当数据量较大时，返回的返回的行数会很大，所以除非有特殊情况，不建议使用没有任何连接条件的查询
