机器情况
p4:2.4
内存:1G
os:windows
数据库:mssqlserver2000
目的:查询性能测试,比较两种查询的性能
SQL查询效率stepbystep
--setp1.
--建表
createtablet_userinfo
(
useridintidentity(1,1)primarykeynonclustered,
nickvarchar(50)notnulldefault'',
classidintnotnulldefault0,
writetimedatetimenotnulldefaultgetdate()
)
go
--建索引
createclusteredindexix_userinfo_classidont_userinfo(classid)
go
--step2.
declare@iint
declare@kint
declare@nickvarchar(10)
set@i=1
while@i<1000000
begin
set@k=@i%10
set@nick=convert(varchar,@i)
insertintot_userinfo(nick,classid,writetime)values(@nick,@k,getdate())
set@i=@i+1
end
--耗时08:27，需要耐心等待
--step3.
selecttop20userid,nick,classid,writetimefromt_userinfo
whereuseridnotin
(
selecttop900000useridfromt_userinfoorderbyuseridasc
)
--耗时8秒,够长的
--step4.
selecta.userid,b.nick,b.classid,b.writetimefrom
(
selecttop20a.useridfrom
(
selecttop900020useridfromt_userinfoorderbyuseridasc
)aorderbya.useriddesc
)ainnerjoint_userinfobona.userid=b.userid
orderbya.useridasc
--耗时1秒，太快了吧，不可以思议
--step5where查询
selecttop20userid,nick,classid,writetimefromt_userinfo
whereclassid=1anduseridnotin
(
selecttop90000useridfromt_userinfo
whereclassid=1
orderbyuseridasc
)
--耗时2秒
--step6where查询
selecta.userid,b.nick,b.classid,b.writetimefrom
(
selecttop20a.useridfrom
(
selecttop900000useridfromt_userinfo
whereclassid=1
orderbyuseridasc
)aorderbya.useriddesc
)ainnerjoint_userinfobona.userid=b.userid
orderbya.useridasc
--查询分析器显示不到1秒.
查询效率分析：
子查询为确保消除重复值，必须为外部查询的每个结果都处理嵌套查询。在这种情况下可以考虑用联接查询来取代。
如果要用子查询，那就用EXISTS替代IN、用NOTEXISTS替代NOTIN。因为EXISTS引入的子查询只是测试是否存在符合子查询中指定条件的行，效率较高。无论在哪种情况下,NOTIN都是最低效的。因为它对子查询中的表执行了一个全表遍历。
建立合理的索引,避免扫描多余数据，避免表扫描！
几百万条数据，照样几十毫秒完成查询
