建立一個 Web 應用，分頁瀏覽功能必不可少。這個問題是數(shù)據(jù)庫處理中十分常見的問題。經(jīng)典的數(shù)據(jù)分頁方法是:ADO 紀錄集分頁法，也就是利用ADO自帶的分頁功能（利用游標）來實現(xiàn)分頁。但這種分頁方法僅適用于較小數(shù)據(jù)量的情形，因為游標本身有缺點：游標是存放在內(nèi)存中，很費內(nèi)存。游標一建立，就將相關的記錄鎖住，直到取消游標。游標提供了對特定集合中逐行掃描的手段，一般使用游標來逐行遍歷數(shù)據(jù)，根據(jù)取出數(shù)據(jù)條件的不同進行不同的操作。而對于多表和大表中定義的游標（大的數(shù)據(jù)集合）循環(huán)很容易使程序進入一個漫長的等待甚至死機。
更重要的是，對于非常大的數(shù)據(jù)模型而言，分頁檢索時，如果按照傳統(tǒng)的每次都加載整個數(shù)據(jù)源的方法是非常浪費資源的。現(xiàn)在流行的分頁方法一般是檢索頁面大小的塊區(qū)的數(shù)據(jù)，而非檢索所有的數(shù)據(jù)，然后單步執(zhí)行當前行。
?
? 最早較好地實現(xiàn)這種根據(jù)頁面大小和頁碼來提取數(shù)據(jù)的方法大概就是“俄羅斯存儲過程”。這個存儲過程用了游標，由于游標的局限性，所以這個方法并沒有得到大家的普遍認可。
? 后來，網(wǎng)上有人改造了此存儲過程，下面的存儲過程就是結合我們的辦公自動化實例寫的分頁存儲過程：
? /* ***********************************************************
?*?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 ,???? -- 每頁容納的記錄數(shù)
????????????????????????????? @ID ? VARCHAR ( 255 ),???? -- 需要排序的不重復的ID號
????????????????????????????? @Sort ? VARCHAR ( 255 )? -- 排序字段及規(guī)則
?????????????????????????)
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 /* 頁數(shù) */ )? * ?
?????????????????????? FROM ???TT
?????????????????????? ORDER ? BY
?????????????????????????????id
??????????????????)?b
??????????? WHERE ??b.id? = ?a.id
???????)
ORDER ? BY ?id? ?
?
即，用not exists來代替not in，但我們前面已經(jīng)談過了，二者的執(zhí)行效率實際上是沒有區(qū)別的。既便如此，用TOP 結合NOT IN的這個方法還是比用游標要來得快一些。
?
雖然用not exists并不能挽救上個存儲過程的效率，但使用SQL SERVER中的TOP關鍵字卻是一個非常明智的選擇。因為分頁優(yōu)化的最終目的就是避免產(chǎn)生過大的記錄集，而我們在前面也已經(jīng)提到了TOP的優(yōu)勢，通過TOP 即可實現(xiàn)對數(shù)據(jù)量的控制。
在分頁算法中，影響我們查詢速度的關鍵因素有兩點：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萬數(shù)據(jù)的辦公自動化系統(tǒng)中的表，在以id（id是主鍵，但并不是聚集索引。）為排序列、提取id字段，分別以第1、10、100、500、1000、1萬、10萬、25萬、50萬頁為例，測試以上三種分頁方案的執(zhí)行速度：（單位：毫秒）
| 頁碼 | 方案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 |
從上表中，我們可以看出，三種存儲過程在執(zhí)行100頁以下的分頁命令時，都是可以信任的，速度都很好。但第一種方案在執(zhí)行分頁1000頁以上后，速度就降了下來。第二種方案大約是在執(zhí)行分頁1萬頁以上后速度開始降了下來。而第三種方案卻始終沒有大的降勢，后勁仍然很足。
?
? 在確定了第三種分頁方案后，我們可以據(jù)此寫一個存儲過程。大家知道SQL SERVER的存儲過程是事先編譯好的SQL語句，它的執(zhí)行效率要比通過WEB頁面?zhèn)鱽淼腟QL語句的執(zhí)行效率要高。下面的存儲過程不僅含有分頁方案，還會根據(jù)頁面?zhèn)鱽淼膮?shù)來確定是否進行數(shù)據(jù)總數(shù)統(tǒng)計。
獲取指定頁的數(shù)據(jù)：
? /* ***********************************************************
?*?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 ,? -- ?返回記錄總數(shù),?非?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，就執(zhí)行總數(shù)統(tǒng)計。以下的所有代碼都是@doCount為0的情況：
???? ELSE
???? BEGIN
???????? IF ? @OrderType ? != ? 0
???????? BEGIN
???????????? SET ? @strTmp ? = ? ' <(select?min '
???????????? SET ? @strOrder ? = ? ' ?order?by? ' ? + ? @fldName ? + ? ' ?desc '
???????????????? -- 如果@OrderType不是0，就執(zhí)行降序，這句很重要！
???????? 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
???????????????????? -- 如果是第一頁就執(zhí)行以上代碼，這樣會加快執(zhí)行速度
???????? END
???????? ELSE
???????? BEGIN
???????????? -- 以下代碼賦予了@strSQL以真正執(zhí)行的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 ,? -- ?返回記錄總數(shù),?非?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，就執(zhí)行總數(shù)統(tǒng)計。以下的所有代碼都是@doCount為0的情況：
???? ELSE
???? BEGIN
???????? IF ? @OrderType ? != ? 0
???????? BEGIN
???????????? SET ? @strTmp ? = ? ' <(select?min '
???????????? SET ? @strOrder ? = ? ' ?order?by? ' ? + ? @fldName ? + ? ' ?desc '
???????????????? -- 如果@OrderType不是0，就執(zhí)行降序，這句很重要！
???????? 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
???????????????????? -- 如果是第一頁就執(zhí)行以上代碼，這樣會加快執(zhí)行速度
???????? END
???????? ELSE
???????? BEGIN
???????????? -- 以下代碼賦予了@strSQL以真正執(zhí)行的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
?
? 上面的這個存儲過程是一個通用的存儲過程，其注釋已寫在其中了。 在大數(shù)據(jù)量的情況下，特別是在查詢最后幾頁的時候，查詢時間一般不會超過9秒；而用其他存儲過程，在實踐中就會導致超時，所以這個存儲過程非常適用于大容量數(shù)據(jù)庫的查詢。 筆者希望能夠通過對以上存儲過程的解析，能給大家?guī)硪欢ǖ膯⑹荆⒔o工作帶來一定的效率提升，同時希望同行提出更優(yōu)秀的實時數(shù)據(jù)分頁算法。
以上的這第三種存儲過程在小數(shù)據(jù)量的情況下，有如下現(xiàn)象：
1、分頁速度一般維持在1秒和3秒之間。
2、在查詢最后一頁時，速度一般為5秒至8秒，哪怕分頁總數(shù)只有3頁或30萬頁。
?
?
更多文章、技術交流、商務合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
