继续浏览精彩内容
慕课网APP
程序员的梦工厂
打开
继续
感谢您的支持,我会继续努力的
赞赏金额会直接到老师账户
将二维码发送给自己后长按识别
微信支付
支付宝支付

适合千万数据查询分页操作的一个通用存储过程

千岁不倒翁
关注TA
已关注
手记 362
粉丝 60
获赞 386

一、引言

               最近上班比较忙,所以就很少写东西了,MongoDB系列的文章也要拖后了,没办法,工作第一,没工作就没饭吃了。今天正好的有点时间,就把我最近搞得一些东西,记录下来。

               在软件行业,稍微大一点的公司,相关数据的存储量就可能会很大,当我们做系统的时候,一定会使用存储过程进行分页显示,至于为什么分页显示,就不用我多说了吧。最近我在做一个系统,为了让系统支持大数据量,做了很多测试,主要是测试各种分页算法的优劣。最后,经过自己的多番测试,确定了这个存储过程。测试环境是:
                                              硬件环境:CPU是12核的,内存是8G的大小,
                                              软件环境:Visual Studio 2015,数据库是 Sql Server 2008 R2,数据量大概有3千万左右
                                              ORM环境:IBatis.Net
 
               测试效果就不贴图了,如果大家想去测试,可以自行操作,首先要说明的一点,如果针对ID主键进行分页查询,最多是300-400毫秒之间就可以把分页数据显示出来,无论是针对第一页还是最后一页显示时间都是差不多的。再者说,如果是针对其他字段的查询分页,必须针对该字段建立索引,查询速度也在毫秒之间可以完成,经过自己的多轮测试,确定了最终的存储过程的样式。当然如果针对查询的分页的字段没有索引页可以查询出来,时间就比较长了。6分钟,10分钟都有可能。由于今天只讨论存储过程,其他的就不说了,比如分库,分表,水平拆分或者垂直拆分等知识。

二、存储过程代码

              当然,这个存储过程也参考了很多前辈的资料,才最终确定的。修改了其原有的Bug,和语法山的一些错误。其他细节也做了一些微调,形成了这个最后的版本,如果大家有更好的写法,也欢迎大家留言讨论。废话不多说了,今天就把代码发出来,该存储过程已经经过测试,完全没有问题,大家可以放心使用,代码如下:

