sqlserver數(shù)據(jù)庫(kù)分頁(yè)查詢技術(shù)

1、第 8 頁(yè) 共 8 頁(yè)SQL Server 數(shù)據(jù)庫(kù)分頁(yè)查詢技術(shù)1.引言在列表查詢時(shí)由于數(shù)據(jù)量非常多，一次性查出來(lái)會(huì)非常慢，就算一次查出來(lái)了，也不能一次性顯示給客戶端，所以要把數(shù)據(jù)進(jìn)行分批查詢出來(lái)，每頁(yè)顯示一定量的數(shù)據(jù)，這就是數(shù)據(jù)要分頁(yè)。2.常用的數(shù)據(jù)分頁(yè)方法我們經(jīng)常會(huì)碰到要取n到m條記錄，就是有分頁(yè)思想，下面羅列一下一般的方法。我本地的一張表 tbl_FlightsDetail，有300多W記錄，主鍵 FlightsDetailID(Guid)，要求按照FlightsDetailID排序 取 3000001 到3000010 之間的10條記錄，也是百萬(wàn)級(jí)。方法1 定位法 (利用ID大于多少)語(yǔ)

2、句形式：select top 10 * from tbl_FlightsDetail where FlightsDetailID>( select max(FlightsDetailID) from ( select top 3000000 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID ) as t) order by FlightsDetailID執(zhí)行計(jì)劃：先查出 top 300000，再聚合取這個(gè)集合中最大的Id1，再過(guò)濾 id大于id1的集合（上圖中使用到索引），再取top 10 條。 方法

3、2 (利用Not In)語(yǔ)句形式：select top 10* from tbl_FlightsDetail where FlightsDetailID not in ( select top 3000000 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID) order by FlightsDetailID執(zhí)行計(jì)劃：和方法一類似，只是過(guò)濾where條件不一樣，這里用到的是not in，上圖中沒(méi)有用到索引，耗時(shí)8秒。如果 FlightsDetailID不是索引的話，方法1和該方法將差不多。 方法3 (利用顛

4、顛倒倒top)語(yǔ)句形式：select top 10* from ( select top 3000010* from tbl_FlightsDetail order by FlightsDetailID) as t order by t.FlightsDetailID desc執(zhí)行計(jì)劃：先取 前面3000010條記錄，再倒序，這時(shí)再取前面10條即是300001 到300010條記錄，沒(méi)有用到索引，耗時(shí)11秒 方法4 (ROW_NUMBER()函數(shù))語(yǔ)句形式：select * from ( select *,ROW_NUMBER() OVER (ORDER BY FlightsDeta

5、ilID) as rank from tbl_FlightsDetail) as t where t.rank between 3000001 and 3000010 執(zhí)行計(jì)劃：Sql 2005版本或以上支持，也沒(méi)用到索引，耗時(shí)2秒，速度還不錯(cuò)。方法5 (利用IN)此方法是由 金色海洋（jyk）陽(yáng)光男孩 回復(fù)的，飛常感謝，語(yǔ)句形式：select top 10 * from tbl_FlightsDetail where FlightsDetailID in( select top 10 FlightsDetailID from( select top 3000010 FlightsD

6、etailID from tbl_FlightsDetail order by FlightsDetailID ) as t order by t.FlightsDetailID desc ) order by FlightsDetailID執(zhí)行計(jì)劃：多次執(zhí)行之后一般維持在4秒左右，用到索引，非常不錯(cuò)，計(jì)劃圖還很長(zhǎng)，只截取部分，可能是繞的多一點(diǎn)。3.千萬(wàn)級(jí)分頁(yè)存儲(chǔ)過(guò)程大家百度一下這個(gè)標(biāo)題立馬會(huì)出現(xiàn)很多相關(guān)信息，都大同小異，我自己拷貝的一個(gè)，應(yīng)項(xiàng)目的需要，修改了一個(gè)排序的bug以及添加了返回總記錄數(shù)，如下：SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO

7、-分頁(yè)存儲(chǔ)過(guò)程 CREATE PROCEDURE dbo.sp_Paging ( Tables nvarchar(1000), -表名/視圖名PrimaryKey nvarchar(100), -主鍵Sort nvarchar(200) = NULL, -排序字段(不帶order by)pageindex int = 1, -當(dāng)前頁(yè)碼PageSize int = 10, -每頁(yè)記錄數(shù)Fields nvarchar(1000) = N'*', -輸出字段Filter nvarchar(1000) = NULL, -where過(guò)濾條件(不帶where)Group nvarchar(

8、1000) = NULL, -Group語(yǔ)句(不帶Group By)TotalCount int OUTPUT -總記錄數(shù)) AS DECLARE SortTable nvarchar(100) DECLARE SortName nvarchar(100) DECLARE strSortColumn nvarchar(200) DECLARE operator char(2) DECLARE type nvarchar(100) DECLARE prec int -設(shè)定排序語(yǔ)句IF Sort IS NULL OR Sort = '' SET Sort = PrimaryKey

