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

简介

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

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

merge join algorithm

merge join示例

创建两个表

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

执行下面的查询：

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的数据频繁的重读对查询性能也是不利的。

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

推荐阅读