联接算法

发布时间:2021-10-25 10:10:14

本文摘自《锋利的SQL》:http://item.jd.com/10380652.html


在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);



?

相关文档

  • 乡镇信息宣传工作实施意见
  • 软件工程师的技能知识图谱0.1(偏后台方向)
  • 初中数学教学文章3篇
  • 百度面试题 java_百度JAVA研发工程师面试经验
  • 未来十年,将是物联网的天下?!
  • 独栋别墅室内装修效果图
  • 大话微服务
  • 电脑更新空间变小了怎么办
  • 小学数学解决问题方法大全
  • WKT解读
  • jpanel是什么
  • 如何写好期末评语
  • 露地香菜种植方法技术_如何在露天种植香菜
  • 全省入学年龄截止日不作统一规定
  • 距离矢量路由协议(distance vector) VS 链路状态路由协议(link-state)
  • amaze ui 的使用
  • 关于甜蜜幸福的短语
  • 关于夜景的优美句子
  • R语言随机抽样
  • 大学生个人总结500字
  • 浙江农村种植什么药材
  • 关于讲文明树新风的作文范文
  • 初一军训总结的发言稿
  • 红木家具怎么看好坏
  • 建筑施工现场消防安全知识
  • 汽车空调压力表正常值
  • 幼儿园创意绘画教案
  • 微信 获取地理位置名称_使用地理名称的目标地理位置
  • 0二叉树/回溯 牛客NC.8二叉树根节点到叶子结点的和为指定值的路径
  • 关于海康摄像头rtsp流转码推流到前端的若干尝试和总结
  • 猜你喜欢

    电脑版