复制代码

  1 /****** Object:  StoredProcedure [dbo].[usp_CommonDataResourcePaged]    Script Date: 2018/4/26 12:11:59 ******/  2 SET ANSI_NULLS ON  3 GO  4 SET QUOTED_IDENTIFIER ON  5 GO  6 -- =============================================  7 -- Author:        <PatrickLiu>  8 -- Create date: <2018-4-16 14:21>  9 -- Description:    <通用的数据分页存储过程> 10 -- ============================================= 11 ALTER PROCEDURE [dbo].[usp_CommonDataResourcePaged] 12 ( 13     @TableName          varchar(500),           ----要显示的表或多个表的连接 14     @FieldList          varchar(5000) = '*',    ----要显示的字段列表 15     @PageSize           int = 20,                ----每页显示的记录个数 16     @PageNumber         int = 1,                 ----要显示那一页的记录 17     @SortFields         varchar(1000) = null,   ----排序字段列表或条件 18     @EnabledSort        bit = 0,                 ----排序方法,0为升序,1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如:' SortA Asc,SortB Desc,SortC ') 19     @QueryCondition     nvarchar(1500) = null,   ----查询条件,不需WHERE 20     @Primarykey         varchar(50),            ----主表的主键 21     @EnabledDistinct    bit = 0,                 ----是否添加查询字段的 DISTINCT 默认0不添加/1添加 22     @PageCount          int = 1 output,          ----查询结果分页后的总页数 23     @RecordCount        int = 1 output           ----查询到的记录数 24 ) 25 AS 26     SET NOCOUNT ON 27     Declare @PrimaryKeyAlias varchar(50) 28     Declare @SqlResult nvarchar(4000)        ----存放动态生成的SQL语句 29     Declare @SqlTotalCount nvarchar(4000)        ----存放取得查询结果总数的查询语句 30     Declare @SqlStartOrEndID  nvarchar(3000)        ----存放取得查询开头或结尾ID的查询语句 31      32     Declare @SortTypeA nvarchar(10)    ----数据排序规则A 33     Declare @SortTypeB nvarchar(10)    ----数据排序规则B 34      35     Declare @SqlDistinct nvarchar(50)         ----对含有DISTINCT的查询进行SQL构造 36     Declare @SqlCountDistinct nvarchar(50)          ----对含有DISTINCT的总数查询进行SQL构造 37      38     declare @ExecuteTime datetime  --耗时测试时间差 39  40     --如果字段名带有表名或者其他的前缀(literature.tbl_Literatures.ID),通过(select * from table)显示后,在排序,此时的字段名是不包含前缀的, 41     --所以要处理一下,该方法适合Sql Server,如果要迁移其他数据库上,可以修改获取字段名的方法 42     DECLARE @ConvertedSortFields varchar(50) 43     IF CHARINDEX('.',@SortFields)>0 44       BEGIN 45         SELECT @ConvertedSortFields=REVERSE(SUBSTRING(REVERSE(@SortFields),1,CHARINDEX('.',REVERSE(@SortFields))-1)) 46       END 47     ELSE 48       BEGIN 49         SET @ConvertedSortFields=@SortFields 50       END 51     SELECT @ExecuteTime=GETDATE() 52  53     --在分页的过程中,会涉及到子查询,当子查询根据主键查询后,外层循环再使用该值的时候已经不是第一次赋的值了,此处要做转换,否则会有错误 54     SET @PrimaryKeyAlias='CustomID' 55      56     if @EnabledDistinct  = 0 57         begin 58             set @SqlDistinct = 'SELECT ' 59             set @SqlCountDistinct = 'Count(*)' 60         end 61     else 62         begin 63             set @SqlDistinct = 'SELECT DISTINCT ' 64             set @SqlCountDistinct = 'Count(DISTINCT '+@Primarykey+')' 65         end 66      67     if @EnabledSort=0 68         begin 69             set @SortTypeB=' ASC ' 70             set @SortTypeA=' DESC ' 71         end 72     else 73         begin 74             set @SortTypeB=' DESC ' 75             set @SortTypeA=' ASC ' 76         end 77      78     --------生成查询语句-------- 79     --此处@SqlTotalCount为取得查询结果数量的语句 80     if @QueryCondition is null or @QueryCondition='' or @QueryCondition='''' or LEN(@QueryCondition)<3   --没有设置显示条件 81         begin 82             set @SqlResult =  @FieldList + ' From ' + @TableName 83             set @SqlTotalCount = @SqlDistinct+' @RecordCount='+@SqlCountDistinct+' FROM '+@TableName 84             set @SqlStartOrEndID = ' From ' + @TableName 85         end 86     else 87         begin 88             set @SqlResult = + @FieldList + ' From ' + @TableName + ' WHERE (1>0) and ' + @QueryCondition 89             set @SqlTotalCount = @SqlDistinct+' @RecordCount='+@SqlCountDistinct+' FROM '+@TableName + ' WHERE (1>0) and ' + @QueryCondition 90             set @SqlStartOrEndID = ' From ' + @TableName + ' WHERE (1>0) and ' + @QueryCondition 91         end 92      93     ----取得查询结果总数量----- 94     exec sp_executesql @SqlTotalCount,N'@RecordCount int out ',@RecordCount out 95  96     declare @TemporaryRecordCount int --临时统计 97     if @RecordCount = 0 98         set @TemporaryRecordCount = 1 99     else100         set @TemporaryRecordCount = @RecordCount101     102         --取得分页总数103         set @PageCount=(@TemporaryRecordCount+@PageSize-1)/@PageSize104     105         /**当前页大于总页数 取最后一页**/106         if @PageNumber>@PageCount107             set @PageNumber=@PageCount108     109         --/*-----数据分页2分处理-------*/110         declare @TemporaryPageCount int --总数/页大小,临时存储总的分页数据111         declare @ExtraPageNumber int --总数%页大小 此处用于判断是否有余数,有就增加一页,没有就保持原有分页总数112     113         set @TemporaryPageCount = @TemporaryRecordCount/@PageSize114         set @ExtraPageNumber = @TemporaryRecordCount%@PageSize115 116         --如果对总记录条数和页大小取余,结果大于零,分页数量需要加一117         if @ExtraPageNumber > 0118           BEGIN119             set @TemporaryPageCount = @TemporaryPageCount + 1120           END121         ELSE122           BEGIN123             SET @ExtraPageNumber=@PageSize124           END125     126         --//***显示分页127         if @QueryCondition is null or @QueryCondition='' or @QueryCondition='''' or LEN(@QueryCondition)<3     --没有设置显示条件128             begin129                 if @TemporaryPageCount<2 or @PageNumber<=@TemporaryPageCount / 2 + @TemporaryPageCount % 2   --前半部分数据处理130                     begin 131                         if @PageNumber=1132                             set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName+' ORDER BY '+ @SortFields +' '+ @SortTypeB133                         else134                         begin135                             if @EnabledSort=1136                                 begin                    137                                     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 +' '+ @SortTypeB138                                 end139                             else140                                 begin141                                     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 +' '+ @SortTypeB142                                 end143                         end    144                     end145                 else146                     begin147                     set @PageNumber = @TemporaryPageCount-@PageNumber+1 --后半部分数据处理148 149                         if @PageNumber <= 1 --最后一页数据显示150                            begin151                             set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@ExtraPageNumber as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName+' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @ConvertedSortFields +' '+ @SortTypeB152                             end153                         else154                             if @EnabledSort=1155                                 begin156                                     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 +' '+ @SortTypeB157                                 end158                             else159                                 begin160                                     set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName161                                         +' 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 +' '+ @SortTypeB162                                 end163                     end164             end165         else --有查询条件166             begin167                 if @TemporaryPageCount<2 or @PageNumber<=@TemporaryPageCount / 2 + @TemporaryPageCount % 2   --前半部分数据处理168                     begin169                         if @PageNumber=1170                             set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName +' WHERE 1=1 and ' + @QueryCondition + ' ORDER BY '+ @SortFields +' '+ @SortTypeB                        171                         else if(@EnabledSort=1)172                             begin                    173                                 set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName174                                     +' 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 +' '+ @SortTypeB175                             end176                         else177                             begin178                                 set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName179                                     +' 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 +' '+ @SortTypeB180                             end           181                     end182                 else183                     begin 184                         set @PageNumber = @TemporaryPageCount-@PageNumber+1 --后半部分数据处理185                         if @PageNumber <= 1 --最后一页数据显示186                                 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 +' '+ @SortTypeB187                         else if(@EnabledSort=1)188                                 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 +' '+ @SortTypeB189                         else190                                 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 +' '+ @SortTypeB191                     end    192             end193     194     ------返回查询结果-----195     print @SqlTotalCount196     exec sp_executesql @SqlTotalCount197     --SELECT datediff(ms,@ExecuteTime,getdate()) as 耗时198     SET NOCOUNT OFF

