千万级数据分页详细设计

\n

贴的html代码太难看了。直接贴成黑白的吧，能看清楚。

\n

千万级数据分页详细设计

\n

1. 引言
1.1目的
为适应大数据量分页的需要，为以后千万级数据分页提供解决方法或者参考，节省开发时间，特制定本详细设计方案

\n

1.2主要阅读对象
脚本设计人员

\n

1.3参考资料
http://www.cnblogs.com/SGSoft/archive/2004/10/23/55800.html

\n

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字段上建立了索引。

\n

/*

\n

经测试，在14483461 条记录中查询第100000 页，每页10 条记录按升序和降序第一次时间均为0.47 秒，第二次时间均为0.43 秒，测试语法如下：

\n

exec GetRecordFromPage news,newsid,10,100000

\n

news 为表名, newsid 为关键字段, 使用时请先对newsid 建立索引。

\n

函数名称: GetRecordFromPage

\n

函数功能: 获取指定页的数据

\n

参数说明: @tblName 包含数据的表名

\n

@fldName 关键字段名

\n

@PageSize 每页记录数

\n

@PageIndex 要获取的页码

\n

@OrderType 排序类型, 0 – 升序, 1 – 降序

\n

@strWhere 查询条件(注意: 不要加where)

\n

创建时间: 2004-07-04

\n

修改时间: 2008-02-13

\n

*/

\n

Alter PROCEDURE [dbo].[GetRecordFromPage]

\n

@tblName varchar(255), — 表名

\n

@fldName varchar(255), — 字段名

\n

@PageSize int = 10, — 页尺寸

\n

@PageIndex int = 1, — 页码

\n

@OrderType bit = 0, — 设置排序类型, 非0 值则降序

\n

@IsCount bit = 0, — 返回记录总数, 非0 值则返回

\n

@strWhere varchar(2000) = ” — 查询条件(注意: 不要加where)

\n

\n

AS

\n

declare @strSQL varchar(6000) — 主语句

\n

declare @strTmp varchar(1000) — 临时变量

\n

declare @strOrder varchar(500) — 排序类型

\n

if @OrderType != 0

\n

begin

\n

