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

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

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

SELECT   *   FROM  ( SELECT   TOP  页大小  *    FROM  ( SELECT   TOP  起始记录 + 页大小  *    FROM  表  ORDER   BY  排序字段  DESC ) t1   ORDER   BY  排序字段) t2  ORDER   BY  排序字段  DESC

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

最近我又从网上找了几种流行的SQL分页，综合考虑效率与通用性，我测试了以下两种： 
   
方案一：利用Not In和SELECT TOP分页

SELECT   TOP  页大小  *   FROM  表  WHERE  (主键  NOT   IN  ( SELECT   TOP  起始记录  主键  FROM  表  ORDER   BY  排序字段))  ORDER   BY  排序字段

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

SELECT   TOP  页大小  *   FROM  表  WHERE  (排序字段  >  ( SELECT   MAX (排序字段)  FROM  ( SELECT   TOP  起始记录  排序字段  FROM  表  ORDER   BY  排序字段)  AS  T))  ORDER   BY  排序字段

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

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

CREATE   TABLE   [ t_hello ]  (
     [ sysid ]   [ uniqueidentifier ]   NOT   NULL   CONSTRAINT   [ DF_t_hello_sysid ]   DEFAULT  ( newid ()),
     [ cdate ]   [ datetime ]   NOT   NULL   CONSTRAINT   [ DF_t_hello_cdate ]   DEFAULT  ( getdate ()),
     [ title ]   [ nvarchar ]  ( 50 ) COLLATE Chinese_PRC_CI_AS  NULL  ,
     CONSTRAINT   [ PK_t_hello ]   PRIMARY   KEY    CLUSTERED  
    (
         [ sysid ]
    )   ON   [ PRIMARY ]  
)  ON   [ PRIMARY ]
GO

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

SqlConnection connection  =   null ;
            
try
{
connection = new SqlConnection(conStr);
connection.Open();

SqlCommand cmd = new SqlCommand();
cmd.Connection=connection;
cmd.CommandTimeout=connection.ConnectionTimeout;            
                
foreach(int start in starts)
{
cmd.CommandText="相应的SQL语句";
SqlDataReader reader = cmd.ExecuteReader();
reader.Close();
}
connection.Close();
}
catch (Exception ex)
{
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.youkuaiyun.com/lihonggen0/archive/2004/09/14/103511.aspx。

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

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

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

SELECT   TOP   ' +cast(@size as nvarchar(8))+ '   *  
FROM  t_hello 
WHERE  (
    cdate  <  (  SELECT   MIN (cdate)  FROM  ( SELECT   TOP   ' +cast(@start as nvarchar(8))+ '  cdate  FROM  t_hello  ORDER   BY  cdate  desc )  AS  T )
    ) 
ORDER   BY  cdate  desc

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

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

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