SQL Server的三种物理连接之Merge join（二）

SQL Server的三种物理连接之Merge join（二）

        </h1>
        <div class="clear"></div>
        <div class="postBody">
            <div id="cnblogs_post_body" class="blogpost-body blogpost-body-html">

简介

merge join 对两个表在连接列上按照相同的规则排序，然后再做merge，匹配的输出。

下面这个动态图展示了merge join的详细过程。

 

merge join示例

创建两个表

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

IF OBJECT_ID( 'dbo.Tbl10' ) IS NOT NULL DROP TABLE dbo.Tbl10; CREATE TABLE dbo.Tbl10(    Id INT IDENTITY(1,1),    Val INT ,    Fill CHAR (7000) NOT NULL DEFAULT REPLICATE( 'Fill' ,1750) );    IF OBJECT_ID( 'dbo.Tbl100' ) IS NOT NULL DROP TABLE dbo.Tbl100; CREATE TABLE dbo.Tbl100(    Id INT IDENTITY(1,1),    Val INT ,    Fill CHAR (7000) NOT NULL DEFAULT REPLICATE( 'Fill' ,1750) );    INSERT INTO dbo.Tbl10(Val) SELECT TOP (10) 1+ROW_NUMBER()OVER( ORDER BY ( SELECT 1))%100 FROM sys.all_columns A, sys.all_columns B, sys.all_columns C;       INSERT INTO dbo.Tbl100(Val) SELECT TOP (100) ROW_NUMBER()OVER( ORDER BY ( SELECT 1)) FROM sys.all_columns A, sys.all_columns B, sys.all_columns C;    SELECT index_type_desc, alloc_unit_type_desc, index_depth, page_count, record_count FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID( 'dbo.Tbl10' ), NULL , NULL , 'SAMPLED' );    SELECT index_type_desc, alloc_unit_type_desc, index_depth, page_count, record_count FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID( 'dbo.Tbl100' ), NULL , NULL , 'SAMPLED' );

 执行下面的查询：

?

1 2 3 4 5 6 7 8

SET STATISTICS IO ON ; GO SELECT * FROM dbo.Tbl100 A INNER MERGE JOIN dbo.Tbl10 B ON A.Val = B.Val; GO SET STATISTICS IO OFF ;

 执行计划显示对每个输入都做了排序。

在执行计划的提示上可以看到表被扫描了一次。可以通过SET STATISTICS IO ON，查看：

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tbl10'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tbl100'. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
两个表的read count说明了每个表都只扫描了一次，我们会发现多出了一个Worktable。

在Sql Server不知道表中join列是唯一的情况下，会准备一个多对多的可能性，因为合并算法的设计是对每一个表中的每一个记录只访问一次，它不能处理多对多的关系。Worktable是在tempdb中的，如果表与表之间的关系是多对多时就会用到Worktable。

merge join操作符有一个多对多的属性，你可以在执行计划工具提示窗口看到。如果此属性设置为“true”，这意味着SQL服务器预计输入有重复的行。

merge join的应用场景：

如果对在表上有索引的或者查询有排序需求的，使用merge join是最好的选择。

merge join的排序十分消耗性能。merge join适合中等数据量的查询，如果是大数据量并且未经过排序的话hash join比较合适。

需要注意的是即使查询的数据只有潜在的多对多的关系时也会导致Worktable的创建和填充，如果有任何重复的值，在Worktable的数据频繁的重读对查询性能也是不利的。

<div id="blog_post_info">

好文要顶 关注我 收藏该文

行千里
粉丝 - 0 关注 - 14

+加关注

0

0

<div class="clear"></div>
<div id="post_next_prev">

<a href="https://www.cnblogs.com/qianlixing/p/4755816.html" class="p_n_p_prefix">« </a> 上一篇：    <a href="https://www.cnblogs.com/qianlixing/p/4755816.html" data-featured-image="" title="发布于 2015-08-29 23:48">SQL Server的三种物理连接之Loop Join（一）</a>
<br>
<a href="https://www.cnblogs.com/qianlixing/p/4764726.html" class="p_n_p_prefix">» </a> 下一篇：    <a href="https://www.cnblogs.com/qianlixing/p/4764726.html" data-featured-image="" title="发布于 2015-08-29 23:51">SQL Server的三种物理连接之Hash Join（三）</a>

posted @ 2015-08-29 23:49  行千里  阅读( 932)  评论( 0)  编辑  收藏  举报

</div><!--end: topics 文章、评论容器-->