建立一個 Web 應用，分頁瀏覽功能必不可少。這個問題是數據庫處理中十分常見的問題。經典的數據分頁方法是:ADO 紀錄集分頁法，也就是利用ADO自帶的分頁功能（利用游標）來實現分頁。但這種分頁方法僅適用于較小數據量的情形，因為游標本身有缺點：游標是存放在內存中，很費內存。游標一建立，就將相關的記錄鎖住，直到取消游標。游標提供了對特定集合中逐行掃描的手段，一般使用游標來逐行遍歷數據，根據取出數據條件的不同進行不同的操作。而對于多表和大表中定義的游標（大的數據集合）循環很容易使程序進入一個漫長的等待甚至死機。
更重要的是，對于非常大的數據模型而言，分頁檢索時，如果按照傳統的每次都加載整個數據源的方法是非常浪費資源的。現在流行的分頁方法一般是檢索頁面大小的塊區的數據，而非檢索所有的數據，然后單步執行當前行。
?
? 最早較好地實現這種根據頁面大小和頁碼來提取數據的方法大概就是“俄羅斯存儲過程”。這個存儲過程用了游標，由于游標的局限性，所以這個方法并沒有得到大家的普遍認可。
? 后來，網上有人改造了此存儲過程，下面的存儲過程就是結合我們的辦公自動化實例寫的分頁存儲過程：
? /* ***********************************************************
?*?Time:?2011-10-05?20:32:46
?*********************************************************** */
CREATE ? PROCEDURE ?page_t(
???? @pagesize ??? INT ,???? -- 頁面大小，如每頁存儲20條記錄
???? @pageindex ?? INT ? -- 當前頁碼
)
AS
???? SET ?NOCOUNT? ON
????
???? BEGIN
???????? DECLARE ? @indextable ?????? TABLE (id? INT ? IDENTITY ( 1 ,? 1 ),?nid? INT )? -- 定義表變量
???????? DECLARE ? @PageLowerBound ?? INT ? -- 定義此頁的底碼
???????? DECLARE ? @PageUpperBound ?? INT ? -- 定義此頁的頂碼
???????? SET ? @PageLowerBound ? = ?( @pageindex ? - 1 )? * ? @pagesize
???????? SET ? @PageUpperBound ? = ? @PageLowerBound ? + ? @pagesize
???????? SET ? ROWCOUNT ? @PageUpperBound
???????? INSERT ? INTO ? @indextable
??????????(
????????????nid
??????????)
???????? SELECT ?id
???????? FROM ???tt?
???????? -- where?id?>dateadd(day,-365,getdate())?order?by?id?desc
???????? SELECT ?O.id
???????? FROM ???tt?O,
??????????????? @indextable ?t
???????? WHERE ??O.id? = ?t.nid
??????????????? AND ?t.id? > ? @PageLowerBound
??????????????? AND ?t.id? <= ? @PageUpperBound
???????? ORDER ? BY
???????????????t.id
???? END
????
???? SET ?NOCOUNT? OFF
????
?
-- --exec?page_t?100,2
?
?
?
? 從tt表中取出第 n 條到第 m 條的記錄：
? DECLARE ? @m ? INT ?
SET ? @m = 10000
set ? @n = 2
SELECT ? TOP ?( @m - @n + 1 )? * ?? FROM ?tt?
WHERE ?(id? NOT ? IN ??( SELECT ? TOP ?( @n - 1 )?id??? FROM ?tt))
id 為tt表的關鍵字
我當時看到這篇文章的時候，真的是精神為之一振，覺得思路非常得好。這個存儲過程也是目前較為流行的一種分頁存儲過程
? /* ***********************************************************
?*?Time:?2011-10-05?21:12:08
?*********************************************************** */
? CREATE ?? PROC ?pagination2(
????????????????????????????? @SQL ? NVARCHAR ( 4000 ),???? -- 不帶排序語句的SQL語句
????????????????????????????? @Page ? INT ,???? -- 頁碼
????????????????????????????? @RecsPerPage ? INT ,???? -- 每頁容納的記錄數
????????????????????????????? @ID ? VARCHAR ( 255 ),???? -- 需要排序的不重復的ID號
????????????????????????????? @Sort ? VARCHAR ( 255 )? -- 排序字段及規則
?????????????????????????)
AS
DECLARE ? @Str ? NVARCHAR ( 4000 )
SET ? @Str ? = ? ' SELECT?TOP? ' ? + ? CAST ( @RecsPerPage ? AS ? VARCHAR ( 20 ))? + ? ' ?*?FROM?
( ' ? + ? @SQL ? + ? ' )?T?WHERE?T. ' ? + ? @ID ? + ? ' ?NOT?IN?(SELECT?TOP? ' ? + ? CAST (( @RecsPerPage ? * ?( @Page ? - 1 ))? AS ? VARCHAR ( 20 ))
???? + ? ' ? ' ? + ? @ID ? + ? ' ?FROM?( ' ? + ? @SQL ? + ? ' )?T9?ORDER?BY? ' ? + ? @Sort ? + ? ' )?ORDER?BY? ' ? + ?
???? @Sort
PRINT ? @Str
EXEC ?sp_ExecuteSql? @Str
GO
-- ---pagination2?'SELECT?id?FROM?tt',2,100,'id','id'?
? ??
?其實，以上語句可以簡化為：
? SELECT ? ? TOP? 100 /* 頁大小 */ *
ORDER ? BY ?ID
?
?
?
FROM ???TT?a
WHERE ?? NOT ? EXISTS
???????(
??????????? SELECT ? *
??????????? FROM ???(
?????????????????????? SELECT ? TOP ( 100 /* 頁大小? */ ? * ? 0 /* 頁數 */ )? * ?
?????????????????????? FROM ???TT
?????????????????????? ORDER ? BY
?????????????????????????????id
??????????????????)?b
??????????? WHERE ??b.id? = ?a.id
???????)
ORDER ? BY ?id? ?
?
即，用not exists來代替not in，但我們前面已經談過了，二者的執行效率實際上是沒有區別的。既便如此，用TOP 結合NOT IN的這個方法還是比用游標要來得快一些。
?
雖然用not exists并不能挽救上個存儲過程的效率，但使用SQL SERVER中的TOP關鍵字卻是一個非常明智的選擇。因為分頁優化的最終目的就是避免產生過大的記錄集，而我們在前面也已經提到了TOP的優勢，通過TOP 即可實現對數據量的控制。
在分頁算法中，影響我們查詢速度的關鍵因素有兩點：TOP和NOT IN。TOP可以提高我們的查詢速度，而NOT IN會減慢我們的查詢速度，所以要提高我們整個分頁算法的速度，就要徹底改造NOT IN，同其他方法來替代它。
?
我們知道，幾乎任何字段，我們都可以通過max(字段)或min(字段)來提取某個字段中的最大或最小值，所以如果這個字段不重復，那么就可以利用這些不重復的字段的max或min作為分水嶺，使其成為分頁算法中分開每頁的參照物。在這里，我們可以用操作符“>”或“<”號來完成這個使命，使查詢語句符合SARG形式。如：?
?
?
FROM ???tt
WHERE ??id? > ? 200
?
?
? 于是就有了如下分頁方案：
select ? top ?頁大小? *
from ?tt
where ?id >
( select ? max ?(id)? from ?
( select ? top ?((頁碼 - 1 ) * 頁大小)?id? from ?tt? order ? by ?id)? as ?T
)?
order ? by ?id
?
?
? 在選擇即不重復值，又容易分辨大小的列時，我們通常會選擇主鍵。下表列出了筆者用有著1000萬數據的辦公自動化系統中的表，在以id（id是主鍵，但并不是聚集索引。）為排序列、提取id字段，分別以第1、10、100、500、1000、1萬、10萬、25萬、50萬頁為例，測試以上三種分頁方案的執行速度：（單位：毫秒）
| 頁碼 | 方案1 | 方案2 | 方案3 |
| 1 | 60 | 30 | 76 |
| 10 | 46 | 16 | 63 |
| 100 | 1076 | 720 | 130 |
| 500 | 540 | 12943 | 83 |
| 1000 | 17110 | 470 | 250 |
| 10000 | 24796 | 4500 | 140 |
| 100000 | 38326 | 42283 | 1553 |
| 250000 | 28140 | 128720 | 2330 |
| 500000 | 121686 | 127846 | 7168 |
從上表中，我們可以看出，三種存儲過程在執行100頁以下的分頁命令時，都是可以信任的，速度都很好。但第一種方案在執行分頁1000頁以上后，速度就降了下來。第二種方案大約是在執行分頁1萬頁以上后速度開始降了下來。而第三種方案卻始終沒有大的降勢，后勁仍然很足。
?
? 在確定了第三種分頁方案后，我們可以據此寫一個存儲過程。大家知道SQL SERVER的存儲過程是事先編譯好的SQL語句，它的執行效率要比通過WEB頁面傳來的SQL語句的執行效率要高。下面的存儲過程不僅含有分頁方案，還會根據頁面傳來的參數來確定是否進行數據總數統計。
獲取指定頁的數據：
? /* ***********************************************************
?*?Time:?2011-10-05?21:40:08
?*********************************************************** */
CREATE ? PROCEDURE ?PAGE01
???? @tblName ? VARCHAR ( 255 ),? -- ?表名
???? @strGetFields ? VARCHAR ( 1000 )? = ? ' * ' ,? -- ?需要返回的列
???? @fldName ? VARCHAR ( 255 )? = ? '' ,? -- ?排序的字段名
???? @PageSize ? INT ? = ? 10 ,? -- ?頁尺寸
???? @PageIndex ? INT ? = ? 1 ,? -- ?頁碼
???? @doCount ? BIT ? = ? 0 ,? -- ?返回記錄總數,?非?0?值則返回
???? @OrderType ? BIT ? = ? 0 ,? -- ?設置排序類型,?非?0?值則降序
???? @strWhere ? VARCHAR ( 1500 )? = ? '''' ? -- ?查詢條件?(注意:?不要加?where)
AS
???? DECLARE ? @strSQL ???? VARCHAR ( 5000 )? -- ?主語句
???? DECLARE ? @strTmp ???? VARCHAR ( 110 )? -- ?臨時變量
???? DECLARE ? @strOrder ?? VARCHAR ( 400 )? -- ?排序類型
???? IF ? @doCount ? != ? 0
???? BEGIN
???????? IF ? @strWhere ? != ? ''''
???????????? SET ? @strSQL ? = ? ' select?count(*)?as?Total?from ' ? + ? ' ? ' ? + ? @tblName ? + ?
???????????????? ' ?where ' ? + ? @strWhere
???????? ELSE
???????????? SET ? @strSQL ? = ? ' select?count(*)?as?Total?from? ' ? + ? @tblName
???? END -- 以上代碼的意思是如果@doCount傳遞過來的不是0，就執行總數統計。以下的所有代碼都是@doCount為0的情況：
???? ELSE
???? BEGIN
???????? IF ? @OrderType ? != ? 0
???????? BEGIN
???????????? SET ? @strTmp ? = ? ' <(select?min '
???????????? SET ? @strOrder ? = ? ' ?order?by? ' ? + ? @fldName ? + ? ' ?desc '
???????????????? -- 如果@OrderType不是0，就執行降序，這句很重要！
???????? END
???????? ELSE
???????? BEGIN
???????????? SET ? @strTmp ? = ? ' >(select?max '
???????????? SET ? @strOrder ? = ? ' ?order?by? ' ? + ? @fldName ? + ? ' ?asc '
???????? END
???????? IF ? @PageIndex ? = ? 1
???????? BEGIN
???????????? IF ? @strWhere ? != ? ''''
???????????????? SET ? @strSQL ? = ? ' select?top? ' ? + ? STR ( @PageSize )? + ? ' ? ' ? + ? @strGetFields
???????????????????? + ? '
?????????????????from? ' ? + ? @tblName ? + ? ' ?where? ' ? + ? @strWhere ? + ? ' ? ' ? + ? @strOrder
???????????? ELSE
???????????????? SET ? @strSQL ? = ? ' select?top? ' ? + ? STR ( @PageSize )? + ? ' ? ' ? + ? @strGetFields
???????????????????? + ? ' ?
???????????from? ' ? + ? @tblName ? + ? ' ? ' ? + ? @strOrder
???????????????????? -- 如果是第一頁就執行以上代碼，這樣會加快執行速度
???????? END
???????? ELSE
???????? BEGIN
???????????? -- 以下代碼賦予了@strSQL以真正執行的SQL代碼
???????????? SET ? @strSQL ? = ? ' select?top? ' ? + ? STR ( @PageSize )? + ? ' ? ' ? + ? @strGetFields ? + ?
???????????????? ' ?from? '
???????????????? + ? @tblName ? + ? ' ?where? ' ? + ? @fldName ? + ? '' ? + ? @strTmp ? + ? ' ( ' ? + ? @fldName ?
???????????????? + ? ' )?
??????????from?(select?top? ' ? + ? STR (( @PageIndex ? - 1 )? * ? @PageSize )? + ? ' ? ' ? + ? @fldName ?
???????????????? + ? ' ?
??????????from? ' ? + ? @tblName ? + ? '' ? + ? @strOrder ? + ? ' )?as?tblTmp) ' ? + ? @strOrder
????????????
???????????? IF ? @strWhere ? != ? ''''
???????????????? SET ? @strSQL ? = ? ' select?top? ' ? + ? STR ( @PageSize )? + ? ' ? ' ? + ? @strGetFields
???????????????????? + ? ' ?from?[ '
???????????????????? + ? @tblName ? + ? ' ]?where?[ ' ? + ? @fldName ? + ? ' ] ' ? + ? @strTmp ? + ? ' ([ '
???????????????????? + ? @fldName ? + ? ' ])?from?(select?top? ' ? + ? STR (( @PageIndex ? - 1 )? * ? @PageSize )?
???????????????????? + ? ' ?[ '
???????????????????? + ? @fldName ? + ? ' ]?from?[ ' ? + ? @tblName ? + ? ' ]?where? ' ? + ? @strWhere ?
???????????????????? + ? ' ? '
???????????????????? + ? @strOrder ? + ? ' )?as?tblTmp)?and? ' ? + ? @strWhere ? + ? ' ? ' ? + ? @strOrder
???????? END
???? END ?
???? EXEC ?( @strSQL )
GO
? -- -----PAGE01?'tt','*','id'
?
?
?
?*?Code?formatted?by?SoftTree?SQL?Assistant??v5.0.97
?*?Time:?2011-10-05?21:35:12
?*********************************************************** */
CREATE ? PROCEDURE ?PAGE1_tt
???? @tblName ? VARCHAR ( 255 ),? -- ?表名
???? @strGetFields ? VARCHAR ( 1000 )? = ? ' * ' ,? -- ?需要返回的列
???? @fldName ? VARCHAR ( 255 )? = ? '' ,? -- ?排序的字段名
???? @PageSize ? INT ? = ? 10 ,? -- ?頁尺寸
???? @PageIndex ? INT ? = ? 1 ,? -- ?頁碼
???? @doCount ? BIT ? = ? 0 ,? -- ?返回記錄總數,?非?0?值則返回
???? @OrderType ? BIT ? = ? 0 ,? -- ?設置排序類型,?非?0?值則降序
???? @strWhere ? VARCHAR ( 1500 )? = ? '''' ? -- ?查詢條件?(注意:?不要加?where)
AS
???? DECLARE ? @strSQL ???? VARCHAR ( 5000 )? -- ?主語句
???? DECLARE ? @strTmp ???? VARCHAR ( 110 )? -- ?臨時變量
???? DECLARE ? @strOrder ?? VARCHAR ( 400 )? -- ?排序類型
???? IF ? @doCount ? != ? 0
???? BEGIN
???????? IF ? @strWhere ? != ? ''''
???????????? SET ? @strSQL ? = ? ' select?count(*)?as?Total?from ' ? + ? ' ? ' ? + ? @tblName ? + ?
???????????????? ' ?where ' ? + ? @strWhere
???????? ELSE
???????????? SET ? @strSQL ? = ? ' select?count(*)?as?Total?from? ' ? + ? @tblName
???? END -- 以上代碼的意思是如果@doCount傳遞過來的不是0，就執行總數統計。以下的所有代碼都是@doCount為0的情況：
???? ELSE
???? BEGIN
???????? IF ? @OrderType ? != ? 0
???????? BEGIN
???????????? SET ? @strTmp ? = ? ' <(select?min '
???????????? SET ? @strOrder ? = ? ' ?order?by? ' ? + ? @fldName ? + ? ' ?desc '
???????????????? -- 如果@OrderType不是0，就執行降序，這句很重要！
???????? END
???????? ELSE
???????? BEGIN
???????????? SET ? @strTmp ? = ? ' >(select?max '
???????????? SET ? @strOrder ? = ? ' ?order?by? ' ? + ? @fldName ? + ? ' ?asc '
???????? END
???????? IF ? @PageIndex ? = ? 1
???????? BEGIN
???????????? IF ? @strWhere ? != ? ''''
???????????????? SET ? @strSQL ? = ? ' select?top? ' ? + ? STR ( @PageSize )? + ? ' ? ' ? + ? @strGetFields
???????????????????? + ? '
?????????????????from? ' ? + ? @tblName ? + ? ' ?where? ' ? + ? @strWhere ? + ? ' ? ' ? + ? @strOrder
???????????? ELSE
???????????????? SET ? @strSQL ? = ? ' select?top? ' ? + ? STR ( @PageSize )? + ? ' ? ' ? + ? @strGetFields
???????????????????? + ? ' ?
???????????from? ' ? + ? @tblName ? + ? ' ? ' ? + ? @strOrder
???????????????????? -- 如果是第一頁就執行以上代碼，這樣會加快執行速度
???????? END
???????? ELSE
???????? BEGIN
???????????? -- 以下代碼賦予了@strSQL以真正執行的SQL代碼
???????????? SET ? @strSQL ? = ? ' select?top? ' ? + ? STR ( @PageSize )? + ? ' ? ' ? + ? @strGetFields ? + ?
???????????????? ' ?from? '
???????????????? + ? @tblName ? + ? ' ?where? ' ? + ? @fldName ? + ? '' ? + ? @strTmp ? + ? ' ( ' ? + ? @fldName ?
???????????????? + ? ' )?
??????????from?(select?top? ' ? + ? STR (( @PageIndex ? - 1 )? * ? @PageSize )? + ? ' ? ' ? + ? @fldName ?
???????????????? + ? ' ?
??????????from? ' ? + ? @tblName ? + ? '' ? + ? @strOrder ? + ? ' )?as?tblTmp) ' ? + ? @strOrder
????????????
???????????? IF ? @strWhere ? != ? ''''
???????????????? SET ? @strSQL ? = ? ' select?top? ' ? + ? STR ( @PageSize )? + ? ' ? ' ? + ? @strGetFields
???????????????????? + ? ' ?from?[ '
???????????????????? + ? @tblName ? + ? ' ]?where?[ ' ? + ? @fldName ? + ? ' ] ' ? + ? @strTmp ? + ? ' ([ '
???????????????????? + ? @fldName ? + ? ' ])?from?(select?top? ' ? + ? STR (( @PageIndex ? - 1 )? * ? @PageSize )?
???????????????????? + ? ' ?[ '
???????????????????? + ? @fldName ? + ? ' ]?from?[ ' ? + ? @tblName ? + ? ' ]?where? ' ? + ? @strWhere ?
???????????????????? + ? ' ? '
???????????????????? + ? @strOrder ? + ? ' )?as?tblTmp)?and? ' ? + ? @strWhere ? + ? ' ? ' ? + ? @strOrder
???????? END
???? END ?
???? EXEC ?( @strSQL )
GO
? -- PAGE1_tt ? 'tt','*','id',100,1,0
?
? 上面的這個存儲過程是一個通用的存儲過程，其注釋已寫在其中了。 在大數據量的情況下，特別是在查詢最后幾頁的時候，查詢時間一般不會超過9秒；而用其他存儲過程，在實踐中就會導致超時，所以這個存儲過程非常適用于大容量數據庫的查詢。 筆者希望能夠通過對以上存儲過程的解析，能給大家帶來一定的啟示，并給工作帶來一定的效率提升，同時希望同行提出更優秀的實時數據分頁算法。
以上的這第三種存儲過程在小數據量的情況下，有如下現象：
1、分頁速度一般維持在1秒和3秒之間。
2、在查詢最后一頁時，速度一般為5秒至8秒，哪怕分頁總數只有3頁或30萬頁。
?
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
