＞SQL Server的三种物理连接之Loop Join（一）

SQL Server的三种物理连接之Loop Join（一）

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

Sql Server有三种物理连接Loop Join,Merge Join,Hash Join， 当表之间连接的时候会选择其中之一，不同的连接产生的性能不同，理解这三种物理连接对性能调优有很大帮助。

Nested Loop Join

简介

两表连接就相当于二重循环，从A表抽一条记录，遍历B表查找匹配记录，然后从a表抽下一条，遍历B表

例如：

       select t1.*,t2.* from t1,t2 where t1.col1=t2.col2;

       访问机制如下：

       for i in (select * from t1) loop

         for j in (select * from t2 where col2=i.col1) loop

         display results;

         end loop;

         end loop;

       类似一个嵌套循环

       嵌套循环执行时，先是外层循环进入内层循环，并在内层循环终止之后

       接着执行外层循环再由外层循环进入内层循环中，当外层循环全部终止时，程序结束。

 

示例演示：

先创建两个测试表

+ View Code ?

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

表被设计为每一行都占用一页。

通过上面查询结果可以看出表都没有聚集索引，执行下面代码：

+ View Code ?

1 2 3 4 5 6 7 8

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

 查询中用到了loop 提示，所以使用的物理连接是nested loop join

可以看到对表dbo.Tbl10扫描了100次，对外表dbo.Tbl100的每一行都扫描一次表dbo.Tbl10，可以通过SET STATISTICS IO ON，查看：

(10 row(s) affected)
Table 'Tbl10'. Scan count 1, logical reads 1000, 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.

因为在表Tbl10上的逻辑读是1000次，表一共有10页，所以整个表被读取了100次。

可以想象执行效率是很低的，我们可以在内层表上加上聚集索引优化查询，内层表如果没有索引就会表扫描，如果加了聚集索引就会使用索引查找。

在表Tbl10上添加聚集索引：

?

1	CREATE UNIQUE CLUSTERED INDEX [dbo.Tbl10(Val, Id) CL] ON dbo.Tbl10(Val, Id);

 再次执行loop join查询

通过sys.dm_db_index_physical_stats 查看 index depth， index depth 决定了执行一次查找需要读多少次。

这个执行计划现在不算是一个真正的嵌套循环了。SQL Server仍然通过左输入循环一行一次。但它不循环第二个输入。相反，它是通过直接查找读取需要的行。然而，查找仍执行了100次。因此，我们可以预期200个读取发生在该表。让我们来看一下统计数据SET STATISTICS IO ON:

?

1 2

Table 'Tbl10'. Scan count 100, logical reads 227, 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.

发现有227个读取，比预期的要多一些，这些额外的读取是因为需要访问额外的元数据页， 如IAM页（索引分配映射（Index Allocation Map：IAM）http://www.tuicool.com/articles/UVZ7Bj）。

Loop Join 优点和缺点：

• 跟merge join和hash join相比只有一步，merge join在join之前需要排序，hash join在join之前需要构建哈希表
• 只要找到一个匹配就立即返回。merge join在没有排完序之前不能返回，hash join在哈希表构建完成前也不能返回。
• 它是唯一的可以处理非等连接的算法。
• 在右输入上一个合适的索引可以节省大量的读取。
• 如果在右输入上没有可用的索引，必须完全读取每一行，使此算法非常昂贵（特别是在大数据集上）。

引用博客

http://sqlity.net/en/1471/a-join-a-day-the-nested-loops-join/

http://www.cnblogs.com/CareySon/archive/2013/01/09/2853094.html

http://www.2cto.com/database/201301/186885.html

分类: SQL Server

<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/4766076.html" class="p_n_p_prefix">« </a> 上一篇：    <a href="https://www.cnblogs.com/qianlixing/p/4766076.html" data-featured-image="" title="发布于 2015-08-29 23:17">Windows命令实现Sleep</a>
<br>
<a href="https://www.cnblogs.com/qianlixing/p/4764680.html" class="p_n_p_prefix">» </a> 下一篇：    <a href="https://www.cnblogs.com/qianlixing/p/4764680.html" data-featured-image="" title="发布于 2015-08-29 23:49">SQL Server的三种物理连接之Merge join（二）</a>

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

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