数据分页的一些想法

• 列表内容

  最近和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   resultAS     (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   resultWHERE  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     resultAS       (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     resultORDER BY IDOFFSET 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     resultAS       (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     resultORDER BY ID ASCOFFSET 0 ROWS FETCH NEXT @end ROWS ONLY;

执行计划如下：

生产上执行效果：

• 效果对比

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

第10万时：

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

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

第10万页时：

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

• 结语

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