SQL SERVER 2012 的分页方法创新版

最新推荐文章于 2021-06-30 21:40:02 发布

fengyun14

最新推荐文章于 2021-06-30 21:40:02 发布

阅读量2.3k

点赞数

分类专栏： SQL SERVER

本文链接：https://blog.youkuaiyun.com/fengyun14/article/details/25337103

版权

SQL SERVER 专栏收录该内容

14 篇文章

订阅专栏

关于SQL SERVER的分页方法，早在2004年，我就写了分页算法，现在这篇：http://blog.youkuaiyun.com/fengyun14/article/details/143223 早已被网上传得漫天飞了。

SQL SERVER 2012新增了 Offset and Fetch 方法，写法如下：

select * from t order by 字段 offset 100 rows fetch next 20 rows only

语句要求必须写order by走索引，可是只要写order by 系统就会浪费CPU；然而我不需要再写明排序，因为我希望走索引的默认排序，那是不需明写排序的，那怎么办呢？只要写

select * from t order by (select 0) offset 100 rows fetch next 20 rows only

这样就可以不走排序了！

上面的这条语句是正常的写法，但执行效率会很低，改进的语句与此类似，但效率提高许多。

select * from t where id in (select id from t  order by (select 0) offset 100 rows fetch next 20 rows only)

当然oracle也可以写 select 0 from dual ；但实际却没效果

上面这条语句等同于利用ROW_NUMBER的另外一种写法：

select * from  (select id from  (select top 120 id, ROW_NUMBER() over(order by (select 0)) AS ROWNUM FROM t) t1 where t1.ROWNUM>100 )

看了执行效率，用后面的这条row_number比fetch 还略好，为何呢？

单看纯粹的分页方法，他们的效率是一模一样的：

select id from t  order by (select 0) offset 100 rows fetch next 20 rows only

select id from (select top 120 id, ROW_NUMBER() over(order by (select 0)) AS ROWNUM FROM t) t1 where tt.ROWNUM>100)

但是加上相同的外套后，却变得截然不同，使用row_number的效率变得异常低，2条语句，用offset的开销占8%，用offset的开销占92%。看他们的I/O COST也一样，区别主要在于：Estimated Operator Cost，它的意思是：“用于执行此操作的查询优化器的开销。此操作的开销以占查询总开销的百分比的形式显示在括号中。由于查询引擎选择最高效的操作来执行查询或执行语句，因此此值应尽可能低。 ”所以，SQL SERVER认为这样的语句，它需要很长的时间来解释，无他。所以，我们在这种情况下，还是尽量用offset来分页吧。（解释见：http://www.51testing.com/html/03/n-817003-3.html）