MS Access数据库分页(直接分页)

分享 Ruby Access 数据库 by 达达 at 2007-07-17

我想用Ruby重写我的Blog，打算用MS Access数据库，因为它方便移动和备份，对于一个个人网站来说Access的性能足够应付了。前几天弄懂了怎么用Ruby操作MS Access数据库，昨天晚上写了一个通用的MS Access数据库分页类，算法是通用的，用在PHP或者ASP也是可以的。

我将分页算法分为两种，一种是直接分页，它提供直接获取某一页内容的功能，这个功能应用在需要显示页码的时候。第二种是间接分页，它提供上获取一页、下一页、第一页、最后一页内容的功能。

为了减小篇幅，我将两种算法分开进行讨论，最后再分析我写的Ruby程序。这篇我先分析直接分页算法。

我粗略测试了一下直接分页的性能，10000条数据的直接分页耗时在0.1-0.2秒之间，50000条数据的直接分页耗时在0.5-0.7秒之间，100000条数据的直接分页耗时在1.3秒左右。可以看出来，当数据到50000条以上，差不多速度就不能忍受了，不过个人网站的数据要到50000条以上还是有些难度的。我另外做了一组间接分页的函数，用前一页的数据来辅助获取下一页内容，速度快了不少，100000条数据的分页时间在0.4秒以内，所以当数据量很大时尽量采用间接分页来提高速度。

首先我们不考虑代码怎么先，我们先想想要获取指定页的内容最少需要几个步骤。假设现在User表有100条ID从1到100的纪录，按每页10条纪录分页，那么我要获取第3页的内容会有以下几个步骤：

1.获取第三页起始纪录的ID (或者第二页最后一条纪录的ID) 2.获取ID大于等于第三页起始纪录ID (或者大于第二页最后一条纪录ID) 的前10条纪录

只需要两步就可以了，然后我们看看怎么实现这两步。

1.获取前20条纪录，因为我们是第3页，前20条纪录就是第1和第2页的所有ID

SELECT TOP 20 [ID] FROM [User] ORDER BY [ID] ASC

2.获取前20条纪录中的最大ID，这就是第二页的最后一条纪录的ID

SELECT TOP 1 [ID] 
FROM (
    SELECT TOP 20 [ID] FROM [User] ORDER BY [ID] ASC
) AS A 
ORDER BY [ID] DESC

或者

SELECT MAX([ID]) 
FROM (
    SELECT TOP 20 [ID] FROM [User] ORDER BY [ID] ASC
) AS A

3.获取大于第二页最后一条纪录ID的前10条纪录

SELECT TOP 10 B.* 
FROM [User] AS B, (
    SELECT TOP 1 [ID] AS P_ID 
    FROM (
        SELECT TOP 20 [ID] FROM [User] ORDER BY [ID] ASC
    ) AS A ORDER BY [ID] DESC
) AS C 
WHERE B.[ID] > C.P_ID 
ORDER BY B.[ID] ASC

或者

SELECT TOP 10 B.* 
FROM [User] AS B, (
    SELECT MAX([ID]) AS P_ID 
    FROM (
        SELECT TOP 20 [ID] FROM [User] ORDER BY [ID] ASC
    ) AS A
) AS C 
WHERE B.[ID] > C.P_ID 
ORDER BY B.[ID] ASC

最后可以看出来，只要通过一个语句就可获取指定页的内容了。上面的两个方式一个用TOP一个用MAX，我都测试过速度，几乎没什么差别，不过用MAX的方式语句会比较短。

我们现在是按升序排列，而作为通用的分页函数，需要满足按任意字段，升序或降序排列，并只返回指定字段而不是所有字段。所以我们需要把我们前面分析的算法进一步抽象。

首先，当作成通用脚本，表名和字段名肯定需要由外部指定，所以表名和字段名都将有变量替代。

我们再考虑当需求变成降序的时候我们的脚本会发生哪些变化。最明显的变化是ASC将会变成DESC，然后是 B.[ID] > C.P_ID 将会变成B.[ID] < C.P_ID 。这两个部分我们也可以用变量替代。

然后我们还需要可以让外部指定返回的字段，这部分也很简单，只要把B.*也用变量替换就可以。

而上面脚本中的20可以通过页码乘以每页数据条数得到。

所以把我们的算法通用化并不难，不是吗？

忘记了，还有一个需求，使用者需要可以传递自己的筛选条件。这个只要在Where部分加上一个变量替代，一样可以做到。

以Ruby为例，最终的通用SQL会变成这样：

sql = %Q{
    SELECT TOP #{pageSize} #{fields} 
    FROM #{@table} AS E, (
      SELECT #{getPreID}(#{@orderKey}) AS P_ID 
      FROM (
        SELECT TOP #{passCount} #{@orderKey} 
        FROM #{@table} 
        ORDER BY #{@orderKey} #{@orderType}
      ) AS A
    ) AS F 
    WHERE E.#{@orderKey} #{condition} F.P_ID 
    ORDER BY #{@orderKey} #{@orderType}
}

这是我从最终代码中抽出了的，当中有实例变量也有函数参数可，所以看起来有个变量有@有的没有。