sql千万级数据库分页与性能优化分析

作者:袖梨 2022-06-29
在之前也有很多人问类似这样的问题,回复这样的问题,我们一般会从索引,水平分区,垂直分区和硬件的升级等方面考虑。
 
 分析
 
对于千万级数据的分页,要求在秒级内响应,解决方案除了刚列的几个方面考虑,这里说一个非常重要的考虑(评估)是,现实意义。
拿CSDN论坛的那1690万数据来说,我按每页显示200行数据,需要84500页。从站在用户(使用者)角度看,对于查看1万页以后的数据的概率是非常小。假设我们是使用者,让我们一页一页的点,点到100页都够郁闷的了,更何况是1万页后的数据了。这里从现实意义角度考虑,1万页以后的现实意义有多大?
 
 解决方案
 
 根据从现实意义角度分析,对千万级数据的分页,我们呈现给用户的,首先考虑的是用户最关心的,对用户来最有价值的信息。对于过期,没意义的数据需要考虑不呈现给用户。因为呈现没意义的数据,对用户来说多余,对后台服务器来说负荷,严重的可能会导致服务器瘫痪。
 在大型网站都有这方面的考虑,控制呈现的总页数,下面我们列出几个网站:
 
 
搜狗最多显示100页:  
 
 
 
 百度最多显示76页,其实你点到76页,显示第75页,感觉这位置有问题,而且相关结果就是1亿个。这里有玩文字的,使用了一个“约”,云里雾里的,蒙人呀。
很多时候你稍注意下,都是返回1亿个,O(∩_∩)O~。
 
 
测试Google,大概分页最大页范围是71-79页,它好一点就是没像百度笼统用“1亿”。
 
 
 淘宝的宝贝搜索最大页是200页
 
 
小结
 

 从前边的问题到分析,及解决方案,我们可以了解对千万级数据分页的处理,最重要的一个评估方面就是,现实意义,需要多站在使用者角度分析问题,参考成功案例,从而找到可行的解决方案。

看个实例

 代码如下 复制代码

if (object_id(N'syspr_TablePaginationSearch',N'P') is not null)
drop procedure dbo.syspr_TablePaginationSearch
go
create procedure syspr_TablePaginationSearch
(
@SelectField nvarchar(512)='*'     -- Select语句字段,调用的时候不要输入"select"
,@FormTables nvarchar(512)      -- Form子句,表名,包括架构名,调用的时候不要输入"from"
,@WhereField nvarchar(512)=null     -- Where语句、判断字段,调用的时候不要输入"where"
-----------------------------------------------------
,@OverPartitionField nvarchar(512)=null   -- 分区字段,
,@OverOrderField nvarchar(512)     -- 分页字段,主要排序字段,必须
,@GroupField nvarchar(512)=null     -- 分组字段
,@HavingField nvarchar(512)=null    -- 分组判断条件
,@OrderField nvarchar(512)=null     -- 排序字段
-----------------------------------------------------
,@PageSize int=10        -- 页长
,@Page int=1         -- 页
-----------------------------------------------------
,@ErrorProcedure nvarchar(128)=null output -- 错误存储过程名
,@ErrorNumber int=0 output      -- 错误编号
,@ErrorMessage nvarchar(512)=null output -- 错误信息
)
as
begin
--定义第一行记录
declare @firstRow int;
--定义最后一条记录
declare @lastRow int;--设置第一条记录
set @firstRow=((@Page-1)*@PageSize)+1;
--设置最后一条记录
set @lastRow=@firstRow+@PageSize;--定义查询SQL字段
declare @sqlString nvarchar(512);
--定义Select语句,内查询使用
declare @selectString nvarchar(512);
--定义Where字段,内查询使用
declare @whereString nvarchar(512);
--定义Form子句,内查询使用
declare @formString nvarchar(512);
--定义分组字段,内查询使用
declare @groupString nvarchar(512);
--定义Having子句,内查询使用
declare @havingString nvarchar(512);
--定义排序字段,外查询使用
declare @OrderString nvarchar(512);--组装Select子句-----------------------------------
set @SelectField=isnull(@SelectField,N' * ');
if(@SelectField='')
   set @SelectField=N' *' ;
