适合千万数据查询分页操作的一个通用存储过程
一、引言
最近上班比较忙，所以就很少写东西了，MongoDB系列的文章也要拖后了，没办法，工作第一，没工作就没饭吃了。今天正好的有点时间，就把我最近搞得一些东西，记录下来。
在软件行业，稍微大一点的公司，相关数据的存储量就可能会很大，当我们做系统的时候，一定会使用存储过程进行分页显示，至于为什么分页显示，就不用我多说了吧。最近我在做一个系统，为了让系统支持大数据量，做了很多测试，主要是测试各种分页算法的优劣。最后，经过自己的多番测试，确定了这个存储过程。测试环境是：
硬件环境：CPU是12核的，内存是8G的大小，
软件环境：Visual Studio 2015，数据库是 Sql Server 2008 R2，数据量大概有3千万左右
ORM环境：IBatis.Net
测试效果就不贴图了，如果大家想去测试，可以自行操作，首先要说明的一点，如果针对ID主键进行分页查询，最多是300-400毫秒之间就可以把分页数据显示出来，无论是针对第一页还是最后一页显示时间都是差不多的。再者说，如果是针对其他字段的查询分页，必须针对该字段建立索引，查询速度也在毫秒之间可以完成，经过自己的多轮测试，确定了最终的存储过程的样式。当然如果针对查询的分页的字段没有索引页可以查询出来，时间就比较长了。6分钟，10分钟都有可能。由于今天只讨论存储过程，其他的就不说了，比如分库，分表，水平拆分或者垂直拆分等知识。
二、存储过程代码
当然，这个存储过程也参考了很多前辈的资料，才最终确定的。修改了其原有的Bug，和语法山的一些错误。其他细节也做了一些微调，形成了这个最后的版本，如果大家有更好的写法，也欢迎大家留言讨论。废话不多说了，今天就把代码发出来，该存储过程已经经过测试，完全没有问题，大家可以放心使用，代码如下：
1 USE [XXXXXXXX.BbbbbbbbCcccccccc.Dataware]
2 GO
3 /****** Object: StoredProcedure [dbo].[usp_CommonDataResourcePaged] Script Date: 2018/4/20 9:01:14 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 -- =============================================
9 -- Author: <PatrickLiu>
10 -- Create date: <2018-4-16 14:21>
11 -- Description: <通用的数据分页存储过程>
12 -- =============================================
13 ALTER PROCEDURE [dbo].[usp_CommonDataResourcePaged]
14 (
15 @TableName nvarchar(200), ----要显示的表或多个表的连接
16 @FieldList nvarchar(1500) = '*', ----要显示的字段列表
17 @PageSize int = 20, ----每页显示的记录个数
18 @PageNumber int = 1, ----要显示那一页的记录
19 @SortFields nvarchar(1000) = null, ----排序字段列表或条件
20 @EnabledSort bit = 0, ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')
21 @QueryCondition nvarchar(1500) = null, ----查询条件,不需WHERE
22 @Primarykey nvarchar(50), ----主表的主键
23 @EnabledDistinct bit = 0, ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
24 @PageCount int = 1 output, ----查询结果分页后的总页数
25 @RecordCount int = 1 output ----查询到的记录数
26 )
27 AS
28 SET NOCOUNT ON
29 Declare @SqlResult nvarchar(1000) ----存放动态生成的SQL语句
30 Declare @SqlTotalCount nvarchar(1000) ----存放取得查询结果总数的查询语句
31 Declare @SqlStartOrEndID nvarchar(1000) ----存放取得查询开头或结尾ID的查询语句
32
33 Declare @SortTypeA nvarchar(10) ----数据排序规则A
34 Declare @SortTypeB nvarchar(10) ----数据排序规则B
35
36 Declare @SqlDistinct nvarchar(50) ----对含有DISTINCT的查询进行SQL构造
37 Declare @SqlCountDistinct nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造
38
39 declare @timediff datetime --耗时测试时间差
40 SELECT @timediff=getdate()
41
42 if @EnabledDistinct = 0
43 begin
44 set @SqlDistinct = 'SELECT '
45 set @SqlCountDistinct = 'Count(*)'
46 end
47 else
48 begin
49 set @SqlDistinct = 'SELECT DISTINCT '
50 set @SqlCountDistinct = 'Count(DISTINCT '+@Primarykey+')'
51 end
52
53 if @EnabledSort=0
54 begin
55 set @SortTypeB=' ASC '
56 set @SortTypeA=' DESC '
57 end
58 else
59 begin
60 set @SortTypeB=' DESC '
61 set @SortTypeA=' ASC '
62 end
63
64 --------生成查询语句--------
65 --此处@SqlTotalCount为取得查询结果数量的语句
66 if @QueryCondition is null or @QueryCondition='' --没有设置显示条件
67 begin
68 set @SqlResult = @FieldList + ' From ' + @TableName
69 set @SqlTotalCount = @SqlDistinct+' @RecordCount='+@SqlCountDistinct+' FROM '+@TableName
70 set @SqlStartOrEndID = ' From ' + @TableName
71 end
72 else
73 begin
74 set @SqlResult = + @FieldList + ' From ' + @TableName + ' WHERE (1>0) and ' + @QueryCondition
75 set @SqlTotalCount = @SqlDistinct+' @RecordCount='+@SqlCountDistinct+' FROM '+@TableName + ' WHERE (1>0) and ' + @QueryCondition
76 set @SqlStartOrEndID = ' From ' + @TableName + ' WHERE (1>0) and ' + @QueryCondition
77 end
78
79 ----取得查询结果总数量-----
80 exec sp_executesql @SqlTotalCount,N'@RecordCount int out ',@RecordCount out
81
82 declare @TemporaryCount int --临时统计
83 if @RecordCount = 0
84 set @TemporaryCount = 1
85 else
86 set @TemporaryCount = @RecordCount
87
88 --取得分页总数
89 set @PageCount=(@TemporaryCount+@PageSize-1)/@PageSize
90
91 /**当前页大于总页数 取最后一页**/
92 if @PageNumber>@PageCount
93 set @PageNumber=@PageCount
94
95 --/*-----数据分页2分处理-------*/
96 declare @pageIndex int --总数/页大小
97 declare @lastcount int --总数%页大小
98
99 set @pageIndex = @TemporaryCount/@PageSize
100 set @lastcount = @TemporaryCount%@PageSize
101 if @lastcount > 0
102 set @pageIndex = @pageIndex + 1
103 else
104 set @lastcount = @pagesize
105
106 --//***显示分页
107 if @QueryCondition is null or @QueryCondition='' --没有设置显示条件
108 begin
109 if @pageIndex<2 or @PageNumber<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
110 begin
111 if @PageNumber=1
112 set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName+' ORDER BY '+ @SortFields +' '+ @SortTypeB
113 else
114 begin
115 if @EnabledSort=1
116 begin
117 set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName
118 +' WHERE '+@Primarykey+' <(SELECT MIN('+ @Primarykey +') FROM ('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-1) as Varchar(20)) +' '+ @Primarykey +' FROM '+@TableName
119 +' ORDER BY '+ @SortFields +' '+ @SortTypeB+') AS TBMinID)'+' ORDER BY '+ @SortFields +' '+ @SortTypeB
120 end
121 else
122 begin
123 set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName
124 +' WHERE '+@Primarykey+' >(SELECT MAX('+ @Primarykey +') FROM ('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-1) as Varchar(20)) +' '+ @Primarykey +' FROM '+@TableName
125 +' ORDER BY '+ @SortFields +' '+ @SortTypeB+') AS TBMinID)'+' ORDER BY '+ @SortFields +' '+ @SortTypeB
126 end
127 end
128 end
129 else
130 begin
131 set @PageNumber = @pageIndex-@PageNumber+1 --后半部分数据处理
132 if @PageNumber <= 1 --最后一页数据显示
133 set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@lastcount as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName
134 +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @SortFields +' '+ @SortTypeB
135 else
136 if @EnabledSort=1
137 begin
138 set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName
139 +' WHERE '+@Primarykey+' >(SELECT MAX('+ @Primarykey +') FROM('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-2)+@lastcount as Varchar(20)) +' '+ @Primarykey +' FROM '+@TableName
140 +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TBMaxID)'
141 +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @SortFields +' '+ @SortTypeB
142 end
143 else
144 begin
145 set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName
146 +' WHERE '+@Primarykey+' <(SELECT MIN('+ @Primarykey +') FROM('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-2)+@lastcount as Varchar(20)) +' '+ @Primarykey +' FROM '+@TableName
147 +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TBMaxID)'
148 +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @SortFields +' '+ @SortTypeB
149 end
150 end
151 end
152
153 else --有查询条件
154 begin
155 if @pageIndex<2 or @PageNumber<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
156 begin
157 if @PageNumber=1
158 set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName
159 +' WHERE 1=1 and ' + @QueryCondition + ' ORDER BY '+ @SortFields +' '+ @SortTypeB
160 else if(@EnabledSort=1)
161 begin
162 set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName
163 +' WHERE '+@Primarykey+' <(SELECT MIN('+ @Primarykey +') FROM ('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-1) as Varchar(20)) +' '+ @Primarykey +' FROM '+@TableName
164 +' WHERE (1=1) and ' + @QueryCondition +' ORDER BY '+ @SortFields +' '+ @SortTypeB+') AS TBMinID)'+' ORDER BY '+ @SortFields +' '+ @SortTypeB
165 end
166 else
167 begin
168 set @SqlTotalCount=@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName
169 +' WHERE '+@Primarykey+' >(SELECT MAX('+ @Primarykey +') FROM ('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-1) as Varchar(20)) +' '+ @Primarykey +' FROM '+@TableName
170 +' WHERE (1=1) and ' + @QueryCondition +' ORDER BY '+ @SortFields +' '+ @SortTypeB+') AS TBMinID)'+' ORDER BY '+ @SortFields +' '+ @SortTypeB
171 end
172 end
173 else
174 begin
175 set @PageNumber = @pageIndex-@PageNumber+1 --后半部分数据处理
176 if @PageNumber <= 1 --最后一页数据显示
177 set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@lastcount as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName
178 +' WHERE (1=1) and '+ @QueryCondition +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @SortFields +' '+ @SortTypeB
179 else if(@EnabledSort=1)
180 set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName
181 +' WHERE '+@Primarykey+' >(SELECT MAX('+ @Primarykey +') FROM('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-2)+@lastcount as Varchar(20)) +' '+ @Primarykey +' FROM '+@TableName
182 +' WHERE (1=1) and '+ @QueryCondition +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TBMaxID)'+' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @SortFields +' '+ @SortTypeB
183 else
184 set @SqlTotalCount=@SqlDistinct+' * FROM ('+@SqlDistinct+' TOP '+ CAST(@PageSize as VARCHAR(4))+' '+ @FieldList+' FROM '+@TableName
185 +' WHERE '+@Primarykey+' <(SELECT MIN('+ @Primarykey +') FROM('+ @SqlDistinct+' TOP '+ CAST(@PageSize*(@PageNumber-2)+@lastcount as Varchar(20)) +' '+ @Primarykey +' FROM '+@TableName
186 +' WHERE (1=1) and '+ @QueryCondition +' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TBMaxID)'+' ORDER BY '+ @SortFields +' '+ @SortTypeA+') AS TempTB'+' ORDER BY '+ @SortFields +' '+ @SortTypeB
187 end
188 end
189
190 ------返回查询结果-----
191 exec sp_executesql @SqlTotalCount
192 --SELECT datediff(ms,@timediff,getdate()) as 耗时
193 print @SqlTotalCount
194 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 /// 查询条件,不需where
38 /// </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 OUTPUT
18
19 SELECT @PageCount as N'@PageCount', @RecordCount as N'@RecordCount'
20
21 SELECT 'Return Value' = @return_value
22
23 GO
查询只后就可以看到先关的结果，我就不贴图了。
五、结束
今天就写到这里，该上班了。对了，我在C#代码中使用IBatis.net，在调用之前需要在配置文件里做相关的设置，比如：传递参数的设置、查询结果映射设置和类型等等的设置，使用起来也是很简单的，就不贴代码了。今天就是这些了，以后有了时间继续我的MongoDB系列之旅吧。
当数据量达到千万级时, 即便是有索引, 得到总页数, 也是比较消耗资源的事情, 查询起来可能要消耗好几秒, 这个数量级就不建议使用关系数据库了
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
