来自 数据库 2020-04-16 04:09 的文章
当前位置: 网上澳门金莎娱乐 > 数据库 > 正文

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

 

最近在分析 Sql Server 2012 中 offset and fetch 的新特性,发现 offset and fetch 无论语法的简洁还是功能的强大,都是相当相当不错的。其中  offset and fetch 最重要的新特性是 用来 分页,既然要分析 分页,就肯定要和之前的分页方式来比较了,特别是  Row_Number() 了,在比较过程中,发现了蛮多,不过最重要的,通过比较本质,得出了优劣,也和大家一起分享下。

 

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

 

 网上澳门金莎娱乐 1

 

内容:

 

网上澳门金莎娱乐 2

 

 

数据量: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的

 

  1. 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

本文由网上澳门金莎娱乐发布于数据库,转载请注明出处:Sql Server 2012 分页方法分析(offset and fetch)网上澳门金莎娱乐

关键词: