讨论：sql server 分页查询结果集和总记录如何查询有效？

分页查询中，会执行两次查询：
总记录查询一次，返回结果集总数。
分页查询又一次，返回某页的记录。

而大部分消耗性能和时间的，都是查询总记录数，因为计算总记录时，每个表都要访问一遍全部记录。
微软为什么在 row_number 排序时没有一个总记录的参数返回呢？有什么办法省去或优化就总记录的查询？

23 个解决方案

#1

如果查询条件固定, 可以在查询用到的字段上创建索引,提升查询速度.

#2

其实是有总记录的数的.

select *,row_number() rk from tb
set @totalrows=@@Rowcount--这个就是总记录数

#3

消耗性能和时间多, 也可能是锁争引起的进程阻塞,如果允许脏读,建议在表名后加with (nolock).

#4

统计一下sql速度
分为
1）    统计条数+分页数据
2）   只分页数据 
看看2个的差别有多大，
有可能数据库用了内存缓存，第一次总数扫描了数据，第二次就很快了

#5

-- 别怪  MS ，连总数一起查出来
create table test(id int identity, xtype varchar(10),oid int, oname varchar(20))
go
insert into test(xtype,oid,oname) select top 100 xtype,id,left(name,20) from sysobjects 
go
insert into test(xtype,oid,oname)  select xtype,oid,oname  from test 
go 5
select COUNT(*) as '这里是总数'from test where xtype ='U'
go
print '这里是结果集'
go
with m as (
select id, xtype, oid, oname , 
       count(*) over() rscount , 
       row_number() over(order by id) rn 
  from test 
where xtype = 'U'
)
select * from m where rn between 101 and 120 
go
drop table test 
go


这里是总数
-----------
352

(1 行受影响)

这里是结果集
id          xtype      oid         oname                rscount     rn
----------- ---------- ----------- -------------------- ----------- --------------------
885         U          98099390    peremployee          352         101
895         U          594101157   class                352         102
897         U          626101271   course               352         103
899         U          658101385   departments          352         104
901         U          690101499   message              352         105
903         U          711673583   test                 352         106
904         U          722101613   sel_cou              352         107
906         U          754101727   student              352         108
908         U          786101841   teach                352         109
910         U          818101955   teacher              352         110
977         U          62623266    sysdiagrams          352         111
978         U          98099390    peremployee          352         112
988         U          594101157   class                352         113
990         U          626101271   course               352         114
992         U          658101385   departments          352         115
994         U          690101499   message              352         116
996         U          711673583   test                 352         117
997         U          722101613   sel_cou              352         118
999         U          754101727   student              352         119
1001        U          786101841   teach                352         120

(20 行受影响)

#6

引用 3 楼 ap0405140 的回复:

消耗性能和时间多, 也可能是锁争引起的进程阻塞,如果允许脏读,建议在表名后加with (nolock).

条件很难固定，类似分页存储过程一样，字段/条件/排序/多表查询 都不确定，常常很难用确定的索引。
如果分页，可以过滤出一页的数据量，主要是求总记录，得另外查询。

#7

引用 2 楼 kingtiy 的回复:

其实是有总记录的数的.

select *,row_number() rk from tb
set @totalrows=@@Rowcount--这个就是总记录数

关键不会只是这样的求出结果。

#8

引用 4 楼 human_2000 的回复:

统计一下sql速度
分为
1）    统计条数+分页数据
2）   只分页数据 
看看2个的差别有多大，
有可能数据库用了内存缓存，第一次总数扫描了数据，第二次就很快了

反过来先分页，总记录还是慢的，多表复杂关联的分页。

#9

不过就是求个count数，只要索引做到位，我不信会慢多少

#10

引用 7 楼 kk185800961 的回复:

Quote: 引用 2 楼 kingtiy 的回复:

其实是有总记录的数的.

select *,row_number() rk from tb
set @totalrows=@@Rowcount--这个就是总记录数

关键不会只是这样的求出结果。

[code=sql]select *,row_number() rk from tb a join xxx on xxx where xxx 
--这里可是一个复杂的查询.
--最后@@rowcount获取总数

#11

引用 10 楼 kingtiy 的回复:

Quote: 引用 7 楼 kk185800961 的回复:

Quote: 引用 2 楼 kingtiy 的回复:

其实是有总记录的数的.

select *,row_number() rk from tb
set @totalrows=@@Rowcount--这个就是总记录数

关键不会只是这样的求出结果。

[code=sql]select *,row_number() rk from tb a join xxx on xxx where xxx 
--这里可是一个复杂的查询.
--最后@@rowcount获取总数

select *,row_number() rk from XX

分页查询不是返回全部结果，要么分页，外层还有个条件如： where rk between 21 and 40 。
如果只是求count，也没必要这么查了

#12

引用 5 楼 wmxcn2000 的回复:

-- 别怪  MS ，连总数一起查出来
create table test(id int identity, xtype varchar(10),oid int, oname varchar(20))
go
insert into test(xtype,oid,oname) select top 100 xtype,id,left(name,20) from sysobjects 
go
insert into test(xtype,oid,oname)  select xtype,oid,oname  from test 
go 5
select COUNT(*) as '这里是总数'from test where xtype ='U'
go
print '这里是结果集'
go
with m as (
select id, xtype, oid, oname , 
       count(*) over() rscount , 
       row_number() over(order by id) rn 
  from test 
where xtype = 'U'
)
select * from m where rn between 101 and 120 
go
drop table test 
go


