位置: 编程技术 - 正文

浅谈SQL Server中的三种物理连接操作(性能比较)(sql server的介绍)

编辑:rootadmin
在SQL Server中,我们所常见的表与表之间的Inner Join,Outer Join都会被执行引擎根据所选的列,数据上是否有索引,所选数据的选择性转化为Loop Join,Merge Join,Hash Join这三种物理连接中的一种。理解这三种物理连接是理解在表连接时解决性能问题的基础,下面我来对这三种连接的原理,适用场景进行描述。 嵌套循环连接(Nested Loop Join) 循环嵌套连接是最基本的连接,正如其名所示那样,需要进行循环嵌套,嵌套循环是三种方式中唯一支持不等式连接的方式,这种连接方式的过程可以简单的用下图展示: 图1.循环嵌套连接的第一步

图2.循环嵌套连接的第二步

由上面两个图不难看出,循环嵌套连接查找内部循环表的次数等于外部循环的行数,当外部循环没有更多的行时,循环嵌套结束。另外,还可以看出,这种连接方式需要内部循环的表有序(也就是有索引),并且外部循环表的行数要小于内部循环的行数,否则查询分析器就更倾向于Hash Join(会在本文后面讲到)。

通过嵌套循环连接也可以看出,随着数据量的增长这种方式对性能的消耗将呈现出指数级别的增长,所以数据量到一定程度时,查询分析器往往就会采用这种方式。

下面我们通过例子来看一下循环嵌套连接,利用微软的AdventureWorks数据库:

图3.一个简单的嵌套循环连接

图3中ProductID是有索引的,并且在循环的外部表中(Product表)符合ProductID=的行有条,因此,对应的SalesOrderDetail表需要查找次。让我们在上面的查询中再考虑另外一个例子,如图4所示。

图4.额外的列带来的额外的书签查找

由图4中可以看出,由于多选择了一个UnitPrice列,导致了连接的索引无法覆盖所求查询,必须通过书签查找来进行,这也是为什么我们要养成只Select需要的列的好习惯,为了解决上面的问题,我们既可以用覆盖索引,也可以减少所需的列来避免书签查找。另外,上面符合ProductID的行仅仅只有5条,所以查询分析器会选择书签查找,假如我们将符合条件的行进行增大,查询分析器会倾向于表扫描(通常来说达到表中行数的1%以上往往就会进行table scan而不是书签查找,但这并不绝对),如图5所示。

图5.查询分析器选择了表扫描

可以看出,查询分析器此时选择了表扫描来进行连接,这种方式效率要低下很多,因此好的覆盖索引和Select *都是需要注意的地方。另外,上面情况即使涉及到表扫描,依然是比较理想的情况,更糟糕的情况是使用多个不等式作为连接时,查询分析器即使知道每一个列的统计分布,但却不知道几个条件的联合分布,从而产生错误的执行计划,如图6所示。

图6.由于无法预估联合分布,导致的偏差

由图6中,我们可以看出,估计的行数和实际的行数存在巨大的偏差,从而应该使用表扫描但查询分析器选择了书签查找,这种情况对性能的影响将会比表扫描更加巨大。具体大到什么程度呢?我们可以通过强制表扫描和查询分析器的默认计划进行比对,如图7所示。

图7.强制表扫描性能反而更好

合并连接(Merge Join)

谈到合并连接,我突然想起在西雅图参加SQL Pass峰会晚上酒吧排队点酒,由于我和另外一哥们站错了位置,貌似我们两个在插队一样,我赶紧说:I'm sorry,i thought here is end of line。对方无不幽默的说:”It's OK,In SQL Server,We called it merge join”。

由上面的小故事不难看出,Merge Join其实上就是将两个有序队列进行连接,需要两端都已经有序,所以不必像Loop Join那样不断的查找循环内部的表。其次,Merge Join需要表连接条件中至少有一个等号查询分析器才会去选择Merge Join。

Merge Join的过程我们可以简单用下面图进行描述:

图8.Merge Join第一步

Merge Join首先从两个输入集合中各取第一行,如果匹配,则返回匹配行。加入两行不匹配,则有较小值的输入集合+1,如图9所示。

图9.更小值的输入集合向下进1

用C#代码表示Merge Join的话如代码1所示。

代码1.Merge Join的C#代码表示

