[原创]sql server inner join 效率测试
今天有兄弟提到inner join，大表与小表的前后顺序会产生查询开销的差异，对此进行了如下测试：
一, 创建测试表, t1, t2, t3
-- create t1.
CREATETABLE[dbo].[T1](
[UID][uniqueidentifier]NOTNULL,
[Name][varchar](20) NULL,
[Memo][varchar](20) NULL,
CONSTRAINT[PK_T1]PRIMARYKEYCLUSTERED
(
[UID]ASC
)WITH (PAD_INDEX =OFF, STATISTICS_NORECOMPUTE =OFF, IGNORE_DUP_KEY =OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS =ON) ON[PRIMARY]
) ON[PRIMARY]
GO
-- create t2.
CREATETABLE[dbo].[T2](
[UID][uniqueidentifier]NOTNULL,
[Name][varchar](20) NULL,
[Memo][varchar](20) NULL,
CONSTRAINT[PK_T2]PRIMARYKEYCLUSTERED
(
[UID]ASC
)WITH (PAD_INDEX =OFF, STATISTICS_NORECOMPUTE =OFF, IGNORE_DUP_KEY =OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS =ON) ON[PRIMARY]
) ON[PRIMARY]
GO
-- create t3.
CREATETABLE[dbo].[T3](
[UID][uniqueidentifier]NOTNULL,
[Name][varchar](20) NULL,
[Memo][varchar](20) NULL,
CONSTRAINT[PK_T3]PRIMARYKEYCLUSTERED
(
[UID]ASC
)WITH (PAD_INDEX =OFF, STATISTICS_NORECOMPUTE =OFF, IGNORE_DUP_KEY =OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS =ON) ON[PRIMARY]
) ON[PRIMARY]
GO
二, 写入测试数据， t1 20w， t2, 2000, t3 30w.
-- insert t1.
declare@IDXint
set@IDX=0
while@IDX<200000
begin
insert T1 selectNEWID(),cast(@IDXasvarchar),'aaaa';
set@IDX=@IDX+1
end
-- insert t2.
declare@IDXint
set@IDX=0
while@IDX<2000
begin
insert T2 selectNEWID(),cast(@IDXasvarchar),'aaaa';
set@IDX=@IDX+1
end
-- insert t3.
declare@IDXint
set@IDX=0
while@IDX<300000
begin
insert T3 selectNEWID(),cast(@IDXasvarchar),'aaaa';
set@IDX=@IDX+1
end
在Name字段创建索引
/****** Object: Index [IX_T1_Name] Script Date: 08/23/2011 18:13:51 ******/
CREATENONCLUSTEREDINDEX[IX_T1_Name]ON[dbo].[T1]
(
[Name]ASC
)WITH (PAD_INDEX =OFF, STATISTICS_NORECOMPUTE =OFF, SORT_IN_TEMPDB =OFF, IGNORE_DUP_KEY =OFF, DROP_EXISTING =OFF, ONLINE =OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS =ON) ON[PRIMARY]
GO
/****** Object: Index [IX_T2_Name] Script Date: 08/23/2011 18:14:10 ******/
CREATENONCLUSTEREDINDEX[IX_T2_Name]ON[dbo].[T2]
(
[Name]ASC
)WITH (PAD_INDEX =OFF, STATISTICS_NORECOMPUTE =OFF, SORT_IN_TEMPDB =OFF, IGNORE_DUP_KEY =OFF, DROP_EXISTING =OFF, ONLINE =OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS =ON) ON[PRIMARY]
GO
/****** Object: Index [IX_T3_Name] Script Date: 08/23/2011 18:14:20 ******/
CREATENONCLUSTEREDINDEX[IX_T3_Name]ON[dbo].[T3]
(
[Name]ASC
)WITH (PAD_INDEX =OFF, STATISTICS_NORECOMPUTE =OFF, SORT_IN_TEMPDB =OFF, IGNORE_DUP_KEY =OFF, DROP_EXISTING =OFF, ONLINE =OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS =ON) ON[PRIMARY]
GO
三, 测试inner join三个表的各种排列的执行效率.
-- 20w inner join 2000
select*from T1
innerjoin T2
on T1.Name=T2.Name
-- 2000 inner join 20w
select*from T2
innerjoin T1
on T2.Name=T1.Name
-- 20w inner join 30w
select*from T1
innerjoin t3
on T1.Name=t3.Name
-- 30w inner join 20w
select*from t3
innerjoin T1
on t3.name=T1.Name
-- 2000 inner join 30w
select*from T2
innerjoin t3
on T2.Name=t3.Name
-- 30w inner join 2000
select*from t3
innerjoin T2
on t3.Name=T2.Name
测试结果:
对比下面的结果，我们会发现，如果在连接的关系字段建立索引，不加任何条件的情况下，查询的开销没有任何差别。
SQL Server 分析和编译时间:
CPU 时间 = 31 毫秒，占用时间 = 36 毫秒。
(2502 行受影响)
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T1'。扫描计数 1，逻辑读取 1452 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T2'。扫描计数 1，逻辑读取 15 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 141 毫秒，占用时间 = 135 毫秒。
(2502 行受影响)
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T1'。扫描计数 1，逻辑读取 1452 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T2'。扫描计数 1，逻辑读取 15 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 125 毫秒，占用时间 = 129 毫秒。
(220128 行受影响)
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T3'。扫描计数 1，逻辑读取 2168 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T1'。扫描计数 1，逻辑读取 1452 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 937 毫秒，占用时间 = 2793 毫秒。
(220128 行受影响)
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T3'。扫描计数 1，逻辑读取 2168 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T1'。扫描计数 1，逻辑读取 1452 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 844 毫秒，占用时间 = 2788 毫秒。
(3485 行受影响)
表 'T3'。扫描计数 2000，逻辑读取 17105 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T2'。扫描计数 1，逻辑读取 15 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 47 毫秒，占用时间 = 69 毫秒。
(3485 行受影响)
表 'T3'。扫描计数 2000，逻辑读取 17105 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T2'。扫描计数 1，逻辑读取 15 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 47 毫秒，占用时间 = 51 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
四, 加入where 条件进行inner join查询， 并查看不同排列的执行效率。
select*from T1
innerjoin T2
on T1.Name=T2.Name
where T1.Name='23'
select*from T2
innerjoin T1
on T1.Name=T2.Name
where T1.Name='23'
测试结果：
对比下面的结果，我们会发现，如果在连接的关系字段建立索引，在where条件1:1匹配的情况下，查询的开销没有任何差别。
SQL Server 分析和编译时间:
CPU 时间 =0 毫秒，占用时间 =6 毫秒。
(1 行受影响)
表 'T1'。扫描计数 1，逻辑读取 6 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T2'。扫描计数 1，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 =0 毫秒，占用时间 =0 毫秒。
(1 行受影响)
表 'T2'。扫描计数 1，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T1'。扫描计数 1，逻辑读取 6 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 =0 毫秒，占用时间 =0 毫秒。
SQL Server 分析和编译时间:
CPU 时间 =0 毫秒，占用时间 =0 毫秒。
SQL Server 执行时间:
CPU 时间 =0 毫秒，占用时间 =0 毫秒。
五, 制造不同数量的索引筛选数据进行测试，并查看测试结果。
对三张表分别生成不同数量的'rrrr'数据，并作为查询条件。
-- t2 1条 rrrr
update T2 set Name='rrrr'
where uid in (
selecttop1 uid from t2)
-- t1 10条 rrrr
update T1 set Name='rrrr'
where uid in (
selecttop10 uid from t1)
-- t3 2000条 rrrr
update t3 set name='rrrr'
where uid in
(
selecttop2000 uid from t3
)
selectCOUNT(1) from T1
selectCOUNT(1) from T2
selectCOUNT(1) from T3
selectCOUNT(1) from T1 where Name='rrrr'
selectCOUNT(1) from T2 where Name='rrrr'
selectCOUNT(1) from T3 where Name='rrrr'
将nam='rrrr'作为连接查询的条件，再次进行不同排列的测试。
select*from T1
innerjoin T2
on T1.Name=T2.Name
where T1.Name='rrrr'
select*from T2
innerjoin T1
on T1.Name=T2.Name
where T1.Name='rrrr'
select*from T3
innerjoin T2
on T3.Name=T2.Name
where T2.Name='rrrr'
select*from T2
innerjoin T3
on T3.Name=T2.Name
where T3.Name='rrrr'
select*from T1
innerjoin t3
on T3.Name=t1.name
where T3.Name='rrrr'
select*from T3
innerjoin t1
on T1.Name=t3.name
where T3.Name='rrrr'
我们再来看看测试结果，差异产生了!
SQL Server 分析和编译时间:
CPU 时间 =16 毫秒，占用时间 =23 毫秒。
(10 行受影响)
表 'T1'。扫描计数 1，逻辑读取 33 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T2'。扫描计数 1，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 =0 毫秒，占用时间 =0 毫秒。
(10 行受影响)
表 'T2'。扫描计数 10，逻辑读取 40 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T1'。扫描计数 1，逻辑读取 33 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 =0 毫秒，占用时间 =0 毫秒。
(2000 行受影响)
表 'T3'。扫描计数 1，逻辑读取 6012 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T2'。扫描计数 1，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 =16 毫秒，占用时间 =12 毫秒。
(2000 行受影响)
表 'T2'。扫描计数 2000，逻辑读取 8000 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T3'。扫描计数 1，逻辑读取 6012 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 =31 毫秒，占用时间 =36 毫秒。
(20000 行受影响)
表 'T1'。扫描计数 2000，逻辑读取 66000 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T3'。扫描计数 1，逻辑读取 6012 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 =94 毫秒，占用时间 =445 毫秒。
(20000 行受影响)
表 'T3'。扫描计数 10，逻辑读取 60120 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T1'。扫描计数 1，逻辑读取 33 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 =46 毫秒，占用时间 =264 毫秒。
SQL Server 分析和编译时间:
CPU 时间 =0 毫秒，占用时间 =0 毫秒。
SQL Server 执行时间:
CPU 时间 =0 毫秒，占用时间 =0 毫秒。
根据执行结果推断，sql server inner查询的效率差异的起因是索引的利用率，我们可以从测试结果，比较直观地得出结果，索引利用率越高的表，放在inner join前面，连接查询的开销越小。
再进行其他一些诸如like、> 、<作为条件的测试，发现如何排列inner join前后的顺序，查询的开销都是相同。
以上仅为测试结果和记录，关于sql server查询机制的细节还要继续恶补。
