老生常谈:也来讨论一下SQL分页的问题

虽然说这个话题真的是外婆级的话题，但却是在我们的开发中经常遇到的问题。一般,我们会抛弃在RecordSet中分页的做法，因为显而易见的原因，这种方法的效率是非常低的。于是SQL分页是一个唯一的选择。

我一直是使用下面这种分页语句：

SELECT*FROM(SELECTTOP页大小*FROM(SELECTTOP起始记录+页大小*FROM表ORDERBY排序字段DESC)t1ORDERBY排序字段)t2ORDERBY排序字段DESC

这种分页方法通用、效率也不低，但有一个致命的问题，就在于最后一页的处理上。如果最后一页不足额定的每页显示条数（页大小），则会从前一页拉一些记录来凑数。

最近我又从网上找了几种流行的SQL分页，综合考虑效率与通用性，我测试了以下两种：

方案一：利用Not In和SELECT TOP分页

SELECTTOP页大小*FROM表WHERE(主键NOTIN(SELECTTOP起始记录主键FROM表ORDERBY排序字段))ORDERBY排序字段

方案二：利用ID大于多少和SELECT TOP分页

SELECTTOP页大小*FROM表WHERE(排序字段>(SELECTMAX(排序字段)FROM(SELECTTOP起始记录排序字段FROM表ORDERBY排序字段)AST))ORDERBY排序字段

加上我常用的那个：利用SELECT TOP来回倒分页，算是方案三吧。一起做了一个简单测试。

先创建表t_hello，很简单的一个表：

CREATETABLE[t_hello](
[sysid][uniqueidentifier]NOTNULLCONSTRAINT[DF_t_hello_sysid]DEFAULT(newid()),
[cdate][datetime]NOTNULLCONSTRAINT[DF_t_hello_cdate]DEFAULT(getdate()),
[title][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL,
CONSTRAINT[PK_t_hello]PRIMARYKEYCLUSTERED
(
[sysid]
)ON[PRIMARY]
)ON[PRIMARY]
GO

然后插入100万条记录，呵呵，有点变态。就可以测试了，写段代码：

SqlConnectionconnection=null;

try
{
connection=newSqlConnection(conStr);
connection.Open();

SqlCommandcmd=newSqlCommand();
cmd.Connection=connection;
cmd.CommandTimeout=connection.ConnectionTimeout;

foreach(intstartinstarts)
{
cmd.CommandText="相应的SQL语句";
SqlDataReaderreader=cmd.ExecuteReader();
reader.Close();
}
connection.Close();
}
catch(Exceptionex)
{
if(connection!=null)connection.Close();
}

其中的starts 是一个int数组，里面有 {10,500,2000,10000,500000}。分别套用三个不同的SQL语句，结果如下：

方案1
开始：13:08:02.408145
结束：13:08:19.793144
Test 1 持续时间：00:00:17.3849984

方案 2
开始：13:08:19.803158
结束：13:08:20.033489
Test 2 持续时间：00:00:00.2303312

方案3
开始：13:08:20.043504
结束：13:08:24.449840
Test 3 持续时间：00:00:04.4063360

大家也可以参考squirrel_sc做的测试，http://www.cnblogs.com/squirrel_sc/archive/2004/10/02/48583.html以及http://blog.csdn.net/lihonggen0/archive/2004/09/14/103511.aspx。

第二种方案的效率真是太明显了（当然如果cdate没有建立索引，它的效率也非常低）。第一方案Not In的方法显然不太可能成为我们的选择。我是看上了方案二的如此高的效率，但进一步研究却发现这个SQL语句在使用中还有不少要注意的地方。

首先，这个MAX(id)对id这个字段是有要求的，GUID就不能用了，这不算太什么，反正我也不会用guid来排序。一般排序的字段也就是日期、名称之类的。

其次，我常用的按日期降序排列(降序应该是最常用的排法了吧？)，那这句话就变成了：

SELECTTOP'+cast(@sizeasnvarchar(8))+'*
FROMt_hello
WHERE(
cdate<(SELECTMIN(cdate)FROM(SELECTTOP'+cast(@startasnvarchar(8))+'cdateFROMt_helloORDERBYcdatedesc)AST)
)
ORDERBYcdatedesc

除了要加上DESC以外，还有两处变化（一是把大小变成小于，二是把MAX变成MIN）。

第三，上面的句子还有点问题，从网上看到 的原始语句中"起始记录(start)"是"页数*页大小"。这里的页数有问题，能不能取0呢？如果取0，TOP语句就会出错，如果不取0，1*页大小， 那第一页的内容就会丢掉。（大家可以试一下，比如1*20）用起始记录也一样，从1开始还是从0开始，所以需要把小于号变成“小于等于”，同理，大于号也 要变成“大于等于”。然后，不用页数*页大小，改成“起始记录”，就解决问题了。

总得来说，方案二的确是一个不错的方法。:)