SQL Server 千萬級分頁存儲過程：KEYSET 分頁與動態 SQL 實現高效能翻頁

面對千萬列資料表，傳統 ROW_NUMBER() OVER (...) + WHERE rn BETWEEN ... 或 OFFSET/FETCH 在深分頁 (第 10 萬頁) 時效能崩潰。本文提供基於鍵集分頁 思想的通用分頁存儲過程，利用排序鍵單調性實現 O(1) 翻頁效能，支援任意排序、篩選、分組、欄位選擇。

核心原理：鍵集分頁

• 記住上一頁最後一筆的 排序鍵值 (如 Id=12345, CreatedAt='2024-01-01')
• 下一頁查詢：WHERE (SortKey > @LastSortKey) ORDER BY SortKey FETCH NEXT @PageSize
• 優勢：無掃描、無排序、索引覆蓋、效能隨頁碼不退化
• 限制：不支援隨機跳頁 (需順序翻頁)、排序鍵必須唯一 (或加 Tie-breaker)

通用分頁預存程序 (相容 SQL 2000+)

IF OBJECT_ID('SP_Pagination') IS NOT NULL DROP PROC SP_Pagination;
GO
CREATE PROCEDURE SP_Pagination
    @Tables      VARCHAR(2000),   -- 表/檢視表名 (可含 Schema)
    @PrimaryKey  VARCHAR(500),    -- 主鍵/唯一排序鍵 (支援複合: Col1, Col2)
    @Sort        VARCHAR(500) = NULL,  -- 排序子句 (不含 ORDER BY)，如 'Id DESC, CreateDate ASC'
    @CurrentPage INT = 1,
    @PageSize    INT = 5,
    @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 VARCHAR(1000), @Operator CHAR(2), @SortTable VARCHAR(1000), @SortName VARCHAR(1000);
    IF CHARINDEX('DESC', @Sort) > 0 BEGIN
        SET @SortColumn = REPLACE(@Sort, 'DESC', '');
        SET @Operator = '<=';
    END ELSE BEGIN
        SET @SortColumn = REPLACE(@Sort, 'ASC', '');
        SET @Operator = '>=';
    END
    SET @SortColumn = LTRIM(RTRIM(@SortColumn));
    
    -- 3. 解析 Schema.Table.Column
    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. 取得排序欄位資料型別 (用於變數宣告)
    DECLARE @Type VARCHAR(1000), @Prec INT;
    SELECT @Type = t.name, @Prec = 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', @Type) > 0 SET @Type = @Type + '(' + CAST(@Prec AS VARCHAR) + ')';
    
    -- 5. 計算分頁參數
    DECLARE @StrPageSize VARCHAR(100) = CAST(@PageSize AS VARCHAR(100));
    DECLARE @StrStartRow VARCHAR(100) = CAST(((@CurrentPage - 1) * @PageSize + 1) AS VARCHAR(100));
    DECLARE @StrFilter VARCHAR(1000) = '', @StrSimpleFilter VARCHAR(1000) = '', @StrGroup VARCHAR(1000) = '';
    IF @Filter IS NOT NULL AND @Filter <> '' BEGIN
        SET @StrFilter = ' WHERE ' + @Filter;
        SET @StrSimpleFilter = ' AND ' + @Filter;
    END
    IF @Group IS NOT NULL AND @Group <> '' SET @StrGroup = ' GROUP BY ' + @Group;
    
    -- 6. 動態 SQL 執行雙查詢法：先取第 N 筆排序鍵值，再取該頁資料
    DECLARE @DynSql NVARCHAR(MAX) = N'
        DECLARE @SortColumn ' + @Type + N';
        SET ROWCOUNT ' + @StrStartRow + N';
        SELECT @SortColumn = ' + @SortColumn + N' FROM ' + @Tables + @StrFilter + @StrGroup + N' ORDER BY ' + @Sort + N';
        SET ROWCOUNT ' + @StrPageSize + N';
        SELECT ' + @Fields + N' FROM ' + @Tables + N' WHERE ' + @SortColumn + @Operator + N' @SortColumn ' + @StrSimpleFilter + @StrGroup + N' ORDER BY ' + @Sort + N';';
    
    EXEC (@DynSql);
END;
GO

呼叫範例

-- Traffic_Sites 表，依 Id 遞增分頁，每頁 5 筆，第 1 頁
EXEC SP_Pagination 'Traffic_Sites', 'Id', 'Id ASC', 1, 5, '*', '', '';

關鍵技術解析

現代化改良版 (SQL 2012+ 推薦)

CREATE PROCEDURE SP_Pagination_Modern
    @Tables       NVARCHAR(500),
    @SortColumns  NVARCHAR(500),  -- 如 'Id ASC, CreateDate DESC'
    @PageSize     INT = 20,
    @LastKeys     NVARCHAR(MAX) = NULL,  -- JSON: '[{"Id":123},{"CreateDate":"2024-01-01"}]'
    @Fields       NVARCHAR(MAX) = N'*',
    @Filter       NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Sql NVARCHAR(MAX) = N'SELECT ' + @Fields + N' FROM ' + @Tables;
    IF @Filter IS NOT NULL SET @Sql += N' WHERE ' + @Filter;
    
    -- 建立動態 WHERE 子句對應 @LastKeys
    IF @LastKeys IS NOT NULL BEGIN
        -- 此處需解析 JSON 產生 (Col1 > @v1) OR (Col1=@v1 AND Col2 > @v2) ...
        -- 簡化：假設單鍵
        SET @Sql += N' WHERE Id > @LastId';
    END
    
    SET @Sql += N' ORDER BY ' + @SortColumns + N' OFFSET 0 ROWS FETCH NEXT ' + CAST(@PageSize AS NVARCHAR(10)) + N' ROWS ONLY;';
    
    -- 實際需配合 sp_executesql 參數化
    EXEC sp_executesql @Sql, N'@LastId BIGINT', @LastId = 123;
END;

適用場景與選型

分頁優化的本質是避免掃描與排序。鍵集分頁將「第 N 頁」轉化為「大於上一頁游標的前 M 筆」，從根本上解決深分頁效能崩潰問題。