set @strTmp = ‘<(select min’

\n

set @strOrder = ‘ order by [\' + @fldName + \'] desc’

\n

end

\n

else

\n

begin

\n

set @strTmp = ‘>(select max’

\n

set @strOrder = ‘ order by [\' + @fldName +\'] asc’

\n

end

\n

set @strSQL = ‘select top ‘ + str(@PageSize) + ‘ * from [\'

\n

+ @tblName + \'] where [\' + @fldName + \']‘ + @strTmp + ‘([\'

\n

+ @fldName + \']) from (select top ‘ + str((@PageIndex-1)*@PageSize) + ‘ [\'

\n

+ @fldName + \'] from [\' + @tblName + \']‘ + @strOrder + ‘) as tblTmp)’

\n

+ @strOrder

\n

if @strWhere != ”

\n

set @strSQL = ‘select top ‘ + str(@PageSize) + ‘ * from [\'

\n

+ @tblName + \'] where [\' + @fldName + \']‘ + @strTmp + ‘([\'

\n

+ @fldName + \']) from (select top ‘ + str((@PageIndex-1)*@PageSize) + ‘ [\'

\n

+ @fldName + \'] from [\' + @tblName + \'] where ‘ + @strWhere + ‘ ‘

\n

+ @strOrder + ‘) as tblTmp) and ‘ + @strWhere + ‘ ‘ + @strOrder

\n

if @PageIndex = 1

\n

begin

\n

set @strTmp = ”

\n

if @strWhere != ”

\n

set @strTmp = ‘ where (‘ + @strWhere + ‘)’

\n

set @strSQL = ‘select top ‘ + str(@PageSize) + ‘ * from [\'

\n

+ @tblName + \']‘ + @strTmp + ‘ ‘ + @strOrder

\n

end

\n

if @IsCount != 0

\n

set @strSQL = ‘select count(‘ + @fldName + ‘) as Total from [\' + @tblName + \']‘

\n

exec (@strSQL)

\n

2.2千万级数据分页实现
2.2.1简介
这次分页我是用Gridview来实现的，测试时间没算，但基本上从10000011纪录中一次查询25条纪录，在10万页以内，时间花费 在1秒以内，10万页以上也不超过2秒。使用其他控件比如DataGrid，DataList或者DataReapter应该花费的时间更短。

\n

2.2.2适用对象
服务器端控件Gridview，DataGrid，DataList，DataReapter等数据绑定控件

\n

2.2.3分页实现
分页效果图如下：

\n

前台控件代码和功能描述如下

\n

控件代码
功能描述

<asp:GridView ID=”GridView1″ runat=”server” AllowPaging=”false” AutoGenerateColumns=”False” CellPadding=”4″ ForeColor=”#333333″ GridLines=”None” PagerSettings-Visible=”false”

\n

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;” onkeypress=”myKeyDown();”></asp:TextBox>
输入要跳转到的某一页的具体数字，只允许输入大于0的数字，输入0或者大于总页数时，弹出警告提示

<asp:Button ID=”BtnChangePage” runat=”server” Text=”GO” onClick=”BtnChangePage_Click” />
执行具体的页面跳转

\n

前台代码如下：

\n

<form id=”form1″ runat=”server”>

\n

<table width=”98%” align=”center” class=”maintableborder”>

\n

<tr>

\n

<td height=”35″ style=”text-align: left;”>

\n

<asp:Label ID=”lbAddProposal” runat=”server”> <img src=”../images/025.gif” width=”13″ height=”13″ /> <br /></asp:Label>

\n

<asp:GridView ID=”GridView1″ runat=”server” AllowPaging=”false” AutoGenerateColumns=”False” CellPadding=”4″ ForeColor=”#333333″ GridLines=”None” PagerSettings-Visible=”false”

\n

Width=”100%” height=”35″>

\n

<FooterStyle BackColor=”#507CD1″ Font-Bold=”True” ForeColor=”White” />

\n

<RowStyle BackColor=”#EFF3FB” />

\n

<EditRowStyle BackColor=”#2461BF” />

\n

<SelectedRowStyle BackColor=”#D1DDF1″ Font-Bold=”True” ForeColor=”#333333″ />

\n

<PagerStyle ForeColor=”White” VerticalAlign=”Top” BackColor=”Transparent” />

\n

<HeaderStyle BackColor=”#507CD1″ Font-Bold=”True” ForeColor=”White” />

\n

<AlternatingRowStyle BackColor=”White” />

\n

<Columns>

\n

<asp:TemplateField HeaderText=”序号”>

\n

<ItemTemplate>

\n

<%# Container.DataItemIndex+1 %>

\n

</ItemTemplate>

\n

</asp:TemplateField>

\n

<asp:BoundField DataField=”I_BriefnessID” HeaderText=”ID” />

\n

<asp:BoundField DataField=”I_KMID” HeaderText=”科目” />

\n

<asp:BoundField DataField=”C_Recno” HeaderText=”试题号” />

\n

<asp:BoundField DataField=”M_Title” HeaderText=”题面” />

\n

<asp:BoundField DataField=”C_Answer” HeaderText=”答案” />

\n

</Columns>

\n

</asp:GridView>

\n

<asp:Panel ID=”panPage” runat=”server”>

\n

<table width=”100%” style=” height:20px; border:solid 1px #cccccc;” cellpadding=”0″ cellspacing=”0″ >

\n

<tr>

\n

<td align=”right”>

\n

当前第

\n

<asp:Label ID=”lblCurrentPage” runat=”server”></asp:Label>页,

\n

总共<asp:Label ID=”lblRecodeCount” runat=”server”></asp:Label>条纪录,

\n

共<asp:Label ID=”lblPageCount” runat=”server”></asp:Label>页,

\n

<asp:Label ID=”Label1″ runat=”server”></asp:Label>

\n

<asp:LinkButton ID=”lnkbtnFrist” runat=”server” onClick=”lnkbtnFrist_Click”><font face=webdings color=”red”>9</font></asp:LinkButton>

\n

<asp:LinkButton ID=”lnkbtnPre” runat=”server” onClick=”lnkbtnPre_Click”><font face=webdings color=”red”>7</font></asp:LinkButton>

\n

<asp:LinkButton ID=”lnkbtnNext” runat=”server” onClick=”lnkbtnNext_Click”><font face=webdings color=”red”>8</font></asp:LinkButton>

\n

<asp:LinkButton ID=”lnkbtnLast” runat=”server” onClick=”lnkbtnLast_Click”><font face=webdings color=”red”>:</font></asp:LinkButton>

\n

跳转到第<asp:TextBox ID=”txtPageIndex” runat=”server” style=”width:40px;” onkeypress=”myKeyDown();”></asp:TextBox>页<asp:Button ID=”BtnChangePage” runat=”server” Text=”GO” onClick=”BtnChangePage_Click” />

\n

</td>

\n

</tr>

\n

</table>

\n

</asp:Panel>

\n

<asp:Label ID=”lbNoMessage” Text=”当前题库下没有任何试卷一定方案” runat=”server” ForeColor=”red”></asp:Label>

\n

</td>

\n

</tr>

\n

</table>

\n

</form>

\n

后台代码如下：

\n

相关解释请看代码注释

\n

using System;

\n

using System.Data;

\n

using System.Configuration;

\n

using System.Web;

\n

using System.Web.Security;

\n

using System.Web.UI;

\n

using System.Web.UI.WebControls;

\n

using System.Web.UI.WebControls.WebParts;

\n

using System.Web.UI.HtmlControls;

\n

public partial class _Default : System.Web.UI.Page

\n

{

\n

protected void Page_Load(object sender, EventArgs e)

\n

{

\n

if (!Page.IsPostBack)

\n

{

\n

TestDataCount();

\n

BindPaperDefineProgramme(1);

\n

}

\n

}

\n

#region 绑定试卷定义方案列表

\n

/// <summary>

\n

/// 统计该表的所有纪录

\n

/// </summary>

\n

private void TestDataCount()

\n

{

\n

DataTable dt = null;

\n

try

\n

{

\n

//public static DataTable GetTestData(string tableName, int pageSize, int pageIndex,string columnName,bool isCount)

\n

//第一个参数为要查询的表，第二个参数为每页的纪录数，这里定为25，第三个为页码，这里初始化为第1页，第四个参数为表的字段

\n

//，第五个参数为是否返回纪录总数，这里为true表示返回纪录总数

\n

dt = ExecProc.GetTestData(“tbl_Briefness”, 25, 1, “I_BriefnessID”,true);

\n

}

\n

catch (Exception ex)

\n

{

\n

Response.Write(ex.Message);

\n

return;

\n

}

\n

if (dt.Rows.Count == 0)

\n

{

\n

GridView1.Visible = false;

\n

panPage.Visible = false;

\n

lbNoMessage.Visible = true;

\n

}

\n

else

\n

{

\n

GridView1.Visible = true;

\n

panPage.Visible = true;

\n

lbNoMessage.Visible = false;

\n

lblRecodeCount.Text = dt.Rows[0]["Total"].ToString();

\n

lblCurrentPage.Text = “1″;//初始化当前页为第一页

\n

int recodeCount = int.Parse(dt.Rows[0]["Total"].ToString()); //获取纪录总数

\n

int pageSize = recodeCount % 25 == 0 ? recodeCount / 25 : recodeCount / 25 + 1;//计算总页数

\n

lblPageCount.Text = pageSize.ToString();//保存总页数

\n

}

\n

}

\n

#endregion

\n

#region 绑定试卷定义方案列表

\n

/// <summary>

\n

/// 根据当前页码查询需要的数据

\n

/// </summary>

\n

/// <param name=”pageIndex”>页码</param>

\n

private void BindPaperDefineProgramme(int pageIndex)

\n

{

\n

DataTable dt = null;

\n

try

\n

{

\n

dt = ExecProc.GetTestData(“tbl_Briefness”, 25, pageIndex, “I_BriefnessID”, false); //根据页码查询需要的纪录

\n

}

\n

catch (Exception ex)

\n

{

\n

Response.Write(ex.Message);

\n

return;

\n

}

\n

if (dt.Rows.Count == 0)

\n

{

\n

GridView1.Visible = false;

\n

panPage.Visible = false;

\n

lbNoMessage.Visible = true;

\n

}

\n

else

\n

{

\n

GridView1.Visible = true;

\n

panPage.Visible = true;

\n

lbNoMessage.Visible = false;

\n

GridView1.DataSource = dt;

\n

GridView1.DataBind();

\n

}

\n

int pageCount = int.Parse(lblPageCount.Text); //获取总页数

\n

if (pageIndex == 1) //如果是第一页，则第一页灰显，作用是避免不必要的点击造成没必要的数据传输

\n

{

\n

lnkbtnFrist.Enabled = false;

\n

}

\n

else

\n

{

\n

lnkbtnFrist.Enabled = true;

\n

}

\n

if (pageIndex > 1) //如果当前页大于1，则上一页显示，否则灰显

\n

{

\n

lnkbtnPre.Enabled = true;

\n

}

\n

else

\n

{

\n

lnkbtnPre.Enabled = false;

\n

}

\n

if (pageIndex < pageCount)//如果当前页小于总页数，则下一页显示，否则灰显

\n

{

\n

lnkbtnNext.Enabled = true;

\n

}

\n

else

\n

{

\n

lnkbtnNext.Enabled = false;

\n

}

\n

if (pageIndex == pageCount)//如果当前页为最后一页，则末页灰显

\n

{

\n

lnkbtnLast.Enabled = false;

\n

}

\n

else

\n

{

\n

lnkbtnLast.Enabled = true;

\n

}

\n

}

\n

#endregion

\n

#region 翻页相关的事件

\n

/// <summary>

\n

/// 处理翻页事件

\n

/// </summary>

\n

/// <param name=”sender”></param>

\n

/// <param name=”e”></param>

\n

protected void lnkbtnFrist_Click(object sender, EventArgs e) //第一页

\n

{

\n

lblCurrentPage.Text = “1″;

\n

BindPaperDefineProgramme(1);

\n

}

\n

protected void lnkbtnPre_Click(object sender, EventArgs e) //上一页

\n

{

\n

int pageIndex = int.Parse(lblCurrentPage.Text);

\n

if (pageIndex > 0)

\n

{

\n

pageIndex–;

\n

lblCurrentPage.Text = pageIndex.ToString();

\n

BindPaperDefineProgramme(pageIndex);

\n

}

\n

}

\n

protected void lnkbtnNext_Click(object sender, EventArgs e)//下一页

\n

{

\n

int pageIndex = int.Parse(lblCurrentPage.Text);

\n

int pageCount = int.Parse(lblPageCount.Text);

\n

if (pageIndex < pageCount)

\n

{

\n

pageIndex++;

\n

lblCurrentPage.Text = pageIndex.ToString();

\n

BindPaperDefineProgramme(pageIndex);

\n

}

\n

}

\n

protected void lnkbtnLast_Click(object sender, EventArgs e)//末页

\n

{

\n

lblCurrentPage.Text = lblPageCount.Text;

\n

BindPaperDefineProgramme(int.Parse(lblPageCount.Text));

\n

}

\n

#endregion

\n

protected void BtnChangePage_Click(object sender, EventArgs e)//跳转到指定页

\n

{

\n

int pageIndex = int.Parse(txtPageIndex.Text);

\n

int pageCount = int.Parse(lblPageCount.Text);

\n

if (pageIndex == 0)//如果为0，则提示错误

\n

{

\n

Response.Write(“<Script>alert(‘请输入正确的页数!’);</script>”);

\n

return;

\n

}

\n

if (pageIndex > pageCount)//如果大于总页数则提示错误

\n

{

\n

Response.Write(“<Script>alert(‘请输入正确的页数!’);</script>”);

\n

return;

\n

}

\n

lblCurrentPage.Text = pageIndex.ToString();

\n

BindPaperDefineProgramme(pageIndex);

\n

}

\n

}

来源:csdn

\n