在項目中，我們經常遇到或用到分頁，那么在大數據量（百萬級以上）下，哪種分頁算法效率最優呢？我們不妨用事實說話。

?

測試環境

硬件：CPU 酷睿雙核T5750? 內存：2G

軟件:Windows server 2003??? +?? Sql server 2005

?

OK,我們首先創建一數據庫：data_Test,并在此數據庫中創建一表：tb_TestTable

?

?1 create?database?data_Test??--創建數據庫data_Test?
?2 GO?
?3 use?data_Test?
?4 GO?
?5 create?table?tb_TestTable???--創建表?
?6 (?
?7 ????id?int?identity(1,1)?primary?key,?
?8 ????userName?nvarchar(20)?not?null,?
?9 ????userPWD?nvarchar(20)?not?null,?
10 ????userEmail?nvarchar(40)?null?
11 )?
12 GO

?

然后我們在數據表中插入2000000條數據:

?

?1 --插入數據?
?2 set?identity_insert?tb_TestTable?on?
?3 declare?@count?int?
?4 set?@count=1?
?5 while?@count<=2000000?
?6 begin??
?7 ????insert?into?tb_TestTable(id,userName,userPWD,userEmail)?values(@count,'admin','admin888','lli0077@yahoo.com.cn')?
?8 ????set?@count=@count+1?
?9 end?
10 set?identity_insert?tb_TestTable?off

?

我首先寫了五個常用存儲過程：

1，利用select top 和select not in進行分頁，具體代碼如下：

?

?1 create?procedure?proc_paged_with_notin??--利用select?top?and?select?not?in?
?2 (?
?3 ????@pageIndex?int,??--頁索引?
?4 ????@pageSize?int????--每頁記錄數?
?5 )?
?6 as?
?7 begin?
?8 ????set?nocount?on;?
?9 ????declare?@timediff?datetime?--耗時?
10 ????declare?@sql?nvarchar(500)?
11 ????select?@timediff=Getdate()?
12 ????set?@sql='select?top?'+str(@pageSize)+'?*?from?tb_TestTable?where(ID?not?in(select?top?'+str(@pageSize*@pageIndex)+'?id?from?tb_TestTable?order?by?ID?ASC))?order?by?ID'?
13 ????execute(@sql)??--因select?top后不支技直接接參數，所以寫成了字符串@sql?
14 ????select?datediff(ms,@timediff,GetDate())?as?耗時?
15 ????set?nocount?off;?
16 end

?

2,利用select top 和 select max(列鍵)

?

?

?1 create?procedure?proc_paged_with_selectMax??--利用select?top?and?select?max(列)?
?2 (?
?3 ????@pageIndex?int,??--頁索引?
?4 ????@pageSize?int????--頁記錄數?
?5 )?
?6 as?
?7 begin?
?8 set?nocount?on;?
?9 ????declare?@timediff?datetime?
10 ????declare?@sql?nvarchar(500)?
11 ????select?@timediff=Getdate()?
12 ????set?@sql='select?top?'+str(@pageSize)+'?*?From?tb_TestTable?where(ID>(select?max(id)?From?(select?top?'+str(@pageSize*@pageIndex)+'?id?From?tb_TestTable?order?by?ID)?as?TempTable))?order?by?ID'?
13 ????execute(@sql)?
14 ????select?datediff(ms,@timediff,GetDate())?as?耗時?
15 set?nocount?off;?
16 end

?

3,利用select top和中間變量--此方法因網上有人說效果最佳，所以貼出來一同測試

?

?

