SQL调优学习之——sqlserver分页从低效到高效
背景
首先感谢网友@aixuexi 在评论中的提醒，原博文介绍的几种都不是最高效，现已修改加入另一种更高效的方法。
以前都是使用mysql和oracle，对sqlserver的使用不多。最近因项目原因，要读取其他项目的数据库，取出某个门的开关历史记录，而对方使用的是sqlserver，所以研究起了sqlserver的分页，经过几次实践，慢慢的从低效的分页写到了高效的分页。
表结构
history表
历史记录ID：id（唯一引索）
操作时间：time
开门或关门：flag
由谁操作：user_Id
属于哪个设备：device_id
下面我们先介绍四种分页的方法，以【一种低效】——【两种较高效】——【一种高效】的顺序进阶的介绍，在最后再附上测试结果。
修改：经过网友@aixuexi 的提醒，加入最后一种【更高效】的方法介绍
低效的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 >某个数字” 的查询方法，又比前几次查询快了一点。
更高效的分页——row_number() over + 只查询一次
最里层：查询出前50010条数据，使用row_number() over 语法，增加一个n字段，代表该条数据时第几行，同时查出我们想要的信息，这里跟其他方法一样查询出所有：*。
最外层：根据where...来匹配n，同时直接拿出 n>50000 且 n<50010 行的数据。
SQL代码：
select * from ( select *,row_number() over ( order by time desc ) n from history ) hhh where hhh.n > 50000 and hhh.n <= 50010
几种分页方法速度比较
以下进行两种测试，第一种是查询出1000-1010条数据，第二种是查询出第50000-50010条数据。记录的秒数是查询50次总共的用时，每组测试5次，最后取平均值。
| 【查询1000-1010条数据】 | 第一次 | 第二次 | 第三次 | 第四次 | 第五次 | 平均 |
| 低效的分页 | 6.344s | 5.687s | 5.797s | 5.704s | 5.641s | 5.835s |
| 较高效的分页（1）——where...= | 4.485s | 5.281s | 5.094s | 5.281s | 5.313s | 5.091s(胜出) |
| 较高效的分页（1）——where...in | 5.093s | 5.328s | 5.14s | 5.406s | 5.297s | 5.253s |
| 高效的分页——row_number() over | 5.437s | 5.39s | 5.156s | 5.016s | 5.344s | 5.269 |
| 更高效的分页——row_number() over + 只查询一次 | 5.188s | 4.875s | 5.172s | 4.953s | 4.875s | 5.0126s（胜出） |
从中可以看出，在查询的行数较少时，使用 【更高效的分页——row_number() over + 只查询一次】是最快的一种分页方法。
| 【查询50000-50010条数据】 | 第一次 | 第二次 | 第三次 | 第四次 | 第五次 | 平均 |
| 低效的分页 | 10.844s | 9.985s | 10.172s | 10.0s | 10.297s | 10.260s |
| 较高效的分页（1）——where...= | 9.625s | 9.469s | 9.14s | 9.171s | 9.219s | 9.325s |
| 较高效的分页（1）——where...in | 9.156s | 9.61s | 9.187s | 9.218s | 9.219s | 9.278s |
| 高效的分页——row_number() over | 7.844s | 6.765s | 6.422s | 7.359s | 6.875s | 7.05s（胜出） |
| 更高效的分页——row_number() over + 只查询一次 | 6.730s | 6.109s | 7.109s | 5.328s | 5.515s | 6.086s（胜出） |
从中可用看出，在查询的行数较多时，使用【更高效的分页——row_number() over + 只查询一次】是最快的一种分页方法，而且快了好几个档次！
