查看: 68|回复: 0

SQL分页查询,纯Top方式和row_number()解析函数的使用及区别

发表于 2017-8-26 08:00:00

来自: http://www.cnblogs.com/ericli-ericli/p/5177076.html

听同事分享几种数据库的分页查询,自己感觉,还是需要整理一下MS SqlSever的分页查询的。

Sql Sever 2005之前版本:

  1. select top 页大小 *
  2. from 表名
  3. where id not in
  4. (
  5. select top 页大小*(查询第几页-1) id from 表名 order by id
  6. )
  7. order by id
复制代码

例如:

  1. select top 10 * --10 为页大小
  2. from [TCCLine].[dbo].[CLine_CommonImage]
  3. where id not in
  4. (
  5. --40是这么计算出来的:10*(5-1)
  6. -- 页大小*(查询第几页-1)
  7. select top 40 id from [TCCLine].[dbo].[CLine_CommonImage] order by id
  8. )
  9. order by id
复制代码

结果为:

Sql Sever 2005及以上版本,多了个分页查询方法:

  1. /*
  2. * firstIndex:起始索引
  3. * pageSize:每页显示的数量
  4. * orderColumn:排序的字段名
  5. * SQL:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句
  6. */
  7. select top pageSize o.* from (select row_number() over(order by orderColumn) as rownumber,* from(SQL) as o where rownumber>firstIndex;
复制代码

例如:

  1. select top 10 numComImg.* from
  2. ( select row_number() over(order by id asc) as rownumber,* from (select * FROM [TCCLine].[dbo].[CLine_CommonImage]) as comImg)
  3. as numComImg where rownumber>40
复制代码

结果:

这两个方法,就仅仅是多了一列 rewnumber 吗?当然不是,来看下内部差别吧:

在两个SQL上,分别加入以下SQL,并使用MS的“包括执行计划”,便于查看执行详情:

  1. SET STATISTICS TIME ON
  2. GO
复制代码

要执行的SQL:

  1. SET STATISTICS TIME ON
  2. GO
  3. select top 10 numComImg.* from
  4. ( select row_number() over(order by id asc) as rownumber,* from (select * FROM [TCCLine].[dbo].[CLine_CommonImage]) as comImg)
  5. as numComImg where rownumber>40
  6. SET STATISTICS TIME ON
  7. GO
  8. select top 10 * --10 为页大小
  9. from [TCCLine].[dbo].[CLine_CommonImage]
  10. where id not in
  11. (
  12. --40是这么计算出来的:10*(5-1)
  13. -- 页大小*(查询第几页-1)
  14. select top 40 id from [TCCLine].[dbo].[CLine_CommonImage] order by id
  15. )
  16. order by id
复制代码

执行之后,查看执行计划:

看得出,两个同样功能的SQL,执行时,使用 row_number() 的,要比是用 纯TOP方式的,查询开销少得多,上图显示 28:72,纯top方式,使用了两次聚集扫描。

再来看下执行时间信息:

row_number()方式的:

纯top方式:

相比之下,还是row_number()解析函数效率比较高写。

扩展阅读给 JavaScript 初心者的 ES2015 实战
SQL Server的分页优化及Row_Number()分页存在的问题
从 MapReduce 到 Hive —— 一次迁移过程小记
使用 Elasticsearch 实现博客站内搜索
使用 AngularJS 开发一个大规模的单页应用(SPA)
为您推荐跟据经纬度实现附近搜索Java实现
PostgreSQL 百万级每秒的流式实时统计应用
从 MapReduce 到 Hive —— 一次迁移过程小记
iOS 苹果官方Demo合集
Cassandra研究报告
更多SQL


回复

使用道具 举报