?1 create?procedure?proc_paged_with_Midvar??--利用ID>最大ID值和中間變量?
?2 (?
?3 ????@pageIndex?int,?
?4 ????@pageSize?int?
?5 )?
?6 as?
?7 ????declare?@count?int?
?8 ????declare?@ID?int?
?9 ????declare?@timediff?datetime?
10 ????declare?@sql?nvarchar(500)?
11 begin?
12 set?nocount?on;?
13 ????select?@count=0,@ID=0,@timediff=getdate()?
14 ????select?@count=@count+1,@ID=case?when?@count<=@pageSize*@pageIndex?then?ID?else?@ID?end?from?tb_testTable?order?by?id?
15 ????set?@sql='select?top?'+str(@pageSize)+'?*?from?tb_testTable?where?ID>'+str(@ID)?
16 ????execute(@sql)?
17 ????select?datediff(ms,@timediff,getdate())?as?耗時?
18 set?nocount?off;?
19 end
20

?

4,利用Row_number() 此方法為SQL server 2005中新的方法,利用Row_number()給數據行加上索引

?

?

?

?1 create?procedure?proc_paged_with_Rownumber??--利用SQL?2005中的Row_number()?
?2 (?
?3 ????@pageIndex?int,?
?4 ????@pageSize?int?
?5 )?
?6 as?
?7 ????declare?@timediff?datetime?
?8 begin?
?9 set?nocount?on;?
10 ????select?@timediff=getdate()?
11 ????select?*?from?(select?*,Row_number()?over(order?by?ID?asc)?as?IDRank?from?tb_testTable)?as?IDWithRowNumber?where?IDRank>@pageSize*@pageIndex?and?IDRank<@pageSize*(@pageIndex+1)?
12 ????select?datediff(ms,@timediff,getdate())?as?耗時?
13 set?nocount?off;?
14 end
15

5，利用臨時表及Row_number

?

?

?1 create?procedure?proc_CTE??--利用臨時表及Row_number?
?2 (?
?3 ????@pageIndex?int,??--頁索引?
?4 ????@pageSize?int????--頁記錄數?
?5 )?
?6 as?
?7 ????set?nocount?on;?
?8 ????declare?@ctestr?nvarchar(400)?
?9 ????declare?@strSql?nvarchar(400)?
10 ????declare?@datediff?datetime?
11 begin?
12 ????select?@datediff=GetDate()?
13 ????set?@ctestr='with?Table_CTE?as?
14 ????????????????(select?ceiling((Row_number()?over(order?by?ID?ASC))/'+str(@pageSize)+')?as?page_num,*?from?tb_TestTable)';?
15 ????set?@strSql=@ctestr+'?select?*?From?Table_CTE?where?page_num='+str(@pageIndex)?
16 end?
17 ????begin?
18 ????????execute?sp_executesql?@strSql?
19 ????????select?datediff(ms,@datediff,GetDate())?
20 ????set?nocount?off;?
21 ????end
22

?

OK,至此，存儲過程創建完畢，我們分別在每頁10條數據的情況下在第2頁，第1000頁，第10000頁，第100000頁，第199999頁進行測試，耗時單位：ms? 每頁測試5次取其平均值

存過	第2頁耗時	第1000頁耗時	第10000頁耗時	第100000頁耗時	第199999頁耗時	效率排行
1用not in	0ms	16ms	47ms	475ms	953ms	3
2用select max	5ms	16ms	35ms	325ms	623ms	1
3中間變量	966ms	970ms	960ms	945ms	933ms	5
4row_number	0ms	0ms	34ms	365ms	710ms	2
4臨時表	780ms	796ms	798ms	780ms	805ms	4

?

測試結果顯示：select max >row_number>not in>臨時表>中間變量

?

于是我對效率最高的select max方法用2分法進行了擴展，代碼取自互聯網，我修改了ASC排序時取不到值的BUG，測試結果:

2分法

156ms

156ms

180ms

470ms

156ms

1*

?

從測試結果來看，使用2分法確實可以提高效率并使效率更為穩定，我又增加了第159999頁的測試，用時僅296ms，效果相當的不錯！

?

下面是2分法使用select max的代碼，已相當完善。

?

?

