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

日期： 2010-03-01 作者：luoht 来源：TechTarget中国 英文

　　用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万条测试数据

　　使用not in 和in操作：

        SET STATISTICS TIME ON
　　GO
　　–备份数据
　　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

　　此操作执行时间：

　　SQL Server 分析和编译时间:

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

　　

　　SQL Server 执行时间:

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

　　(100000 行受影响)

　　SQL Server 分析和编译时间:

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

　　

　　

　　–更改当前表状态

　　update Info set upstate=1 where id in(select id from dbo.bakInfo)

　　此操作执行时间：

　　SQL Server 分析和编译时间:

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

　　SQL Server 执行时间:

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

　　(100000 行受影响)

　　SQL Server 分析和编译时间:

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

　　

　　

　　

　　–删除当前表数据

　　delete from Info where upstate=1 and id in(select id from dbo.bakInfo)

　　此操作执行时间：

　　SQL Server 分析和编译时间:

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

　　

　　SQL Server 执行时间:

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

　　(100000 行受影响)

　　SQL Server 分析和编译时间:

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

　　使用join连接替代方案：

　　SET STATISTICS TIME ON
　　GO
　　
　　–备份数据
　　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;

　　此操作执行时间：

　　SQL Server 分析和编译时间:

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

　　SQL Server 执行时间:

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

　　(100000 行受影响)

　　SQL Server 分析和编译时间:

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

　　–更改当前表状态

　　update Info set upstate=1
　　FROM Info INNER JOIN
　　 bakInfo ON Info.id = bakInfo.id

　　此操作执行时间：

　　SQL Server 分析和编译时间:

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

　　SQL Server 执行时间:

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

　　(100000 行受影响)

　　SQL Server 分析和编译时间:

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

　　–删除当前表数据

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

　　此操作执行时间：

　　SQL Server 分析和编译时间:

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

　　SQL Server 执行时间:

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

　　(100000 行受影响)

　　SQL Server 分析和编译时间:

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

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

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论，本站保留删除与本文无关和不雅评论的权力。

作者

luoht
luoht

相关推荐