高效分页排序方法_分页数据排序方法-优快云博客

本文链接：https://blog.youkuaiyun.com/xuezhongsong/article/details/4972065

本文详细探讨了在面对十万乃至百万级别数据量时，如何通过优化分页排序策略来提高数据库查询效率。介绍了三种不同的方法，并通过实际测试验证了它们在不同数据量下的性能表现。重点分析了每种方法的特点、适用场景以及存在的局限性，旨在为开发者提供实用的分页排序解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

分页排序，当数据达到十万、百万级别的时候，使用何种方式分页排序效率高呢！最近在这个问题上不断的纠缠，网上也有很多方法，但各有各的不足之处。现在列出以下方法，仅供参考：

以下方法测试环境：数据量总共166983条记录，每页30条，总共有5567页，读取的字段数目为28列

第1种方法：

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go --分页存储过程-- --2009-12-8----- --韩保新-------- --此存储过程只针对2005版本--------------------- ALTER Procedure [dbo].[H_PageList01] @TableName varchar(50), --表名 @Fields varchar(5000) = '*', --字段名(全部字段为*) @OrderField varchar(5000), --排序字段(支持多字段)但必须这样写 oder1name desc,order2name asc,order23name @sqlWhere varchar(5000) = Null,--条件语句(不用加where) @pageSize int, --每页多少条记录 @pageIndex int = 1 , --指定当前为第几页 @orderby varchar(10), ---排序方式 desc asc @keyName varchar(20), ---关键字段必须 @TotalPage int output, --返回总页数 @TotalCount int output --返回总记录数 as Declare @sql nvarchar(4000); ---制定排序规则 if (@OrderField='' or @OrderField is null) begin if (@orderby ='desc') set @OrderField= @keyName + ' desc ' else set @OrderField= @keyName + ' asc ' end else begin if (@orderby ='desc') set @OrderField = @OrderField+ ' desc, ' + @keyName + ' desc' else set @OrderField = @OrderField+ ' asc ,' + @keyName + ' asc' end --计算总记录数 if (@sqlWhere!='' and @sqlWhere is not null) set @sql = 'select @TotalCount = count(*) from ' + @TableName + ' where ' + @sqlWhere else set @sql = 'select @TotalCount = count(*) from ' + @TableName EXEC sp_executesql @sql,N'@TotalCount int OUTPUT',@TotalCount OUTPUT--计算总记录数 --计算总页数 set @TotalPage=CEILING((@TotalCount+0.0)/@pageSize) if (@sqlWhere!='' and @sqlWhere is not null) set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @sqlWhere else set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName --处理页数超出范围情况 if @pageIndex<=0 Set @pageIndex = 1 if @pageIndex>@TotalPage and @pageIndex>0 Set @pageIndex = @TotalPage --处理开始点和结束点 Declare @StartRecord int Declare @EndRecord int set @StartRecord = (@pageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @pageSize - 1 --继续合成sql语句 set @sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord) Exec(@sql)

此方法有两个缺点：

第一：只能用在sqlserver2005中，2000不能使用

第二：当页数达到五千页后，最后页数的时间在1-2秒之间，效率不高

