浅谈五种SqlServer存储过程分页方式

基础数据

--定义表结构
CREATE TABLE [dbo].[Users](
	[ID] [INT] IDENTITY(1,1) NOT NULL,
	[NAME] [NVARCHAR](50) NOT NULL,
 CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_TEST_NAME]  DEFAULT ('') FOR [NAME]
GO

--插入100w条基础数据
DECLARE @i INT = 1
WHILE(@i <= 1000000)
BEGIN
	INSERT INTO dbo.Users
	(
	    NAME
	)
	VALUES
	(	
		N'Random' + CAST(@i AS NVARCHAR(20)) -- NAME - nvarchar(20)
	 )

	SET @i += 1;
END
GO

方式

方式一：利用 Select Top 和 Select Not In

CREATE PROCEDURE PROC_TopWithNotIn
	@pageSize INT,	--页记录数量
	@pageIndex INT	--当前页
AS
SET NOCOUNT ON;
BEGIN

	DECLARE @timediff DATETIME = GETDATE(),
			@sql NVARCHAR(2000)

	SET @sql = N'Select Top '+ STR(@pageSize) +' * 
				From Users 
				Where(
					ID Not In(
						Select Top '+ STR(@pageSize*(@pageIndex-1)) +' ID 
						From Users 
						Order By ID)) 
						Order By ID';
						
	EXEC(@sql);

	SELECT DATEDIFF(MICROSECOND,@timediff,GETDATE()) AS	耗时;

END

方式二：利用 Select Top 和 Max(列键) 函数

CREATE PROCEDURE PROC_TopWithMax
	@pageSize INT,	--页记录数量
	@pageIndex INT	--当前页
AS
SET NOCOUNT ON;
BEGIN

	DECLARE @timediff DATETIME = GETDATE(),
			@sql NVARCHAR(2000)

	SET @sql = N'Select Top ' + STR(@pageSize) + ' * 
				From Users
				Where ID > (
					Select IsNull(Max(ID),0) 
					From (
						Select Top ' + STR(@pageSize*(@pageIndex - 1)) + ' ID 
						From Users
						Order By ID
					)As t
				)Order By ID';

	EXEC(@sql);

	SELECT DATEDIFF(MICROSECOND,@timediff,GETDATE()) AS	耗时;

END

方式三：利用 Select Top 和 中间变量

CREATE PROCEDURE PROC_TopWithMidvar
	@pageSize INT,	--页记录数量
	@pageIndex INT	--当前页
AS
SET NOCOUNT ON;
BEGIN

	DECLARE @timediff DATETIME = GETDATE(),
			@midvar INT = 0,
			@id INT = 0,
			@sql NVARCHAR(2000)

	SELECT @midvar = @midvar + 1,
		   @id = CASE WHEN @midvar <= @pageSize * (@pageIndex - 1) THEN ID 
				 ELSE @id END FROM Users ORDER BY ID  

	SET @sql = N'Select Top '+STR(@pageSize)+' * 
				From Users Where ID > '+STR(@id)
	EXEC(@sql);

	SELECT DATEDIFF(MICROSECOND,@timediff,GETDATE()) AS	耗时;

END

方式四：利用 Row_Number()

--适用于SqlServer2005及以上版本
CREATE PROCEDURE PROC_RowNumber
	@pageSize INT,	--页记录数量
	@pageIndex INT	--当前页
AS
SET NOCOUNT ON;
BEGIN

	DECLARE @timediff DATETIME = GETDATE()

	DECLARE @startNum INT,
			@endNum INT;
	SET @startNum = @pageSize * (@pageIndex - 1) + 1;
	SET @endNum = @pageSize * @pageIndex;

	SELECT * FROM
	(
		SELECT ROW_NUMBER() OVER(ORDER BY ID) AS num,*
		FROM Users
	)AS t
	WHERE t.num BETWEEN LTRIM(STR(@startNum)) AND LTRIM(STR(@endNum));

	SELECT DATEDIFF(MICROSECOND,@timediff,GETDATE()) AS	耗时;

END

方式五：利用 CTE 和 Row_Number()

CREATE PROCEDURE PROC_CTEWithRowNumber
	@pageSize INT,	--页记录数量
	@pageIndex INT	--当前页
AS
SET NOCOUNT ON;
BEGIN

	DECLARE @timediff DATETIME = GETDATE(),
			@sql NVARCHAR(2000)

	SET @sql = N'With cteTable
				As (
					Select 
					Ceiling((Row_Number() Over(Order By ID) - 1)/'+STR(@pageSize)+') as num,* 
					From Users
				) Select * From cteTable Where num = ' + STR(@pageIndex - 1);

	EXEC(@sql);

	SELECT DATEDIFF(MICROSECOND,@timediff,GETDATE()) AS	耗时;

END

执行测试

针对数据进行分段测试，测试结果如下：

1、pageSize = 20 pageIndex = 1

2、pageSize = 20 pageIndex = 500

3、pageSize = 20 pageIndex = 5000

4、pageSize = 20 pageIndex = 50000

统计结果

测试/方式	方式一	方式二	方式三	方式四	方式五	排名最优
1	3000	0	216000	0	220000	方式二/方式四
2	50000	78000	180000	50000	216000	方式一/方式四
3	63000	53000	180000	80000	173000	方式二
4	320000	283000	260000	676000	273000	方式三

总结：
1、五种分页方式当数据位置越往后偏移，执行耗时越长，方式一、二、四表现明显。
2、当数据量小的时候（百万级以下）推荐使用方式一、二、四
3、当数据量大的时候（百万级以上）推荐使用方式三

参考资料

五种SQL Server分页存储过程的方法及性能比较
SqlServer存储过程分页模板

Digression

本文博主只是针对百万级数据的查询效率做了测试，如果有朋友有更好的分页方式或者测试结果欢迎私信呦~ 一起学习✊