【转】sql分页算法

sql分页算法

转自:http://www.knowsky.com/339541.html
分页算法，是Web开发人员很关心的一个问题，几乎每个数据库相关的应用都要涉及到分页算法，有很多人写过这方面的文档，似乎没有什么好讨论的了；但实际上换一种数据的表现形式，就可以采用一种更好的分页算法，那我们现在就看看还能不能做得更好。

先说说已有的一些方法。
一是通过 DataGrid 分页，这种方式最简单，但效率不高，需要读取所需数据前面的所有数据。
二是通过指定起始记录和记录数的 DbDataAdapter.Fill 来填充 DataSet 的分页方式，这种方式也简单，但同样效率不高，也需要读取所需数据前面的所有数据。
三是通过多个 select top 和多次排序，从表的中间选取需要的记录；为了使相邻页的数据不重复，需要使用 not in，会导致在选择大数据量的表格的尾部数据时，数据库的性能会有很大的降低。

假设我们换一种表格的展现形式，就以传统的 C/S 应用下的带滚动条的 Grid 展现方式为例；其实这种方式才是数据库表格最合适的展现方式，而 Web 应用下常用的 1,2,3....的页码连接方式或上一页、下一页按钮的页码浏览条方式，都是不得已而为之，因为不能通过简单的技术，在 Web 应用下实现带滚动条的 Grid。

数据库的表格都是带有主键的，以区分表格中不同的记录；用户界面上的 Grid 里的数据从逻辑上也是有主键的，否则数据会有歧义，但大多数的应用，没有设置，也无法知道所读出数据的主键；即使少数应用设置了，也知道所读数据的主键，但并没有将其应用到分页中；其实只要知道了所读数据的主键，就可以非常容易的进行分页。

首页的算法很简单
select top 页大小 * from 表名 order by 主键

对于带滚动条的表格，数据是一页一页顺序滚动，即使拖动滚动条，也可以一页页滚动到所选的位置，当拖动到一个新页时的算法为
select top 页大小 * from 表名 where 主键 > 上一页末记录的主键 order by 主键

如果采用了缓存的方式，所有的数据都只需要下载一次，只有滚动到尾部时，才下载新的数据。

这种算法要求知道 Grid 中数据的主键，并将主键的数据应用到分页；对于多主键和排序的表格，算法是一样的，只是语句复杂了一些。不但可以从首页开始，也可以从末页开始，向前翻滚。
这个算法的性能没有问题，对于不论多大的表，选择那个位置的记录都是一样的，比较适合于用首页、上一页、下一页和末页的分页选择方式，更适合于带滚动条的 Grid ；不适合于指定页码的分页。

采用该算法的带滚动条的 Grid 可以参考我们的演示

请您在提出疑问时，先考虑两点，否则可能认识不到这个算法和我们系统结合的优势：
第一：传统的 C/S 应用的表格和Web上的分页表格，谁更便利。
第一，我们实现的带滚动条的表格，和传统的 C/S 应用的表格的差别有多大。

对回复的解释：
有的回复提出无法实现“跳转到xx页”这样的操作。
可我们想想，在 C/S 的应用环境下，如果谁用这种页面跳转的方式，大家肯定会觉得怪异。
而我们实现的带滚动条的 Grid 在局域网环境下，速度几乎和以前的 C/S 应用差不多，在广域网的情况下，速度也很快。
Web 应用的“跳转到XX页的操作”实际上是不得已而为之，如果能实现 传统的 C/S 应用的Grid，为什么还要用这个呢？

SQL Server数据库三种分页方案详尽分析
（转自：http://www.xker.com/page/e2007/0718/27883.html）
本文采用三种分页办法，最后对三种分页办法分析了其优缺点。

建立表

CREATE TABLE [TestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

插入数据：(2万条，用更多的数据测试会明显一些)

SET IDENTITY_INSERT TestTable ON

declare @i int
set @i=1
while @i<=20000
begin
    insert into TestTable([id], FirstName, LastName, Country,Note)
values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')
    set @i=@i+1
end

SET IDENTITY_INSERT TestTable OFF

分页方案一：

(利用Not In和SELECT TOP分页)

语句形式：

SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 20 id
         FROM TestTable
         ORDER BY id))
ORDER BY ID

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 页大小*页数 id
         FROM 表
         ORDER BY id))
ORDER BY ID

分页方案二：

(利用ID大于多少和SELECT TOP分页）

语句形式：

SELECT TOP 10 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 20 id
                 FROM TestTable
                 ORDER BY id) AS T))
ORDER BY ID

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 页大小*页数 id
                 FROM 表
                 ORDER BY id) AS T))
ORDER BY ID

分页方案三：

(利用SQL的游标存储过程分页)

create procedure XiaoZhengGe
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,
@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize)
as 总页数--,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off

其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。

建议优化的时候，加上主键和索引，查询效率会提高。

通过SQL 查询分析器，显示比较：我的结论是:

分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句。

分页方案一：(利用Not In和SELECT TOP分页)效率次之，需要拼接SQL语句。

分页方案三：(利用SQL的游标存储过程分页)效率最差，但是最为通用。