3种通用分页SQL的比较

多次top倒腾法、【改进版】多次top倒腾法、row_number()法

【改进版】多次top倒腾：倒腾时仅仅使用关键字字段，不再是*，这样io会少很多
实测结果也的确是快了很多！ 

（如果row_number()法也这样改进，不知道能快多少）

 

declare @top int,@skip int,@dt datetime

set @top=20

set @skip=5000000

 

set nocount on

 

print 'top='+cast(@top as varchar(20))

print 'skip='+cast(@skip as varchar(20))

 

 

set @dt=getdate()

--多次top倒腾法

select

* from (

    select top(@top)

    *

    from (

       select top(@skip+@top)

       *

       from tbtestitem

       order by fid

    ) a

    order by fid desc

) a

order by fid

 

print datediff(ms,@dt,getdate())

 

 

 

set @dt=getdate()

--【改进版】多次top倒腾法

select b.* from (

 

    select top(@top)

    fid

    from (

       select top(@skip+@top)

       fid

       from tbtestitem

       order by fid

    ) a

    order by fid desc

) a

left join tbtestitem b on a.fid=b.fid

order by a.fid

 

print datediff(ms,@dt,getdate())

 

 

 

set @dt=getdate()

--row_number()法

select * from (

    select

    ROW_NUMBER() Over(order by fid) fo,

    *

    from tbtestitem

) a

where fo>@skip and fo<=@skip+@top

order by fid

 

print datediff(ms,@dt,getdate())

 

 

 

set @dt=getdate()

--【改进版】row_number()法

select b.* from (

    select * from (

       select

        ROW_NUMBER() Over(order by fid) fo,fid

       from tbtestitem

    ) a

    where fo>@skip and fo<=@skip+@top

) a

left join tbtestitem b on a.fid=b.fid

order by b.fid

 

print datediff(ms,@dt,getdate())

 

 

 

看执行计划，多次top倒腾法、row_number()法 是一样的开销，

【改进版】多次top倒腾法、row_number()法 比，则前者开销为0！

但是实际情况，跳过的记录数越多，top倒腾的就慢得越厉害
再祥林嫂一句：如果t-sql支持select skip 10000 top 20 * from ...，就好了

结果：先多次top倒腾，中【改进版】多次top倒腾，后row_number，单位:ms

 

top=20
skip=1000
0
0
16

 

top=20
skip=10000
46
0
33

 

top=20
skip=100000
673
126
200

 

top=20
skip=1000000

10046

1720

1906

 

top=20
skip=2000000
21176
4063
3673

 

top=20
skip=5000000
65010
10660
8423

 

top=20

skip=10000000
159156
23833
17536

 

4个sql的结果：

top=20
skip=5000000
64616
11066
6603
2740

top=20
skip=1000000
9266
1730
1406
546