Sql Server 2012 分页方法分析(offset and fetch)
性感女神jennifer水中诱惑,爱奇鱼,永泰千江月休闲农场
其中 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某个日期是当年的第几周
/* *周一作为一周的开始 *当年的1月1号所在的周算作第一周 */ CREATE function GetWeekIndexFirstDate ( @...
[阅读全文]
-
-
数据库SQL---范式
1、数据冗余导致的问题:冗余存储、更新异常、插入异常、删除异常。 2、函数依赖:一种完整性约束。 在关系模式r(R)中,α属于R,β属于R。 1)α函数...
[阅读全文]
-
-
数据库SQL---查询
1、查询所有列 select *from emp;--*表示所有的,from emp表示从emp表中查询。 2、查询指定列 select empno,e...
[阅读全文]
-
-
-
-
网友评论