SQL查询效率-100w数据查询只要1秒

机器情况
p4: 2.4
内存: 1 G
os: windows 2003
数据库: ms sql server 2000
目的: 查询性能测试,比较两种查询的性能

SQL查询效率 step by step

--  setp 1.
--  建表
create   table  t_userinfo
(
userid  int   identity ( 1 , 1 )  primary   key   nonclustered ,
nick  varchar ( 50 )  not   null   default   '' ,
classid  int   not   null   default   0 ,
writetime  datetime   not   null   default   getdate ()
)
go

--  建索引
create   clustered   index  ix_userinfo_classid  on  t_userinfo(classid)
go

--  step 2.

declare   @i   int  
declare   @k   int
declare   @nick   varchar ( 10 )
set   @i   =   1
while   @i < 1000000
begin
set   @k   =   @i   %   10
set   @nick   =   convert ( varchar , @i )
insert   into  t_userinfo(nick,classid,writetime)  values ( @nick , @k , getdate ())
set   @i   =   @i   +   1
end
--  耗时 08:27 ，需要耐心等待

--  step 3.
select   top   20  userid,nick,classid,writetime  from  t_userinfo 
where  userid  not   in
(
select   top   900000  userid  from  t_userinfo  order   by  userid  asc
)

--  耗时 8 秒 ,够长的

--  step 4.
select  a.userid,b.nick,b.classid,b.writetime  from
(
select   top   20  a.userid  from  
(
select   top   900020  userid  from  t_userinfo  order   by  userid  asc
) a  order   by  a.userid  desc
) a  inner   join  t_userinfo b  on  a.userid  =  b.userid 
order   by  a.userid  asc

--  耗时 1 秒，太快了吧，不可以思议

--  step 5 where 查询
select   top   20  userid,nick,classid,writetime  from  t_userinfo 
where  classid  =   1   and  userid  not   in
(
select   top   90000  userid  from  t_userinfo 
where  classid  =   1
order   by  userid  asc
)
--  耗时 2 秒

--  step 6 where 查询
select  a.userid,b.nick,b.classid,b.writetime  from
(
select   top   20  a.userid  from  
(
select   top   90000  userid  from  t_userinfo
where  classid  =   1
order   by  userid  asc
) a  order   by  a.userid  desc
) a  inner   join  t_userinfo b  on  a.userid  =  b.userid 
order   by  a.userid  asc

--  查询分析器显示不到 1 秒.

 

查询效率分析：
子查询为确保消除重复值，必须为外部查询的每个结果都处理嵌套查询。在这种情况下可以考虑用联接查询来取代。
如果要用子查询，那就用EXISTS替代IN、用NOT EXISTS替代NOT IN。因为EXISTS引入的子查询只是测试是否存在符合子查询中指定条件的行，效率较高。无论在哪种情况下,NOT IN都是最低效的。因为它对子查询中的表执行了一个全表遍历。

建立合理的索引,避免扫描多余数据，避免表扫描！
几百万条数据，照样几十毫秒完成查询.