9、IF CHARINDEX('DESC',Sort)>0 BEGIN SET strSortColumn = REPLACE(Sort, 'DESC', '') SET operator = '<=' END ELSE BEGIN SET strSortColumn = REPLACE(Sort, 'ASC', '') SET operator = '>=' END IF CHARINDEX('.', strSortColumn) > 0 BEG

10、IN SET SortTable = SUBSTRING(strSortColumn, 0, CHARINDEX('.',strSortColumn) SET SortName = SUBSTRING(strSortColumn, CHARINDEX('.',strSortColumn) + 1, LEN(strSortColumn) END ELSE BEGIN SET SortTable = Tables SET SortName = strSortColumn END -設(shè)置排序字段類型和精度 SELECT type=, prec=c.prec

11、 FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype WHERE = SortTable AND = SortName IF CHARINDEX('char', type) > 0 SET type = type + '(' + CAST(prec AS varchar) + ')' DECLARE strPageSize nvarchar(50) DECLARE strStartR

12、ow nvarchar(50) DECLARE strFilter nvarchar(1000) DECLARE strSimpleFilter nvarchar(1000) DECLARE strGroup nvarchar(1000) IF pageindex <1 SET pageindex = 1 SET strPageSize = CAST(PageSize AS nvarchar(50) -設(shè)置開(kāi)始分頁(yè)記錄數(shù) SET strStartRow = CAST(pageindex - 1)*PageSize + 1) AS nvarchar(50) -篩選以及分組語(yǔ)句IF Filt

13、er IS NOT NULL AND Filter != '' BEGIN SET strFilter = ' WHERE ' + Filter + ' ' SET strSimpleFilter = ' AND ' + Filter + ' ' END ELSE BEGIN SET strSimpleFilter = '' SET strFilter = '' END IF Group IS NOT NULL AND Group != '' SET strGroup

14、 = ' GROUP BY ' -計(jì)算總記錄數(shù)DECLARE TotalCountSql nvarchar(1000)SET TotalCountSql=N'SELECT TotalCount=COUNT(*)' +N' FROM ' + Tables + strFilterEXEC sp_executesql TotalCountSql,N'TotalCount int OUTPUT',TotalCount OUTPUT-執(zhí)行查詢語(yǔ)句 EXEC('DECLARE SortColumn ' + type + 

15、9;SET ROWCOUNT ' + strStartRow + 'SELECT SortColumn=' + strSortColumn + ' FROM ' + Tables + strFilter + ' ' + strGroup + ' ORDER BY ' + Sort + 'SET ROWCOUNT ' + strPageSize + 'SELECT ' + Fields + ' FROM ' + Tables + ' WHERE ' + strS

16、ortColumn + operator + ' SortColumn ' + strSimpleFilter + ' ' + strGroup + ' ORDER BY ' + Sort + '') 現(xiàn)在我們來(lái)測(cè)試一下：DECLARE return_value int, TotalCount intEXEC return_value = dbo.sp_Paging Tables = N'tbl_FlightsDetail', PrimaryKey = N'FlightsDetailID'

17、, Sort = N'FlightsDetailID', pageindex = 299999, PageSize = 10, Fields = '*', Filter = NULL, Group = NULL, TotalCount = TotalCount OUTPUTSELECT TotalCount as N'TotalCount'SELECT 'Return Value' = return_value執(zhí)行計(jì)劃：看時(shí)間的確是快，執(zhí)行計(jì)劃顯示4個(gè)查詢查詢1，是利用系統(tǒng)表獲取排序字段、類型和精度，這個(gè)很快，全是索引。查詢2，返回總記錄數(shù)，第一次會(huì)慢點(diǎn)，后面就很快了。查詢3 和查詢4（用到索引） 才是我們要分頁(yè)取的數(shù)據(jù)，查詢3 是排序，取一個(gè)最大的值賦給變量，查詢4是大于這個(gè)變量的值 取數(shù)據(jù)，直接看sql語(yǔ)句，把上面的exec動(dòng)態(tài)語(yǔ)句改成如下：DECLARE SortColumn varchar(40)-即 top 3000001，取出最大的 id覆蓋SortColumn SET ROWCOUNT 3000001SELECT SortColumn= FlightsDetailID FROM tbl_FlightsDetail ORDER BY FlightsDetailID -即 top 10SET