??1 --/*-----存儲過程?分頁處理?孫偉?2005-03-28創建?-------*/?
??2 --/*-----存儲過程?分頁處理?浪塵?2008-9-1修改----------*/?
??3 --/*-----?對數據進行了2分處理使查詢前半部分數據與查詢后半部分數據性能相同?-------*/?
??4
??5 alter?PROCEDURE?proc_paged_2part_selectMax?
??6 (?
??7 @tblName?????nvarchar(200),????????----要顯示的表或多個表的連接?
??8 @fldName?????nvarchar(500)?=?'*',????----要顯示的字段列表?
??9 @pageSize????int?=?10,????????----每頁顯示的記錄個數?
?10 @page????????int?=?1,????????----要顯示那一頁的記錄?
?11 @fldSort????nvarchar(200)?=?null,????----排序字段列表或條件?
?12 @Sort????????bit?=?0,????????---- 排序方法，0為升序，1為降序(如果是多字段排列Sort指代最后一個排序字段的排列順序(最后一個排序字段不加排序標記)--程序傳參 如：'?SortA?Asc,SortB?Desc,SortC?')?
?13 @strCondition????nvarchar(1000)?=?null,????----查詢條件,不需where?
?14 @ID????????nvarchar(150),????????----主表的主鍵?
?15 @Dist?????????????????bit?=?0,???????????----是否添加查詢字段的?DISTINCT?默認0不添加/1添加?
?16 @pageCount????int?=?1?output,????????????----查詢結果分頁后的總頁數?
?17 @Counts????int?=?1?output????????????????----查詢到的記錄數?
?18 )?
?19 AS?
?20 SET?NOCOUNT?ON?
?21 Declare?@sqlTmp?nvarchar(1000)????????----存放動態生成的SQL語句?
?22 Declare?@strTmp?nvarchar(1000)????????----存放取得查詢結果總數的查詢語句?
?23 Declare?@strID?????nvarchar(1000)????????----存放取得查詢開頭或結尾ID的查詢語句?
?24
?25 Declare?@strSortType?nvarchar(10)????----數據排序規則A?
?26 Declare?@strFSortType?nvarchar(10)????----數據排序規則B?
?27
?28 Declare?@SqlSelect?nvarchar(50)?????????----對含有DISTINCT的查詢進行SQL構造?
?29 Declare?@SqlCounts?nvarchar(50)??????????----對含有DISTINCT的總數查詢進行SQL構造?
?30
?31 declare?@timediff?datetime??--耗時測試時間差?
?32 select?@timediff=getdate()?
?33
?34 if?@Dist??=?0?
?35 begin?
?36 ????set?@SqlSelect?=?'select?'?
?37 ????set?@SqlCounts?=?'Count(*)'?
?38 end?
?39 else?
?40 begin?
?41 ????set?@SqlSelect?=?'select?distinct?'?
?42 ????set?@SqlCounts?=?'Count(DISTINCT?'+@ID+')'?
?43 end?
?44
?45
?46 if?@Sort=0?
?47 begin?
?48 ????set?@strFSortType='?ASC?'?
?49 ????set?@strSortType='?DESC?'?
?50 end?
?51 else?
?52 begin?
?53 ????set?@strFSortType='?DESC?'?
?54 ????set?@strSortType='?ASC?'?
?55 end?
?56
?57
?58
?59 --------生成查詢語句--------?
?60 --此處@strTmp為取得查詢結果數量的語句?
?61 if?@strCondition?is?null?or?@strCondition=''?????--沒有設置顯示條件?
?62 begin?
?63 ????set?@sqlTmp?=??@fldName?+?'?From?'?+?@tblName?
?64 ????set?@strTmp?=?@SqlSelect+'?@Counts='+@SqlCounts+'?FROM?'+@tblName?
?65 ????set?@strID?=?'?From?'?+?@tblName?
?66 end?
?67 else?
?68 begin?
?69 ????set?@sqlTmp?=?+?@fldName?+?'From?'?+?@tblName?+?'?where?(1>0)?'?+?@strCondition?
?70 ????set?@strTmp?=?@SqlSelect+'?@Counts='+@SqlCounts+'?FROM?'+@tblName?+?'?where?(1>0)?'?+?@strCondition?
?71 ????set?@strID?=?'?From?'?+?@tblName?+?'?where?(1>0)?'?+?@strCondition?
?72 end?
?73
?74 ----取得查詢結果總數量-----?
?75 exec?sp_executesql?@strTmp,N'@Counts?int?out?',@Counts?out?
?76 declare?@tmpCounts?int?
?77 if?@Counts?=?0?
?78 ????set?@tmpCounts?=?1?
?79 else?
?80 ????set?@tmpCounts?=?@Counts?
?81
?82 ????--取得分頁總數?
?83 ????set?@pageCount=(@tmpCounts+@pageSize-1)/@pageSize?
?84
?85 ????/**//**當前頁大于總頁數?取最后一頁**/?
?86 ????if?@page>@pageCount?
?87 ????????set?@page=@pageCount?
?88
?89 ????--/*-----數據分頁2分處理-------*/?
?90 ????declare?@pageIndex?int?--總數/頁大小?
?91 ????declare?@lastcount?int?--總數%頁大小??
?92
?93 ????set?@pageIndex?=?@tmpCounts/@pageSize?
?94 ????set?@lastcount?=?@tmpCounts%@pageSize?
?95 ????if?@lastcount?>?0?
?96 ????????set?@pageIndex?=?@pageIndex?+?1?
?97 ????else?
?98 ????????set?@lastcount?=?@pagesize?
?99
100 ????--//***顯示分頁?
101 ????if?@strCondition?is?null?or?@strCondition=''?????--沒有設置顯示條件?
102 ????begin?
103 ????????if?@pageIndex<2?or?@page<=@pageIndex?/?2?+?@pageIndex?%?2???--前半部分數據處理?
104 ????????????begin??
105 ????????????????if?@page=1?
106 ????????????????????set?@strTmp=@SqlSelect+'?top?'+?CAST(@pageSize?as?VARCHAR(4))+'?'+?@fldName+'?from?'+@tblName?????????????????????????
107 ????????????????????????+'?order?by?'+?@fldSort?+'?'+?@strFSortType?
108 ????????????????else?
109 ????????????????begin?
110 ????????????????????if?@Sort=1?
111 ????????????????????begin?????????????????????
112 ????????????????????set?@strTmp=@SqlSelect+'?top?'+?CAST(@pageSize?as?VARCHAR(4))+'?'+?@fldName+'?from?'+@tblName?
113 ????????????????????????+'?where?'+@ID+'?<(select?min('+?@ID?+')?from?('+?@SqlSelect+'?top?'+?CAST(@pageSize*(@page-1)?as?Varchar(20))?+'?'+?@ID?+'?from?'+@tblName?
114 ????????????????????????+'?order?by?'+?@fldSort?+'?'+?@strFSortType+')?AS?TBMinID)'?
115 ????????????????????????+'?order?by?'+?@fldSort?+'?'+?@strFSortType?
116 ????????????????????end?
117 ????????????????????else?
118 ????????????????????begin?
119 ????????????????????set?@strTmp=@SqlSelect+'?top?'+?CAST(@pageSize?as?VARCHAR(4))+'?'+?@fldName+'?from?'+@tblName?
120 ????????????????????????+'?where?'+@ID+'?>(select?max('+?@ID?+')?from?('+?@SqlSelect+'?top?'+?CAST(@pageSize*(@page-1)?as?Varchar(20))?+'?'+?@ID?+'?from?'+@tblName?
121 ????????????????????????+'?order?by?'+?@fldSort?+'?'+?@strFSortType+')?AS?TBMinID)'?
122 ????????????????????????+'?order?by?'+?@fldSort?+'?'+?@strFSortType??
123 ????????????????????end?
124 ????????????????end?????
125 ????????????end?
126 ????????else?
127 ????????????begin?
128 ????????????set?@page?=?@pageIndex-@page+1?--后半部分數據處理?
129 ????????????????if?@page?<=?1?--最后一頁數據顯示?????????????????
130 ????????????????????set?@strTmp=@SqlSelect+'?*?from?('+@SqlSelect+'?top?'+?CAST(@lastcount?as?VARCHAR(4))+'?'+?@fldName+'?from?'+@tblName?
131 ????????????????????????+'?order?by?'+?@fldSort?+'?'+?@strSortType+')?AS?TempTB'+'?order?by?'+?@fldSort?+'?'+?@strFSortType??
132 ????????????????else?
133 ????????????????????if?@Sort=1?
134 ????????????????????begin?
135 ????????????????????set?@strTmp=@SqlSelect+'?*?from?('+@SqlSelect+'?top?'+?CAST(@pageSize?as?VARCHAR(4))+'?'+?@fldName+'?from?'+@tblName?
136 ????????????????????????+'?where?'+@ID+'?>(select?max('+?@ID?+')?from('+?@SqlSelect+'?top?'+?CAST(@pageSize*(@page-2)+@lastcount?as?Varchar(20))?+'?'+?@ID?+'?from?'+@tblName?
137 ????????????????????????+'?order?by?'+?@fldSort?+'?'+?@strSortType+')?AS?TBMaxID)'?
138 ????????????????????????+'?order?by?'+?@fldSort?+'?'+?@strSortType+')?AS?TempTB'+'?order?by?'+?@fldSort?+'?'+?@strFSortType?
139 ????????????????????end?
140 ????????????????????else?
141 ????????????????????begin?
142 ????????????????????set?@strTmp=@SqlSelect+'?*?from?('+@SqlSelect+'?top?'+?CAST(@pageSize?as?VARCHAR(4))+'?'+?@fldName+'?from?'+@tblName?
143 ????????????????????????+'?where?'+@ID+'?<(select?min('+?@ID?+')?from('+?@SqlSelect+'?top?'+?CAST(@pageSize*(@page-2)+@lastcount?as?Varchar(20))?+'?'+?@ID?+'?from?'+@tblName?
144 ????????????????????????+'?order?by?'+?@fldSort?+'?'+?@strSortType+')?AS?TBMaxID)'?
145 ????????????????????????+'?order?by?'+?@fldSort?+'?'+?@strSortType+')?AS?TempTB'+'?order?by?'+?@fldSort?+'?'+?@strFSortType??
146 ????????????????????end?
147 ????????????end?
148 ????end?
149
150 ????else?--有查詢條件?
151 ????begin?
152 ????????if?@pageIndex<2?or?@page<=@pageIndex?/?2?+?@pageIndex?%?2???--前半部分數據處理?
153 ????????begin?
154 ????????????????if?@page=1?
155 ????????????????????set?@strTmp=@SqlSelect+'?top?'+?CAST(@pageSize?as?VARCHAR(4))+'?'+?@fldName+'?from?'+@tblName?????????????????????????
156 ????????????????????????+'?where?1=1?'?+?@strCondition?+?'?order?by?'+?@fldSort?+'?'+?@strFSortType?
157 ????????????????else?if(@Sort=1)?
158 ????????????????begin?????????????????????
159 ????????????????????set?@strTmp=@SqlSelect+'?top?'+?CAST(@pageSize?as?VARCHAR(4))+'?'+?@fldName+'?from?'+@tblName?
160 ????????????????????????+'?where?'+@ID+'?<(select?min('+?@ID?+')?from?('+?@SqlSelect+'?top?'+?CAST(@pageSize*(@page-1)?as?Varchar(20))?+'?'+?@ID?+'?from?'+@tblName?
161 ????????????????????????+'?where?(1=1)?'?+?@strCondition?+'?order?by?'+?@fldSort?+'?'+?@strFSortType+')?AS?TBMinID)'?
162 ????????????????????????+'?'+?@strCondition?+'?order?by?'+?@fldSort?+'?'+?@strFSortType?
163 ????????????????end?
164 ????????????????else?
165 ????????????????begin?
166 ????????????????????set?@strTmp=@SqlSelect+'?top?'+?CAST(@pageSize?as?VARCHAR(4))+'?'+?@fldName+'?from?'+@tblName?
167 ????????????????????????+'?where?'+@ID+'?>(select?max('+?@ID?+')?from?('+?@SqlSelect+'?top?'+?CAST(@pageSize*(@page-1)?as?Varchar(20))?+'?'+?@ID?+'?from?'+@tblName?
168 ????????????????????????+'?where?(1=1)?'?+?@strCondition?+'?order?by?'+?@fldSort?+'?'+?@strFSortType+')?AS?TBMinID)'?
169 ????????????????????????+'?'+?@strCondition?+'?order?by?'+?@fldSort?+'?'+?@strFSortType??
170 ????????????????end????????????
171 ????????end?
172 ????????else?
173 ????????begin??
174 ????????????set?@page?=?@pageIndex-@page+1?--后半部分數據處理?
175 ????????????if?@page?<=?1?--最后一頁數據顯示?
176 ????????????????????set?@strTmp=@SqlSelect+'?*?from?('+@SqlSelect+'?top?'+?CAST(@lastcount?as?VARCHAR(4))+'?'+?@fldName+'?from?'+@tblName?
177 ????????????????????????+'?where?(1=1)?'+?@strCondition?+'?order?by?'+?@fldSort?+'?'+?@strSortType+')?AS?TempTB'+'?order?by?'+?@fldSort?+'?'+?@strFSortType??????????????????????
178 ????????????else?if(@Sort=1)?
179 ????????????????????set?@strTmp=@SqlSelect+'?*?from?('+@SqlSelect+'?top?'+?CAST(@pageSize?as?VARCHAR(4))+'?'+?@fldName+'?from?'+@tblName?
180 ????????????????????????+'?where?'+@ID+'?>(select?max('+?@ID?+')?from('+?@SqlSelect+'?top?'+?CAST(@pageSize*(@page-2)+@lastcount?as?Varchar(20))?+'?'+?@ID?+'?from?'+@tblName?
181 ????????????????????????+'?where?(1=1)?'+?@strCondition?+'?order?by?'+?@fldSort?+'?'+?@strSortType+')?AS?TBMaxID)'?
182 ????????????????????????+'?'+?@strCondition+'?order?by?'+?@fldSort?+'?'+?@strSortType+')?AS?TempTB'+'?order?by?'+?@fldSort?+'?'+?@strFSortType?????
183 ????????????else?
184 ????????????????????set?@strTmp=@SqlSelect+'?*?from?('+@SqlSelect+'?top?'+?CAST(@pageSize?as?VARCHAR(4))+'?'+?@fldName+'?from?'+@tblName?
185 ????????????????????????+'?where?'+@ID+'?<(select?min('+?@ID?+')?from('+?@SqlSelect+'?top?'+?CAST(@pageSize*(@page-2)+@lastcount?as?Varchar(20))?+'?'+?@ID?+'?from?'+@tblName?
186 ????????????????????????+'?where?(1=1)?'+?@strCondition?+'?order?by?'+?@fldSort?+'?'+?@strSortType+')?AS?TBMaxID)'?
187 ????????????????????????+'?'+?@strCondition+'?order?by?'+?@fldSort?+'?'+?@strSortType+')?AS?TempTB'+'?order?by?'+?@fldSort?+'?'+?@strFSortType?????????????
188 ????????end?????
189 ????end?
190
191 ------返回查詢結果-----?
192 exec?sp_executesql?@strTmp?
193 select?datediff(ms,@timediff,getdate())?as?耗時?
194 --print?@strTmp?
195 SET?NOCOUNT?OFF?
196 GO
197

?

執行示例：exec proc_paged_2part_selectMax 'tb_testTable','ID,userName,userPWD,userEmail',10,100000,'ID',0,null,'ID',0

?

這種測試只在單機進行，并且沒有在實際開發WEB項目中分頁測試，測試項也比較單一，所以不夠全面系統，但從其效率相比上，我們可以在數據庫分頁算法上進行有效的控制。

各種分頁存儲過程性能比較