您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

高效 SQL Server 分頁查詢五大方案實測：從 NOT IN 到 ROW_NUMBER，大數據量下誰是王者？

編輯：關於SqlServer

測試環境

表 ARTICLE：53,210 行，聚簇索引 ID，非聚簇索引 YEAR。
每頁 30 行，對比第 1500 頁（深分頁）與第 34 頁（淺分頁）。
SQL Server 2008 R2，統計 100 次平均耗時。

五大方案代碼與耗時對比

方案	核心 SQL 模式	深分頁(1500頁)	淺分頁(34頁)
1. NOT IN	`TOP N WHERE ID NOT IN (TOP M ...)`	45 s	80 s
2. 雙重 TOP + 反序	`TOP N FROM (TOP M ... ORDER BY DESC) ORDER BY ASC`	138 s	30 s
3. 連接子查詢 TOP	`JOIN (SELECT TOP N ID FROM (SELECT TOP M ID ... ORDER BY DESC) ORDER BY ASC) w2`	21 s	12 s
4. IN 子查詢 TOP	`WHERE ID IN (SELECT TOP N ID FROM (SELECT TOP M ID ... ORDER BY DESC) ORDER BY ASC)`	20 s	13 s
5. ROW_NUMBER	`JOIN (SELECT TOP M ROW_NUMBER()...) w2 WHERE RowId > M-N`	15 s	14 s

深度分析

淺分頁：方案 3/4/5 接近，方案 1 最慢（NOT IN 執行計劃差），方案 2 最慢（雙重排序）。
深分頁：方案 5（ROW_NUMBER）穩居第一，方案 3/4 緊隨，方案 1/2 暴增。
綜合推薦：方案 5（ROW_NUMBER） 全場景最優，且代碼標準、可維護性強。

通用分頁存儲過程（基於方案 4/5 思想）

CREATE PROCEDURE dbo.sys_Page_v2
    @PCount INT OUTPUT, @RCount INT OUTPUT,
    @sys_Table NVARCHAR(100), @sys_Key VARCHAR(50),
    @sys_Fields NVARCHAR(500), @sys_Where NVARCHAR(3000),
    @sys_Order NVARCHAR(100), @sys_Begin INT,
    @sys_PageIndex INT, @sys_PageSize INT
AS
BEGIN
    SET NOCOUNT ON; SET ANSI_WARNINGS ON;
    IF @sys_PageSize < 0 OR @sys_PageIndex < 0 RETURN;

    DECLARE @new_where NVARCHAR(3000), @new_order1 NVARCHAR(100), @new_order2 NVARCHAR(100);
    DECLARE @Sql NVARCHAR(4000), @SqlCount NVARCHAR(4000);

    IF @sys_Begin <= 0 SET @sys_Begin = 0 ELSE SET @sys_Begin = @sys_Begin - 1;

    SET @new_where = CASE WHEN ISNULL(@sys_Where,'')='' THEN '' ELSE ' WHERE '+@sys_Where END;
    IF ISNULL(@sys_Order,'')<>''
    BEGIN
        SET @new_order1 = ' ORDER BY '+REPLACE(REPLACE(@sys_Order,'desc',''),'asc','')+' DESC';
        SET @new_order2 = ' ORDER BY '+@sys_Order;
    END ELSE BEGIN
        SET @new_order1 = ' ORDER BY ID DESC'; SET @new_order2 = ' ORDER BY ID ASC';
    END

    SET @SqlCount = 'SELECT @RCount=COUNT(1),@PCount=CEILING((COUNT(1)+0.0)/'+CAST(@sys_PageSize AS NVARCHAR)+') FROM '+@sys_Table+@new_where;
    EXEC sp_executesql @SqlCount, N'@RCount INT OUTPUT,@PCount INT OUTPUT', @RCount OUTPUT, @PCount OUTPUT;

    IF @sys_PageIndex > CEILING((@RCount+0.0)/@sys_PageSize)
        SET @sys_PageIndex = CEILING((@RCount+0.0)/@sys_PageSize);

    SET @Sql = 'SELECT '+@sys_Fields+' FROM '+@sys_Table+' w1 WHERE '+@sys_Key+' IN ('+
               'SELECT TOP '+LTRIM(STR(@sys_PageSize))+' '+@sys_Key+' FROM ('+
               'SELECT TOP '+LTRIM(STR(@sys_PageSize*@sys_PageIndex+@sys_Begin))+' '+@sys_Key+' FROM '+@sys_Table+@new_where+@new_order2+
               ') w '+@new_order1+') '+@new_order2;

    EXEC(@Sql);
END
GO

遷移到 SQL 2012+ 的最佳寫法

SELECT @sys_Fields FROM @sys_Table
WHERE @sys_Where
ORDER BY @sys_Order
OFFSET (@sys_PageIndex-1)*@sys_PageSize ROWS
FETCH NEXT @sys_PageSize ROWS ONLY;