SQL Server 千萬級數據高效分頁存儲過程：基於主鍵範圍查詢優化方案

面對千萬級大表，傳統 OFFSET ... FETCH 或 ROW_NUMBER() 分頁在深翻頁時性能急劇下降。本文提供一個生產驗證的存儲過程 GetRecordFromPage，採用 主鍵範圍定位 + 臨時表索引覆蓋 技術，實現固定低延遲的深分頁查詢。

核心優化原理

1. 避免全表掃描：利用聚簇索引（主鍵）有序特性，通過 TOP N 定位邊界主鍵值，而非掃描前 N 行。
2. 臨時表僅存主鍵：@indextable 僅存儲目標頁邊界內的主鍵 ID，極窄行寬，內存佔用極小。
3. 動態構建 WHERE 子句：根據排序方向（ASC/DESC）自動生成 > (SELECT MIN...) 或 < (SELECT MAX...) 範圍條件，利用索引 Seek 定位。
4. SET ROWCOUNT 截斷：在填充臨時表時設置 SET ROWCOUNT @PageUpperBound，達到上限立即停止，避免多餘掃描。

存儲過程完整代碼

CREATE PROCEDURE GetRecordFromPage
    @tblName       VARCHAR(255),   -- 表名
    @fldName       VARCHAR(255),   -- 分頁依據字段（建議主鍵/唯一索引）
    @PageSize      INT = 10,       -- 每頁條數
    @PageIndex     INT = 1,        -- 頁碼（從 1 開始）
    @IsCount       BIT = 0,        -- 1=返回總記錄數
    @OrderType     BIT = 0,        -- 0=升序，1=降序
    @strWhere      VARCHAR(1000) = '' -- 查詢條件（不含 WHERE 關鍵字）
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @strSQL      VARCHAR(6000),
            @strTmp      VARCHAR(100),
            @strOrder    VARCHAR(400),
            @PageLowerBound INT,
            @PageUpperBound INT;

    -- 計算邊界
    SET @PageLowerBound = (@PageIndex - 1) * @PageSize;
    SET @PageUpperBound = @PageIndex * @PageSize;

    -- 構建排序子句
    IF @OrderType != 0
    BEGIN
        SET @strTmp = '< (SELECT MIN';
        SET @strOrder = ' ORDER BY [' + @fldName + '] DESC';
    END
    ELSE
    BEGIN
        SET @strTmp = '> (SELECT MAX';
        SET @strOrder = ' ORDER BY [' + @fldName + '] ASC';
    END

    -- 首頁直接走索引順掃 TOP N
    IF @PageIndex = 1
    BEGIN
        SET @strTmp = '';
        IF @strWhere != ''
            SET @strTmp = ' WHERE ' + @strWhere;

        SET @strSQL = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' * FROM [' + @tblName + ']' + @strTmp + ' ' + @strOrder;
    END
    ELSE
    BEGIN
        -- 非首頁：構建範圍查詢
        DECLARE @whereClause VARCHAR(1000) = '';
        IF @strWhere != ''
            SET @whereClause = ' WHERE ' + @strWhere;

        SET @strSQL = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' * FROM [' + @tblName + '] ' +
                      'WHERE [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) ' +
                      'FROM (SELECT TOP ' + CAST(@PageUpperBound AS VARCHAR) + ' [' + @fldName + '] ' +
                      'FROM [' + @tblName + ']' + @whereClause + ' ' + @strOrder + ') AS tblTmp) ' +
                      @whereClause + ' ' + @strOrder;
    END

    -- 可選：返回總記錄數
    IF @IsCount != 0
        SET @strSQL = 'SELECT COUNT(*) AS Total FROM [' + @tblName + ']' + 
                      CASE WHEN @strWhere != '' THEN ' WHERE ' + @strWhere ELSE '' END;

    EXEC (@strSQL);
END
GO

調用示例

-- 查詢 Orders 表第 3 頁，每頁 20 條，按 OrderID 降序，篩選 2023 年訂單
EXEC GetRecordFromPage 
    @tblName = 'Orders',
    @fldName = 'OrderID',
    @PageSize = 20,
    @PageIndex = 3,
    @OrderType = 1,
    @strWhere = 'YEAR(OrderDate) = 2023';

-- 同時獲取總數（用於前端分頁控件）
EXEC GetRecordFromPage 
    @tblName = 'Orders',
    @fldName = 'OrderID',
    @PageSize = 20,
    @PageIndex = 3,
    @OrderType = 1,
    @strWhere = 'YEAR(OrderDate) = 2023',
    @IsCount = 1;

性能對比（千萬級測試表，主鍵聚簇索引）

適用條件與限制

• ✅ 分頁字段必須有索引（最佳為聚簇主鍵或唯一非聚簇索引）。
• ✅ 分頁字段值 唯一，否則邊界定位會遺漏/重複行。
• ✅ @strWhere 條件列最好有覆蓋索引，否則仍會回表。
• ❌ 不支持游標式「下一頁」語義（需記錄上頁最大/最小主鍵值）。
• ❌ 動態 SQL 存在注入風險，調用層需嚴格驗證表名、列名、條件合法性。

現代替代方案（SQL Server 2012+）

若版本支持，優先使用標準語法，優化器內部已實現類似邏輯：

SELECT * FROM Orders
WHERE YEAR(OrderDate) = 2023
ORDER BY OrderID DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

但對於 2008/2008 R2 老版本或極端深翻頁場景，本存儲過程仍是經典可靠的選擇。