因此,通常来说Merge Join如果输入两端有序,则Merge Join效率会非常高,但是如果需要使用显式Sort来保证有序实现Merge Join的话,那么Hash Join将会是效率更高的选择。但是也有一种例外,那就是查询中存在order by,group by,distinct等可能导致查询分析器不得不进行显式排序,那么对于查询分析器来说,反正都已经进行显式Sort了,何不一石二鸟的直接利用Sort后的结果进行成本更小的MERGE JOIN?在这种情况下,Merge Join将会是更好的选择。

另外,我们可以由Merge Join的原理看出,当连接条件为不等式(但不包括!=),比如说> < >=等方式时,Merge Join有着更好的效率。

下面我们来看一个简单的Merge Join,这个Merge Join是由聚集索引和非聚集索引来保证Merge Join的两端有序,如图所示。

图.由聚集索引和非聚集索引保证输入两端有序

当然,当Order By,Group By时查询分析器不得不用显式Sort,从而可以一箭双雕时,也会选择Merge Join而不是Hash Join,如图所示。

图.一箭双雕的Merge Join

哈希匹配(Hash Join)

哈希匹配连接相对前面两种方式更加复杂一些,但是哈希匹配对于大量数据,并且无序的情况下性能均好于Merge Join和Loop Join。对于连接列没有排序的情况下(也就是没有索引),查询分析器会倾向于使用Hash Join。

哈希匹配分为两个阶段,分别为生成和探测阶段,首先是生成阶段,第一阶段生成阶段具体的过程可以如图所示。

图.哈希匹配的第一阶段

图中,将输入源中的每一个条目经过散列函数的计算都放到不同的Hash Bucket中,其中Hash Function的选择和Hash Bucket的数量都是黑盒,微软并没有公布具体的算法,但我相信已经是非常好的算法了。另外在Hash Bucket之内的条目是无序的。通常来讲,查询优化器都会使用连接两端中比较小的哪个输入集来作为第一阶段的输入源。

接下来是探测阶段,对于另一个输入集合,同样针对每一行进行散列函数,确定其所应在的Hash Bucket,在针对这行和对应Hash Bucket中的每一行进行匹配,如果匹配则返回对应的行。

通过了解哈希匹配的原理不难看出,哈希匹配涉及到散列函数,所以对CPU的消耗会非常高,此外,在Hash Bucket中的行是无序的,所以输出结果也是无序的。图是一个典型的哈希匹配,其中查询分析器使用了表数据量比较小的Product表作为生成,而使用数据量大的SalesOrderDetail表作为探测。

图.一个典型的哈希匹配连接

上面的情况都是内存可以容纳下生成阶段所需的内存,如果内存吃紧,则还会涉及到Grace哈希匹配和递归哈希匹配,这就可能会用到TempDB从而吃掉大量的IO。这里就不细说了,有兴趣的同学可以移步: 总结 下面我们通过一个表格简单总结这几种连接方式的消耗和使用场景: 嵌套循环连接合并连接哈希连接适用场景外层循环小,内存循环条件列有序输入两端都有序数据量大,且没有索引CPU低低(如果没有显式排序)高内存低低(如果没有显式排序)高IO可能高可能低低可能高可能低理解SQL Server这几种物理连接方式对于性能调优来说必不可少,很多时候当筛选条件多表连接多时,查询分析器就可能不是那么智能了,因此理解这几种连接方式对于定位问题变得尤为重要。此外,我们也可以通过从业务角度减少查询范围来减少低下性能连接的可能性。 参考文献: 文章来自:

推荐整理分享浅谈SQL Server中的三种物理连接操作(性能比较)(sql server的介绍),希望有所帮助,仅作参考,欢迎阅读内容。

浅谈SQL Server中的三种物理连接操作(性能比较)(sql server的介绍)

文章相关热门搜索词:sql server的sql语句,sql server用处,sql server用处,sql server用处,sql servers,sql server?,sql servers,sql server用处,内容如对您有帮助,希望把文章链接给更多的朋友!

SQL Server误区日谈 第1天 正在运行的事务在服务器故障转移后继续执行 误区#1:在服务器故障转移后,正在运行的事务继续执行这当然是错误的!每次故障转移都伴随着某种形式的恢复。但是如果当正在执行的事务没有Commit