这里是总数
-----------
352

(1 行受影响)

这里是结果集
id          xtype      oid         oname                rscount     rn
----------- ---------- ----------- -------------------- ----------- --------------------
885         U          98099390    peremployee          352         101
895         U          594101157   class                352         102
897         U          626101271   course               352         103
899         U          658101385   departments          352         104
901         U          690101499   message              352         105
903         U          711673583   test                 352         106
904         U          722101613   sel_cou              352         107
906         U          754101727   student              352         108
908         U          786101841   teach                352         109
910         U          818101955   teacher              352         110
977         U          62623266    sysdiagrams          352         111
978         U          98099390    peremployee          352         112
988         U          594101157   class                352         113
990         U          626101271   course               352         114
992         U          658101385   departments          352         115
994         U          690101499   message              352         116
996         U          711673583   test                 352         117
997         U          722101613   sel_cou              352         118
999         U          754101727   student              352         119
1001        U          786101841   teach                352         120

(20 行受影响)

我这有个分页查询，30多个表关联，众多查询条件。分页记录集现查询优化到1秒钟，总记录数计算却要12秒。
分页这个查询加上 count(*) over()  ，几分钟没出来……

#13

引用 12 楼 kk185800961 的回复:

我这有个分页查询，30多个表关联，众多查询条件。分页记录集现查询优化到1秒钟，总记录数计算却要12秒。
分页这个查询加上 count(*) over()  ，几分钟没出来……

这做法本身就很奇葩，分页记录数本身就是你查询结果的行数，你却要在每一行里去count一下，这种行数一帮应用程序都能获得，就算你要在sqlserver里获取，也应该是分完页后的数据再去count下，我不信count20条结果会出不来

#14

统计总记录数的时候要关联30个表吗？只需要关联对记录行数有影响的表就可以了啊，显示的字段用到的表不需要在这里连接。消耗资源，这样也是可以提高一部分性能的

#15

引用 13 楼 xdashewan 的回复:

Quote: 引用 12 楼 kk185800961 的回复:

我这有个分页查询，30多个表关联，众多查询条件。分页记录集现查询优化到1秒钟，总记录数计算却要12秒。
分页这个查询加上 count(*) over()  ，几分钟没出来……

这做法本身就很奇葩，分页记录数本身就是你查询结果的行数，你却要在每一行里去count一下，这种行数一帮应用程序都能获得，就算你要在sqlserver里获取，也应该是分完页后的数据再去count下，我不信count20条结果会出不来

这个是回答楼上的问题，count(*) over() 也是来测试看看。

#16

引用 14 楼 KanzakiOrange 的回复:

统计总记录数的时候要关联30个表吗？只需要关联对记录行数有影响的表就可以了啊，显示的字段用到的表不需要在这里连接。消耗资源，这样也是可以提高一部分性能的

都是这样想的。
但有些查询的查询条件都是最后才拼接上去过滤的，中间关联的字段和条件字段只取需要的字段，业务没想象的这么简单的。。

求总记录count(*) ，是按页扫描的，锁粒度大了必然对其他有影响。对于使用一些标志位的字段（如 是否删除，是否更新之类的标识，只有几个值）的条件，加索引不合适，这些表没那么多过滤的会并行或大量记录返回做物理连接。

#17

with list as(
				SELECT ROW_NUMBER() OVER(order by order by ) AS num,*
		FROM 表
		)
		select (select COUNT(1) from list) as Count,* from list
		where num between 1 and  50 

这是我现在用的 每行第一列count就是总条数

#18

引用 17 楼 kfrhappy 的回复:

with list as(
				SELECT ROW_NUMBER() OVER(order by order by ) AS num,*
		FROM 表
		)
		select (select COUNT(1) from list) as Count,* from list
		where num between 1 and  50 

这是我现在用的 每行第一列count就是总条数

其实也是跟我这一样的。
求总记录一个语句：

select COUNT(1) from list

分页一个语句：

with list as(
        SELECT ROW_NUMBER() OVER(order by order by ) AS num,*
        FROM 表
)
select * from list
where num between 1 and  50 

还是执行了两次。
如果  list 表是很多表和条件关联的，查询不同还是有的慢。

#19

可以在分页的查询里面用 count(1) over() 上面有楼层说了，这样子就是一次查询出来。

#20

引用 19 楼 jinfengyiye 的回复:

可以在分页的查询里面用 count(1) over() 上面有楼层说了，这样子就是一次查询出来。

这个会更加的慢

#21

引用 20 楼 kk185800961 的回复:

Quote: 引用 19 楼 jinfengyiye 的回复:

可以在分页的查询里面用 count(1) over() 上面有楼层说了，这样子就是一次查询出来。

这个会更加的慢

这个你试过吗？我只是之前听到sql高手所说的性能改善，但是我没有试过。但是这样确实少了一次查询没有得到改善吗？

#22

引用 21 楼 jinfengyiye 的回复:

Quote: 引用 20 楼 kk185800961 的回复:

Quote: 引用 19 楼 jinfengyiye 的回复:

可以在分页的查询里面用 count(1) over() 上面有楼层说了，这样子就是一次查询出来。

这个会更加的慢

这个你试过吗？我只是之前听到sql高手所说的性能改善，但是我没有试过。但是这样确实少了一次查询没有得到改善吗？

我刚试了，确实不推荐，这样反页更慢。

#23

好问题啊，mysql中就可以做到，写存储过程方便了去了
可惜SQLServer没有