第2种方法：

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROC [dbo].[H_PageList02] @TableName sysname, --要分页显示的表名 @keyName sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段 @pageIndex int=1, --要显示的页码 @pageSize int=10, --每页的大小(记录数) @Fields varchar(5000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段 @OrderField varchar(5000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC --用于指定排序顺序 @orderby varchar(10), ---排序方式 desc asc @sqlWhere varchar(5000)='', --查询条件 @TotalCount int OUTPUT, --总记录数 @TotalPage int OUTPUT --总页数 AS DECLARE @sql nvarchar(4000) SET NOCOUNT ON --检查对象是否有效 IF OBJECT_ID(@TableName) IS NULL BEGIN RAISERROR(N'对象"%s"不存在',1,16,@TableName) RETURN END IF OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsTable')=0 AND OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsView')=0 AND OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsTableFunction')=0 BEGIN RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@TableName) RETURN END --分页字段检查 IF ISNULL(@keyName,N'')='' BEGIN RAISERROR(N'分页处理需要主键（或者惟一键）',1,16) RETURN END --其他参数检查及规范 IF ISNULL(@pageIndex,0)<1 SET @pageIndex=1 IF ISNULL(@pageSize,0)<1 SET @pageSize=10 IF ISNULL(@Fields,N'')=N'' SET @Fields=N'*' IF ISNULL(@OrderField,N'')=N'' SET @OrderField=N'' ELSE SET @OrderField=N'ORDER BY '+LTRIM(@OrderField) IF ISNULL(@sqlWhere,N'')=N'' SET @sqlWhere=N'' ELSE SET @sqlWhere=N'WHERE ('+@sqlWhere+N')' --如果@TotalPage为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@TotalPage赋值) IF @TotalPage IS NULL BEGIN SET @sql=N'SELECT @TotalPage=COUNT(*)' +N' FROM '+@TableName +N' '+@sqlWhere EXEC sp_executesql @sql,N'@TotalPage int OUTPUT',@TotalPage OUTPUT SET @TotalCount = @TotalPage SET @TotalPage=(@TotalPage+@pageSize-1)/@pageSize END --计算分页显示的TOPN值 DECLARE @TopN varchar(20),@TopN1 varchar(20) SELECT @TopN=@pageSize, @TopN1=@pageIndex*@pageSize --第一页直接显示 IF @pageIndex=1 EXEC(N'SELECT TOP '+@TopN +N' '+@Fields +N' FROM '+@TableName +N' '+@sqlWhere +N' '+@OrderField) ELSE BEGIN SELECT @pageIndex=@TopN1, @sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN +N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@keyName +N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@TableName +N' '+@sqlWhere +N' '+@OrderField SET ROWCOUNT @pageIndex EXEC sp_executesql @sql, N'@n int,@s nvarchar(4000) OUTPUT', @pageIndex,@sql OUTPUT SET ROWCOUNT 0 IF @sql=N'' EXEC(N'SELECT TOP 0' +N' '+@Fields +N' FROM '+@TableName) ELSE BEGIN SET @sql=STUFF(@sql,1,1,N'') --执行查询 EXEC(N'SELECT TOP '+@TopN +N' '+@Fields +N' FROM '+@TableName +N' WHERE '+@keyName +N' IN('+@sql +N') '+@OrderField) END END

此种方法每页用的时间在0.3-0.5秒之间

第3种方法：

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[H_PageList03] @TableName varchar(50), -- 表名 @Fields varchar(5000), -- 选择的字段列表以,分隔 @OrderField varchar(5000), -- 排序字段以,分隔（不能含keyName指定的字段，可为空） @sqlWhere varchar(1000) = '', -- 查询条件(注意: 不要加where) @pageSize int = 10, -- 页尺寸 @pageIndex int = 1, -- 页码 @orderby varchar(5000), -- 排序字段及排序方向， @keyName varchar(20), -- 主键字段 @TotalPage int output, --返回总页数 @TotalCount int output --返回总记录数 AS declare @sqlWhereA varchar(1200) -- 临时变量，给sqlwhere加where declare @strOrderA varchar(2000) -- 第一次排序类型 declare @strOrderB varchar(2000) -- 第二次排序类型 declare @strSqlA varchar(4000) -- 第一次选出 declare @strSqlB varchar(8000) -- 第二次选出 declare @strSQL varchar(8000) -- 最后选出 declare @keyStr varchar(4000) /* 条件*/ if @sqlWhere != '' set @sqlWhereA = ' where ' + @sqlWhere else set @sqlWhereA = '' set @keyStr=@keyName /* 选择字段列表*/ if @Fields is null or rtrim(@Fields) = '' set @Fields = '*' /* 排序字段列表*/ if (@OrderField='' or @OrderField is null) begin if (@orderby ='desc') set @OrderField= @keyName + ' desc ' else set @OrderField= @keyName + ' asc ' end else begin set @keyStr=@keyName+' , ' +@OrderField if (@orderby ='desc') set @OrderField = @OrderField+ ' desc, ' + @keyName + ' desc' else set @OrderField = @OrderField+ ' asc ,' + @keyName + ' asc' end set @orderby = ' order by ' + @OrderField set @strOrderA = UPPER(@orderby) set @strOrderB = replace(@strOrderA,'DESC','DESC1') set @strOrderB = replace(@strOrderB,'ASC','DESC') set @strOrderB = replace(@strOrderB,'DESC1','ASC') set @keyStr = UPPER(@keyStr) set @keyStr=replace(@keyStr,'DESC','') set @keyStr=replace(@keyStr,'ASC','') --取得总记录数 declare @sql nvarchar(500) declare @maxCount int declare @maxPage int declare @tempRowCount int set @sql ='select @maxCount = count('+@keyName+') from [' + @TableName + ']' + @sqlWhereA exec sp_executesql @sql,N'@maxCount int output',@maxCount output set @TotalCount=@maxCount set @TotalPage=CEILING((@TotalCount+0.0)/@pageSize) set @maxPage = @maxCount / @pageSize if(@maxCount % @pageSize > 0) set @maxPage = @maxPage + 1 /* 第一页*/ if @pageIndex = 1 set @strSQL = 'select top ' + str(@pageSize) + ' ' + @Fields + ' from [' + @TableName + '] with(nolock)' + @sqlWhereA + ' ' + @strOrderA else begin /* 最后一页*/ if @pageIndex >= @maxPage begin set @pageIndex = @maxPage set @strSqlA = char(13) + '(select top '+str(@maxCount % @pageSize)+' ' + @keyStr + ' from [' + @TableName + '] as a with(nolock) ' + @sqlWhereA + @strOrderB + ' )' + char(13) set @strSqlB = char(13) + '(select ' + @keyName +' from ' + @strSqlA + ' as b )' + char(13) set @strSQL = 'select ' + @Fields + ' from [' + @TableName + '] where ([' + @keyName + '] in '+@strSqlB+')' + @strOrderA + char(13) end else begin /* 不是第一页，也不是最后一页*/ if(@pageIndex <= @maxPage / 2) begin --前半数的页 set @tempRowCount = @pageIndex * @pageSize /* 构建SQL，本分页算法的目的是为了实现高效的非主键排序的分页。 */ /* 1、先按指定字段+主键字段按降序选出tempRowCount条记录*/ set @strSqlA = char(13) + '(select top '+str(@tempRowCount)+' ' + @keyStr + ' from [' + @TableName + '] as a with(nolock) ' + @sqlWhereA + @strOrderA + ' )' + char(13) /* 2、再从选出的记录中按升序选出pageSize条记录*/ set @strSqlB = char(13) + '(select top '+str(@pageSize)+' ' + @keyName + ' from ' + @strSqlA + ' as b ' + @strOrderB + ' )' + char(13) /* 3、从数据库中选出主键在第二次选出的记录中的记录，按降序排列，分页完成*/ set @strSQL = 'select top '+ str(@pageSize) + ' '+ @Fields + ' from [' + @TableName + '] where ([' + @keyName + '] in '+@strSqlB+')' + @strOrderA end else begin --后半数的页 set @tempRowCount = @maxCount - (@pageIndex -1) * @pageSize /* 构建SQL，本分页算法的目的是为了实现高效的非主键排序的分页。 */ /* 1、先按指定字段+主键字段按降序选出tempRowCount条记录*/ set @strSqlA = char(13) + '(select top '+str(@tempRowCount)+' ' + @keyStr + ' from [' + @TableName + '] as a with(nolock) ' + @sqlWhereA + @strOrderB + ' )' + char(13) /* 2、再从选出的记录中按升序选出pageSize条记录*/ set @strSqlB = char(13) + '(select top '+str(@pageSize)+' ' + @keyName + ' from ' + @strSqlA + ' as b ' + @strOrderA + ' )' + char(13) /* 3、从数据库中选出主键在第二次选出的记录中的记录，按降序排列，分页完成*/ set @strSQL = 'select top ' + str(@pageSize) + ' ' + @Fields + ' from [' + @TableName + '] where ([' + @keyName + '] in '+@strSqlB+')' + @strOrderA end end end set nocount on /*print @strSQL*/ --显示SQL exec (@strSQL) set nocount off RETURN

此种方法前后页面读取时间在0.1-0.4秒之间，中间页读取的时间在0.4-0.8秒之间。

先把第1、3中方法用C#写出类，直接用sql语句，不用存储过程。

C#类方法1

public DataTable GetPage() { string sql = ""; string where = ""; if (!String.IsNullOrEmpty(this.WhereCondition)) { where = " where " + this.WhereCondition; } sql = "select count(*) from " + this.TableName + where; int RecordCount = dal.getCount(sql); if (RecordCount % this.PageSize == 0) { this.PageCount = (int)(RecordCount / this.PageSize); } else { this.PageCount = (int)(RecordCount / this.PageSize) + 1; } this.RecordCount = RecordCount; //处理页数超出范围情况 if (this.PageIndex <= 0) this.PageIndex = 1; if (this.PageIndex > this.PageCount) this.PageIndex = this.PageCount; string strSQL = ""; string strTmp = ""; string strOrder = ""; if (String.IsNullOrEmpty(this.OrderName) || this.OrderName == this.KeyName) { this.OrderName = this.KeyName; if (this.Order == "desc") { strTmp = "<(select min"; strOrder = " order by " + this.OrderName + " desc"; } else { strTmp = ">(select max"; strOrder = " order by " + this.OrderName + " asc"; } if (this.PageIndex == 1) { if (!String.IsNullOrEmpty(this.WhereCondition)) strSQL = "select top " + this.PageSize + " " + this.SelectStr + " from " + this.TableName + " where " + this.WhereCondition + " " + strOrder; else strSQL = "select top " + this.PageSize + " " + this.SelectStr + " from " + this.TableName + " " + strOrder; } else { if (!String.IsNullOrEmpty(this.WhereCondition)) { strSQL = "select top " + this.PageSize + " " + this.SelectStr + " from "; strSQL += this.TableName + " where " + this.OrderName + "" + strTmp + "([" + this.OrderName + "]) from (select top " + (this.PageIndex - 1) * this.PageSize + " ["; strSQL += this.OrderName + "] from " + this.TableName + " where (" + this.WhereCondition + ") " + strOrder + ") as tblTmp) and ("; strSQL += this.WhereCondition + ") " + strOrder; } else { strSQL = "select top " + this.PageSize + " " + this.SelectStr + " from " + this.TableName + " where " + this.OrderName + "" + strTmp + "(["; strSQL += this.OrderName + "]) from (select top " + (this.PageIndex - 1) * this.PageSize + " ["; strSQL += this.OrderName + "] from " + this.TableName + "" + strOrder + ") as tblTmp)"; strSQL += strOrder; } } } else { if (this.Order == "desc") { strOrder = " order by " + this.OrderName + " desc, " + this.KeyName + " desc"; } else { strOrder = " order by " + this.OrderName + " asc ," + this.KeyName + " asc"; } strTmp = this.KeyName + " not in( select " + this.KeyName; if (this.PageIndex == 1) { if (!String.IsNullOrEmpty(this.WhereCondition)) strSQL = "select top " + this.PageSize + " " + this.SelectStr + " from " + this.TableName + " where " + this.WhereCondition + " " + strOrder; else strSQL = "select top " + this.PageSize + " " + this.SelectStr + " from " + this.TableName + " " + strOrder; } else { if (!String.IsNullOrEmpty(this.WhereCondition)) { strSQL = "select top " + this.PageSize + " " + this.SelectStr + " from "; strSQL += this.TableName + " where " + strTmp + " from (select top " + (this.PageIndex - 1) * this.PageSize + " ["; strSQL += this.KeyName + "] from " + this.TableName + " where (" + this.WhereCondition + ") " + strOrder + ") as tblTmp) and ("; strSQL += this.WhereCondition + ") " + strOrder; } else { strSQL = "select top " + this.PageSize + " " + this.SelectStr + " from "; strSQL += this.TableName + " where " + strTmp + " from (select top " + (this.PageIndex - 1) * this.PageSize + " ["; strSQL += this.KeyName + "] from " + this.TableName + "" + strOrder + ") as tblTmp)"; strSQL += strOrder; } } } return dal.getList(strSQL); }

C#类方法2

public DataTable getPage2() { string sql = ""; string where = ""; if (!String.IsNullOrEmpty(this.WhereCondition)) { where = " where " + this.WhereCondition; } sql = "select count(*) from " + this.TableName + where; int RecordCount = dal.getCount(sql); if (RecordCount % this.PageSize == 0) { this.PageCount = (int)(RecordCount / this.PageSize); } else { this.PageCount = (int)(RecordCount / this.PageSize) + 1; } this.RecordCount = RecordCount; //处理页数超出范围情况 if (this.PageIndex <= 0) this.PageIndex = 1; if (this.PageIndex > this.PageCount) this.PageIndex = this.PageCount; string strSQL = ""; string strOrder = ""; string keyStr = this.KeyName; if (String.IsNullOrEmpty(this.OrderName) || this.OrderName == this.KeyName) { if (this.Order == "desc") { strOrder = " order by " + this.OrderName + " desc, " + this.KeyName + " desc"; } else { strOrder = " order by " + this.OrderName + " asc ," + this.KeyName + " asc"; } } else { keyStr = this.KeyName + " , " + this.OrderName; if (this.Order == "desc") { strOrder = " order by " + this.OrderName + " desc, " + this.KeyName + " desc"; } else { strOrder = " order by " + this.OrderName + " asc ," + this.KeyName + " asc"; } } string strOrderA = strOrder.ToUpper(); string strOrderB = strOrderA; strOrderB = strOrderB.Replace("DESC", "DESC1").Replace("ASC", "DESC").Replace("DESC1", "ASC"); keyStr = keyStr.ToUpper(); keyStr = keyStr.Replace("DESC", "").Replace("ASC", ""); if (this.PageIndex == 1) strSQL = "select top " + this.PageSize + " " + this.SelectStr + " from [" + this.TableName + "] with(nolock) " + where + " " + strOrderA; else { if (this.PageIndex >= this.PageCount) { this.PageIndex = this.PageCount; strSQL = "select " + this.SelectStr + " from[" + this.TableName + "] where ([" + this.KeyName + "] in "; strSQL += " (select " + this.KeyName + " from "; strSQL += " (select Top " + this.RecordCount % this.PageSize + " " + keyStr + " from [" + this.TableName + "] as a with(nolock) " + where + strOrderB + " )"; strSQL += " as b)"; strSQL += " )" + strOrderA; } else { int tempRowCount = 0; string strSqlA = ""; string strSqlB = ""; /* 不是第一页，也不是最后一页*/ if (this.PageIndex <= this.PageCount / 2) { //前半数的页 tempRowCount = this.PageIndex * this.PageSize; /* 构建SQL，本分页算法的目的是为了实现高效的非主键排序的分页。 */ /* 1、先按指定字段+主键字段按降序选出tempRowCount条记录*/ strSqlA = " (select top " + tempRowCount + " " + keyStr + " from [" + this.TableName + "] as a with(nolock) " + where + strOrderA + " )"; /* 2、再从选出的记录中按升序选出pageSize条记录*/ strSqlB = " (select top " + this.PageSize + " " + this.KeyName + " from " + strSqlA + " as b " + strOrderB + " )"; /* 3、从数据库中选出主键在第二次选出的记录中的记录，按降序排列，分页完成*/ strSQL = " select top " + this.PageSize + " " + this.SelectStr + " from [" + this.TableName + "] where ([" + this.KeyName + "] in " + strSqlB + ")" + strOrderA; } else { //后半数的页 tempRowCount = this.RecordCount - (this.PageIndex - 1) * this.PageSize; /* 构建SQL，本分页算法的目的是为了实现高效的非主键排序的分页。 */ /* 1、先按指定字段+主键字段按降序选出tempRowCount条记录*/ strSqlA = " (select top " + tempRowCount + " " + keyStr + " from [" + this.TableName + "] as a with(nolock) " + where + strOrderB + " )"; /* 2、再从选出的记录中按升序选出pageSize条记录*/ strSqlB = " (select top " + this.PageSize + " " + this.KeyName + " from " + strSqlA + " as b " + strOrderA + " )"; /* 3、从数据库中选出主键在第二次选出的记录中的记录，按降序排列，分页完成*/ strSQL = " select top " + this.PageSize + " " + this.SelectStr + " from [" + this.TableName + "] where ([" + this.KeyName + "] in " + strSqlB + ")" + strOrderA; } } } return dal.getList(strSQL); }

ps：

            //this.KeyName;关键字段，唯一
            //this.Order;排序方式desc或者asc
            //this.OrderName;排序字段
            //this.PageCount;总页面数目
            //this.PageIndex;当前页面数目
            //this.PageSize;页面大小
            //this.RecordCount;总记录数目
            //this.SelectStr;要显示的列
            //this.TableName;表名
            //this.WhereCondition;查询条件