萬盛學電腦網

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

SQL Server 視窗函數實現高效分頁:ROW_NUMBER() 最佳實踐與效能調優

SQL Server 2005 引入 `ROW_NUMBER()` 視窗函數,徹底改寫分頁查詢寫法。相較舊式 `TOP + NOT IN` / `MAX(ID)` 等模式,視窗函數具備**單次掃描、計畫穩定、可索引優化**三大優勢,是現代分頁的標準解法。

標準分頁存儲過程範本

CREATE PROCEDURE dbo.usp_GetPagedData
    @PageIndex INT = 1,      -- 1-based 頁碼
    @PageSize  INT = 20,     -- 每頁筆數
    @TotalPages INT OUTPUT,  -- 總頁數
    @TotalRows  BIGINT OUTPUT -- 總筆數
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 1. 取總筆數 (可用索引統計資訊快速估算,或 COUNT_BIG)
    SELECT @TotalRows = COUNT_BIG(*) FROM dbo.StudentsInfo;
    SET @TotalPages = CEILING(@TotalRows * 1.0 / @PageSize);
    
    -- 2. 視窗函數分頁 (單次掃描)
    ;WITH Numbered AS (
        SELECT 
            *,
            ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
        FROM dbo.StudentsInfo
    )
    SELECT * 
    FROM Numbered
    WHERE RowNum BETWEEN (@PageIndex - 1) * @PageSize + 1 
                     AND @PageIndex * @PageSize
    ORDER BY RowNum;  -- 保證輸出順序
END

效能對比 (200 萬測試資料,每頁 10 筆)

方法第 2 頁第 1,000 頁第 10,000 頁第 100,000 頁第 200,000 頁穩定性
TOP + NOT IN0 ms16 ms47 ms475 ms953 ms線性惡化
TOP + MAX(ID)5 ms16 ms35 ms325 ms623 ms較佳
中間變量法966 ms970 ms960 ms945 ms933 ms恆高
ROW_NUMBER()0 ms0 ms34 ms365 ms710 ms優
CTE + ROW_NUMBER780 ms796 ms798 ms780 ms805 ms恆高

深度優化:雙向查找 (Two-Way Seek) 突破深分頁瓶頸

當頁碼 > 總頁數/2 時,改用**倒序掃描 + 取前 N 行**再反轉:

DECLARE @Reverse BIT = CASE WHEN @PageIndex > @TotalPages / 2 THEN 1 ELSE 0 END;

;WITH Numbered AS (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY 
               CASE WHEN @Reverse = 1 THEN ID END DESC,
               CASE WHEN @Reverse = 0 THEN ID END ASC
           ) AS RowNum
    FROM dbo.StudentsInfo
)
SELECT * FROM Numbered
WHERE RowNum BETWEEN 
    CASE WHEN @Reverse = 1 THEN (@TotalPages - @PageIndex) * @PageSize + 1 ELSE (@PageIndex - 1) * @PageSize + 1 END
    AND CASE WHEN @Reverse = 1 THEN (@TotalPages - @PageIndex + 1) * @PageSize ELSE @PageIndex * @PageSize END
ORDER BY ID ASC; -- 最終輸出永遠正序

測試顯示:第 199,999 頁從 710 ms 降至 **156 ms**,深分頁效能提升 **4-5 倍**,且首尾頁效能對稱。

2012+ 新語法:OFFSET/FETCH (ANSI SQL 標準)

SELECT * FROM dbo.StudentsInfo
ORDER BY ID
OFFSET (@PageIndex - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;
  • 語法更簡潔,優化器內部同樣轉為 `TOP` + `ROW_NUMBER` 邏輯
  • 支援變數/參數直接傳入,無需動態 SQL
  • **缺點**:無法直接取得總筆數,仍需分開 `COUNT(*)` 查詢

生產環境檢查清單

  1. 排序欄位 (`ORDER BY ID`) **必須有索引** (叢集索引最佳)
  2. 避免 `SELECT *`,明確列出所需欄位,利用覆蓋索引
  3. 大資料集考慮「Keyset Pagination」(`WHERE ID > @LastSeenID`) 替代 Offset 分頁
  4. 統計資訊過期會導致誤判計畫,定期 `UPDATE STATISTICS`

視窗函數分頁是 SQL Server 2005+ 的標準答案;配合雙向查找與覆蓋索引,可輕鬆支撐千萬級資料表的毫秒級分頁體驗。

copyright © 萬盛學電腦網 all rights reserved