DataGrid基于Access的快速分页法(2)

     有关主键和索引的概念
  
  　　在 Aclearcase/" target="_blank" >ccess中，一个表的主键（PRIMARY KEY，又称主索引）必然是唯一索引（UNIQUE INDEX），它的值是不会重复的。除此之外，索引依据索引列的值进行排序，每个索引记录包含着一个指向它所引用的数据行的指针，这对ORDER BY的执行非常有帮助。我们可以利用主键这两个特点来实现对某条记录的定位，从而快速地取出某个分页上要显示的记录。
  
  　　举个例子，假设主键字段为INTEGER型，在数据库表中，记录的索引已经按主键字段的值升序排好（默认情况下），那么主键字段值为“11”的记录的索引，肯定刚好在值为“12”的记录的索引前面（假设数据库表中存在主键的值为“12”的记录）。如果主键字段不具备UNIQUE约束，数据库表中将有可能存在两个或两个以上主键字段的值为“11”的记录，这样就无法确定这些记录之间的前后位置了。
  
  　　下面就让我们看看如何利用主键来进行数据的分段查询吧。
  
  　　快速分页法的原理
  
  　　其实该分页法是从其他方法衍生而来的。本人对原来的方法认真地分析，发现通过优化和改进可以非常有效地提高它的效率。原算法本身效率很高，但缺乏对具体问题的具体分析。同一个分页算法，可能在取第一页的数据时效率非常高，但是在取最后一页的数据时可能反而效率更低。
  
  　　经过分析，我们可以把分页算法的效率状态分为四种情况：
  
  　　（1）@PageIndex <= @FirstIndex
  
  　　（2）@FirstIndex < @PageIndex <= @MiddleIndex
  
  　　（3）@MiddleIndex < @PageIndex < @LastIndex
  
  　　（4）@PageIndex >= @LastIndex
  
  　　状态（1）和（4）分别表示第一页和最后一页。它们属于特殊情况，我们不必对其使用特殊算法，直接用TOP就可以解决了，不然会把问题复杂化，反而降低了效率。对于剩下的两种状态，如果分页总数为偶数，我们可以看作是从数据库表中删掉第一页和最后一页的记录，再把剩下的按前后位置平分为两部分，即前面的一部分，也就是状态（2），后面的为另一部分，也就是状态（3）；如果分页总数为奇数，则属于中间页面的记录归于前面的部分。这四种状态分别对应着四组SQL语句，每组SQL语句由升序和降序两条SQL语句组成。
  
  　　下面是一个数据库表，左边第一列是虚拟的，不属于该数据库表结构的一部分，它表示相应记录所在的分页索引。该表将用于接下来的SQL语句的举例中：
  
  PageIndex ItemId ProductId Price
  0 001 0011 $12
  002 0011 $13
  1 003 0012 $13
  004 0012 $11
  2 005 0013 $14
  006 0013 $12
  3 007 0011 $13
  008 0012 $15
  4 009 0013 $12
  010 0013 $11
  
  　　由表可得：@PageSize = 2，@RecordCount = 10，@PageCount = 5
  
  　　升序的SQL语句
  
  　　（1）@PageIndex <= @FirstIndex
  
  　　取第一页的数据是再简单不过了，我们只要用TOP @PageSize就可以取出第一页要显示的记录了。
  
  SELECT TOP @PageSize @QueryFields FROM @TableName WHERE @Condition ORDER BY @PrimaryKey ASC
  
  　　（2）@FirstIndex < @PageIndex <= @MiddleIndex
  
  　　把取数据表前半部分记录和取后半部分记录的SQL语句分开写，可以有效地改善性能。后面我再详细解释这个原因。现在看看取前半部分记录的SQL语句。先取出当前页之前的所有记录的主键值，再从中选出最大值，然后取出主键值大于该最大值的前@PageSize条记录。这里@PrimaryKey的数据类型可以不是INTEGER类型，CHAR、VARCHAR等其他类型照样可以。
  
  SELECT TOP @PageSize @QueryFields
  
  FROM @TableName
  
  WHERE @PrimaryKey > (
  
  SELECT MAX(@PrimaryKey) FROM (
  
  SELECT TOP @PageSize*@PageIndex @PrimaryKey
  
  FROM @TableName
  
  WHERE @Condition
  
  ORDER BY @PrimaryKey ASC
  
  ) TableA
  
  ) WHERE @Condition
  
  ORDER BY @PrimaryKey ASC
  
  
  　　例如：@PageIndex=1，红-->黄-->蓝
  
  
  
  （3）@MiddleIndex < @PageIndex < @LastIndex
  
  　　接下来看看取数据库表中后半部分记录的SQL语句。该语句跟前面的语句算法的原理是一样的，只是方法稍微不同。先取出当前页之后的所有记录的主键值，再从中选出最小值，然后取出主键值小于该最小值的前@PageSize条记录。
  
  SELECT * FROM (
  
  SELECT TOP @PageSize @QueryFields
  
  FROM @TableName
  
  WHERE @PrimaryKey < (
  
  SELECT MIN(@PrimaryKey) FROM (
  
  SELECT TOP (@RecordCount-@PageSize*(@PageIndex+1)) @PrimaryKey
  
  FROM @TableName
  
  WHERE @Condition
  
  ORDER BY @PrimaryKey DESC
  
  ) TableA
  
  ) WHERE @Condition
  
  ORDER BY @PrimaryKey DESC
  
  ) TableB
  
  ORDER BY @PrimaryKey ASC
  
  　　之所以把取数据表前半部分记录和取后半部分记录的SQL语句分开写，是因为使用取前半部分记录的SQL语句时，当前页前面的记录数目随页数递增，而我们还要从这些记录中取出它们的主键字段的值再从中选出最大值。这样一来，分页速度将随着页数的增加而减慢。因此我没有这样做，而是在当前页索引大于中间页索引时（@MiddleIndex < @PageIndex）选用了分页速度随着页数的增加而加快的算法。由此可见，假设把所有分页面划分为前面、中间和后面三部分，则最前面和最后面的分页速度最快，最中间的分页速度最慢。
  
  　　例如：@PageIndex=3，红 --> 黄 --> 蓝
  
  
  
  4）@PageIndex >= @LastIndex
  
  　　取最后一页的记录可以简单地使用类似状态（1）的做法：
  
  SELECT * FROM (
  
  SELECT TOP @PageSize @QueryFields
  
  FROM @TableName
  
  WHERE @Condition
  
  ORDER BY @PrimaryKey DESC
  
  ) TableA ORDER BY @PrimaryKey ASC
  
  　　不过，这样产生的最后一页不一定是实际意义上的最后一页。因为最后一页的记录数未必刚好跟@PageSize相等，而上面的SQL语句是直接取得倒数的@PageSize条记录。如果想要精确地取得最后一页的记录，应该在先计算出该页的记录数，作为TOP语句的条件：
  
  SELECT * FROM (
  
  SELECT TOP (@RecordCount-@PageSize*@LastIndex) @QueryFields
  
  FROM @TableName WHERE @Condition
  
  ORDER BY @PrimaryKey DESC
  
  ) TableA ORDER BY @PrimaryKey ASC
  

原文转自：http://www.ltesting.net