數(shù)據(jù)庫(kù)分頁(yè)是老生常談的問(wèn)題了。如果使用ORM框架，再使用LINQ的話(huà)，一個(gè)Skip和Take就可以搞定。但是有時(shí)由于限制，需要使用存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)。在SQLServer中使用存儲(chǔ)過(guò)程實(shí)現(xiàn)分頁(yè)的已經(jīng)有很多方法了。之前在面試中遇到過(guò)這一問(wèn)題，問(wèn)如何高效實(shí)現(xiàn)數(shù)據(jù)庫(kù)分頁(yè)。剛好上周在業(yè)務(wù)中也遇到了這個(gè)需求，所以在這里簡(jiǎn)單記錄和分享一下。

一 需求

這里以SQLServer的示例數(shù)據(jù)庫(kù)NorthWind為例，里面有一張Product表，現(xiàn)在假設(shè)我們的需求是要以UnitPrice降序排列，并且分頁(yè)，每一頁(yè)10條記錄。要求服務(wù)端分頁(yè)。參數(shù)為每頁(yè)記錄數(shù)和頁(yè)碼。

二 實(shí)現(xiàn)

Top分頁(yè)

當(dāng)時(shí)采用的最直接做法就是使用兩個(gè)Top來(lái)實(shí)現(xiàn)， 最后返回的結(jié)果是升序的，在C#代碼里再處理一下就可以了。 這里作為演示，語(yǔ)句中使用 * 為了方便，實(shí)際開(kāi)發(fā)中要替換為具體的列名。下面的方法簡(jiǎn)單吧。

SELECT TOP (@pageSize)* FROM ( SELECT TOP ( @pageSize * @pageIndex )*FROM [Northwind].[dbo].[Products]ORDER BY UnitPrice DESC) AS product ORDER BY product.UnitPrice

但是這個(gè)代碼是有問(wèn)題的，不知道各位發(fā)現(xiàn)了沒(méi)有。當(dāng)符合條件的紀(jì)錄集小于每頁(yè)記錄數(shù)時(shí)，沒(méi)有問(wèn)題，但是當(dāng)大于就有問(wèn)題了，比如，在實(shí)例數(shù)據(jù)庫(kù)中Products中有 77 條記錄，當(dāng)每頁(yè)10條記錄，第8頁(yè)只應(yīng)該返回7條記錄，第9頁(yè)應(yīng)該返回空，但是使用如上的方法，每次都會(huì)返回10條記錄。

沿用上面的思路，把代碼修改為了如下采用三層Select，最內(nèi)一層查詢(xún)所有記錄之前的數(shù)據(jù)，然后第二層選擇Top PageSize個(gè)所有NOT IN 第一層數(shù)據(jù)中的數(shù)據(jù)即可，因?yàn)槭褂昧薔OT IN所以不存在第一種方法中的bug

SELECT * FROM dbo.Products WHERE ProductID IN (SELECT TOP ( @pageSize )ProductIDFROM dbo.ProductsWHERE ProductID NOT IN ( SELECT TOP ( @pageSize * (@pageIndex-1) )ProductIDFROM dbo.ProductsORDER BY UnitPrice DESC )ORDER BY dbo.Products.UnitPrice DESC ) ORDER BY dbo.Products.UnitPrice ASC

使用ROW_NUMBER 函數(shù)分頁(yè)

其實(shí)還有一種最簡(jiǎn)單最直接的思路，那就是采用臨時(shí)表，即在內(nèi)存中創(chuàng)建一個(gè)表變量，該變量中包含一個(gè)自增列，表關(guān)鍵字列，然后將待排序的表按照排序條件和規(guī)則插入到這張表中，然后就可以將自增列作為行號(hào)使用了，在比較早的如SQLServer 2000中，只能這樣做，但是對(duì)于大數(shù)據(jù)量的記錄集，需要?jiǎng)?chuàng)建的臨時(shí)表也比較大，效率比較低，這里就不介紹了。

在SQLServer2005中引入了ROW_NUMBER()?函數(shù),通過(guò)這個(gè)函數(shù)，可以根據(jù)給定好的排序字段規(guī)則，生成記錄序號(hào)，其基本用法為：

SELECT ROW_NUMBER() OVER ( ORDER BY dbo.Products.ProductID DESC ) AS rownum ,* FROM dbo.Products

這樣，結(jié)果集中第一列就為 rownum，從1開(kāi)始按步長(zhǎng)為1遞增，這有點(diǎn)類(lèi)似從1開(kāi)始步長(zhǎng)為1的自增字段。 這里需要提一下的是，這個(gè)語(yǔ)句中賦值的rownum列不能使用在當(dāng)前的where語(yǔ)句中，也不可以把整個(gè)ROW_NUMBER()語(yǔ)句放到where中作為條件，下面兩種使用方式都是錯(cuò)誤的。

