in替代 sql优化not_SQLServer 优化SQL语句：in 和not in的替代方案

用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。

但是用IN的SQL性能总是比较低的，从SQL执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：

SQL试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。 推荐在业务密集的SQL当中尽量不采用IN操作符

NOT IN 此操作是强列推荐不使用的，因为它不能应用表的索引。推荐用NOT EXISTS 或(外连接+判断为空)方案代替

在数据库中有两个表，一个是当前表Info(id,PName,remark,impdate,upstate)，一个是备份数据表bakInfo(id,PName,remark,impdate,upstate)，将当前表数据备份到备份表去，就涉及到not in 和in 操作了：

首先，添加10万条测试数据

代码

1 createprocedureAddData2 as3 declare@idint4 set@id=05 while(@id<100000)6 begin7 insertintodbo.Info(id,PName,remark,impdate,upstate)8 values(@id,convert(varchar,@id)+'0','abc',getdate(),0)9 set@id=@id+110 end11 12 execAddData

使用not in 和in操作：

1

SET STATISTICS TIME ON

2

GO

3

--备份数据

4

insert into bakInfo(id,PName,remark,impdate,upstate)

5

select id,PName,remark,impdate,upstatefrom dbo.Info

6

where idnot in(select idfrom dbo.bakInfo)

7

GO

8

SET STATISTICS TIME OFF

此操作执行时间：

1

SQL Server 分析和编译时间:

2

CPU 时间 = 0 毫秒，占用时间 = 3 毫秒。

3

SQL Server 执行时间:

4

CPU 时间 = 453 毫秒，占用时间 = 43045 毫秒。

5

(100000 行受影响)

6

SQL Server 分析和编译时间:

7

CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

8

--更改当前表状态

9

update  Infoset upstate=1where idin(select idfrom dbo.bakInfo)

此操作执行时间：

1

SQL Server 分析和编译时间:

2

CPU 时间 = 62 毫秒，占用时间 = 79 毫秒。

3

SQL Server 执行时间:

4

CPU 时间 = 188 毫秒，占用时间 = 318 毫秒。

5

(100000 行受影响)

6

SQL Server 分析和编译时间:

7

CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

8

--删除当前表数据

9

delete from Infowhere upstate=1and idin(select idfrom dbo.bakInfo)

此操作执行时间：

1

SQL Server 分析和编译时间:

2

CPU 时间 = 183 毫秒，占用时间 = 183 毫秒。

3

SQL Server 执行时间:

4

CPU 时间 = 187 毫秒，占用时间 = 1506 毫秒。

5

(100000 行受影响)

6

SQL Server 分析和编译时间:

7

CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

使用join连接替代方案：

01

SET STATISTICS TIME ON

02

GO

03

--备份数据

04

insert into bakInfo(id,PName,remark,impdate,upstate)

05

select id,PName,remark,impdate,upstatefrom

06

(SELECT     Info.id,Info.PName, Info.remark, Info.impdate,Info.upstate, bakInfo.idAS bakID

07

FROM         Infoleft JOIN

08

bakInfoON Info.id = bakInfo.id )as t

09

where t.bakIDis null and t.upstate=0

10

GO

11

SET STATISTICS TIME OFF;

此操作执行时间：

1

SQL Server 分析和编译时间:

2

CPU 时间 = 247 毫秒，占用时间 = 247 毫秒。

3

SQL Server 执行时间:

4

CPU 时间 = 406 毫秒，占用时间 = 475 毫秒。

5

(100000 行受影响)

6

SQL Server 分析和编译时间:

7

CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

1

--更改当前表状态

2

update Infoset upstate=1

3

FROM         InfoINNER JOIN

4

bakInfoON Info.id = bakInfo.id

此操作执行时间：

1

SQL Server 分析和编译时间:

2

CPU 时间 = 4 毫秒，占用时间 = 4 毫秒。

3

SQL Server 执行时间:

4

CPU 时间 = 219 毫秒，占用时间 = 259 毫秒。

5

(100000 行受影响)

6

SQL Server 分析和编译时间:

7

CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

1

--删除当前表数据

2

delete from Info

3

FROM         InfoINNER JOIN

4

bakInfoON Info.id = bakInfo.id

5

where  Info.upstate=1

此操作执行时间：

1

SQL Server 分析和编译时间:

2

CPU 时间 = 177 毫秒，占用时间 = 177 毫秒。

3

SQL Server 执行时间:

4

CPU 时间 = 219 毫秒，占用时间 = 550 毫秒。

5

(100000 行受影响)

6

SQL Server 分析和编译时间:

7

CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

可以看出使用join方案比使用not in 和in执行时间要短很多了