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;
类似一个嵌套循环
嵌套循环执行时,先是外层循环进入内层循环,并在内层循环终止之后
接着执行外层循环再由外层循环进入内层循环中,当外层循环全部终止时,程序结束。
示例演示:
先创建两个测试表
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 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上添加聚集索引:
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:
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在哈希表构建完成前也不能返回。
- 它是唯一的可以处理非等连接的算法。
- 在右输入上一个合适的索引可以节省大量的读取。
- 如果在右输入上没有可用的索引,必须完全读取每一行,使此算法非常昂贵(特别是在大数据集上)。