一、引言
最近上班比较忙，所以就很少写东西了，MongoDB系列的文章也要拖后了，没办法，工作第一，没工作就没饭吃了。今天正好的有点时间，就把我最近搞得一些东西，记录下来。
在软件行业，稍微大一点的公司，相关数据的存储量就可能会很大，当我们做系统的时候，一定会使用存储过程进行分页显示，至于为什么分页显示，就不用我多说了吧。最近我在做一个系统，为了让系统支持大数据量，做了很多测试，主要是测试各种分页算法的优劣。最后，经过自己的多番测试，确定了这个存储过程。测试环境是：
硬件环境：CPU是12核的，内存是8G的大小，
软件环境：Visual Studio 2015，数据库是 Sql Server 2008 R2，数据量大概有3千万左右
ORM环境：IBatis.Net
测试效果就不贴图了，如果大家想去测试，可以自行操作，首先要说明的一点，如果针对ID主键进行分页查询，最多是300-400毫秒之间就可以把分页数据显示出来，无论是针对第一页还是最后一页显示时间都是差不多的。再者说，如果是针对其他字段的查询分页，必须针对该字段建立索引，查询速度也在毫秒之间可以完成，经过自己的多轮测试，确定了最终的存储过程的样式。当然如果针对查询的分页的字段没有索引页可以查询出来，时间就比较长了。6分钟，10分钟都有可能。由于今天只讨论存储过程，其他的就不说了，比如分库，分表，水平拆分或者垂直拆分等知识。
二、存储过程代码
当然，这个存储过程也参考了很多前辈的资料，才最终确定的。修改了其原有的Bug，和语法山的一些错误。其他细节也做了一些微调，形成了这个最后的版本，如果大家有更好的写法，也欢迎大家留言讨论。废话不多说了，今天就把代码发出来，该存储过程已经经过测试，完全没有问题，大家可以放心使用，代码如下：
/****** Object: StoredProcedure [dbo].[usp_CommonDataResourcePaged] Script Date: 2018/4/26 12:11:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <PatrickLiu> -- Create date: <2018-4-16 14:21> -- Description: <通用的数据分页存储过程> -- ============================================= ALTER PROCEDURE [dbo].[usp_CommonDataResourcePaged] ( @TableName varchar(500), ----要显示的表或多个表的连接 @FieldList varchar(5000) = '*', ----要显示的字段列表 @PageSize int = 20, ----每页显示的记录个数 @PageNumber int = 1, ----要显示那一页的记录 @SortFields varchar(1000) = null, ----排序字段列表或条件 @EnabledSort bit = 0, ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ') @QueryCondition nvarchar(1500) = null, ----查询条件,不需WHERE @Primarykey varchar(50), ----主表的主键 @EnabledDistinct bit = 0, ----是否添加查询字段的 DISTINCT 默认0不添加/1添加 @PageCount int = 1 output, ----查询结果分页后的总页数 @RecordCount int = 1 output ----查询到的记录数 ) AS SET NOCOUNT ON Declare @PrimaryKeyAlias varchar(50) Declare @SqlResult nvarchar(4000) ----存放动态生成的SQL语句 Declare @SqlTotalCount nvarchar(4000) ----存放取得查询结果总数的查询语句 Declare @SqlStartOrEndID nvarchar(3000) ----存放取得查询开头或结尾ID的查询语句 Declare @SortTypeA nvarchar(10) ----数据排序规则A Declare @SortTypeB nvarchar(10) ----数据排序规则B Declare @SqlDistinct nvarchar(50) ----对含有DISTINCT的查询进行SQL构造 Declare @SqlCountDistinct nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造 declare @ExecuteTime datetime --耗时测试时间差 --如果字段名带有表名或者其他的前缀(literature.tbl_Literatures.ID)，通过(select * from table)显示后，在排序，此时的字段名是不包含前缀的， --所以要处理一下，该方法适合Sql Server，如果要迁移其他数据库上，可以修改获取字段名的方法 DECLARE @ConvertedSortFields varchar(50) IF CHARINDEX('.',@SortFields)>0 BEGIN SELECT @ConvertedSortFields=REVERSE(SUBSTRING(REVERSE(@SortFields),1,CHARINDEX('.',REVERSE(@SortFields))-1)) END ELSE BEGIN SET @ConvertedSortFields=@SortFields END SELECT @ExecuteTime=GETDATE() --在分页的过程中，会涉及到子查询，当子查询根据主键查询后，外层循环再使用该值的时候已经不是第一次赋的值了，此处要做转换，否则会有错误 SET @PrimaryKeyAlias='CustomID' if @EnabledDistinct = 0 begin set @SqlDistinct = 'SELECT ' set @SqlCountDistinct = 'Count(*)' end else begin set @SqlDistinct = 'SELECT DISTINCT ' set @SqlCountDistinct = 'Count(DISTINCT '+@Primarykey+')' end if @EnabledSort=0 begin set @SortTypeB=' ASC ' set @SortTypeA=' DESC ' end else begin set @SortTypeB=' DESC ' set @SortTypeA=' ASC ' end --------生成查询语句-------- --此处@SqlTotalCount为取得查询结果数量的语句 if @QueryCondition is null or @QueryCondition='' or @QueryCondition='''' or LEN(@QueryCondition)<3 --没有设置显示条件 begin set @SqlResult = @FieldList + ' From ' + @TableName set @SqlTotalCount = @SqlDistinct+' @RecordCount='+@SqlCountDistinct+' FROM '+@TableName set @SqlStartOrEndID = ' From ' + @TableName end else begin set @SqlResult = + @FieldList + ' From ' + @TableName + ' WHERE (1>0) and ' + @QueryCondition set @SqlTotalCount = @SqlDistinct+' @RecordCount='+@SqlCountDistinct+' FROM '+@TableName + ' WHERE (1>0) and ' + @QueryCondition set @SqlStartOrEndID = ' From ' + @TableName + ' WHERE (1>0) and ' + @QueryCondition end ----取得查询结果总数量----- exec sp_executesql @SqlTotalCount,N'@RecordCount int out ',@RecordCount out declare @TemporaryRecordCount int --临时统计 if @RecordCount = 0 set @TemporaryRecordCount = 1 else set @TemporaryRecordCount = @RecordCount --取得分页总数 set @PageCount=(@TemporaryRecordCount+@PageSize-1)/@PageSize /**当前页大于总页数 取最后一页**/ if @PageNumber>@PageCount set @PageNumber=@PageCount --/*-----数据分页2分处理-------*/ declare @TemporaryPageCount int --总数/页大小，临时存储总的分页数据 declare @ExtraPageNumber int --总数%页大小 此处用于判断是否有余数，有就增加一页，没有就保持原有分页总数 set @TemporaryPageCount = @TemporaryRecordCount/@PageSize set @ExtraPageNumber = @TemporaryRecordCount%@PageSize --如果对总记录条数和页大小取余，结果大于零，分页数量需要加一 if @ExtraPageNumber > 0 BEGIN set @TemporaryPageCount = @TemporaryPageCount + 1 END ELSE BEGIN SET @ExtraPageNumber=@PageSize END --//***显示分页 if @QueryCondition is null or @QueryCondition='' or @QueryCondition='''' or LEN(@QueryCondition)<3 --没有设置显示条件 begin if @TemporaryPageCount<2 or @PageNumber<=@TemporaryPageCount / 2 + @TemporaryPageCount % 2 --前半部分数据处理 begin if @PageNumber=1 set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName+' ORDER BY '+ @SortFields +' '+ @SortTypeB else begin if @EnabledSort=1 begin set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName +' WHERE '+@Primarykey+' <(SELECT MIN('+ @PrimaryKeyAlias +') FROM ('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-1) as Varchar(20)) +' '+ @Primarykey +' AS '+@PrimaryKeyAlias+' FROM '+@TableName +' ORDER BY '+ @SortFields +' '+ @SortTypeB+') AS TBMinID)'+' ORDER BY '+ @SortFields +' '+ @SortTypeB end else begin set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName +' WHERE '+@Primarykey+' >(SELECT MAX('+ @PrimaryKeyAlias +') FROM ('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-1) as Varchar(20)) +' '+ @Primarykey +' AS '+@PrimaryKeyAlias+' FROM '+@TableName +' ORDER BY '+ @SortFields +' '+ @SortTypeB+') AS TBMinID)'+' ORDER BY '+ @SortFields +' '+ @SortTypeB end end end else begin set @PageNumber = @TemporaryPageCount-@PageNumber+1 --后半部分数据处理 if @PageNumber <= 1 --最后一页数据显示 begin set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@ExtraPageNumber as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName+' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @ConvertedSortFields +' '+ @SortTypeB end else if @EnabledSort=1 begin set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName +' WHERE '+@Primarykey+' >(SELECT MAX('+ @PrimaryKeyAlias +') FROM('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-2)+@ExtraPageNumber as Varchar(20)) +' '+ @Primarykey +' AS '+@PrimaryKeyAlias+' FROM '+@TableName+' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TBMaxID)'+' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @ConvertedSortFields +' '+ @SortTypeB end else begin set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName +' WHERE '+@Primarykey+' <(SELECT MIN('+ @PrimaryKeyAlias +') FROM('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-2)+@ExtraPageNumber as Varchar(20)) +' '+ @Primarykey +' AS '+@PrimaryKeyAlias+' FROM '+@TableName +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TBMaxID)'+' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @ConvertedSortFields +' '+ @SortTypeB end end end else --有查询条件 begin if @TemporaryPageCount<2 or @PageNumber<=@TemporaryPageCount / 2 + @TemporaryPageCount % 2 --前半部分数据处理 begin if @PageNumber=1 set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName +' WHERE 1=1 and ' + @QueryCondition + ' ORDER BY '+ @SortFields +' '+ @SortTypeB else if(@EnabledSort=1) begin set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName +' WHERE '+@Primarykey+' <(SELECT MIN('+ @PrimaryKeyAlias +') FROM ('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-1) as Varchar(20)) +' '+ @Primarykey +' AS '+@PrimaryKeyAlias+' FROM '+@TableName+' WHERE (1=1) and ' + @QueryCondition +' ORDER BY '+ @SortFields +' '+ @SortTypeB+') AS TBMinID)'+' and '+ @QueryCondition +' ORDER BY '+ @SortFields +' '+ @SortTypeB end else begin set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName +' WHERE '+@Primarykey+' >(SELECT MAX('+ @PrimaryKeyAlias +') FROM ('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-1) as Varchar(20)) +' '+ @Primarykey +' AS '+@PrimaryKeyAlias+' FROM '+@TableName +' WHERE (1=1) and ' + @QueryCondition +' ORDER BY '+ @SortFields +' '+ @SortTypeB+') AS TBMinID)'+' and '+ @QueryCondition +' ORDER BY '+ @SortFields +' '+ @SortTypeB end end else begin set @PageNumber = @TemporaryPageCount-@PageNumber+1 --后半部分数据处理 if @PageNumber <= 1 --最后一页数据显示 set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@ExtraPageNumber as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName +' WHERE (1=1) and '+ @QueryCondition +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @ConvertedSortFields +' '+ @SortTypeB else if(@EnabledSort=1) set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName+' WHERE '+@Primarykey+' >(SELECT MAX('+ @PrimaryKeyAlias +') FROM('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-2)+@ExtraPageNumber as Varchar(20)) +' '+ @Primarykey+' AS '+@PrimaryKeyAlias+' FROM '+@TableName+' WHERE (1=1) and '+ @QueryCondition +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TBMaxID)' +' and '+ @QueryCondition+' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @ConvertedSortFields +' '+ @SortTypeB else set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName+' WHERE '+@Primarykey+' <(SELECT MIN('+ @PrimaryKeyAlias +') FROM('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-2)+@ExtraPageNumber as Varchar(20)) +' '+ @Primarykey +' AS '+@PrimaryKeyAlias+' FROM '+@TableName+' WHERE (1=1) and '+ @QueryCondition +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TBMaxID)'+' and '+ @QueryCondition+' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @ConvertedSortFields +' '+ @SortTypeB end end ------返回查询结果----- print @SqlTotalCount exec sp_executesql @SqlTotalCount --SELECT datediff(ms,@ExecuteTime,getdate()) as 耗时 SET NOCOUNT OFF以上就是存储 过程，代码很简单，都有注释，就不做解释了。 三、相关实体类
因为存储过程代码是在C#代码调用的，为了方便参数的传递，我特意建立一个实体类，用于传递和接受参数，如：总页数和记录条数，类的名称就是：CommonDataResourcePaged。废话不多说，直接上代码，代码如下：
/// <summary>
/// 通用数据源匪分页
/// </summary>
public sealed class CommonDataResourcePaged
{
/// <summary>
/// 要显示的表或多个表的连接
/// </summary>
public string TableName { get; set; }
/// <summary>
/// 要显示的字段列表
/// </summary>
public string FieldList { get; set; }
/// <summary>
/// 每页显示的记录个数
/// </summary>
public int PageSize { get; set; }
/// <summary>
/// 要显示的那一页的记录的页码
/// </summary>
public int PageNumber { get; set; }
/// <summary>
/// 排序字段列表或条件
/// </summary>
public string SortFields { get; set; }
/// <summary>
/// 排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')
/// </summary>
public bool EnabledSort { get; set; }
/// <summary>
/// 查询条件,不需where
/// </summary>
public string QueryCondition { get; set; }
/// <summary>
/// 主表的主键
/// </summary>
public string Primarykey { get; set; }
/// <summary>
/// 是否添加查询字段的 DISTINCT 默认0不添加/1添加
/// </summary>
public bool EnabledDistinct { get; set; }
/// <summary>
/// 该字段是从数据库返回来的字段值，在数据库中是Output字段，查询结果分页后的总页数
/// </summary>
public int PageCount { get; set; }
/// <summary>
/// 该字段是从数据库返回来的字段值，在数据库中是Output字段，查询到的记录数
/// </summary>
public int RecordCount { get; set; }
}
以上就是实体类的代码，很简单，就不解释了。四、Sql Server的调用代码
在这里，我只演示了在Sql Server 中怎么调用这个存储过程，其实很简单，大家都应该知道。好了，就是这些，代码都是很简单，想调用存储过程，调用实例如下：
USE CXHDJ
GO
DECLARE @return_value int, @PageCount int, @RecordCount int
SELECT @PageCount = 0 SELECT @RecordCount = 0
EXEC @return_value = [dbo].[usp_CommonDataResourcePaged]
@TableName = N'[CXHDJ].[dbo].[T_Scan_Code]',
@FieldList = N'[seq_id],[fac_id],[line] ,[mo],[san_barcode] ,[scan_qty],[remark],[user_update]
,[last_update] ,[serial_type],[mes_barcode],[co_barcode],[pallet_no]',
--@FieldList =' * ',
@PageSize = 5,
@PageNumber = 3,
@SortFields = 'san_barcode',
@EnabledSort =N'0' ,
@QueryCondition = N'[mo]=''WO09941_2_IG_1''',
@Primarykey = N'[san_barcode]',
@EnabledDistinct = 1,
@PageCount = @PageCount OUTPUT,
@RecordCount = @RecordCount OUTPUT
SELECT @PageCount as N'@PageCount', @RecordCount as N'@RecordCount'
SELECT 'Return Value' = @return_value
GO
调用方法2
USE CXHDJ
GO
DECLARE @return_value int, @PageCount int, @RecordCount int
SELECT @PageCount = 0 SELECT @RecordCount = 0
EXEC @return_value = [dbo].[usp_CommonDataResourcePaged]
@TableName = N'(SELECT mo,san_barcode,mes_barcode FROM [CXHDJ].[dbo].[T_Scan_Code]) tsc
INNER JOIN (SELECT mo,jz_barcode FROM dbo.T_JZ_CenerateBarcode ) tjc
ON tjc.mo = tsc.mo',
@FieldList = N'tsc.mo,tjc.jz_barcode,tsc.mes_barcode',
--@FieldList =' * ',
@PageSize = 10,
@PageNumber = 3,
@SortFields = '[jz_barcode]',
@EnabledSort =N'0' ,
@QueryCondition = N'tsc.mo=''WO09941_2_IG_1''',
@Primarykey = N'[jz_barcode]',
@EnabledDistinct = 1,
@PageCount = @PageCount OUTPUT,
@RecordCount = @RecordCount OUTPUT
SELECT @PageCount as N'@PageCount', @RecordCount as N'@RecordCount'
SELECT 'Return Value' = @return_value
GO
DECLARE @return_value int, @PageCount int, @RecordCount int
SELECT @PageCount = 0 SELECT @RecordCount = 0
调用方法3
USE CXHDJ
GO
DECLARE @return_value int, @PageCount int, @RecordCount int
SELECT @PageCount = 0 SELECT @RecordCount = 0
EXEC @return_value = [dbo].[usp_CommonDataResourcePaged]
@TableName = N'(SELECT mo,san_barcode,mes_barcode FROM [CXHDJ].[dbo].[T_Scan_Code] where mo=''WO09941_2_IG_1'') tsc
INNER JOIN (SELECT mo,jz_barcode FROM dbo.T_JZ_CenerateBarcode ) tjc
ON tjc.mo = tsc.mo',
@FieldList = N'tsc.mo,tjc.jz_barcode,tsc.mes_barcode',
--@FieldList =' * ',
@PageSize = 10,
@PageNumber = 3,
@SortFields = '[jz_barcode]',
@EnabledSort =N'0' ,
@QueryCondition = N'',
@Primarykey = N'[jz_barcode]',
@EnabledDistinct = 1,
@PageCount = @PageCount OUTPUT,
@RecordCount = @RecordCount OUTPUT
SELECT @PageCount as N'@PageCount', @RecordCount as N'@RecordCount'
SELECT 'Return Value' = @return_value
GO
DECLARE @return_value int, @PageCount int, @RecordCount int
SELECT @PageCount = 0 SELECT @RecordCount = 0
查询只后就可以看到先关的结果，我就不贴图了。
五、结束
今天就写到这里，该上班了。对了，我在C#代码中使用IBatis.net，在调用之前需要在配置文件里做相关的设置，比如：传递参数的设置、查询结果映射设置和类型等等的设置，使用起来也是很简单的，就不贴代码了。今天就是这些了，以后有了时间继续我的MongoDB系列之旅吧。
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
