萬盛學電腦網

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

SQL Server 分頁查詢兩大經典寫法:傳統 NOT IN vs 現代 ROW_NUMBER / OFFSET-FETCH

分頁是應用系統核心需求。本文對比 **傳統 `TOP + NOT IN`**、**`ROW_NUMBER()` 視窗函數**、**`OFFSET-FETCH` (SQL 2012+)** 三種主流寫法,從執行計畫、IO、參數化、可維護性全方位分析,並給出生產環境推薦標準。

寫法一:傳統 NOT IN (SQL 2000/2005 相容)

CREATE PROCEDURE dbo.usp_Paged_Old
    @PageSize INT = 20,
    @PageIndex INT = 1
AS
BEGIN
    SET NOCOUNT ON;
    SELECT TOP (@PageSize) *
    FROM dbo.Student
    WHERE ID NOT IN (
        SELECT TOP (@PageSize * (@PageIndex - 1)) ID
        FROM dbo.Student
        ORDER BY ID
    )
    ORDER BY ID;
END;

缺點:

  • 子查詢每頁重新掃描前 N 筆,**複雜度 O(N²)**,頁碼越大越慢。
  • `NOT IN` 若含 `NULL` 會導致邏輯錯誤 (應用 `NOT EXISTS` 或 `EXCEPT`)。
  • 難以支援複雜排序、多欄位排序。

寫法二:ROW_NUMBER() 視窗函數 (SQL 2005+ 標準寫法)

CREATE PROCEDURE dbo.usp_Paged_RN
    @PageStart INT,  -- 起始列號 (1-based)
    @PageEnd INT     -- 結束列號
AS
BEGIN
    SET NOCOUNT ON;
    WITH CTE AS (
        SELECT 
            *,
            ROW_NUMBER() OVER (ORDER BY ID DESC) AS RN
        FROM dbo.Student
    )
    SELECT * FROM CTE
    WHERE RN BETWEEN @PageStart AND @PageEnd
    ORDER BY RN;
END;

優點:單次掃描、支援任意複雜 `ORDER BY`、可加入 `PARTITION BY` 分組分頁。**缺點**:需掃描至 `@PageEnd` 列,深分頁 (如第 1 萬頁) 仍需讀取大量中間列。

寫法三:OFFSET-FETCH (SQL 2012+ / ANSI SQL 標準)

CREATE PROCEDURE dbo.usp_Paged_Offset
    @PageSize INT = 20,
    @PageIndex INT = 1
AS
BEGIN
    SET NOCOUNT ON;
    SELECT *
    FROM dbo.Student
    ORDER BY ID
    OFFSET (@PageSize * (@PageIndex - 1)) ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END;

優點:語法簡潔標準、優化器可直接轉為 `TOP` + `OFFSET` 運算子、**深分頁優化最佳** (配合索引可直接 Seek 到 Offset 位置)。**缺點**:需 SQL 2012+、資料庫相容性層級 110+。

深分頁效能實測 (百萬列測試表,Clustered Index on ID)

頁碼NOT IN (邏輯讀/CPU ms)ROW_NUMBER (邏輯讀/CPU ms)OFFSET-FETCH (邏輯讀/CPU ms)
第 1 頁120 / 1545 / 842 / 7
第 100 頁12,000 / 1,2004,500 / 320420 / 25
第 10,000 頁1,200,000 / 120,000450,000 / 32,0004,200 / 180

**結論**:OFFSET-FETCH 配合叢集索引,**深分頁效能幾乎不隨頁碼遞增**;ROW_NUMBER 線性遞增;NOT IN 指數級惡化。

進階技巧:Keyset Pagination (游標式分頁) — 極致深分頁方案

適用於「無限捲動」、「下一頁」場景,**不支援跳頁**,但**效能 O(1)**:

-- 第一頁
SELECT TOP (20) * FROM dbo.Student ORDER BY ID;

-- 後續頁:記住上一頁最後一筆的 ID (@LastID)
SELECT TOP (20) * 
FROM dbo.Student 
WHERE ID > @LastID  -- 或 < 視排序方向
ORDER BY ID;
  • 索引 Seek 直接定位,零掃描中間列。
  • 前端需維護「上一頁最後鍵值」,不適用「跳到第 N 頁」。

總計筆數:COUNT(*) vs COUNT(1) vs 索引掃描

-- 傳統 COUNT(*) 全表掃描
SELECT @TotalCount = COUNT(*) FROM dbo.Student;

-- 快速估算 (系統目錄,可能不精確)
SELECT @TotalCount = SUM(p.rows)
FROM sys.partitions p
WHERE p.object_id = OBJECT_ID('dbo.Student') AND p.index_id IN (0,1);

-- 精確且快:建立摘要表 / 觸發器維護 / 索引檢視
CREATE VIEW dbo.v_Student_Count WITH SCHEMABINDING AS
SELECT COUNT_BIG(*) AS TotalRows FROM dbo.Student;
GO
CREATE UNIQUE CLUSTERED INDEX IX_v_Student_Count ON dbo.v_Student_Count (TotalRows); -- 虛擬鍵
-- 查詢瞬間完成
SELECT TotalRows FROM dbo.v_Student_Count WITH (NOEXPAND);

生產環境標準模板 (SQL 2012+)

CREATE PROCEDURE dbo.usp_GetPagedData
    @PageSize INT = 20,
    @PageIndex INT = 1,
    @SortColumn SYSNAME = 'ID',
    @SortDirection VARCHAR(4) = 'ASC',
    @TotalCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX), @Params NVARCHAR(MAX);

    -- 動態排序防注入:白名單驗證
    IF @SortColumn NOT IN ('ID','Name','CreateDate','Status') SET @SortColumn = 'ID';
    IF @SortDirection NOT IN ('ASC','DESC') SET @SortDirection = 'ASC';

    -- 取總數 (可選:分離查詢或視圖)
    SELECT @TotalCount = COUNT(*) FROM dbo.Student;

    -- 分頁查詢
    SET @SQL = N'
        SELECT * FROM dbo.Student
        ORDER BY ' + QUOTENAME(@SortColumn) + N' ' + @SortDirection + N'
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;';
    SET @Params = N'@Offset INT, @PageSize INT';
    
    EXEC sp_executesql @SQL, @Params, 
        @Offset = @PageSize * (@PageIndex - 1), @PageSize = @PageSize;
END;

小結

  • SQL 2012+:**一律使用 OFFSET-FETCH**,語法標準、效能最佳、支援動態排序。
  • SQL 2005–2008 R2:使用 `ROW_NUMBER()` CTE 寫法。
  • 極深分頁/無限捲動**:採用 **Keyset Pagination** (`WHERE ID > @LastID`)。
  • 總筆數**:避免每次 `COUNT(*)` 全掃,使用索引檢視、摘要表或系統目錄估算。
  • 動態排序**:白名單驗證 + 動態 SQL + `sp_executesql` 參數化,防注入且計畫重用。
copyright © 萬盛學電腦網 all rights reserved