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

发布时间：2020-12-16 01:18:06 所属栏目：百科来源：网络整理

导读：一、引言 ?????????????? 最近上班比较忙，所以就很少写东西了，MongoDB系列的文章也要拖后了，没办法，工作第一，没工作就没饭吃了。今天正好的有点时间，就把我最近搞得一些东西，记录下来。 ?????????????? 在软件行业，稍微大一点的公司，相关数据的存储

一、引言

?????????????? 最近上班比较忙，所以就很少写东西了，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   5   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          5000) = '*',1)">--要显示的字段列表
 15     @PageSize           int = 20,1)">--每页显示的记录个数
 16     @PageNumber         1,1)">--要显示那一页的记录
 17     @SortFields         1000) = null,1)">--排序字段列表或条件
 18     @EnabledSort        bit 0,1)">--排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')
 19     @QueryCondition     nvarchar(1500) --查询条件,不需WHERE
 20     @Primarykey         50),1)">--主表的主键
 21     @EnabledDistinct    --是否添加查询字段的 DISTINCT 默认0不添加/1添加
 22     @PageCount          1 output,1)">--查询结果分页后的总页数
 23     @RecordCount        1 output           --查询到的记录数
 24 )
 25 AS
 26     SET NOCOUNT  27     Declare @PrimaryKeyAlias 50 28     @SqlResult 4000)        --存放动态生成的SQL语句
 29     @SqlTotalCount --存放取得查询结果总数的查询语句
 30     @SqlStartOrEndID  3000)        --存放取得查询开头或结尾ID的查询语句
 31     
 32     @SortTypeA 10)    --数据排序规则A
 33     @SortTypeB --数据排序规则B
 34     
 35     @SqlDistinct 50)         --对含有DISTINCT的查询进行SQL构造
 36     @SqlCountDistinct 50)          --对含有DISTINCT的总数查询进行SQL构造
 37     
 38     declare @ExecuteTime datetime  耗时测试时间差
 39 
 40     如果字段名带有表名或者其他的前缀(literature.tbl_Literatures.ID)，通过(select * from table)显示后，在排序，此时的字段名是不包含前缀的，
 41     所以要处理一下，该方法适合Sql Server，如果要迁移其他数据库上，可以修改获取字段名的方法
 42     DECLARE @ConvertedSortFields  43     IF CHARINDEX(.@SortFields)>0
 44       BEGIN
 45         SELECT @ConvertedSortFields=REVERSE(SUBSTRING(REVERSE(@SortFields),@SortFields))-1))
 46       END
 47     ELSE
 48        49         SET =@SortFields
 50        51     @ExecuteTimeGETDATE()
 52 
 53     在分页的过程中，会涉及到子查询，当子查询根据主键查询后，外层循环再使用该值的时候已经不是第一次赋的值了，此处要做转换，否则会有错误
 54     @PrimaryKeyAliasCustomID'
 55     
 56     if @EnabledDistinct   57         begin
 58             set @SqlDistinct SELECT  59             @SqlCountDistinct Count(*) 60         end
 61     else
 62          63             SELECT DISTINCT  64             Count(DISTINCT '+@Primarykey+) 65          66     
 67     @EnabledSort= 68          69             @SortTypeB ASC  70             @SortTypeA DESC  71          72      73          74              75              76          77     
 78     ------生成查询语句--------
 79     此处@SqlTotalCount为取得查询结果数量的语句
 80     @QueryCondition is null or @QueryCondition'' '''' or LEN(@QueryCondition)<3   没有设置显示条件
 81          82             @SqlResult =  @FieldList +  From ' + @TableName
 83             @SqlTotalCount @SqlDistinct @RecordCount=@SqlCountDistinct FROM  84             @SqlStartOrEndID  85          86      87          88             @TableName  WHERE (1>0) and @QueryCondition
 89              90              91          92     
 93     --取得查询结果总数量-----
 94     exec sp_executesql @SqlTotalCount,N@RecordCount int out @RecordCount out
 95 
 96     @TemporaryRecordCount int 临时统计
 97     @RecordCount  98         @TemporaryRecordCount 1
 99     100         @RecordCount
101     
102         取得分页总数
103         @PageCount=(@TemporaryRecordCount@PageSize1)/@PageSize
104     
105         *当前页大于总页数 取最后一页*106         @PageNumber>@PageCount
107             108     
109         /*-----数据分页2分处理-------*/
110         @TemporaryPageCount 总数/页大小，临时存储总的分页数据
111         @ExtraPageNumber 总数%页大小 此处用于判断是否有余数，有就增加一页，没有就保持原有分页总数
112     
113         @TemporaryPageCount 114         @ExtraPageNumber %115 
116         如果对总记录条数和页大小取余，结果大于零，分页数量需要加一
117         > 118           119             + 120           121         122           123             @ExtraPageNumber124           125     
126         //***显示分页
127         3     128             129                 @TemporaryPageCount2 <=/ % 2   前半部分数据处理
130                     begin 
131                         132                             @SqlTotalCount TOP CAST(@PageSize as VARCHAR(4))' @FieldList@TableName ORDER BY @SortFields @SortTypeB
133                         134                         135                             136                                 begin                    
137                                     @TableName     WHERE  <(SELECT MIN(@PrimaryKeyAlias ) FROM (*(1) Varchar(20)) @Primarykey  AS ) AS TBMinID)138                                 139                             140                                 141                                      >(SELECT MAX(142                                 143                         end    
144                     145                 146                     147                     @PageNumber -+1 后半部分数据处理
148 
149                         <= 最后一页数据显示
150                            151                              * FROM () AS TempTB@ConvertedSortFields 152                             153                         154                             155                                 156                                     ) FROM(2)) AS TBMaxID)157                                 158                             159                                 160                                     161                                         162                                 163                     164             165         else 有查询条件
166             167                 168                     169                         170                              WHERE 1=1 and @QueryCondition @SortTypeB                        
171                         if(172                             173                                 174                                      WHERE (1=1) and  and 175                             176                         177                             178                                 179                                     180                             end           
181                     182                 183                     184                         185                         186                                 187                         188                                 189                         190                                 191                     192             193     
194     ----返回查询结果-----
195     print @SqlTotalCount
196     197     SELECT datediff(ms,@ExecuteTime,getdate()) as 耗时
198     OFF

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

?
三、相关实体类

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

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

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

四、Sql Server的调用代码

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

 1 USE Xxxxxxx.Bbbbbb.Dataware] GO //名称注释掉了
 2 
 3 @return_value int,1)">@PageCount @RecordCount int
 4 
@PageCount 0  6 
 7 EXEC @return_value ]

   @TableName = N[literature].[tbl_Literatures]', 8 [ID],[Title],[Authors],[PeriodicalName],[PublicationDate],[VolumeNumber] 9 @PageSize 3010 11 ID]12 @EnabledSort =N113 [ChiefAuthor]=''ffffff'''14 [ID]15 @EnabledDistinct 16 @PageCount OUTPUT,1)">17  OUTPUT
18 
19 as N@PageCount@RecordCount21 SELECT Return Value@return_value
22 
23 GO

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

五、结束

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!