SELECT ROW_NUMBER() OVER ( ORDER BY dbo.Products.ProductID DESC ) AS rownum ,* FROM dbo.Products WHERE rownum BETWEEN 1 AND 10

會(huì)提示錯(cuò)誤：

Invalid column name 'rownum'. SELECT ROW_NUMBER() OVER ( ORDER BY dbo.Products.ProductID DESC ) AS rownum ,* FROM dbo.Products WHERE ( ROW_NUMBER() OVER (ORDER BY City) AS rown ) BETWEEN 1 AND 10

會(huì)提示錯(cuò)誤：

Incorrect syntax near the keyword 'AS'.

正確的做法是，把查詢(xún)的結(jié)果作為一個(gè)內(nèi)查詢(xún)，再在外面套上一個(gè)外查詢(xún)語(yǔ)句：

SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY dbo.Products.ProductID DESC ) AS rownum ,*FROM dbo.Products) AS temp WHERE temp.rownum BETWEEN 1 AND 10

有了以上基礎(chǔ)之后，我們就可以利用ROW_NUMBER這個(gè)特性來(lái)進(jìn)行排序了。

SELECT * FROM ( SELECT TOP ( @pageSize * @pageIndex )ROW_NUMBER() OVER ( ORDER BY dbo.Products.UnitPrice DESC ) AS rownum ,*FROM dbo.Products) AS temp WHERE temp.rownum > ( @pageSize * ( @pageIndex - 1 ) ) ORDER BY temp.UnitPrice

策略很簡(jiǎn)單，首先我們選取包含要查頁(yè)的數(shù)據(jù)，然后使用ROW_NUMER函數(shù)進(jìn)行編號(hào)， 然后在外查詢(xún)中指定rownum大于頁(yè)起始記錄即可。這種方式簡(jiǎn)單快捷。

這里還有一種使用CTE的方式?(common_table_expression，公用表表達(dá)式，不是CTE四六級(jí)哦， 我第一次接觸到這個(gè)是面試的時(shí)候被問(wèn)到如何使用SQL編寫(xiě)遞歸， 呵呵)，使用很簡(jiǎn)單，就是把內(nèi)查詢(xún)放在CTE 里面，如下：

WITH ProductEntityAS ( SELECT TOP ( @pageSize * @pageIndex )ROW_NUMBER() OVER ( ORDER BY dbo.Products.UnitPrice DESC ) AS rownum ,*FROM dbo.Products) SELECT * FROM ProductEntity WHERE ProductEntity.rownum > ( @pageSize * ( @pageIndex - 1 ) ) ORDER BY ProductEntity.UnitPrice

這種性能和上面的類(lèi)似。但是在某些情況下， 使用CTE會(huì)比直接采用外接查詢(xún)具有更好的效率。例如，我們可以?xún)H使用CTE來(lái)存儲(chǔ)行號(hào)，關(guān)鍵字以及排序字段，然后用來(lái)和原表做join查詢(xún)，如下：

WITH ProductEntityAS ( SELECT TOP ( @pageSize * @pageIndex )ROW_NUMBER() OVER ( ORDER BY dbo.Products.UnitPrice DESC ) AS rownum ,ProductID ,--主鍵，UnitPrice--待排序字段FROM dbo.Products) SELECT * FROM ProductEntityINNER JOIN dbo.Products ON dbo.Products.ProductID = ProductEntity.ProductID WHERE ProductEntity.rownum > ( @pageSize * ( @pageIndex - 1 ) ) ORDER BY ProductEntity.UnitPrice

使用ROW_NUMBER來(lái)進(jìn)行分頁(yè)是一種使用很廣的分頁(yè)方式， 在本文開(kāi)頭講到在LINQ中可以采用的TAKE 和 SKIP語(yǔ)句，但是與數(shù)據(jù)庫(kù)交互只能使用SQL語(yǔ)句，LINQ在內(nèi)部會(huì)幫我們轉(zhuǎn)化為合適的SQL語(yǔ)句，語(yǔ)句里面其實(shí)也是采用ROW_NUMBER這一函數(shù)，為了演示，我們新建一個(gè)Console程序，然后在里面添加一個(gè)LINQ To SQL的類(lèi)，使用方法非常簡(jiǎn)單，如下：

