背景

  以前都是使用mysql和oracle,对sqlserver的使用不多。最近因项目原因,要读取其他项目的数据库,取出某个门的开关历史记录,而对方使用的是sqlserver,所以研究起了sqlserver的分页,经过几次实践,慢慢的从低效的分页写到了高效的分页。

 

表结构

history表

历史记录ID:id(唯一引索)

操作时间:time

开门或关门:flag

由谁操作:user_Id

属于哪个设备:device_id

 

下面我们先介绍四种分页的方法,以【一种低效】——【两种较高效】——【一种高效】的顺序进阶的介绍,在最后再附上测试结果。

低效的sql

 思路:

 

最里层:先从history表根据时间倒序查出前50010条记录

中间层:从以上的查询结果中根据时间正序查出前10条记录,一正一反刚好就拿出了第10000条到10010条记录了。

最外层:根据时间倒序拿出以上的查询结果

SQL代码:

select * from 
(
    select top 10 * from 
    (
        select top 50010 * from history 
        order by 
        time desc  
    ) h 
    order by  
    h.time asc
) hh  
order by  
hh.time desc

经下面的检验,这种查询效率比较低下。

其原因是因为每一层的查询都使用了select * ,即扫描所有的这段,但是 “最里层” 和 “中间层” 根本就没必要select * ,这两层目的只是为了把最后一层的搜索范围定位在第10000-10010条之间,所以,在这两层里,我们只要拿出关键字ID和排序字段time就好。

较高效的SQL(1)————使用where ... =

 

最里层:先从history表根据时间倒序查出前50010条记录,只拿出id和time

中间层:从以上的查询结果中根据时间正序查出前10条记录,只拿出id和time,一正一反刚好就拿出了第10000条到10010条记录了。

最外层:根据时间倒序拿出以上的查询结果,select *拿出所有字段,查询范围用where ... = ... 来匹配。

SQL代码:

select * from history hh,
( 
    select top 10 id ,time from  
    ( 
        select top 50010 id ,time from history  
        order by 
        time desc  
    ) h  
    order by  
    h.time asc 
) hhh  
where hhh.id = hh.id 
order by  
hhh.time desc

经检验,这种分页 方法比上一种快一点点。

主要原因在与“最里层”和“中间层”的两次查询,都只是查出id和time,而不是select * ,从这个角度讲提升了效率。但最后又用了where...=语法,比起上一种分页方法,又降低了一点效率。但是where...=语法速度很快,所以总体上还是这种分页方法更快一些。

较高效的分页(2)————使用where...in

 

最里层:先从history表根据时间倒序查出前50010条记录,只拿出id和time

中间层:从以上的查询结果中根据时间正序查出前10条记录,只拿出id和time一正一反刚好就拿出了第10000条到10010条记录了。

最外层:根据时间倒序拿出以上的查询结果,select *拿出所有字段,查询范围用where...in ()来匹配

SQL代码:

select * from history hh 
where id in 
( 
    select top 10 id from  
    ( 
        select top 50010 id ,time from history  
        order by  
        time desc 
    ) h  
    order by 
    h.time asc 
)
order by  
hh.time des

这种分页方法,与上一种分页方法比起来,区别是这种使用了where...in(),而不是where...=,原理上差别不大,但可能是SQLServer内部优化的原因,使用where...in比使用where...=要快一些。具体在下面的计较表格可以看出。

高效的分页————使用row_number() over

 

最里层:查询出前50010条数据,只拿出ID字段,同时使用row_number() over 语法,增加一个n字段,代表该条数据时第几行

最外层:根据where...=来匹配id,同时直接拿出 n>50000 的数据。

SQL代码:

select hhh.n , hh.* from history hh ,
( 
    select top 50010 row_number() over  
    ( 
        order by  
        time desc 
    ) n,id 
    from history  
) hhh 
where hhh.id = hh.id 
and hhh.n > 50000 
order by  
hhh.n desc

这种分页方法,首先只是两次查询,这无非提高了效率。最里层查询出来的“虚列”——n,sqlserver不知道会不会为其加上索引,个人认为会,但想不出什么验证的方法。假如有加入索引的话,那在这个地方,使用 “n >某个数字” 的查询方法,又比前几次查询快了一点。

几种分页方法速度比较

 以下进行两种测试,第一种是查询出1000-1010条数据,第二种是查询出第50000-50010条数据。记录的秒数是查询50次总共的用时,每组测试5次,最后取平均值。

 

【查询1000-1010条数据】第一次第二次第三次第四次第五次平均
低效的分页6.344s5.687s5.797s5.704s5.641s5.835s
较高效的分页(1)——where...=4.485s5.281s5.094s5.281s5.313s5.091s(胜出)
较高效的分页(1)——where...in5.093s5.328s5.14s5.406s5.297s5.253s
高效的分页——row_number() over5.437s5.39s5.156s5.016s5.344s5.269

从中可以看出,在查询的行数较少时,使用“较高的分页(1)——where...=”是最快的一种分页方法。

 

【查询50000-50010条数据】第一次第二次第三次第四次第五次平均
低效的分页10.844s9.985s10.172s10.0s10.297s10.260s
较高效的分页(1)——where...=9.625s9.469s9.14s9.171s9.219s9.325s
较高效的分页(1)——where...in9.156s9.61s9.187s9.218s9.219s9.278s
高效的分页——row_number() over7.844s6.765s6.422s7.359s6.875s  7.053s(胜出)

从中可用看出,在查询的行数较多时,使用"高效的分页——row_number() over"是最快的一种分页方法,而且快了好几个档次