千万级数据分页详细设计
1. 引言
1.1目的
为适应大数据量分页的需要,为以后千万级数据分页提供解决方法或者参考,节省开发时间,特制定本详细设计方案
1.2主要阅读对象
脚本设计人员
1.3参考资料
http://www.cnblogs.com/SGSoft/archive/2004/10/23/55800.html
2. 详细设计
2.1分页存储过程
2.1.1简介
2.1.2分页存储过程代码
以下代码是网上找的分页存储过程,我是在原存储过程的基础上加了一个@IsCount bit = 0, 主要是用来返回纪录总数,当为非0值时返回。下面注释部分是原作者的测试部分。我在本机sql server 2005上的测试是在10000011纪录中查询第100000页,每页10条纪录按升序和降序时间均为0.38秒,测试语法如下:exec GetRecordFromPage tbl_Briefness,I_BriefnessID,10,100000,其中在tbl_Briefness表I_BriefnessID字段上建立了索引。
/*
经测试,在14483461 条记录中查询第100000 页,每页10 条记录按升序和降序第一次时间均为0.47 秒,第二次时间均为0.43 秒,测试语法如下:
exec GetRecordFromPage news,newsid,10,100000
news 为表名, newsid 为关键字段, 使用时请先对newsid 建立索引。
函数功能: 获取指定页的数据
参数说明: @tblName 包含数据的表名
@fldName 关键字段名
@PageSize 每页记录数
@PageIndex 要获取的页码
@OrderType 排序类型, 0 - 升序, 1 - 降序
@strWhere 查询条件(注意: 不要加where)
创建时间: 2004-07-04
修改时间: 2008-02-13
*/
Alter PROCEDURE [ dbo ] . [ GetRecordFromPage ]
@tblName varchar ( 255 ), -- 表名
@fldName varchar ( 255 ), -- 字段名
@PageSize int = 10 , -- 页尺寸
@PageIndex int = 1 , -- 页码
@OrderType bit = 0 , -- 设置排序类型, 非0 值则降序
@IsCount bit = 0 , -- 返回记录总数, 非0 值则返回
@strWhere varchar ( 2000 ) = '' -- 查询条件(注意: 不要加where)
AS
declare @strSQL varchar ( 6000 ) -- 主语句
declare @strTmp varchar ( 1000 ) -- 临时变量
declare @strOrder varchar ( 500 ) -- 排序类型
if @OrderType != 0
begin
set @strTmp = ' <(select min '
set @strOrder = ' order by [ ' + @fldName + ' ] desc '
end
else
begin
set @strTmp = ' >(select max '
set @strOrder = ' order by [ ' + @fldName + ' ] asc '
end
set @strSQL = ' select top ' + str ( @PageSize ) + ' * from [ '
+ @tblName + ' ] where [ ' + @fldName + ' ] ' + @strTmp + ' ([ '
+ @fldName + ' ]) from (select top ' + str (( @PageIndex - 1 ) * @PageSize ) + ' [ '
+ @fldName + ' ] from [ ' + @tblName + ' ] ' + @strOrder + ' ) as tblTmp) '
+ @strOrder
if @strWhere != ''
set @strSQL = ' select top ' + str ( @PageSize ) + ' * from [ '
+ @tblName + ' ] where [ ' + @fldName + ' ] ' + @strTmp + ' ([ '
+ @fldName + ' ]) from (select top ' + str (( @PageIndex - 1 ) * @PageSize ) + ' [ '
+ @fldName + ' ] from [ ' + @tblName + ' ] where ' + @strWhere + ' '
+ @strOrder + ' ) as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where ( ' + @strWhere + ' ) '
set @strSQL = ' select top ' + str ( @PageSize ) + ' * from [ '
+ @tblName + ' ] ' + @strTmp + ' ' + @strOrder
end
if @IsCount != 0
set @strSQL = ' select count( ' + @fldName + ' ) as Total from [ ' + @tblName + ' ] '
exec ( @strSQL )
go
2.2千万级数据分页实现
2.2.1简介
这次分页我是用Gridview来实现的,测试时间没算,但基本上从10000011纪录中一次查询25条纪录,在10万页以内,时间花费 在1秒以内,10万页以上也不超过2秒。使用其他控件比如DataGrid,DataList或者DataReapter应该花费的时间更短。
2.2.2适用对象
服务器端控件Gridview,DataGrid,DataList,DataReapter等数据绑定控件
2.2.3分页实现
前台控件代码和功能描述如下
控件代码
功能描述
<asp:GridView ID="GridView1" runat="server" AllowPaging="false" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None" PagerSettings-Visible="false"
Width="100%" height="35">
显示当前页数据,其中AllowPaging="false",就是去掉自带的分页功能
<asp:Label ID="lblCurrentPage" runat="server"></asp:Label>
用来纪录或者显示当前页
<asp:Label ID="lblRecodeCount" runat="server"></asp:Label>
用来纪录或者显示所有纪录数
<asp:Label ID="lblPageCount" runat="server"></asp:Label>
用来纪录或者显示总页数
<asp:LinkButton ID="lnkbtnFrist" runat="server" OnClick="lnkbtnFrist_Click"><font face=webdings color="red">9</font></asp:LinkButton>
首页,点击则翻到第一页
<asp:LinkButton ID="lnkbtnPre" runat="server" OnClick="lnkbtnPre_Click"><font face=webdings color="red">7</font></asp:LinkButton>
上一页,点击进入上一页
<asp:LinkButton ID="lnkbtnNext" runat="server" OnClick="lnkbtnNext_Click"><font face=webdings color="red">8</font></asp:LinkButton>
下一页,点击进入下一页
<asp:LinkButton ID="lnkbtnLast" runat="server" OnClick="lnkbtnLast_Click"><font face=webdings color="red">:</font></asp:LinkButton>
末页,点击进入最后一页
<asp:TextBox ID="txtPageIndex" runat="server" style="width:40px;" οnkeypress="myKeyDown();"></asp:TextBox>
输入要跳转到的某一页的具体数字,只允许输入大于0的数字,输入0或者大于总页数时,弹出警告提示
<asp:Button ID="BtnChangePage" runat="server" Text="GO" OnClick="BtnChangePage_Click" />
执行具体的页面跳转
前台代码如下:
< table width ="98%" align ="center" class ="maintableborder" >
< tr >
< td height ="35" style ="text-align: left;" >
< asp:Label ID ="lbAddProposal" runat ="server" > < img src ="../images/025.gif" width ="13" height ="13" /> < br /></ asp:Label >
< asp:GridView ID ="GridView1" runat ="server" AllowPaging ="false" AutoGenerateColumns ="False" CellPadding ="4" ForeColor ="#333333" GridLines ="None" PagerSettings-Visible ="false"
Width ="100%" height ="35" >
< FooterStyle BackColor ="#507CD1" Font-Bold ="True" ForeColor ="White" />
< RowStyle BackColor ="#EFF3FB" />
< EditRowStyle BackColor ="#2461BF" />
< SelectedRowStyle BackColor ="#D1DDF1" Font-Bold ="True" ForeColor ="#333333" />
< PagerStyle ForeColor ="White" VerticalAlign ="Top" BackColor ="Transparent" />
< HeaderStyle BackColor ="#507CD1" Font-Bold ="True" ForeColor ="White" />
< AlternatingRowStyle BackColor ="White" />
< Columns >
< asp:TemplateField HeaderText ="序号" >
< ItemTemplate >
<% ... # Container.DataItemIndex+1 %>
</ ItemTemplate >
</ asp:TemplateField >
< asp:BoundField DataField ="I_BriefnessID" HeaderText ="ID" />
< asp:BoundField DataField ="I_KMID" HeaderText ="科目" />
< asp:BoundField DataField ="C_Recno" HeaderText ="试题号" />
< asp:BoundField DataField ="M_Title" HeaderText ="题面" />
< asp:BoundField DataField ="C_Answer" HeaderText ="答案" />
</ Columns >
</ asp:GridView >
< asp:Panel ID ="panPage" runat ="server" >
< table width ="100%" style =" height:20px; border:solid 1px #cccccc;" cellpadding ="0" cellspacing ="0" >
< tr >
< td align ="right" >
当前第
< asp:Label ID ="lblCurrentPage" runat ="server" ></ asp:Label > 页,
总共 < asp:Label ID ="lblRecodeCount" runat ="server" ></ asp:Label > 条纪录,
共 < asp:Label ID ="lblPageCount" runat ="server" ></ asp:Label > 页,
< asp:Label ID ="Label1" runat ="server" ></ asp:Label >
< asp:LinkButton ID ="lnkbtnFrist" runat ="server" OnClick ="lnkbtnFrist_Click" >< font face =webdings color ="red" > 9 </ font ></ asp:LinkButton >
< asp:LinkButton ID ="lnkbtnPre" runat ="server" OnClick ="lnkbtnPre_Click" >< font face =webdings color ="red" > 7 </ font ></ asp:LinkButton >
< asp:LinkButton ID ="lnkbtnNext" runat ="server" OnClick ="lnkbtnNext_Click" >< font face =webdings color ="red" > 8 </ font ></ asp:LinkButton >
< asp:LinkButton ID ="lnkbtnLast" runat ="server" OnClick ="lnkbtnLast_Click" >< font face =webdings color ="red" > : </ font ></ asp:LinkButton >
跳转到第 < asp:TextBox ID ="txtPageIndex" runat ="server" style ="width:40px;" onkeypress ="myKeyDown();" ></ asp:TextBox > 页 < asp:Button ID ="BtnChangePage" runat ="server" Text ="GO" OnClick ="BtnChangePage_Click" />
</ td >
</ tr >
</ table >
</ asp:Panel >
< asp:Label ID ="lbNoMessage" Text ="当前题库下没有任何试卷一定方案" runat ="server" ForeColor ="red" ></ asp:Label >
</ td >
</ tr >
</ table >
</ form >
后台代码如下:
相关解释请看代码注释
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class _Default : System.Web.UI.Page
... {
protected void Page_Load(object sender, EventArgs e)
...{
if (!Page.IsPostBack)
...{
TestDataCount();
BindPaperDefineProgramme(1);
}
}
绑定试卷定义方案列表#region 绑定试卷定义方案列表
/**//// <summary>
/// 统计该表的所有纪录
/// </summary>
private void TestDataCount()
...{
DataTable dt = null;
try
...{
//public static DataTable GetTestData(string tableName, int pageSize, int pageIndex,string columnName,bool isCount)
//第一个参数为要查询的表,第二个参数为每页的纪录数,这里定为25,第三个为页码,这里初始化为第1页,第四个参数为表的字段
//,第五个参数为是否返回纪录总数,这里为true表示返回纪录总数
dt = ExecProc.GetTestData("tbl_Briefness", 25, 1, "I_BriefnessID",true);
}
catch (Exception ex)
...{
Response.Write(ex.Message);
return;
}
if (dt.Rows.Count == 0)
...{
GridView1.Visible = false;
panPage.Visible = false;
lbNoMessage.Visible = true;
}
else
...{
GridView1.Visible = true;
panPage.Visible = true;
lbNoMessage.Visible = false;
lblRecodeCount.Text = dt.Rows[0]["Total"].ToString();
lblCurrentPage.Text = "1";//初始化当前页为第一页
int recodeCount = int.Parse(dt.Rows[0]["Total"].ToString()); //获取纪录总数
int pageSize = recodeCount % 25 == 0 ? recodeCount / 25 : recodeCount / 25 + 1;//计算总页数
lblPageCount.Text = pageSize.ToString();//保存总页数
}
}
#endregion
绑定试卷定义方案列表#region 绑定试卷定义方案列表
/**//// <summary>
/// 根据当前页码查询需要的数据
/// </summary>
/// <param name="pageIndex">页码</param>
private void BindPaperDefineProgramme(int pageIndex)
...{
DataTable dt = null;
try
...{
dt = ExecProc.GetTestData("tbl_Briefness", 25, pageIndex, "I_BriefnessID", false); //根据页码查询需要的纪录
}
catch (Exception ex)
...{
Response.Write(ex.Message);
return;
}
if (dt.Rows.Count == 0)
...{
GridView1.Visible = false;
panPage.Visible = false;
lbNoMessage.Visible = true;
}
else
...{
GridView1.Visible = true;
panPage.Visible = true;
lbNoMessage.Visible = false;
GridView1.DataSource = dt;
GridView1.DataBind();
}
int pageCount = int.Parse(lblPageCount.Text); //获取总页数
if (pageIndex == 1) //如果是第一页,则第一页灰显,作用是避免不必要的点击造成没必要的数据传输
...{
lnkbtnFrist.Enabled = false;
}
else
...{
lnkbtnFrist.Enabled = true;
}
if (pageIndex > 1) //如果当前页大于1,则上一页显示,否则灰显
...{
lnkbtnPre.Enabled = true;
}
else
...{
lnkbtnPre.Enabled = false;
}
if (pageIndex < pageCount)//如果当前页小于总页数,则下一页显示,否则灰显
...{
lnkbtnNext.Enabled = true;
}
else
...{
lnkbtnNext.Enabled = false;
}
if (pageIndex == pageCount)//如果当前页为最后一页,则末页灰显
...{
lnkbtnLast.Enabled = false;
}
else
...{
lnkbtnLast.Enabled = true;
}
}
#endregion
翻页相关的事件#region 翻页相关的事件
/**//// <summary>
/// 处理翻页事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void lnkbtnFrist_Click(object sender, EventArgs e) //第一页
...{
lblCurrentPage.Text = "1";
BindPaperDefineProgramme(1);
}
protected void lnkbtnPre_Click(object sender, EventArgs e) //上一页
...{
int pageIndex = int.Parse(lblCurrentPage.Text);
if (pageIndex > 0)
...{
pageIndex--;
lblCurrentPage.Text = pageIndex.ToString();
BindPaperDefineProgramme(pageIndex);
}
}
protected void lnkbtnNext_Click(object sender, EventArgs e)//下一页
...{
int pageIndex = int.Parse(lblCurrentPage.Text);
int pageCount = int.Parse(lblPageCount.Text);
if (pageIndex < pageCount)
...{
pageIndex++;
lblCurrentPage.Text = pageIndex.ToString();
BindPaperDefineProgramme(pageIndex);
}
}
protected void lnkbtnLast_Click(object sender, EventArgs e)//末页
...{
lblCurrentPage.Text = lblPageCount.Text;
BindPaperDefineProgramme(int.Parse(lblPageCount.Text));
}
#endregion
protected void BtnChangePage_Click(object sender, EventArgs e)//跳转到指定页
...{
int pageIndex = int.Parse(txtPageIndex.Text);
int pageCount = int.Parse(lblPageCount.Text);
if (pageIndex == 0)//如果为0,则提示错误
...{
Response.Write("<Script>alert('请输入正确的页数!');</script>");
return;
}
if (pageIndex > pageCount)//如果大于总页数则提示错误
...{
Response.Write("<Script>alert('请输入正确的页数!');</script>");
return;
}
lblCurrentPage.Text = pageIndex.ToString();
BindPaperDefineProgramme(pageIndex);
}
}