SQL Server误区日谈 第2天 DBCC CHECKDB会导致阻塞 误区#2:DBCCCHECKDB会引起阻塞,因为这个命令默认会加锁这是错误的!在SQLServer7.0以及之前的版本中,DBCCCHECKDB命令的本质是C语言实现的一个不断嵌套循

SQL Server误区日谈 第3天 即时文件初始化特性可以在SQL Server中开启和关闭 本系列文章是我在sqlskill.com的PAUL的博客看到的,很多误区都比较具有典型性和代表性,原文来自T-SQLTuesday#:Misconceptionsabout....EVERYTHING!!,经过我们团队

标签: sql server的介绍

本文链接地址:https://www.jiuchutong.com/biancheng/347814.html 转载请保留说明!

上一篇:sqlserver获取各种形式的时间(sql server 获取今天的数据)

下一篇:SQL Server误区30日谈 第1天 正在运行的事务在服务器故障转移后继续执行(sql server错误和使用情况报告)

  • 小规模增值税会计处理流程
  • 纸质发票红冲操作流程
  • 税率变更后账务怎么处理
  • 已经缴纳的税款怎么做账
  • 跨年度退货可以开红字发票吗
  • 季报企业所税缴税了,但年报是亏损的
  • 接受的捐赠收入要交税吗
  • 工资薪金所得应纳税所得额
  • 科技人员股权奖励
  • 进货没有开具发票能退吗
  • 没办税务登记公示怎么办
  • 源泉扣缴税率是多少
  • 发生破损
  • 水利基金减免政策2019 未超过10万
  • 对境外支付佣金的规定
  • 领用原材料安装固定资产
  • 存货变卖残值取得的收益计入哪个科目
  • 不交社保是否可以马上辞职
  • 企业所得税法如何确认应税收入
  • 固定资产清查表
  • 少数股东权益怎么填
  • 收到一张增值税红冲发票
  • 前端向后端发送请求
  • macos快捷键一览
  • php字符串定义的三种方式
  • win10右键没有新建excel和word
  • msqry32.exe进程是什么文件 作用是什么 msqry32进程查询
  • 带薪缺勤会计分录
  • php cmd
  • PHP基于什么语言
  • vue3使用高德地图
  • 滚动条基本样式有哪些
  • 分享2款CSS3母亲节主题寄语文字动画特效
  • vmware17虚拟机安装教程
  • 用css画一个扇形
  • php判断用户是否登录
  • 增值税怎么算出来的
  • 盈余公积转增会计分录
  • 社会保险基数怎么办
  • 开发成本结转开发产品的分录
  • 工资外一次性收入88000交多少税
  • 为什么社保卡里有钱
  • 织梦相关文章调用
  • centos上安装vscode
  • MySQL中使用_____语句更新表中的数据
  • 电子发票与纸质发票具有不同的法律效力
  • 增值税发票丢失怎么补开
  • 汇算清缴后发现成本多做了
  • 退物业费如何记帐
  • 什么叫做增值税的新增价值呢
  • 公司交的养老保险的钱可以取出来吗
  • 进项税额加计10
  • 可供分配利润包括哪些
  • 预付款充值发票
  • 普通发票作废要去税局吗
  • 在Linux环境下mysql的root密码忘记解决方法(三种)
  • ubuntu更新软件
  • linux系统中的一切都归结为
  • xp系统怎么删除应用程序
  • win7 mysql
  • Linux中如何查看文件大小
  • 未能打开这台计算机上的组策略对象
  • windows的气泡屏保会加速
  • 用win7的人多吗
  • 手机用xp系统
  • dghm.exe是什么程序
  • win7旗舰版安装教程u盘
  • linux设置mac
  • linux系统怎么隐藏文件
  • 【Cocso2d-x Lua笔记五】quick中的display
  • Unity AssetBundle爬坑手记
  • css浮动和清除浮动
  • 占位符设置
  • asp.net 4.8
  • mkdirs linux
  • 用简单的方法做好玩的手工视频教程
  • 在javascript中什么方法可以对数组元素进行排序
  • Python对象转列表
  • 岗位回避什么意思
  • 重庆市国税电子税务局
  • 免责声明:网站部分图片文字素材来源于网络,如有侵权,请及时告知,我们会第一时间删除,谢谢! 邮箱:opceo@qq.com

    鄂ICP备2023003026号

    网站地图: 企业信息 工商信息 财税知识 网络常识 编程技术

    友情链接: 武汉网站建设