萬盛學電腦網

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

SQL Server 千萬級資料分頁存儲過程:基於鍵值定位的高效能分頁方案 (SQL 2000/2005 相容)

面對千萬級資料量,傳統 `TOP N` + `NOT IN` / `ROW_NUMBER()` (2005+) 分頁在深分頁 (第 1000 頁) 時效能崩塌。本文解析一個基於 **「記錄排序鍵值定位 + ROWCOUNT 限制」** 的經典分頁存儲過程,適用 SQL Server 2000/2005/2008/R2,並對比現代 `OFFSET/FETCH` (2012+) 方案。

一、 核心演算法原理:鍵值定位法 (Keyset Pagination / Seek Method)

  1. **第一步**:按排序條件 (`@Sort`) 讀取前 `@StartRow` 行 (使用 `SET ROWCOUNT`),**記住最後一行的排序鍵值** (`@SortColumn`)。
  2. 第二步:重設 `ROWCOUNT = @PageSize`,查詢 `WHERE 排序鍵 <運算子> @SortColumn` 的前 `@PageSize` 行。

**優勢**:避免掃描並丟棄前 N 頁資料,直接 **Index Seek 定位** 到分頁起點,效能隨頁碼增長**不退化** (O(1) 定位 vs O(N) 掃描)。

二、 存儲過程完整代碼 (整理修正版)

IF OBJECT_ID('dbo.SP_Pagination', 'P') IS NOT NULL DROP PROCEDURE dbo.SP_Pagination;
GO

CREATE PROCEDURE dbo.SP_Pagination
    @Tables      VARCHAR(2000),   -- 表/視圖名 (含 Schema 建議)
    @PrimaryKey  VARCHAR(500),    -- 主鍵/唯一鍵 (作為最終輸出排序保證)
    @Sort        VARCHAR(500) = NULL, -- 排序欄位 (不含 ORDER BY),如 'OrderDate DESC, Id ASC'
    @CurrentPage INT = 1,
    @PageSize    INT = 50,
    @Fields      VARCHAR(2000) = '*',  -- 輸出欄位
    @Filter      VARCHAR(1000) = NULL, -- WHERE 條件 (不含 WHERE)
    @Group       VARCHAR(1000) = NULL  -- GROUP BY 條件 (不含 GROUP BY)
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 1. 預設排序 = 主鍵
    IF @Sort IS NULL OR @Sort = '' SET @Sort = @PrimaryKey;
    
    -- 2. 解析排序方向與欄位 (簡化版,假設單欄位排序或首欄位決定方向)
    DECLARE @SortColumn NVARCHAR(500), @Operator NCHAR(2), @SortTable NVARCHAR(500), @SortName NVARCHAR(500);
    DECLARE @DataType NVARCHAR(100), @Precision INT;
    
    IF CHARINDEX('DESC', @Sort) > 0 BEGIN
        SET @SortColumn = LTRIM(RTRIM(REPLACE(@Sort, 'DESC', '')));
        SET @Operator = N'<=';  -- 降冪:下一頁值 <= 上頁最小值
    END ELSE BEGIN
        IF CHARINDEX('ASC', @Sort) = 0 SET @Sort = @Sort + ' ASC'; -- 明確指定 ASC
        SET @SortColumn = LTRIM(RTRIM(REPLACE(@Sort, 'ASC', '')));
        SET @Operator = N'>=';  -- 升冪:下一頁值 >= 上頁最大值
    END
    
    -- 3. 解析 表.欄位 格式
    IF CHARINDEX('.', @SortColumn) > 0 BEGIN
        SET @SortTable = SUBSTRING(@SortColumn, 1, CHARINDEX('.', @SortColumn)-1);
        SET @SortName  = SUBSTRING(@SortColumn, CHARINDEX('.', @SortColumn)+1, LEN(@SortColumn));
    END ELSE BEGIN
        SET @SortTable = @Tables;
        SET @SortName  = @SortColumn;
    END
    
    -- 4. 獲取排序欄位資料型別與精度 (用於變數宣告)
    SELECT @DataType = t.name, @Precision = 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', @DataType) > 0 
        SET @DataType = @DataType + '(' + CAST(@Precision AS VARCHAR(10)) + ')';
    
    -- 5. 參數處理
    IF @CurrentPage < 1 SET @CurrentPage = 1;
    DECLARE @StartRow VARCHAR(20) = CAST((@CurrentPage - 1) * @PageSize + 1 AS VARCHAR(20));
    DECLARE @PageSizeStr VARCHAR(20) = CAST(@PageSize AS VARCHAR(20));
    DECLARE @FilterStr NVARCHAR(1100) = CASE WHEN @Filter IS NOT NULL AND @Filter <> '' THEN N' WHERE ' + @Filter + N' ' ELSE N'' END;
    DECLARE @SimpleFilter NVARCHAR(1100) = CASE WHEN @Filter IS NOT NULL AND @Filter <> '' THEN N' AND ' + @Filter + N' ' ELSE N'' END;
    DECLARE @GroupStr NVARCHAR(1100) = CASE WHEN @Group IS NOT NULL AND @Group <> '' THEN N' GROUP BY ' + @Group + N' ' ELSE N'' END;
    
    -- 6. 動態 SQL 執行 (核心雙查詢)
    DECLARE @DynamicSql NVARCHAR(MAX) = N'
        DECLARE @SortVal ' + @DataType + N';
        SET ROWCOUNT ' + @StartRow + N';
        SELECT @SortVal = ' + @SortColumn + N' FROM ' + @Tables + @FilterStr + @GroupStr + N' ORDER BY ' + @Sort + N';
        SET ROWCOUNT ' + @PageSizeStr + N';
        SELECT ' + @Fields + N' FROM ' + @Tables + N' WHERE ' + @SortColumn + @Operator + N' @SortVal ' + @SimpleFilter + @GroupStr + N' ORDER BY ' + @Sort + N';';
    
    -- PRINT @DynamicSql; -- 除錯用
    EXEC sp_executesql @DynamicSql;
    
    -- 7. 重置 ROWCOUNT (關鍵!避免影響後續連接池連接)
    SET ROWCOUNT 0;
