标题: MYSQL网页编程之年夜数据量分页存储历程效力测试附测试代... [打印本页] 作者: 莫相离 时间: 2015-1-16 22:15 标题: 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
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
--//***显现分页
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