sql 2012之后分页查询速度问题

一.SQL Server 2012使用OFFSET/FETCH NEXT分页，比SQL Server 2005/2008中的RowNumber()有显著改进。今天特地作了简单测试，现将过程分享如下：

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

DECLARE @page INT, @size INT
SELECT @page = 3, @size = 10

;WITH cte AS (
SELECT TOP (@page * @size)
CustomerID,
CustomerName,
CustomerCity,
ROW_NUMBER() OVER(ORDER BY CustomerName ) AS Seq,
COUNT(*) OVER(PARTITION BY '') AS Total
FROM Customers
WHERE CustomerCity IN ('A-City','B-City')
ORDER BY CustomerName ASC
)
SELECT * FROM cte
WHERE seq BETWEEN (@page - 1 ) * @size + 1 AND @page * @size
ORDER BY seq;

SELECT
*,
COUNT(*) OVER(PARTITION BY '') AS Total
FROM Customers
WHERE CustomerCity IN ('A-City','B-City')
ORDER BY CustomerID
OFFSET (@page -1) * @size ROWS
FETCH NEXT @size ROWS ONLY;
GO

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

结果：

二.统计信息解释

在平时优化SQL的时候，最长用的就是：SET STATISTICS ON，它可以用来查看我们写的查询语句到底性能如何，不过，究竟这个性能的指标是怎么样的呢？首先需要明白的，就是各项数据的意义。

以下解释来自MSDN（点击查看） 

输出项	含义
Table	表的名称。
scan count	执行的扫描次数。
logical reads	从数据缓存读取的页数。
physical reads	从磁盘读取的页数。
read-ahead reads	为进行查询而放入缓存的页数。
lob logical reads	从数据缓存读取的 text、ntext、image 或大值类型 (varchar(max)、nvarchar(max)、varbinary(max)) 页的数目。
lob physical reads	从磁盘读取的 text、ntext、image 或大值类型页的数目。
lob read-ahead reads	为进行查询而放入缓存的 text、ntext、image 或大值类型页的数目。

以下解释来自园子里面的一位大师，嘿嘿（点击查看原文）

扫描计数(Scan Count)：在查询中涉及到的表被访问的次数。在我们的例子中，其中的表只被访问了1次，由于查询中不包括连接命令，这一信息并不是十分有用，但如果查询中包含有一个或多个连接，则这一信息是十分有用的。（一个循环外部的表的Scan Count值为1，但对于一个循环内的表而言，其值为循环的次数。可以想象得到，对于一个循环内的表而言，其Scan Count值越小，它所使用的资源越少，查询的性能也就越高。因此在调节一个带连接的查询的性能时，需要关注Scan Count的值，在进行调节时，注意观察它是增加还是减少了。）

逻辑读取(Logical Reads)：这是SET STATISTICS IO或SET STATISTICS TIME命令提供的最有用的 数据。我们知道，SQL Server在可以对任何数据进行操作前，必须首先把数据读取到其数据缓冲区中。此外，我们也知道SQL Server何时会从数据缓冲区中读取数据，并把数据读取到大小为8K字节的页中。那么Logical Reads的意义是什么呢？Logical Reads是指SQL Server为得到查询中的结果而必须从数据缓冲区读取的页数。在执行查询时，SQL Server不会读取比实际需求多或少的数据，因此，当在相同的数据集上执行同一个查询，得到的Logical Reads的数字总是相同的。（SQL Server执行查询时的Logical Reads值每一次这个数值是不会变化的。因此，在进行查询性能的调节时，这是一个可以用来衡量你的调节措施是否成功的一个很好的标准。如果 Logical Reads值下降，就表明查询使用的服务器资源减少，查询的性能有所提高。如果Logical Reads值增加，则表示调节措施降低了查询的性能。在其他条件不变的情况下，一个查询使用的逻辑读越少，其效率就越高，查询的速度就越快。）

物理读取(Physical Reads)：物理读，在执行真正的查询操作前，SQL Server必须从磁盘上向数据缓冲区中读取它所需要的数据。在SQL Server开始执行查询前，它要作的第一件事就是检查它所需要的数据是否在数据缓冲区中，如果在，就从中读取，如果不在，SQL Server必须首先将它需要的数据从磁盘上读到数据缓冲区中。我们可以想象得到，SQL Server在执行物理读时比执行逻辑读需要更多的服务器资源。因此，在理想情况下，我们应当尽量避免物理读操作。下面的这一部分听起来让人容易感到糊涂 了。在对查询的性能进行调节时，可以忽略物理读而只专注于逻辑读。你一定会纳闷儿，刚才不是还说物理读比逻辑读需要更多的服务器资源吗？情况确实是这样， SQL Server在执行查询时所需要的物理读次数不可能通过性能调节而减少的。减少物理读的次数是DBA的一项重要工作，但它涉及到整个服务器性能的调节，而 不仅仅是查询性能的调节。在进行查询性能调节时，我们不能控制数据缓冲区的大小或服务器的忙碌程度以及完成查询所需要的数据是在数据缓冲区中还是在磁盘 上，唯一我们能够控制的数据是得到查询结果所需要执行的逻辑读的次数。因此，在查询性能的调节中，我们可以心安理得地不理会SET STATISTICS IO命令提供的Physical Read的值。（减少物理读次数、加快SQL Server运行速度的一种方式是确保服务器的物理内存足够多。）

预计(Read-Ahead Reads)：与Physical Reads一样，这个值在查询性能调节中也没有什么用。Read-Ahead Reads表示SQL Server在执行预读机制时读取的物理页。为了优化其性能，SQL Server在认为它需要数据之前预先读取一部分数据，根据SQL Server对数据需求预测的准确程度，预读的数据页可能有用，也可能没用。