大数据量的分页处理

我们知道，在数据量小的时候，数据的分页处理，一般情况下都没有什么问题，但是在大的数据量的情况下，我们就会有数据压力的问题，以前把所有数据都取出来，对数据库以及数据流量的压力也是非常大的，这样会造成性能会非常差。对于大的数据量分页的方法主要有下面的三种思想，它们共同的思路是：只取出我们要分页显示的数据记录，而不是全部取出来。
下面我们来具体分析这三种方法的实现以及性能的测试情况。先说明一下数据表的建立情况。

CREATE TABLE [dbo].[Test] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [T_Name] [char] (32) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [Voting] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Test] WITH NOCHECK ADD 
    CONSTRAINT [PK_Test] PRIMARY KEY  CLUSTERED 
    (
        [ID]
    )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Test] ADD 
    CONSTRAINT [DF_Test_voting] DEFAULT (0) FOR [Voting]
GO

CREATE  INDEX [IX_Test_tName_asc] ON [dbo].[Test]([T_Name]) ON [PRIMARY]
GO

CREATE  INDEX [IX_Test_voting_asc] ON [dbo].[Test]([Voting]) ON [PRIMARY]
GO

CREATE  INDEX [IX_Test_voting_desc] ON [dbo].[Test]([ID]) ON [PRIMARY]
GO

CREATE  INDEX [IX_Test_tName_DESC] ON [dbo].[Test]([T_Name] DESC ) ON [PRIMARY]
GO

我们新建了一个数据库表TEST,ID是PK并且是自增长的，CLUSTERED索引。T_Name和Voting分别建了ASC和DESC的索引。由于我们只单独对t_Name和Voting字段做分页查询，所以只建立了单独的索引，而没有建相关的组合索引了。
现在只对Voting做分页处理的一个案例。分页处理，一般情况下，我们知道要显示的哪一页Page,以及分页显示的PageSize.对于一些例外在这里我们不用考虑。

方法 一 ： 用INDEX和ROWCOUNT结合来做分页处理。

CREATE PROC dbo.TestPage_Index
           @pageSize  INT,
           @pageIndex INT
AS
  BEGIN
      DECLARE
        @PageLower INT,
        @PageUpper INT
      DECLARE
        @id INT
      DECLARE
        @voting    INT,
        @maxvoting INT
                   
      SET @PageLower = (@pageIndex - 1) * @PageSize
                                          
      SET @PageUpper = @PageLower + @PageSize
                                    
      SET RowCount  @PageLower
      
      SELECT   @id = Id,
               @voting = voTing
      FROM     Test.dbo.Test (NoLock )
      ORDER BY voTing ASC
               
      SET RowCount  @PageUpper
      SELECT   @maxvoting = voTing
      FROM     Test.dbo.Test (NoLock )
      ORDER BY voTing
               
      SET RowCount  @PageSize
      
      SELECT   Id,
               t_Name,
               voTing
      FROM     Test.dbo.Test (NoLock )
      WHERE    (voTing = @voting
                AND Id > @id)
                OR (voTing > @voting
                    AND voTing <= @maxVoting)
      ORDER BY voTing
               
      SET RowCount  0
      
  END

GO

第二种方法：用临时表的方法来分页处理

CREATE PROC dbo.TestPage_Tab
           @pageSize  INT,
           @pageIndex INT
AS
  BEGIN
      DECLARE
        @PageLower INT,
        @PageUpper INT
      DECLARE
        @id INT
      DECLARE
        @voting    INT,
        @maxvoting INT
                   
      CREATE TABLE ##IndexTable (
        Id  INT IDENTITY( 1  , 1  ),
        nId INT)
      
      SET @PageLower = (@pageIndex - 1) * @PageSize
                                          
      SET @PageUpper = @PageLower + @PageSize
      SET RowCount  @PageUpper
      INSERT INTO ##IndexTable
                 (nId)
      SELECT   Id
      FROM     Test.dbo.Test (NoLock )
      ORDER BY voTing
      SET RowCount  @PageSize
      SELECT   a.*
      FROM     Test.dbo.Test a (NoLock),
               ##IndexTable t (NoLock)
      WHERE    a.Id = t.nId
               AND t.Id > @PageLower
               AND t.Id <= @PageUpper
      ORDER BY t.Id
      SET RowCount  0
      DROP TABLE ##IndexTable 
      
  END

GO

第三种方法：用Cursor来做分页处理

CREATE PROC dbo.TestPage_CurSor
           @pageSize  INT,
           @pageIndex INT
AS
  BEGIN
      DECLARE
        @PageLower INT,
        @PageUpper INT
      DECLARE
        @id INT,
        @i  INT
      DECLARE
        @voting    INT,
        @maxvoting INT
      DECLARE
        @condition CHAR(400)
      DECLARE
        @cur  AS  CURSOR
      SET @PageLower = (@pageIndex - 1) * @PageSize
      SET @PageUpper = @PageLower + @PageSize
      SET RowCount  @PageUpper
      SET @cur = CURSOR FAST_FORWARD READ_ONLY FOR SELECT   Id
                                                   FROM     Test.dbo.Test (NoLock )
                                                   ORDER BY voTing
      OPEN @cur
      SET RowCount  0
      FETCH NEXT FROM @cur
      INTO @id
      SET @i = 1
      WHILE @@FETCH_STATUS = 0
          BEGIN
          
              IF (@i >= @PageLower + 1)
                 AND @i <= @pageUpper
                  BEGIN
                      IF @i = @PageLower + 1
                          BEGIN
                              SET @condition = Rtrim(CAST(@id AS CHAR(10)))
                          END
                       ELSE
                        BEGIN
                            SET @condition = Rtrim(@condition) + ',' + Rtrim(CAST(@id AS CHAR(10)))
                        END
                  END
              SET @i = @i + 1
              FETCH NEXT FROM @cur
              INTO @id
          END
      CLOSE @cur
      DEALLOCATE @cur
      IF @condition IS NOT NULL 
          EXEC( 'select * from test..test(nolock) where id in (' + @Condition + ')')
  END
GO

测试比较
我现在的机器是T43 ,CPU 1.86G SQL SERVER 2000
TEST表里有400,000条记录
测试程序如下：

DECLARE  @dt DATETIME

SET @dt = Getdate()

EXEC TestPage_CurSor  25,200

SELECT Getdate() - @dt AS m_CurSor

SET @dt = Getdate()

EXEC TestPage_Index  25,200

SELECT Getdate() - @dt AS m_Index

SET @dt = Getdate()

EXEC TestPage_Tab  25,200

SELECT Getdate() - @dt AS m_Tab

测试的结果为：
 


我们可以看到测试的结果是如此的悬殊：
第一种对比下来，基本上没有花时间，就处理了
第二种花了50ms的时间，但是多了一个临时表
第三种花了近6s的时间才能处理。

在数据量小的时候，三种方法都差不多，但是在大的数据量的情况却是如此之大，所以如果有开发人员遇到相同的问题的时候，可以参考这个例子。

对于用临时表的做法，如果查询的视图，没有主键的时候，请用临时表，第一种处理完全依赖于索引，速度才如此快。