数据分页的一些想法

列表内容

最近和DBA讨论了一下项目中经常用到数据分页的问题，收益颇多，这里总结下自己关于分页的一些想法和建议。
目前了解到的分页方法有三种，下面一一介绍：

方法一

之前采用的是使用ROW_NUMBER函数为数据添加序列号，之后按照这个序列号，取自己需要的一段。
SQL如下：

DECLARE @start AS INT, @end AS INT, @TotalItemCount AS INT;

SET @start = 1 * 1;

SET @end = 1 * 10;

SELECT @TotalItemCount = COUNT(1)
FROM   [PkgInputDB].[dbo].Prd_ResourceSalesPriceDetail AS R WITH (NOLOCK)
WHERE  1 = 1;

WITH   result
AS     (SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS RowNumber,
               R.ID,
               R.SalesPriceID,
               R.EffectDate,
               R.ExpireDate,
               R.SalesPrice,
               R.Active,
               R.InUse
        FROM   [PkgInputDB].[dbo].Prd_ResourceSalesPriceDetail AS R WITH (NOLOCK)
        WHERE  1 = 1)
SELECT @TotalItemCount AS TotalItemCount,
       *
FROM   result
WHERE  RowNumber BETWEEN @start AND @end;

其执行计划：

在生产上执行效果：

是微软在SQL Server 2012？中推出的一种方法，采用OFFSET FETCH 语句。
SQL如下:

DECLARE @start AS INT, @end AS INT, @TotalItemCount AS INT;

SET @start = (@PageIndex - 1) * @PageSize + 1;

SET @end = @PageIndex * @PageSize;

SELECT @TotalItemCount = COUNT(1)
FROM   [PkgInputDB].[dbo].Prd_ResourceSalesPriceDetail AS R WITH (NOLOCK)
WHERE  1 = 1;

WITH     result
AS       (SELECT R.ID,
                 R.SalesPriceID,
                 R.EffectDate,
                 R.ExpireDate,
                 R.SalesPrice,
                 R.Active,
                 R.InUse
          FROM   [PkgInputDB].[dbo].Prd_ResourceSalesPriceDetail AS R WITH (NOLOCK)
          WHERE  1 = 1)

SELECT   @TotalItemCount AS TotalItemCount,
         *
FROM     result
ORDER BY ID
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

执行计划如下：

可以看出新推出的方法执行过程比第一种少很多。
生产执行效果：

DBA同学推荐的做法是：按照索引列进行排序，并在where 条件中增加 >min值 然后查询top N N是每页显示数据量，翻页时将上一次查询的最大值 作为这次查询的最小值。
SQL语句如下：

DECLARE @start AS INT, @end AS INT, @TotalItemCount AS INT;

SET @end = 10;

SELECT @TotalItemCount = COUNT(1)
FROM   [PkgInputDB].[dbo].Prd_ResourceSalesPriceDetail AS R WITH (NOLOCK)
WHERE  1 = 1;

WITH     result
AS       (SELECT R.ID,
                 R.SalesPriceID,
                 R.EffectDate,
                 R.ExpireDate,
                 R.SalesPrice,
                 R.Active,
                 R.InUse
          FROM   [PkgInputDB].[dbo].Prd_ResourceSalesPriceDetail AS R WITH (NOLOCK)
          WHERE  1 = 1
                 AND ID > 0)
SELECT   *
FROM     result
ORDER BY ID ASC
OFFSET 0 ROWS FETCH NEXT @end ROWS ONLY;

执行计划如下：

生产上执行效果：

从执行计划上看只有第一种过程略微复杂，但对性能似乎并没有什么影响，不过考虑到过程越复杂，损耗可能就越大，故采用2,3比较合适；我们再测试下，分页数变多时，各sql执行情况
第二种分页方法：
第10000页时：

第10万时：

第100万页时：
SQL超时，无结果！

第三种分页方法：
第10000页时：

第10万页时：

可以看出随着分页数的增加，第二种分页方法耗时会越来越大，而第三种方法则不受影响。

第一种在性能没有优势，且写法复杂可以直接淘汰。这里主要讨论第二种和第三种分页方式的优缺点，以及适用的场景。
当需求需要获取记录总数（多数情况下其实都可以不用返回总数的，只要和产品经理动之以情晓之以理，一般都会取消获取总数的）时，分页耗时相对获取总数耗时可以忽略不计，故这里只讨论不需要获取记录总数的情况。
因为第二种分页方法会随着分页数增加，耗时增大，故无特殊要求时采用第三种分页比较好。
当然如果需要跳页时，因为第三种分页是基于上次一次分页结果，所以不好实现，这种情况下可以采用第二种分页方法。
排序要求比较复杂，且无固定顺序时，也只能采用第二种分页方法。