当前位置:Gxlcms > mysql > 联接算法

联接算法

时间:2021-07-01 10:21:17 帮助过:35人阅读

在Microsoft SQLServer Management Studio中执行查询时,如果选定工具栏中的 按钮,可以看到为查询生成的执行计划。执行计划以图形方式显示了SQL Server查询优化器选择的数据检索方法,如表扫描、排序、哈希匹配等。对于联接查询,SQL Server会根据联接表之

在Microsoft SQLServer Management Studio中执行查询时,如果选定工具栏中的\按钮,可以看到为查询生成的执行计划。执行计划以图形方式显示了SQL Server查询优化器选择的数据检索方法,如表扫描、排序、哈希匹配等。对于联接查询,SQL Server会根据联接表之间的数据、索引等情况,选择使用嵌套循环联接、合并联接或哈希联接。

7.7.1嵌套循环联接

嵌套循环联接也称为“嵌套迭代”,它将一个联接输入用作外部输入表(显示为图形执行计划中的顶端输入),将另一个联接输入用作内部(底端)输入表。外部循环逐行处理外部输入表。内部循环会针对每个外部行执行,在内部输入表中搜索匹配行。简单地讲,就是扫描其中的一个联接表,并为该表中的每一行在另一个联接表中搜索匹配行。

如果外部输入较小(不到10行)而内部输入较大且预先创建了索引,则嵌套循环联接尤其有效。在许多小事务中(如那些只影响较小的一组行的事务),索引嵌套循环联接优于合并联接和哈希联接。但在大型查询中,嵌套循环联接通常不是最佳选择。

例如,下面的查询由于Sales.Customer表行数只有1行,而Sales.SalesOrderHeader数据量较大,因此将使用嵌套循环联接,生成的执行计划如图7-11所示。

USE AdventureWorks;

GO

SELECT *

FROM Sales.Customer

INNER JOINSales.SalesOrderHeader

ONCustomer.CustomerID = SalesOrderHeader.CustomerID

WHERE Customer.CustomerID = 1;

\

图7-11使用嵌套循环的执行计划

在该计划中存在两个嵌套循环,其中只有左边的嵌套循环符用于Sales.Customer与Sales.SalesOrderHeader的联接,而右边的嵌套循环是用于Sales.SalesOrderHeader的索引查找与物理行定位(键查找)之间的联接。执行计划右上角的Sales.Customer表被作为外部输入,在聚集索引中查找客户。对于每个客户,嵌套循环运算将对SalesOrderHeader.CustomerID列上的IX_SalesOrderHeader_CustomerID索引执行一次查找,然后再跟一个键查找来定位要访问的数据行。

7.7.2合并联接

合并联接要求两个输入都在合并列上排序,合并列由联接谓词的等效(ON)子句定义。由于每个输入都已排序,因此合并联接将从每个输入获取一行并将其进行比较。例如,对于内联接操作,如果行相等则返回。如果行不相等,则废弃值较小的行并从该输入获得另一行。这一过程将重复进行,直到处理完所有的行为止。

合并联接操作可以是常规操作,也可以是多对多操作。多对多合并联接使用临时表存储行。如果每个输入中有重复值,则在处理其中一个输入中的每个重复项时,另一个输入必须重绕到重复项的开始位置。

合并联接本身的速度很快,但是如果合并列上未建立索引,选择合并联接有可能会非常费时,因为它首先要对列进行排序操作。然而,如果数据量很大且能够从索引中获得预排序的所需数据,则合并联接通常是最快的可用联接算法。

例如,下面的查询语句将获取订单的详细信息,由于SalesOrderHeader和SalesOrderDetail在合并列SalesOrderID上都具有聚集索引,已经将列进行了排序,所以查询优化器会选择合并联接。如图7-12所示。

USE AdventureWorks;

GO

SELECT *

FROM Sales.SalesOrderHeader

INNER JOINSales.SalesOrderDetail

ONSalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID;

\

图7-12使用合并联接的执行计划

7.7.3哈希联接

哈希联接可以有效处理未排序的大型非索引输入。因此,它对处理复杂查询的中间结果很有用。查询的中间结果是未经索引的,而且通常不会为查询计划中的下一个操作进行适当的排序。并且,查询优化器只估计中间结果的大小。而对于复杂查询,估计可能有很大的误差,因此如果中间结果比预期的大得多,则处理中间结果的算法不仅必须有效而且必须适度弱化。再像合并联接那样严格要求具备排序列,对于中间结果而言是不现实的,排序成本的付出可能要远远大于数据的直接检索成本。

选择哈希联接的两种情况:一是没有为联接创建合适的索引,二是中间结果比较大。

哈希联接有两种输入:生成输入和探测输入。查询优化器会选择二者中较小的那个作为生成输入,对联接列值应用哈希函数,将生成输入中的行分配到哈希桶中。哈希桶是一种存放所访问数据位置的结构,有了它,进行数据检索时,可以避免不必要的表扫描。

为了验证无索引情况下的哈希联接使用,首先使用下面的语句创建Sales.Customer和Sales.SalesOrderHeader表的副本。

USE AdventureWorks;

GO

SELECT TOP 10 *

INTO MyCustomer

FROM Sales.Customer

ORDER BY CustomerID;

SELECT TOP 100 *

INTO MySalesOrderHeader

FROM Sales.SalesOrderHeader

ORDER BY CustomerID;

执行下面的查询,可以看到如图7-13所示的执行计划。

SELECT *

