Sql Server 資料分頁

標籤：des   blog   http   使用   os   strong   資料   art   

1.引言

在列表查詢時由於資料量非常多，一次性查出來會非常慢，就算一次查出來了，也不能一次性顯示給用戶端，所以要把資料進行分批查詢出來，每頁顯示一定量的資料，這就是資料要分頁。

2.常用的資料分頁方法

我們經常會碰到要取n到m條記錄，就是有分頁思想，下面羅列一下一般的方法。

我本地的一張表 tbl_FlightsDetail，有300多W記錄，主鍵 FlightsDetailID(Guid)，要求按照FlightsDetailID排序 取 3000001 到3000010 之間的10條記錄，也是百萬級。

方法1 定位法 (利用ID大於多少)

語句形式：

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

執行計畫：

先查出 top 300000，再彙總取這個集合中最大的Id1，再過濾 id大於id1的集合（中使用到索引），再取top 10 條。

 

方法2 (利用Not In)

語句形式：

select top 10* from tbl_FlightsDetail where FlightsDetailID not in (       select top 3000000 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID) order by FlightsDetailID

執行計畫：

和方法一類似，只是過濾where條件不一樣，這裡用到的是not in，中沒有用到索引，耗時8秒。如果 FlightsDetailID不是索引的話，方法1和該方法將差不多。

 

方法3 (利用顛顛倒倒top)

語句形式：

select top 10* from (       select top 3000010* from tbl_FlightsDetail order by FlightsDetailID) as t  order by t.FlightsDetailID desc

執行計畫：

先取 前面3000010條記錄，再倒序，這時再取前面10條即是300001 到300010條記錄，沒有用到索引，耗時11秒

 

方法4 (ROW_NUMBER()函數)

語句形式：

select * from (       select *,ROW_NUMBER() OVER (ORDER BY FlightsDetailID) as rank from tbl_FlightsDetail)  as t where t.rank between 3000001 and 3000010

 執行計畫：

Sql 2005版本或以上支援，也沒用到索引，耗時2秒，速度還不錯。

方法5 (利用IN)

此方法是由 金色海洋（jyk）陽光男孩 回複的，飛常感謝，語句形式：

select top 10 * from tbl_FlightsDetail  where FlightsDetailID in(        select top 10 FlightsDetailID from(                select top 3000010 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID       ) as t order by t.FlightsDetailID desc ) order by FlightsDetailID

執行計畫：

多次執行之後一般維持在4秒左右，用到索引，非常不錯，計劃圖還很長，只截取部分，可能是繞的多一點。

3.千萬級分頁預存程序

大家百度一下這個標題立馬會出現很多相關資訊，都大同小異，我自己拷貝的一個，應項目的需要，修改了一個排序的bug以及添加了返回總記錄數，如下：

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--分頁預存程序  CREATE PROCEDURE [dbo].[sp_Paging] ( @Tables nvarchar(1000),                --表名/視圖名@PrimaryKey nvarchar(100),             --主鍵@Sort nvarchar(200) = NULL,            --排序欄位(不帶order by)@pageindex int = 1,                    --當前頁碼@PageSize int = 10,                    --每頁記錄數@Fields nvarchar(1000) = N‘*‘,         --輸出欄位@Filter nvarchar(1000) = NULL,         --where過濾條件(不帶where)@Group nvarchar(1000) = NULL,          --Group語句(不帶Group By)@TotalCount int OUTPUT                 --總記錄數) AS   DECLARE @SortTable nvarchar(100) DECLARE @SortName nvarchar(100) DECLARE @strSortColumn nvarchar(200) DECLARE @operator char(2) DECLARE @type nvarchar(100) DECLARE @prec int --設定排序語句IF @Sort IS NULL OR @Sort = ‘‘        SET @Sort = @PrimaryKey      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     BEGIN             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 --設定排序欄位類型和精度 SELECT @type=t.name, @prec=c.prec FROM sysobjects o        JOIN syscolumns c on o.id=c.id        JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName        IF CHARINDEX(‘char‘, @type) > 0       SET @type = @type + ‘(‘ + CAST(@prec AS varchar) + ‘)‘   DECLARE @strPageSize nvarchar(50) DECLARE @strStartRow 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)) --設定開始分頁記錄數 SET @strStartRow = CAST(((@pageindex - 1)*@PageSize + 1) AS nvarchar(50))  --篩選以及分組語句IF @Filter 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 = ‘ GROUP BY ‘ --計算總記錄數DECLARE @TotalCountSql nvarchar(1000)SET @TotalCountSql=N‘SELECT @TotalCount=COUNT(*)‘ +N‘ FROM ‘ + @Tables + @strFilterEXEC sp_executesql @TotalCountSql,N‘@TotalCount int OUTPUT‘,@TotalCount OUTPUT--執行查詢語句    EXEC(‘DECLARE @SortColumn ‘ + @type + ‘SET ROWCOUNT ‘ + @strStartRow + ‘SELECT @SortColumn=‘ + @strSortColumn + ‘ FROM ‘ + @Tables + @strFilter + ‘ ‘ + @strGroup + ‘ ORDER BY ‘ + @Sort + ‘SET ROWCOUNT ‘ + @strPageSize + ‘SELECT ‘ + @Fields + ‘ FROM ‘ + @Tables + ‘ WHERE ‘ + @strSortColumn + @operator + ‘ @SortColumn ‘ + @strSimpleFilter + ‘ ‘ + @strGroup + ‘ ORDER BY ‘ + @Sort + ‘‘)

 現在我們來測試一下：

DECLARE    @return_value int,        @TotalCount intEXEC    @return_value = [dbo].[sp_Paging]        @Tables = N‘tbl_FlightsDetail‘,        @PrimaryKey = N‘FlightsDetailID‘,        @Sort = N‘FlightsDetailID‘,        @pageindex = 299999,        @PageSize = 10,        @Fields = ‘*‘,        @Filter = NULL,        @Group = NULL,        @TotalCount = @TotalCount OUTPUTSELECT    @TotalCount as N‘@TotalCount‘SELECT    ‘Return Value‘ = @return_value

執行計畫：

看時間的確是快，執行計畫顯示4個查詢

查詢1，是利用系統資料表擷取排序欄位、類型和精度，這個很快，全是索引。

查詢2，返回總記錄數，第一次會慢點，後面就很快了。

查詢3 和查詢4（用到索引） 才是我們要分頁取的資料，查詢3 是排序，取一個最大的值賦給變數，查詢4是大於這個變數的值 取資料，直接看sql語句，把上面的exec動態語句改成如下：

DECLARE @SortColumn varchar(40)--即 top 3000001，取出最大的 id覆蓋@SortColumn SET ROWCOUNT  3000001SELECT @SortColumn= FlightsDetailID  FROM tbl_FlightsDetail ORDER BY FlightsDetailID  --即 top 10SET ROWCOUNT 10SELECT *  FROM  tbl_FlightsDetail  WHERE FlightsDetailID   >= @SortColumn  ORDER BY  FlightsDetailID   

你會發現，原來它跟我們標題2 常用的資料分頁方法 中的 方法1 定位 類似，原來奧秘在這。

4.小結

還有一些用遊標、表變數的那個效能差不作考慮。分頁預存程序看起來挺複雜的，語句多，其實都在判斷，在左組裝，右組裝，最終組裝成類似 標題2中常用的分頁方法中的 的一種語句，掌握了常用的資料分頁方法，大家就可以自己寫了，當然還有其它的方法，大家可以分享出來。