END
GO

三、 使用範例

-- 表 Traffic_Sites,主鍵 Id,依 Id 升冪分頁,第 1 頁 5 筆
EXEC dbo.SP_Pagination 
    @Tables = 'Traffic_Sites', 
    @PrimaryKey = 'Id', 
    @Sort = 'Id ASC', 
    @CurrentPage = 1, 
    @PageSize = 5, 
    @Fields = '*', 
    @Filter = '', 
    @Group = '';

四、 關鍵限制與避坑指南

限制/風險說明緩解方案
排序鍵必須唯一若排序欄位有重複值,`WHERE SortCol >= @LastVal` 會漏掉同值的後續行,或重複返回同值行。**排序欄位必須包含唯一鍵** (如 `OrderDate DESC, Id DESC`),`@PrimaryKey` 參數即為此用途,但舊版代碼未自動組合,需呼叫端保證 `@Sort` 含唯一鍵。
SET ROWCOUNT 影響範圍`SET ROWCOUNT` 為連接級會話設定,影響後續所有語句,甚至連接池復用時殘留。**結尾必須 `SET ROWCOUNT 0` 重置**。現代版本改用 `TOP (@Var)` 避免此問題。
動態 SQL 注入風險`@Tables`、`@Sort`、`@Filter`、`@Group` 直接拼接。嚴格白名單驗證表名/欄位名;`@Filter`/`@Group` 建議改用 Table-Valued Parameter 或應用層構建安全 SQL。
統計資訊依賴優化器需準確統計資訊選擇正確索引 Seek。定期 `UPDATE STATISTICS` 或啟用自動更新。
不支援複雜排序多欄位混合 ASC/DESC (如 `Col1 ASC, Col2 DESC`) 邏輯極複雜,此過程僅處理首欄位方向。升級至 SQL 2012+ 使用 `OFFSET/FETCH` 原生支援任意複雜排序。
視圖/衍生表支援有限註釋提到視圖「目前還有點小問題」。視圖需確保排序欄位來自基礎表且有索引支援。

五、 現代替代方案:OFFSET / FETCH (SQL Server 2012+)

-- 標準、簡潔、安全、支援任意複雜排序、無 ROWCOUNT 副作用
DECLARE @PageSize INT = 50, @PageNumber INT = 3;
SELECT Id, Col1, Col2
FROM dbo.BigTable
WHERE Status = 1
ORDER BY CreatedDate DESC, Id DESC  -- 必須含唯一鍵保證確定性
OFFSET (@PageNumber - 1) * @PageSize ROWS 
FETCH NEXT @PageSize ROWS ONLY;
  • **優化器支援**:可產生 `Top` + `Index Seek` 計畫,效能等同鍵值定位法。
  • **參數化安全**:無動態 SQL 拼接風險。
  • **可讀性/維護性**:碾壓舊版動態 SQL 方案。

結語

此存儲過程是 SQL 2000/2005 時代 **高效能分頁的經典之作**,核心思想「鍵值定位 + ROWCOUNT」至今仍是分散式資料庫分頁 (如 `WHERE id > last_seen_id`) 的基石。**現代環境 (SQL 2012+) 請直接使用 `OFFSET/FETCH`**,僅在維護舊系統、跨版本相容、或極端效能調校需手動控制求值順序時參考此模式。重構時務必修復「排序唯一性保證」、「動態 SQL 注入」、「ROWCOUNT 殘留」三大硬傷。

copyright © 萬盛學電腦網 all rights reserved