SQL Server 数据库的分页查询

SQL Server 数据库的分页查询

SQL Server中进行分页查询并不像MySQL那样方便,前几天刚好有空整理了一下常见的分页写法,做一个简单的归纳。

1. 利用Top分页

TOP函数用来限制返回行数,如果使用两层TOP嵌套便可以实现分页。按照这个思路可以写出如下的查询语句:

但是很明显,上面的写法是不对的,且不论性能如何,该方法永远返回一页内容,当翻页到最后一页甚至再试图往后翻页时,都是如此,Bug!所以,借助TOP函数来分页的常见做法是结合 NOT IN 来写的,如下:

该写法比较简单,通用性强,但也有个很明显的缺点,那就是当页数越靠后时,NOT IN中的数据会变得非常多从而影响性能。当然,当表中数据量不大时还是可以满足的。

结合 NOT IN 的写法之所以会产生性能上的缺陷是因为NOT IN 中的数据量可能会变得很大,那么,针对一些特殊情况是可以做个简单优化的,比如,当排序条件是像自增主键这样比较简单的情况时,我们可以做出如下改进:

可以看出,这种改进是把 NOT IN 改为可比较值处理,所以性能上会有一定提升,但是适用面较窄,可用于排序条件单一且可比较的情况。

2. 利用ROW_NUMBER 分页

分页其实本质上是返回满足条件的结果集中连续的若干行,如果利用ROW_NUMBER函数将结果集编号,在指定返回行号在一个区间内的子集即可完成任务,于是,借助带有ROW_NUMBER的子查询来分页是一个很自热的思路:

上面的查询有个问题,那就是子查询会把所有满足条件的结果都查出并编号返回,对资源会造成很大的浪费,所以,我们可以借助TOP函数让子查询每次仅返回必要的结果来做个改进:

上面经过改进的语句基本可以满足绝大部分的查询了,但是,尽可能少的返回字段是我们写出高效SQL的一个原则,如果子查询返回太多字段的话,对性能的损耗也是非常大的。下面是一个仅让子查询返回必要字段后和原表链接查询的优化例子:

使用ROW_NUMBER函数来分页要比借助两层TOP的方式可靠高效,但这种写法的可读性较差,另外对于SQL Server 2005以前的版本也不适用。

3. 使用OFFSET FETCH子句分页

在SQL Server 2012 及以后的版本中终于出现了类似MySQL中LIMIT的写法了,那就是 OFFSET-FETCH 。

OFFSET子句用来声明跳过结果集中的若干行,FETCH子句用于声明从剩余的结果集中获取多少行。用法如下:

看以看到,这种方式非常简单,可读性好,而且经测试性能也可靠。但是对于OFFSET的使用还需要注意一些限制,下面四条摘录自MSDN:

Limitations in Using OFFSET-FETCH

  • ORDER BY is mandatory to use OFFSET and FETCH clause.
  • OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
  • TOP cannot be combined with OFFSET and FETCH in the same query expression.
  • The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.

小结

如今,大部分的SQL Server应该都是在使用较新的版本了,分页查询作为SQL Server数据库用户的一个痛点的历史也应该成为过去了,愉快的使用OFFSET FETCH来分页吧。

发表评论

电子邮件地址不会被公开。