List<Product> product; int pageSize = 10; int pageIndex = 8; using (ProductsDataContext context = new ProductsDataContext()) {product = context.Products.OrderByDescending(x => x.UnitPrice)//排序.Skip(pageSize * (pageIndex-1))//跳過(guò)前面的記錄.Take(pageSize)//選取每一頁(yè)個(gè)數(shù).ToList(); }

寥寥幾句就實(shí)現(xiàn)了分頁(yè)。

我們知道LINQ其實(shí)是將C#表達(dá)式樹(shù)轉(zhuǎn)換成了SQL語(yǔ)言，通過(guò)SQLServer Profile 工具，我們可以看到程序發(fā)送給SQLServer的請(qǐng)求，如下：

我把下面的語(yǔ)句拷貝出來(lái)，可以看到

EXEC sp_executesql N'SELECT [t1].[ProductID], [t1].[ProductName], [t1].[SupplierID], [t1].[CategoryID], [t1].[QuantityPerUnit], [t1].[UnitPrice], [t1].[UnitsInStock], [t1].[UnitsOnOrder], [t1].[ReorderLevel], [t1].[Discontinued] FROM (SELECT ROW_NUMBER() OVER (ORDER BY [t0].[UnitPrice] DESC) AS [ROW_NUMBER], [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]FROM [dbo].[Products] AS [t0]) AS [t1] WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1 ORDER BY [t1].[ROW_NUMBER]', N'@p0 int,@p1 int', @p0 = 70, @p1 = 10

這正是我們之前手寫(xiě)的采用ROW_NUMBER 的分頁(yè)程序。可見(jiàn)，簡(jiǎn)簡(jiǎn)單單的一句SKIP和TAKE，LINQ在后面幫我們做了很多工作。

使用OFFSET FETCH子句分頁(yè)

既然LINQ這么簡(jiǎn)單的搞定了分頁(yè)，那么SQLServer中有沒(méi)有類(lèi)似的簡(jiǎn)單的語(yǔ)句就能搞定分頁(yè)了，答案是有的，那就是SQL Server Compact 4.0中引入的OFFSET FETCH子句。

SELECT * FROM dbo.Products ORDER BY UnitPrice DESC OFFSET ( @pageSize * ( @pageIndex - 1 )) ROWS FETCH NEXT @pageSize ROWS ONLY;

是不是和LINQ很像，OFFSEET相當(dāng)于SKIP，FETCH NEXT相當(dāng)于TAKE。

可以在官網(wǎng)上下載SQL Server CE 4.0，目前僅支持SQL Server 2012及SQL Server 2014，不過(guò)可以使用Microsoft Webmatrix這個(gè)工具來(lái)用這一新功能。

比較

在討論性能之前，首先需要明確的是，我們?cè)诰帉?xiě)SQL語(yǔ)句的時(shí)候，盡量要減少不必要字段的輸出，文中出于演示，所以都用的*，在實(shí)際中不要這樣。還有就是要根據(jù)業(yè)務(wù)邏輯，比如查詢(xún)條件，建立合適的聚合索引和非聚合索引，索引對(duì)于查找的效率影響非常大，SQL中的索引其實(shí)就是建立某種平衡查找樹(shù)，如B樹(shù)來(lái)進(jìn)行，這方面的知識(shí)可以看我之前寫(xiě)的算法中的文章，再有就是了解一下SQL Server 的一些特性比如CTE，IN 和Exist的區(qū)別等等，有些小的地方對(duì)性能可能有一定的影響。

在上面這些處理好了之后，我們現(xiàn)在來(lái)討論那種分頁(yè)方案更好。

• 采用Top – Not In - Top方案比較復(fù)雜，里面包含了in語(yǔ)句，效率不高，但是兼容個(gè)版本的SQL Server。
• 采用ROW_Number方法實(shí)現(xiàn)分頁(yè)難易適中，效率較高。LINQ中的SKIP和TAKE也是采用這種方式來(lái)進(jìn)行分頁(yè)的，應(yīng)該是目前采用的比較廣泛的分頁(yè)方式。
• FFSET FETCH 方法是SQL Server CE 4.0?中才引入的，由于本文沒(méi)有SQL Server 2012以及測(cè)試數(shù)據(jù)，從comparing-performance-for-different-sql-server-paging-methods這篇文章來(lái)以及園子里的Sql Server 2012 分頁(yè)方法分析(offset and fetch)，性能應(yīng)該是比較好的。

以上是對(duì)SQLServer數(shù)據(jù)庫(kù)SQL分頁(yè)的一點(diǎn)總結(jié)，希望對(duì)您有所幫助。

總結(jié)

以上是生活随笔為你收集整理的浅谈SQL Server数据库分页的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。