MYSQL网页编程之年夜数据量分页存储历程效力测试附测试代...
不可否认，MySQL也是一个很好的关系型数据库，或许在技术上它与其他领先的关系数据库相差并不大，或不具有劣势。但是，对于一些企业环境来说，MySQL显然不具有优势。测试情况硬件：CPU酷睿双核T5750内存：2G
软件:Windowsserver2003+sqlserver2005
OK,我们起首创立一数据库：data_Test,并在此数据库中创立一表：tb_TestTable
复制代码代码以下:
createdatabasedata_Test--创立数据库
data_Test
GO
usedata_Test
GO
createtabletb_TestTable--创立表
(idintidentity(1,1)primarykey,
userNamenvarchar(20)notnull,
userPWDnvarchar(20)notnull,
userEmailnvarchar(40)null)
GO
然后我们在数据表中拔出2000000条数据:
复制代码代码以下:
--拔出数据
setidentity_inserttb_TestTableon
declare@countint
set@count=1
while@count<=2000000
begin
insertintotb_TestTable(id,userName,userPWD,userEmail)values(@count,admin,admin888,lli0077@yahoo.com.cn)
set@count=@count+1
end
setidentity_inserttb_TestTableoff
我起首写了五个经常使用存储历程：
1，使用selecttop和selectnotin举行分页，详细代码以下：
复制代码代码以下:createprocedureproc_paged_with_notin--使用selecttopandselectnotin
(
@pageIndexint,--页索引
@pageSizeint--每页纪录数
)
as
begin
setnocounton;
declare@timediffdatetime--耗时
declare@sqlnvarchar(500)
select@timediff=Getdate()
set@sql=selecttop+str(@pageSize)+*fromtb_TestTablewhere(IDnotin(selecttop+str(@pageSize*@pageIndex)+idfromtb_TestTableorderbyIDASC))orderbyID
execute(@sql)--因selecttop后不支技间接接参数，以是写成了字符串@sql
selectdatediff(ms,@timediff,GetDate())as耗时
setnocountoff;
end
2,使用selecttop和selectmax(列键)
复制代码代码以下:createprocedureproc_paged_with_selectMax--使用selecttopandselectmax(列)
(
@pageIndexint,--页索引
@pageSizeint--页纪录数
)
as
begin
setnocounton;
declare@timediffdatetime
declare@sqlnvarchar(500)
select@timediff=Getdate()
set@sql=selecttop+str(@pageSize)+*Fromtb_TestTablewhere(ID>(selectmax(id)From(selecttop+str(@pageSize*@pageIndex)+idFromtb_TestTableorderbyID)asTempTable))orderbyID
execute(@sql)
selectdatediff(ms,@timediff,GetDate())as耗时
setnocountoff;
end
3,使用selecttop和两头变量--此办法因网上有人说效果最好，以是贴出来一同测试
复制代码代码以下:createprocedureproc_paged_with_Midvar--使用ID>最年夜ID值和两头变量
(
@pageIndexint,
@pageSizeint
)
as
declare@countint
declare@IDint
declare@timediffdatetime
declare@sqlnvarchar(500)
begin
setnocounton;
select@count=0,@ID=0,@timediff=getdate()
select@count=@count+1,@ID=casewhen@count<=@pageSize*@pageIndexthenIDelse@IDendfromtb_testTableorderbyid
set@sql=selecttop+str(@pageSize)+*fromtb_testTablewhereID>+str(@ID)
execute(@sql)
selectdatediff(ms,@timediff,getdate())as耗时
setnocountoff;
end
4,使用Row_number()此办法为sqlserver2005中新的办法,使用Row_number()给数据行加上索引
复制代码代码以下:createprocedureproc_paged_with_Rownumber--使用SQL2005中的Row_number()
(
@pageIndexint,
@pageSizeint
)
as
declare@timediffdatetime
begin
setnocounton;
select@timediff=getdate()
select*from(select*,Row_number()over(orderbyIDasc)asIDRankfromtb_testTable)asIDWithRowNumberwhereIDRank>@pageSize*@pageIndexandIDRank<@pageSize*(@pageIndex+1)
selectdatediff(ms,@timediff,getdate())as耗时
setnocountoff;
end
5，使用一时表及Row_number
复制代码代码以下:createprocedureproc_CTE--使用一时表及Row_number
(
@pageIndexint,--页索引
@pageSizeint--页纪录数
)
as
setnocounton;
declare@ctestrnvarchar(400)
declare@strSqlnvarchar(400)
declare@datediffdatetime
begin
select@datediff=GetDate()
set@ctestr=withTable_CTEas
(selectceiling((Row_number()over(orderbyIDASC))/+str(@pageSize)+)aspage_num,*fromtb_TestTable);
set@strSql=@ctestr+select*FromTable_CTEwherepage_num=+str(@pageIndex)
end
begin
executesp_executesql@strSql
selectdatediff(ms,@datediff,GetDate())
setnocountoff;
end
OK,至此，存储历程创立终了，我们分离在每页10条数据的情形下在第2页，第1000页，第10000页，第100000页，第199999页举行测试，耗时单元：ms每页测试5次取其均匀值
存过第2页耗时第1000页耗时第10000页耗时第100000页耗时第199999页耗时效力排行
1用notin0ms16ms47ms475ms953ms3
2用selectmax5ms16ms35ms325ms623ms1
3两头变量966ms970ms960ms945ms933ms5
4row_number0ms0ms34ms365ms710ms2
4一时表780ms796ms798ms780ms805ms4
测试了局显现：selectmax>row_number>notin>一时表>两头变量
因而我对效力最高的selectmax办法用2分法举行了扩大，代码取自互联网，我修正了ASC排序时取不到值的BUG，测试了局:
2分法156ms156ms180ms470ms156ms1*
从测试了局来看，利用2分法的确能够进步效力并使效力更加不乱，我又增添了第159999页的测试，用时仅296ms，效果相称的不错！
上面是2分法利用selectmax的代码，已相称完美。
复制代码代码以下:
--/*-----存储历程分页处置孙伟2005-03-28创立-------*/
--/*-----存储历程分页处置浪尘2008-9-1修正----------*/
--/*-----对数据举行了2分处置使查询前半部分数据与查询后半部分数据功能不异-------*/
alterPROCEDUREproc_paged_2part_selectMax
(
@tblNamenvarchar(200),----要显现的表或多个表的毗连
@fldNamenvarchar(500)=*,----要显现的字段列表
@pageSizeint=10,----每页显现的纪录个数
@pageint=1,----要显现那一页的纪录
@fldSortnvarchar(200)=null,----排序字段列表或前提
@Sortbit=0,----排序办法，0为升序，1为降序(假如是多字段分列Sort指代最初一个排序字段的分列按次(最初一个排序字段不加排序标志)--程序传参如：SortAAsc,SortBDesc,SortC)
@strConditionnvarchar(1000)=null,----查询前提,不需where
@IDnvarchar(150),----主表的主键
@Distbit=0,----是不是增加查询字段的DISTINCT默许0不增加/1增加
@pageCountint=1output,----查询了局分页后的总页数
@Countsint=1output----查询到的纪录数
)
AS
SETNOCOUNTON
Declare@sqlTmpnvarchar(1000)----寄存静态天生的SQL语句
Declare@strTmpnvarchar(1000)----寄存获得查询了局总数的查询语句
Declare@strIDnvarchar(1000)----寄存获得查询开首或开头ID的查询语句
Declare@strSortTypenvarchar(10)----数据排序划定规矩A
Declare@strFSortTypenvarchar(10)----数据排序划定规矩B
Declare@SqlSelectnvarchar(50)----对含有DISTINCT的查询举行SQL机关
Declare@SqlCountsnvarchar(50)----对含有DISTINCT的总数查询举行SQL机关
declare@timediffdatetime--耗时测试工夫差
select@timediff=getdate()
if@Dist=0
begin
set@SqlSelect=select
set@SqlCounts=Count(*)
end
else
begin
set@SqlSelect=selectdistinct
set@SqlCounts=Count(DISTINCT+@ID+)
end
if@Sort=0
begin
set@strFSortType=ASC
set@strSortType=DESC
end
else
begin
set@strFSortType=DESC
set@strSortType=ASC
end
--------天生查询语句--------
--此处@strTmp为获得查询了局数目的语句
if@strConditionisnullor@strCondition=--没有设置显现前提
begin
set@sqlTmp=@fldName+From+@tblName
set@strTmp=@SqlSelect+@Counts=+@SqlCounts+FROM+@tblName
set@strID=From+@tblName
end
else
begin
set@sqlTmp=+@fldName+From+@tblName+where(1>0)+@strCondition
set@strTmp=@SqlSelect+@Counts=+@SqlCounts+FROM+@tblName+where(1>0)+@strCondition
set@strID=From+@tblName+where(1>0)+@strCondition
end
----获得查询了局总数目-----
execsp_executesql@strTmp,N@Countsintout,@Countsout
declare@tmpCountsint
if@Counts=0
set@tmpCounts=1
else
set@tmpCounts=@Counts
--获得分页总数
set@pageCount=(@tmpCounts+@pageSize-1)/@pageSize
/**//**//**//**以后页年夜于总页数取最初一页**/
if@page>@pageCount
set@page=@pageCount
--/*-----数据分页2分处置-------*/
declare@pageIndexint--总数/页巨细
declare@lastcountint--总数%页巨细
set@pageIndex=@tmpCounts/@pageSize
set@lastcount=@tmpCounts%@pageSize
if@lastcount>0
set@pageIndex=@pageIndex+1
else
set@lastcount=@pagesize
--//***显现分页
if@strConditionisnullor@strCondition=--没有设置显现前提
begin
if@pageIndex<2or@page<=@pageIndex/2+@pageIndex%2--前半部分数据处置
begin
if@page=1
set@strTmp=@SqlSelect+top+CAST(@pageSizeasVARCHAR(4))++@fldName+from+@tblName
+orderby+@fldSort++@strFSortType
else
begin
if@Sort=1
begin
set@strTmp=@SqlSelect+top+CAST(@pageSizeasVARCHAR(4))++@fldName+from+@tblName
+where+@ID+<(selectmin(+@ID+)from(+@SqlSelect+top+CAST(@pageSize*(@page-1)asVarchar(20))++@ID+from+@tblName
+orderby+@fldSort++@strFSortType+)ASTBMinID)
+orderby+@fldSort++@strFSortType
end
else
begin
set@strTmp=@SqlSelect+top+CAST(@pageSizeasVARCHAR(4))++@fldName+from+@tblName
+where+@ID+>(selectmax(+@ID+)from(+@SqlSelect+top+CAST(@pageSize*(@page-1)asVarchar(20))++@ID+from+@tblName
+orderby+@fldSort++@strFSortType+)ASTBMinID)
+orderby+@fldSort++@strFSortType
end
end
end
else
begin
set@page=@pageIndex-@page+1--后半部分数据处置
if@page<=1--最初一页数据显现
set@strTmp=@SqlSelect+*from(+@SqlSelect+top+CAST(@lastcountasVARCHAR(4))++@fldName+from+@tblName
+orderby+@fldSort++@strSortType+)ASTempTB+orderby+@fldSort++@strFSortType
else
if@Sort=1
begin
set@strTmp=@SqlSelect+*from(+@SqlSelect+top+CAST(@pageSizeasVARCHAR(4))++@fldName+from+@tblName
+where+@ID+>(selectmax(+@ID+)from(+@SqlSelect+top+CAST(@pageSize*(@page-2)+@lastcountasVarchar(20))++@ID+from+@tblName
+orderby+@fldSort++@strSortType+)ASTBMaxID)
+orderby+@fldSort++@strSortType+)ASTempTB+orderby+@fldSort++@strFSortType
end
else
begin
set@strTmp=@SqlSelect+*from(+@SqlSelect+top+CAST(@pageSizeasVARCHAR(4))++@fldName+from+@tblName
+where+@ID+<(selectmin(+@ID+)from(+@SqlSelect+top+CAST(@pageSize*(@page-2)+@lastcountasVarchar(20))++@ID+from+@tblName
+orderby+@fldSort++@strSortType+)ASTBMaxID)
+orderby+@fldSort++@strSortType+)ASTempTB+orderby+@fldSort++@strFSortType
end
end
end
else--有查询前提
begin
if@pageIndex<2or@page<=@pageIndex/2+@pageIndex%2--前半部分数据处置
begin
if@page=1
set@strTmp=@SqlSelect+top+CAST(@pageSizeasVARCHAR(4))++@fldName+from+@tblName
+where1=1+@strCondition+orderby+@fldSort++@strFSortType
elseif(@Sort=1)
begin
set@strTmp=@SqlSelect+top+CAST(@pageSizeasVARCHAR(4))++@fldName+from+@tblName
+where+@ID+<(selectmin(+@ID+)from(+@SqlSelect+top+CAST(@pageSize*(@page-1)asVarchar(20))++@ID+from+@tblName
+where(1=1)+@strCondition+orderby+@fldSort++@strFSortType+)ASTBMinID)
++@strCondition+orderby+@fldSort++@strFSortType
end
else
begin
set@strTmp=@SqlSelect+top+CAST(@pageSizeasVARCHAR(4))++@fldName+from+@tblName
+where+@ID+>(selectmax(+@ID+)from(+@SqlSelect+top+CAST(@pageSize*(@page-1)asVarchar(20))++@ID+from+@tblName
+where(1=1)+@strCondition+orderby+@fldSort++@strFSortType+)ASTBMinID)
++@strCondition+orderby+@fldSort++@strFSortType
end
end
else
begin
set@page=@pageIndex-@page+1--后半部分数据处置
if@page<=1--最初一页数据显现
set@strTmp=@SqlSelect+*from(+@SqlSelect+top+CAST(@lastcountasVARCHAR(4))++@fldName+from+@tblName
+where(1=1)+@strCondition+orderby+@fldSort++@strSortType+)ASTempTB+orderby+@fldSort++@strFSortType
elseif(@Sort=1)
set@strTmp=@SqlSelect+*from(+@SqlSelect+top+CAST(@pageSizeasVARCHAR(4))++@fldName+from+@tblName
+where+@ID+>(selectmax(+@ID+)from(+@SqlSelect+top+CAST(@pageSize*(@page-2)+@lastcountasVarchar(20))++@ID+from+@tblName
+where(1=1)+@strCondition+orderby+@fldSort++@strSortType+)ASTBMaxID)
++@strCondition+orderby+@fldSort++@strSortType+)ASTempTB+orderby+@fldSort++@strFSortType
else
set@strTmp=@SqlSelect+*from(+@SqlSelect+top+CAST(@pageSizeasVARCHAR(4))++@fldName+from+@tblName
+where+@ID+<(selectmin(+@ID+)from(+@SqlSelect+top+CAST(@pageSize*(@page-2)+@lastcountasVarchar(20))++@ID+from+@tblName
+where(1=1)+@strCondition+orderby+@fldSort++@strSortType+)ASTBMaxID)
++@strCondition+orderby+@fldSort++@strSortType+)ASTempTB+orderby+@fldSort++@strFSortType
end
end
------前往查询了局-----
execsp_executesql@strTmp
selectdatediff(ms,@timediff,getdate())as耗时
--print@strTmp
SETNOCOUNTOFF
GO
实行示例：execproc_paged_2part_selectMaxtb_testTable,ID,userName,userPWD,userEmail,10,100000,ID,0,null,ID,0
这类测试只在单机举行，而且没有在实践开辟WEB项目平分页测试，测试项也对照单一，以是不敷周全体系，但从其效力比拟上，我们能够在数据库分页算法长进行无效的把持。使用DBaaS能让收入损失从其他业务上得到弥补，如软件更新和硬件管理。也许决定走DBaaS之路的客户可能会跳过解决方案提供商，尽管这个决策看起来有点短视。 SQLServer的异构移植功能个人感觉最好了。（如果对比过SQLServer的链接服务器和Oracle的透明网关的朋友会发现SQLServer的sp_addlinkedserver(openquery)异构数据库系列比Oracle真是强太多了。） 无法深入到数据库系统层面去了解和探究 外键的级联更能扩展可能大部分的同行在设计OLTP系统的时候都不愿意建立外键，都是通过程序来控制父子数据的完整性。 而写到本地,我又考虑到效率问题.大家来讨论讨论吧,分数不打紧,就给10分,十全十美,没啥对错,各抒己见,但是要有说服力的哦~ 换言之，只有在不断的失败中尝试成功，而关于失败的总结却是很少的 对一张百万级别的表建游标，同时又没有什么过滤条件，取得游标效率是如果直接SQL查询百万条数据；如果再对每条记录做处理，耗时将更长。 然后最好有实践机会，能够把实践到的和实践结合起来，其实理论思考是个非常困扰和痛苦的事情 财务软件要用SQL也只是后台的数据库而已,软件都是成品的,当然多学东西肯定是有好处的..
页:
[1]