萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 高效分頁查詢方案對比測試:TOP+子查詢 vs ROW_NUMBER 實測數據

測試環境

  • 表 ARTICLE:53,210 筆真實客戶資料
  • 欄位:ID (叢集索引)、YEAR (無索引)
  • 分頁大小:30 筆
  • SQL Server 2008 R2

五種方案與實測結果 (平均 100 次執行時間)

場景 A:查詢第 1500 頁 (第 45001-45030 筆) - 深分頁

方案SQL 核心邏輯耗時排名
1. NOT INTOP 30 ... WHERE ID NOT IN (TOP 45000 ...)45 秒4
2. 雙重 TOP 正反排序TOP 30 FROM (TOP 45030 ... ORDER BY DESC) ORDER BY ASC138 秒5 (最差)
3. 關聯子查詢 (JOIN)JOIN (TOP 30 ID FROM (TOP 45030 ...) ORDER BY ASC)21 秒2
4. IN 子查詢WHERE ID IN (TOP 30 FROM (TOP 45030 ...) ORDER BY ASC)20 秒1 (最佳)
5. ROW_NUMBERROW_NUMBER() OVER(...) n, WHERE n > 45000 AND n <= 4503015 秒1 (最佳)

場景 B:查詢第 34 頁 (第 1001-1030 筆) - 淺分頁

方案耗時排名
1. NOT IN80 秒5
2. 雙重 TOP30 秒4
3. JOIN 子查詢12 秒2
4. IN 子查詢13 秒3
5. ROW_NUMBER14 秒1

方案程式碼完整版

方案 4:IN 子查詢 (深分頁最佳)

SELECT * FROM ARTICLE w1
WHERE ID IN (
    SELECT TOP 30 ID FROM (
        SELECT TOP 45030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
    ) w ORDER BY w.YEAR ASC, w.ID ASC
)
ORDER BY w1.YEAR DESC, w1.ID DESC;

方案 5:ROW_NUMBER (通用最佳、語法最清晰)

SELECT w2.n, w1.* 
FROM ARTICLE w1,
     (SELECT TOP 45030 ROW_NUMBER() OVER (ORDER BY YEAR DESC, ID DESC) AS n, ID 
      FROM ARTICLE) w2
WHERE w1.ID = w2.ID AND w2.n > 45000
ORDER BY w2.n ASC;

通用分頁儲存程序 (基於方案 4)

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),-- WHERE 條件
    @sys_Order NVARCHAR(100), -- ORDER BY
    @sys_Begin INT,           -- 起始位移
    @sys_PageIndex INT,       -- 頁碼
    @sys_PageSize INT         -- 頁大小
AS
BEGIN
    SET NOCOUNT ON; SET ANSI_WARNINGS ON;
    IF @sys_PageSize < 1 OR @sys_PageIndex < 1 RETURN;

    DECLARE @new_where NVARCHAR(3000), @new_order1 NVARCHAR(100), @new_order2 NVARCHAR(100);
    DECLARE @Sql NVARCHAR(MAX), @SqlCount NVARCHAR(MAX), @Top INT;

    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 > @PCount SET @sys_PageIndex = @PCount;

    -- 動態分頁查詢
    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;

    PRINT @Sql;  -- 除錯用
    EXEC(@Sql);
END

結論與建議

  • SQL 2012+:直接使用 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY,最佳化器原生支援,最簡潔高效
  • SQL 2005-2008 R2:ROW_NUMBER 方案 (5) 為首選,語法標準、效能穩定、深淺分頁表現均衡
  • 深分頁極端案例:方案 4 (IN 子查詢) 略勝一籌,但可讀性較差
  • 避免方案 1 (NOT IN):深分頁效能崩潰,且 NOT IN 含 NULL 時語意陷阱
  • 避免方案 2 (雙重 TOP):排序反轉導致大量排序操作,效能最差

總結

分頁查詢無銀彈,需根據 SQL Server 版本、資料量、分頁深度、排序複雜度綜合選擇。現代版本請直接使用 OFFSET/FETCH;舊版本以 ROW_NUMBER 為標準解法,配合適當索引 (含排序欄位) 可獲得最佳效能。

copyright © 萬盛學電腦網 all rights reserved