Sql Server 2012 分页方法分析(offset and fetch)

其中 offset and fetch 最重要的新特性是 用来 分页，既然要分析 分页，就肯定要和之前的分页方式来比较了，特别是 row_number() 了，在比较过程中，发现了蛮多，不过最重要的，通过比较本质，得出了优劣，也和大家一起分享下。

准备工作，建立测试表：article_detail，主要是用来存放一些文章信息，测试的时间，都是从网易上面转载的新闻，同时，测试表数据字段类型是比较均匀的，为了更好的测试，表结构如下图：

 

内容：

数据量：129,991 条记录

语法分析

1. ntile() 的分页方法

ntile() 方法可以用来分页，但是应用场景十分的狭窄，并且性能差劲，和 row_number() 与 offset fetch 分页比起来没有任何优势，也只有在只读表上面分页的话，还是比较合适的；虽然不好用，但是还能来分页的，所以只简单的介绍下。

语法：

ntile (integer_expression) over ( [ <partition_by_clause> ] < order_by_clause > )
将有序分区中的行分发到指定数目的组中。 各个组有编号，编号从一开始。 对于每一个行，ntile 将返回此行所属的组的编号。

测试中用到的 sql 语句 :

set statistics time on
set statistics io on
set statistics profile on;
with #pager as
(
select id,title,ntile(8666) over(order by id) as pageid from article_detail
)
select id,title from #pager where pageid=50
set statistics profile on;

其中上述数字中的 8666 是根据 rowcount / pagesize 计算出来的，不过多介绍，可以自行参考 msdn的

2. row_number() 的分页方法

在 sql server 2000 之后的版本中，row_number() 这种分页方式一直都是很不错的，比起之前的游标分页，性能好了很多，因为 row_number() 并不会引起全表扫表，但是，语法比较复杂，并且，随着页码的增加，性能也越来越差。
语法 ：
row_number ( ) over ( [ partition by value_expression , ... [ n ] ] order_by_clause )
测试中用到的 sql 语句：

dbcc freeproccache
dbcc dropcleanbuffers
set statistics time on
set statistics io on
set statistics profile on;
with #pager as
(
select id,title,row_number() over(order by id) as rowid from article_detail
)
select id,title from #pager where rowid between (15 * (50-1)+1) and 15 * 50
set statistics profile off;

3. offset and fetch 的分页方法

语法：
offset { integer_constant | offset_row_count_expression } { row | rows }
fetch { first | next } { integer_constant | fetch_row_count_expression } { row | rows } only
从语法可以看出来 两个方法 后面不但能接 intege 类型的参数，还能接 表达式的，比如 1*2 +3 之类的，同时， row 或者 rows 是不区分大小写和单复数的哦
在看测试用的 sql 语句，真的是简洁的不能再简洁了，看两遍都能记住的语法，分页可以如此的简洁：

dbcc freeproccache
dbcc dropcleanbuffers
set statistics time on
set statistics io on
set statistics profile on;
select id,title from article_detail order by id offset (15 * (50-1)) row fetch next 15 rows only
set statistics profile off;

一句就搞定！
性能比较
1. ntile() 的执行计划

从执行计划中，就可以看出来，进行了一次全表扫表，两次 nested loops ，还有无数其他运算，就一次全表扫表，就知道性能之差了


2. row_number() 的执行计划

从执行计划中可以看出来， 聚集索引扫描占用了100% 的资源，但是通过 estimaterows = 100 和 rows = 750 可以看出来，并没有进行全表扫描，并且io 操作很小，所以性能还是很不错的

3. offset and fetch 的 执行计划

执行计划只有3行，并且占用资源 100% 的io 操作 ，estimaterows = 100 和 rows = 750 是和 row_number() 完全一样的，但是其他的一些操作却少了很多，也就是说，并没有全表扫描，并降低了cpu 的消耗。

综合比较：

在 sql server 2012 里面，分页方法中，offset and fetch 同 row_number() 比较起来，无论是性能还是语法，都是有优势的。

但是性能方面，优势并不是太大，两者 的 io 消耗完全相同，只是 在 cpu 方面，offset and fetch 方面要好一些，但是不明显。如果对于一个 每秒都要处理成千上万条的分页sql语句的db 来说，offset and fetch 在cpu 方面的优势会比较明显的，否则，性能的提升并不明显。

语法方面 offset and fetch 则是十分的简洁，一句搞定，比起 row_number() 好了太多 ~
同是 offset and fetch 并不仅仅可以用来分页哦，具体其他使用，大家可以自行参考 msdn