FROM MyCustomer

INNER JOINMySalesOrderHeader

ONMyCustomer.CustomerID = MySalesOrderHeader.CustomerID;

\

图7-13使用哈希联接的执行计划

下面再来看一个比较有趣的示例。下面的查询语句中仅选择了Sales.Customer中CustomerID = 1的行与Sales.SalesOrderHeader进行联接,由于联接行数很小,所产生中间结果的数据量也比较小,因此,可以看到查询优化器为语句使用了嵌套循环联接。如图7-14所示。

USE AdventureWorks;

GO

SELECT *

FROM Sales.Customer

INNER JOINSales.SalesOrderHeader

ONCustomer.CustomerID = SalesOrderHeader.CustomerID

WHERE Customer.CustomerID = 1;

\

图7-14数据量较小时使用嵌套循环联接

同样是上面的联接,去除掉WHERE筛选条件后数据量明显增大,执行该语句会发现查询优化器使用了哈希联接方式。如图7-15所示。

SELECT *

FROM Sales.Customer

INNER JOINSales.SalesOrderHeader

ONCustomer.CustomerID = SalesOrderHeader.CustomerID;

\

图7-15数据量较大时使用哈希联接

7.7.4使用联接提示强制联接策略

联接提示用于指定查询优化器在两个表之间强制执行联接策略,提示符包括LOOP JOIN、MERGE JOIN和HASH JOIN,分别用于嵌套循环、哈希和合并联接。如果指定了多个联接提示,则优化器从允许的联接策略中选择开销最少的联接策略。此外,也可以使用OPTION子句指定联接策略。但是这种方式会影响查询中的所有联接,通常用于旧式联接语法。

1.为每个联接指定单独的联接策略

可以在FROM子句中使用LOOP JOIN、MERGE JOIN和HASH JOIN提示符为每个联接单独指定联接策略。例如,下面的查询语句指定使用嵌套循环联接。

USE AdventureWorks;

GO

SELECT *

FROM Sales.Customer

INNER LOOPJOIN Sales.SalesOrderHeader

ONCustomer.CustomerID = SalesOrderHeader.CustomerID;

又如,下面的查询语句指定使用合并联接。

USE AdventureWorks;

GO

SELECT *

FROM Sales.Customer

INNERMERGE JOIN Sales.SalesOrderHeader

ONCustomer.CustomerID = SalesOrderHeader.CustomerID;

在多表联接中使用联接提示时,会影响联接的执行顺序。在前面介绍了,在不影响返回结果正确的情况下,查询优化器会按照效率优先的原则,选择首先执行的联接。例如,下面语句的执行计划如图7-16所示,可以看到首先执行的是Sales.SalesOrderHeader与Sales.SalesOrderDetail的联接,然后将联接结果再与Sales.Customer进行联接。

USE AdventureWorks;

GO

SELECT *

FROM Sales.Customer

INNER JOINSales.SalesOrderHeader

ONCustomer.CustomerID = SalesOrderHeader.CustomerID

INNER JOINSales.SalesOrderDetail

ONSalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID;

\

图7-16未使用联接提示的执行计划

下面的语句为Sales.Customer和Sales.SalesOrderHeader指定了合并联接提示,并且这个提示仅对这两个表起作用,与Sales.SalesOrderDetail的联接策略仍旧由查询优化器决定。由于明确指定了Sales.Customer与Sales.SalesOrderHeader使用合并联接,优化器会先执行该联接,而不是先执行Sales.SalesOrderHeader与Sales.SalesOrderDetail的联接。否则,就会造成Sales.Customer与Sales.SalesOrderHeader和Sales.SalesOrderDetail的联接结果再执行合并联接。图7-17是该语句的执行计划。

SELECT *

FROM Sales.Customer

INNERMERGE JOIN Sales.SalesOrderHeader

ONCustomer.CustomerID = SalesOrderHeader.CustomerID

INNER JOINSales.SalesOrderDetail

ONSalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID;

\

图7-17使用联接提示后的执行计划

如果希望Sales.Customer与Sales.SalesOrderHeader和Sales.SalesOrderDetail的联接结果执行合并联接,则应当使用嵌套联接的方式实现,参考下面的语句:

SELECT *

FROM Sales.Customer

INNERMERGE JOIN (Sales.SalesOrderHeader

INNER JOIN Sales.SalesOrderDetail

ONSalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID)

ONCustomer.CustomerID = SalesOrderHeader.CustomerID;

2.为全部联接指定统一的联接策略

当使用旧式联接语法时,应当使用OPTION子句指定联接策略,但是,这种策略会影响语句中的全部联接,无法为每个联接单独指定不同的联接策略,如:

SELECT *

FROM Sales.Customer, Sales.SalesOrderHeader,Sales.SalesOrderDetail

WHERE Customer.CustomerID =SalesOrderHeader.CustomerID

ANDSalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID

OPTION (MERGE JOIN);

该语句的执行计划如图7-18所示,可以看到三个表之间全部使用了合并联接策略。

\

图7-18为全部联接使用统一联接策略的执行计划

在ANSI SQL:1992规范中,也可以使用OPTION子句,它同样也是影响语句中的全部联接,如:

SELECT *

FROM Sales.Customer

INNER JOINSales.SalesOrderHeader

ONCustomer.CustomerID = SalesOrderHeader.CustomerID

INNER JOINSales.SalesOrderDetail

ONSalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID

OPTION (MERGE JOIN);

人气教程排行