--组装select子句到row_number()
set @selectString=N' select '+ @SelectField+ ', Row_Number() over( ';
--组装到分区partition by 表达式
set @OverPartitionField=isnull(@OverPartitionField,N'');
if (@OverPartitionField<>'')
   set @selectString= @selectString+ ' partition by '+ @OverPartitionField;
--组装到排序分页order by 表达式
set @OverOrderField=isnull(@OverOrderField,N'');
if (@OverOrderField<>'')
   set @selectString= @selectString+ ' order by '+ @OverOrderField+ N') as RowNumber ';
--over---------------------------------------------
--组装Form子句------------------------------------
set @formString= N' from '+ @FormTables;
--组装Where判断子句------------------------------
set @WhereField=isnull(@WhereField,N'');
if(@WhereField<>'')
   set @whereString=N' where '+ @WhereField;
else
   set @whereString=N'';
--where判断子句组装完毕-------------------------
--组装group by分组子句--------------------------
set @GroupField=isnull(@GroupField,N'');
if (@GroupField<>'')
begin
   set @groupString= N' group by '+ @GroupField;   --组装having分组判断条件
   set @HavingField= isnull(@HavingField,N'');
   if (@HavingField<> N'')
    set @havingString= N' having '+ @HavingField;
   else
    set @havingString=N'';
end
else
begin
   set @groupString= N'';
   set @havingString=N'';
end
--over-------------------------------------------
--组装order by外排序子句-----------------------
set @OrderField=isnull(@OrderField,N'');
if (@OrderField<>'')
   set @OrderString= N' order by '+ @OverOrderField+ N' , '+ @OrderField;
else
   set @OrderString= N'';
--如果@groupString不为空,则外排序不起作用
if (@groupString<>N'')
   set @OrderString=N'';
--over-------------------------------------------
--组装分页查询语句
set @sqlString=
   N'select * '+
   N'from '+
   N' ( '+
    @selectString+
    @formString+
    @whereString+
    @groupString+
    @havingString+
   N' ) as TB '+
   N'where '+
   N' TB.RowNumber>='+cast(@firstRow as nvarchar(10))+
   N' and TB.RowNumber<'+cast(@lastRow as nvarchar(10))+
   @orderString
--over-------------------------------------------
--执行SQL,返回影响行数;如有异常,则抛出,并赋值输出参数
begin try
   exec (@sqlString);
   return @@rowcount;
end try
begin catch
   set @ErrorProcedure=Error_Procedure();
   set @ErrorNumber=error_number();
   set @ErrorMessage=error_message();
end catch;end
go-----------------------------------------------------------
--简单的测试,查询AdventureWorks库的Person.Address表。
--以addressid字段顺序分页、并按PostalCode ,StateProvinceID等字段排序。exec dbo.syspr_TablePaginationSearch
@SelectField='*'
,@FormTables='AdventureWorks.Person.Address'
,@whereField='addressid>100'
,@OverOrderField='addressid asc'
,@OrderField='PostalCode ,StateProvinceID'
,@Page=1
,@PageSize=10
go
--简单的测试,分组统计addressid字段,并分页显示结果集。exec dbo.syspr_TablePaginationSearch
@SelectField='count(addressid) as addressCount,city'
,@FormTables='AdventureWorks.Person.Address'
,@OverOrderField='count(addressid) desc'
,@GroupField='city'
,@Page=1
,@PageSize=10
go
--连接查询并分页
exec dbo.syspr_TablePaginationSearch
@SelectField=N'
   addr.AddressLine1,
   addr.AddressLine2,
   addr.City,
   sp.Name as ProvinceName,
   addr.PostalCode,
   addr.rowguid,
   addr.ModifiedDate'
,@FormTables=N'
   AdventureWorks.Person.Address as addr
   inner join
   AdventureWorks.Person.StateProvince as sp
    on addr.StateProvinceID=sp.StateProvinceID'
,@OverOrderField='addressid asc'
,@Page=1
,@PageSize=10
go

相关文章

精彩推荐