MSSqlServer 2012 offset and fetch实现分页方法分析()

MSSqlServer 2012 offset and fetch实现分页方法分析() 有需要的朋友可参考一下

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

 

 

 

内容：

 

 

 

数据量：129,991 条记录

 

 

语法分析

 

1. NTILE() 的分页方法

 

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

 

语法：

NTILE (integer_expression) <span style="color: #0000ff">OVER</span> ( <span style="color: #ff0000">[</span><span style="color: #ff0000"> <partition_by_clause> </partition_by_clause></span><span style="color: #ff0000">]</span> <span style="color: #808080"> order_by_clause <span style="color: #808080">&gt;</span> )</span>

 

将有序分区中的行分发到指定数目的组中。 各个组有编号，编号从一开始。 对于每一个行，NTILE 将返回此行所属的组的编号。

 

测试中用到的 Sql 语句 : 

试中用到的 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;
按 Ctrl+C 复制代码
其中上述数字中的 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() 好了太多 ~