首页 > 代码库 > SQL Server 中的三种分页方式
SQL Server 中的三种分页方式
USE tempdbGOSET NOCOUNT ON--创建表结构IF OBJECT_ID(N‘ClassB‘, N‘U‘) IS NOT NULL DROP TABLE ClassBGOCREATE TABLE ClassB(ID INT PRIMARY KEY, Name VARCHAR(16), CreateDate DATETIME, AID INT, Status INT)CREATE INDEX IDX_CreateDate ON ClassB(CreateDate)CREATE INDEX IDX_AID ON ClassB(AID)GO--插入测试数据DECLARE @ID INTSET @ID = 1WHILE @ID <= 100000BEGIN INSERT INTO ClassB VALUES(@ID, ‘fx‘, GETDATE(), @ID % 20, @ID % 20) SET @ID = @ID + 1END--统计总行数SELECT ‘ClassB‘ AS ClassB, count(1) AS Count FROM ClassB
查询条件如下：根据CreateDate倒序排序，CreateDate一致时按照ID倒序；时间区间在2014-07-13和2014-07-14之间；每页20条，当前页数第3页；
方案A：双Top，取出之前页的所有ID后，使用NOT IN排除这些ID进行查询，这是性能最差一种，因为他使用相同的查询谓词，查询了两次数据，且两次排序；
DECLARE @page_size INT = 20;DECLARE @page_index INT = 3;--A: 双Top：取出之前页的所有ID后，使用NOT IN排除这些ID进行查询，性能最差WITH ID_List_Excluded AS( SELECT TOP (@page_size * (@page_index - 1)) ID FROM ClassB WHERE CreateDate BETWEEN ‘2014-07-13‘ AND ‘2014-07-14‘ ORDER BY CreateDate DESC, ID DESC)SELECT TOP (@page_size) * FROM ClassB WHERE ID NOT IN (SELECT ID FROM ID_List_Excluded) AND CreateDate BETWEEN ‘2014-07-13‘ AND ‘2014-07-14‘ ORDER BY CreateDate DESC, ID DESC;
表 ‘ClassB‘。扫描计数 2，逻辑读取 136 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 ‘Worktable‘。扫描计数 1，逻辑读取 197 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
查询页数越靠后逻辑读取，下面是查询第30页的逻辑读取情况：
表 ‘ClassB‘。扫描计数 2，逻辑读取 1243 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 ‘Worktable‘。扫描计数 1，逻辑读取 2574 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
方案B：ROW_NUMBER，取出截止到当前页所有符合查询谓词的记录，并给每条记录加上序号，然后根据序号查询，性能次之；
DECLARE @page_size INT = 20;DECLARE @page_index INT = 3;--B: ROW_NUMBER：取出截至到当前页所有符合查询谓词的记录，并给每条记录加上序号，然后根据序号查询，性能次之WITH NewClassB AS( SELECT TOP (@page_size * @page_index) ROW_NUMBER() OVER (ORDER BY CreateDate DESC, ID DESC) AS ROWID, * FROM ClassB WHERE CreateDate BETWEEN ‘2014-07-13‘ AND ‘2014-07-14‘)SELECT TOP (@page_size) * FROM NewClassB WHERE ROWID BETWEEN (@page_size * (@page_index - 1) + 1) AND (@page_size * @page_index);
表 ‘ClassB‘。扫描计数 1，逻辑读取 134 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
方案B已经比方案A快了近一倍，少了一个Worktable，同样，page_index越大，扫描次数越多，因为子查询中要查询多余的（之前页）数据和字段，下面是查询第30页时的IO读取情况；
表 ‘ClassB‘。扫描计数 1，逻辑读取 1241 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
方案C：双Top，取出截止到当前页为止的所有的ID，去掉之前页的ID，然后根据ID（通常是主键）作常规查询，性能最优；
DECLARE @page_size INT = 20;DECLARE @page_index INT = 3;WITH ID_List AS( SELECT TOP (@page_size * @page_index) ID FROM ClassB WHERE CreateDate BETWEEN ‘2014-07-13‘ AND ‘2014-07-14‘ ORDER BY CreateDate DESC, ID DESC),ID_List_Current AS( SELECT ID FROM ID_List WHERE ID NOT IN(SELECT TOP (@page_size * (@page_index - 1)) ID FROM ID_List))SELECT * FROM ClassB WHERE ID IN (SELECT ID FROM ID_List_Current);
表 ‘ClassB‘。扫描计数 2，逻辑读取 52 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
查询第30页的数据仅比查询第3页的数据多出两次逻辑读取，方案C和B的唯一在于：子查询中的查询列，方案B中返回了所有字段，所有要通过聚集索引走lookup，而方案C直接返回索引键即可。
表 ‘ClassB‘。扫描计数 2，逻辑读取 54 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。