快捷導(dǎo)航

SQLServer 優(yōu)化SQL語(yǔ)句 in 和not in的替代方案

更新時(shí)間：2010年04月30日 16:52:04 作者：

用IN寫(xiě)出來(lái)的SQL的優(yōu)點(diǎn)是比較容易寫(xiě)及清晰易懂，這比較適合現(xiàn)代軟件開(kāi)發(fā)的風(fēng)格。

但是用IN的SQL性能總是比較低的，從SQL執(zhí)行的步驟來(lái)分析用IN的SQL與不用IN的SQL有以下區(qū)別：
SQL試圖將其轉(zhuǎn)換成多個(gè)表的連接，如果轉(zhuǎn)換不成功則先執(zhí)行IN里面的子查詢(xún)，再查詢(xún)外層的表記錄，如果轉(zhuǎn)換成功則直接采用多個(gè)表的連接方式查詢(xún)。由此可見(jiàn)用IN的SQL至少多了一個(gè)轉(zhuǎn)換的過(guò)程。一般的SQL都可以轉(zhuǎn)換成功，但對(duì)于含有分組統(tǒng)計(jì)等方面的SQL就不能轉(zhuǎn)換了。推薦在業(yè)務(wù)密集的SQL當(dāng)中盡量不采用IN操作符
NOT IN 此操作是強(qiáng)列推薦不使用的，因?yàn)樗荒軕?yīng)用表的索引。推薦用NOT EXISTS 或（外連接+判斷為空）方案代替
　　在數(shù)據(jù)庫(kù)中有兩個(gè)表，一個(gè)是當(dāng)前表Info(id,PName,remark,impdate,upstate)，一個(gè)是備份數(shù)據(jù)表bakInfo(id,PName,remark,impdate,upstate)，將當(dāng)前表數(shù)據(jù)備份到備份表去，就涉及到not in 和in 操作了：
　　首先，添加10萬(wàn)條測(cè)試數(shù)據(jù)

復(fù)制代碼代碼如下:

 
create procedure AddData 
as 
declare @id int 
set @id=0 
while(@id<100000) 
begin 
insert into dbo.Info(id,PName,remark,impdate,upstate) 
values(@id,convert(varchar,@id)+'0','abc',getdate(),0) 
set @id=@id+1 
end 
exec AddData 

使用not in 和in操作：

復(fù)制代碼代碼如下:

 
SET STATISTICS TIME ON 
GO 
--備份數(shù)據(jù) 
insert into bakInfo(id,PName,remark,impdate,upstate) 
select id,PName,remark,impdate,upstate from dbo.Info 
where id not in(select id from dbo.bakInfo) 
GO 
SET STATISTICS TIME OFF 

此操作執(zhí)行時(shí)間：

復(fù)制代碼代碼如下:

 
SQL Server 分析和編譯時(shí)間: 
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 3 毫秒。 
SQL Server 執(zhí)行時(shí)間: 
CPU 時(shí)間 = 453 毫秒，占用時(shí)間 = 43045 毫秒。 
(100000 行受影響) 
SQL Server 分析和編譯時(shí)間: 
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 1 毫秒。 
--更改當(dāng)前表狀態(tài) 
update Info set upstate=1 where id in(select id from dbo.bakInfo) 

　　此操作執(zhí)行時(shí)間：

復(fù)制代碼代碼如下:

 
SQL Server 分析和編譯時(shí)間: 
CPU 時(shí)間 = 62 毫秒，占用時(shí)間 = 79 毫秒。 
SQL Server 執(zhí)行時(shí)間: 
CPU 時(shí)間 = 188 毫秒，占用時(shí)間 = 318 毫秒。 
(100000 行受影響) 
SQL Server 分析和編譯時(shí)間: 
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 1 毫秒。 
--刪除當(dāng)前表數(shù)據(jù) 
delete from Info where upstate=1 and id in(select id from dbo.bakInfo) 

　　此操作執(zhí)行時(shí)間：

復(fù)制代碼代碼如下:

 
SQL Server 分析和編譯時(shí)間: 
CPU 時(shí)間 = 183 毫秒，占用時(shí)間 = 183 毫秒。 
SQL Server 執(zhí)行時(shí)間: 
CPU 時(shí)間 = 187 毫秒，占用時(shí)間 = 1506 毫秒。 
(100000 行受影響) 
SQL Server 分析和編譯時(shí)間: 
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 1 毫秒。 

　　使用join連接替代方案：

復(fù)制代碼代碼如下:

 
SET STATISTICS TIME ON 
GO 
--備份數(shù)據(jù) 
insert into bakInfo(id,PName,remark,impdate,upstate) 
select id,PName,remark,impdate,upstate from 
(SELECT Info.id,Info.PName, Info.remark, Info.impdate,Info.upstate, bakInfo.id AS bakID 
FROM Info left JOIN 
bakInfo ON Info.id = bakInfo.id ) as t 
where t.bakID is null and t.upstate=0 
GO 
SET STATISTICS TIME OFF; 

　　此操作執(zhí)行時(shí)間：

復(fù)制代碼代碼如下:

 
SQL Server 分析和編譯時(shí)間: 
CPU 時(shí)間 = 247 毫秒，占用時(shí)間 = 247 毫秒。 
SQL Server 執(zhí)行時(shí)間: 
CPU 時(shí)間 = 406 毫秒，占用時(shí)間 = 475 毫秒。 
(100000 行受影響) 
SQL Server 分析和編譯時(shí)間: 
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 1 毫秒。 
--更改當(dāng)前表狀態(tài) 
update Info set upstate=1 
FROM Info INNER JOIN 
bakInfo ON Info.id = bakInfo.id 

　　此操作執(zhí)行時(shí)間：

復(fù)制代碼代碼如下:

 
SQL Server 分析和編譯時(shí)間: 
CPU 時(shí)間 = 4 毫秒，占用時(shí)間 = 4 毫秒。 
SQL Server 執(zhí)行時(shí)間: 
CPU 時(shí)間 = 219 毫秒，占用時(shí)間 = 259 毫秒。 
(100000 行受影響) 
SQL Server 分析和編譯時(shí)間: 
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 1 毫秒。 

--刪除當(dāng)前表數(shù)據(jù)

復(fù)制代碼代碼如下:

 
delete from Info 
FROM Info INNER JOIN 
bakInfo ON Info.id = bakInfo.id 
where Info.upstate=1 

　　此操作執(zhí)行時(shí)間：

復(fù)制代碼代碼如下:

 
SQL Server 分析和編譯時(shí)間: 
CPU 時(shí)間 = 177 毫秒，占用時(shí)間 = 177 毫秒。 
SQL Server 執(zhí)行時(shí)間: 
CPU 時(shí)間 = 219 毫秒，占用時(shí)間 = 550 毫秒。 
(100000 行受影響) 
SQL Server 分析和編譯時(shí)間: 
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 1 毫秒。 

　　可以看出使用join方案比使用not in 和in執(zhí)行時(shí)間要短很多了

您可能感興趣的文章: