使用row_number()进行分页的疑问

Question

随着页码数字的增加，会导致速度越来越慢，我发现“where row_numbers>(@pageSize*(@pageindex-1))”这段如果数字比较大的话，效率明显下降很多，是不是因为这个字段没有索引所导致在筛选的时候会全表检索而使得速度变得越来越慢。

CREATE procedure pro_A
(

@pageIndex int
@pageSize tinyint
)
as

select top(@pageSize) *
from
(
select *,row_number() over(order by pm_plancode asc) as row_numbers
from v_planmembers
) as t
where row_numbers>(@pageSize*(@pageindex-1))

Answer 1

个人对这个函数用于分页是比较讨厌的，因为一般很少人会去考虑怎么用才是合理的（我是看到了一堆人用这个写了个通用的分页，还沾沾自喜的到处传播）

从执行计划上年，row_number是在查询结果集上去算这个值的，所以你的页越靠后，需要读取的数据就越多，自然也就越慢

正确的用法是只对主键分页，对分页得到的主键再去关联明细数据，也就是应该用类似下面的这种处理

with
id as(
	select 
		row_id = row_number() over(order by pm_plancode asc)
		id	-- 主键列名
	from v_planmembers
)
select 
	data.*
from v_planmembers data
	inner join id
		on id.id = data.id
where id.row_id >(@pageSize*(@pageindex-1))
	and id.row_id < (@pageSize*(@pageindex))
;

Answer 2

用主键分页固然是性能提高，但是考虑一下某些场景，如果是需要根据某字段排序之后在进行分页，那么使用主键分页就有问题了。因为这时候主键的顺序已经打乱，已经不能根据主键的数字大小来判断顺序了。

Answer 3

select 
		row_id = row_number() over(order by pm_plancode asc)
		id	-- 主键列名
	from v_planmembers
这段是不是也要对整表进行扫描的，会不会这块会影响性能

Answer 4

我试了你的方法，速度是快了近1倍。但是还是要近30秒时间，而且也会随着pageindex的数字上升导致速度严重变慢。虽然我发现真正影响速度的是v_planmembers这个view里join了2个表，不管是删除B还是C，都会使速度变得可用。假设B是一个view，这个view里使用了union all链接了两个表，我觉得是这个view影响了性能。

select 
from A a
join B b on a.xx=b.xx
join  C c on c.xxx=a.xxx

Answer 5

可以用indexed view来提高性能。不过会影响insert/delete/update的性能，增加空间占用。要仔细权衡利弊。

---

想不想时已是想，不如不想都不想。

Answer 6

@zjcxc.邹建

"只对主键分页，对分页得到的主键再去关联明细数据" 思路感觉蛮好的，不过你的写法并没有体现你的说法，还是需要对整个表进行扫描排列的。

Answer 7

@怡红公子

除了indexed view，直接用为pm_plancode 建INDEX应该也可以的吧，因为就一个表嘛 ，其他的需要的列作为包含列。

Answer 8

你这个表的数据量大概是多少？

数据量不是很大的话，直接给pm_plancode加上索引即可。

Answer 9

@怡红公子

除了indexed view，直接用为pm_plancode 建INDEX应该也可以的吧，因为就一个表嘛 ，其他的需要的列作为包含列。

你哪里看见是一个表了？楼主不是说了吗，v_planmembers这个view里join了2个表

---

想不想时已是想，不如不想都不想。

Answer 10

不好意思，看错了,这么说来，只能用indexed view才能取得比较好的性能了。

Answer 11

可以对pm_plancode加上聚集索引，这样分页的时候应该不会慢

---

给我写信： QQ我：

Answer 12

用到的字段都已经加了索引，结果的都一样。关键还是在view里面自身的问题。但是在之前要生成row_number，总是要扫描整表，这个可真是个问题。

Answer 13

用以下语句分析一下，或者看一下执行计划

set statistics profile on 
set statistics io on

或者看一下这篇帖子

http://topic.csdn.net/u/20100712/09/196befc8-ef1c-4493-9ba6-0a30d0c63340.html

分析了row_number() over的作分页的性能

---

给我写信： QQ我：

Answer 14

一般我都是用主表、临时表做分页的因为支持sql2000，比较通用，性能还可以

USE [GPOSDB]
GO
/****** 对象:  StoredProcedure [dbo].[CT_Append_Get]    脚本日期: 06/29/2012 15:26:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-----------------------------------------------------------------
-- Name: CT_Append Store Procedure
-- Author: -- DateTime: 2011-07-22
-- Description: Data TableCT_Append , Get List
-----------------------------------------------------------------
/*
@PageIndex 
@TotalRecords 
*/	
ALTER PROCEDURE [dbo].[CT_Append_Get]
(
	@PageIndex int,
	@TotalRecords int
)
AS
BEGIN
    DECLARE @Page int
	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	DECLARE @RowsToReturn int

	SET @Page = (@PageIndex - 1)

	-- First set the rowcount
	SET @RowsToReturn = @TotalRecords * (@Page + 1)
	SET ROWCOUNT @RowsToReturn

	-- Set the page bounds
	SET @PageLowerBound = @TotalRecords * @Page
	SET @PageUpperBound = @PageLowerBound + @TotalRecords + 1

	-- Create a temp table to store the select results
	CREATE TABLE #PageIndex 
	(
		IndexId int IDENTITY (1, 1) NOT NULL,
		VC_A_SNNO INT 
	)
	
	INSERT INTO #PageIndex (VC_A_SNNO)
	SELECT 
		[VC_A_SNNO]
	FROM 
		[CT_Append]
		
    
	SELECT
		c.VC_A_SNNO,
		[VC_A_AppendType] ,
		[VC_A_CardNO] ,
		[I_A_CardType] ,
		[I_A_PointToOil] ,
		[VC_TicketType] ,
		[VC_TicketNO] ,
		[DE_A_BAmount] ,
		[DE_A_AppendAmount] ,
		[DE_A_AAmount] ,
		[D_A_AppendDateTime] ,
		[VC_A_Remark] ,
		[VC_A_OperatorNO] 
	FROM [CT_Append] c , #PageIndex PageIndex
	WHERE
		c.VC_A_SNNO = PageIndex.VC_A_SNNO AND
		PageIndex.IndexID > @PageLowerBound AND
		PageIndex.IndexID < @PageUpperBound
	
	SELECT COUNT(VC_A_SNNO) AS TotalRecords FROM [CT_Append]
	
END

---

给我写信： QQ我：

Answer 15

SQL Server 2012 已经原生支持 Paging.  http://msdn.microsoft.com/en-us/library/ms188385.aspx

SELECT *
FROM v_planmembers
ORDER BY pm_plancode ASC
OFFSET @pageSize*(@pageIndex-1) ROWS FETCH NEXT @pageSize ROWS ONLY

Answer 16

2012还没有正式版吧

Answer 17

还是RTM版，没有正式版

---

给我写信： QQ我：

Answer 18

RTM is formal release.

Answer 19

RTM(Release to Manufacturing)版

软件在正式在零售商店上架前，需要一段时间来压片，包装、配销，所以程序代码必须在正式发行前一段时间就要完成，这个完成的程序代码叫做final.code，程序代码开发完成之后，要将母片送到工厂大量压片，这个版本就叫做rtm版。rtm版的程序码和正式版一样。但是和正式版也有不一样的地方：例如正式版中的oem不能升级安装，升级版要全新安装的话会检查旧版操作系统光盘等，这些就是Rtm和正式版不同的地方，但是它们的主要程序代码都是一样的。严格的说这种版本还是属于fpp零售版，需要激活。不过一般都习惯把正式版就称为RTM版。

---

给我写信： QQ我：

Answer 20

SQL Server 2012 已经原生支持 Paging.  http://msdn.microsoft.com/en-us/library/ms188385.aspx

SELECT *
FROM v_planmembers
ORDER BY pm_plancode ASC
OFFSET @pageSize*(@pageIndex-1) ROWS FETCH NEXT @pageSize ROWS ONLY

测试了，这个分页和 row_number() 分页差不多，没有针对分页做专门的性能优化的，比较郁闷