复制代码

 


       以上就是存储 过程,代码很简单,都有注释,就不做解释了。

 
三、相关实体类

           因为存储过程代码是在C#代码调用的,为了方便参数的传递,我特意建立一个实体类,用于传递和接受参数,如:总页数和记录条数,类的名称就是:CommonDataResourcePaged。废话不多说,直接上代码,代码如下:

复制代码

 1 /// <summary> 2     /// 通用数据源匪分页 3     /// </summary> 4     public sealed class CommonDataResourcePaged 5     { 6         /// <summary> 7         /// 要显示的表或多个表的连接 8         /// </summary> 9         public string TableName { get; set; }10 11         /// <summary>12         /// 要显示的字段列表13         /// </summary>14         public string FieldList { get; set; }15 16         /// <summary>17         /// 每页显示的记录个数18         /// </summary>19         public int PageSize { get; set; }20 21         /// <summary>22         /// 要显示的那一页的记录的页码23         /// </summary>24         public int PageNumber { get; set; }25 26         /// <summary>27         /// 排序字段列表或条件28         /// </summary>29         public string SortFields { get; set; }30 31         /// <summary>32         /// 排序方法,0为升序,1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如:' SortA Asc,SortB Desc,SortC ')33         /// </summary>34         public bool EnabledSort { get; set; }35 36         /// <summary>37         /// 查询条件,不需where38         /// </summary>39         public string QueryCondition { get; set; }40 41         /// <summary>42         /// 主表的主键43         /// </summary>44         public string Primarykey { get; set; }45 46         /// <summary>47         /// 是否添加查询字段的 DISTINCT 默认0不添加/1添加48         /// </summary>49         public bool EnabledDistinct { get; set; }50 51         /// <summary>52         /// 该字段是从数据库返回来的字段值,在数据库中是Output字段,查询结果分页后的总页数53         /// </summary>54         public int PageCount { get; set; }55 56         /// <summary>57         /// 该字段是从数据库返回来的字段值,在数据库中是Output字段,查询到的记录数58         /// </summary>59         public int RecordCount { get; set; }60     }

复制代码


      以上就是实体类的代码,很简单,就不解释了。

四、Sql Server的调用代码

             在这里,我只演示了在Sql Server 中怎么调用这个存储过程,其实很简单,大家都应该知道。好了,就是这些,代码都是很简单,想调用存储过程,调用实例如下:

 

复制代码

 1 USE [Xxxxxxx.Bbbbbb.Dataware] GO //名称注释掉了 2  3 DECLARE @return_value int,   @PageCount int,   @RecordCount int 4  5 SELECT @PageCount = 0 SELECT @RecordCount = 0 6  7 EXEC @return_value = [dbo].[usp_CommonDataResourcePaged]
   @TableName = N'[literature].[tbl_Literatures]',   
 8 @FieldList = N'[ID],[Title],[Authors],[PeriodicalName],[PublicationDate],[VolumeNumber]',   
 9 @PageSize = 30,   
10 @PageNumber = 50,   
11 @SortFields = [ID],   
12 @EnabledSort =N'1' ,   
13 @QueryCondition = N'[ChiefAuthor]=''ffffff''',   
14 @Primarykey = N'[ID]',  
15 @EnabledDistinct = 1,   
16 @PageCount = @PageCount OUTPUT,   
17 @RecordCount = @RecordCount OUTPUT18 19 SELECT @PageCount as N'@PageCount',   @RecordCount as N'@RecordCount'20 21 SELECT 'Return Value' = @return_value22 23 GO

复制代码


      查询只后就可以看到先关的结果,我就不贴图了。

五、结束

           今天就写到这里,该上班了。对了,我在C#代码中使用IBatis.net,在调用之前需要在配置文件里做相关的设置,比如:传递参数的设置、查询结果映射设置和类型等等的设置,使用起来也是很简单的,就不贴代码了。今天就是这些了,以后有了时间继续我的MongoDB系列之旅吧。

天下国家,可均也;爵禄,可辞也;白刃,可蹈也;中庸不可能也

原文出处

打开App,阅读手记
1人推荐
发表评论
随时随地看视频慕课网APP