SQL Server 通用分頁存儲過程：高效處理千萬級數據

前言

在處理大量數據的應用場景中，分頁查詢是核心需求之一。本文介紹一個經過實戰驗證的通用分頁存儲過程 MyPagination，它能夠高效支撐千萬級數據的分頁顯示，並提供靈活的參數配置。

存儲過程設計思路

該存儲過程採用動態 SQL 構建方式，根據頁碼採取不同的查詢策略：

• 首頁優化：當 @PageIndex = 1 時，直接使用 TOP 子句查詢，避免臨時表開銷，大幅提升首頁加載速度。
• 非首頁查詢：利用 IDENTITY 函數為結果集生成行號，存入臨時表 #tmpTable，再根據行號篩選目標頁數據。
• 總記錄數統計：通過可選參數 @doCount 控制是否執行 COUNT(*)，統計結果暫存於臨時表 tmpTable 後讀取並銷毀。

參數詳解

核心代碼邏輯

-- 總記錄數統計
IF (@doCount != 0)
BEGIN
    DECLARE @sWhere varchar(2000) = ''
    IF (@strWhere != '') SET @sWhere = ' WHERE ' + @strWhere
    SET @strSQL = 'if exists (select * from dbo.sysobjects where id = object_id(''[dbo].[tmpTable]'') and OBJECTPROPERTY(id, ''IsUserTable'') = 1) '
    SET @strSQL = @strSQL + ' UPDATE tmpTable SET Total = (SELECT COUNT(*) FROM [' + @tblName + '] ' + @sWhere + ') '
    SET @strSQL = @strSQL + ' ELSE SELECT COUNT(*) AS Total INTO tmpTable FROM [' + @tblName + '] ' + @sWhere
    EXEC (@strSQL)
    SELECT @RecordCount=Total FROM tmpTable
    EXEC ('DROP TABLE tmpTable')
END

-- 構建排序子句
IF (@strOrderBy != '') SET @strOrder = ' ORDER BY ' + @strOrderBy

-- 首頁直接 TOP 查詢
IF (@PageIndex = 1)
BEGIN
    IF (@strWhere != '')
        SET @strSQL = 'SELECT TOP ' + str(@PageSize) + ' ' + @strGetFields + ' FROM [' + @tblName + '] WHERE ' + @strWhere + @strOrder
    ELSE
        SET @strSQL = 'SELECT TOP ' + str(@PageSize) + ' ' + @strGetFields + ' FROM ['+ @tblName + '] '+ @strOrder
END
ELSE
BEGIN
    -- 非首頁：生成帶行號的臨時表
    SET @strSQL = 'SELECT TOP ' + str(@PageIndex*@PageSize) + ' IDENTITY(int,1,1) AS IID, ' + @strGetFields + ' INTO #tmpTable FROM [' + @tblName + ']'
    IF (@strWhere != '') SET @strSQL = @strSQL + ' WHERE ' + @strWhere + @strOrder
    ELSE SET @strSQL = @strSQL + @strOrder
    -- 從臨時表篩選目標頁
    SET @strSQL = @strSQL + ' SELECT ' + @strGetFields + ' FROM #tmpTable WHERE IID > ' + str((@PageIndex-1)*@PageSize) + ' DROP TABLE #tmpTable'
END

EXEC (@strSQL)

使用示例

DECLARE @TotalCount INT
EXEC MyPagination 
    @tblName = 'Orders',
    @strGetFields = 'OrderID, CustomerID, OrderDate, TotalAmount',
    @PageSize = 20,
    @PageIndex = 3,
    @doCount = 1,
    @strOrderBy = 'OrderDate DESC',
    @strWhere = 'Status = 1',
    @RecordCount = @TotalCount OUTPUT

SELECT @TotalCount AS TotalRecords

注意事項與優化建議

1. 確保 @strOrderBy 欄位存在索引，否則大數據量下排序會成為瓶頸。
2. 避免在 @strWhere 中使用導致索引失效的寫法（如函數包裹列、前置通配符 LIKE '%...'）。
3. 對於極大表（億級以上），建議改用 Keyset Pagination（基於游標/主鍵的分頁）替代 OFFSET/FETCH 或行號法。
4. 存儲過程使用動態 SQL，需防範 SQL 注入；調用端應嚴格驗證輸入參數。

總結

MyPagination 通過首頁直取、非首頁行號法的混合策略，在兼顧通用性的前提下實現了不錯的性能表現。對於千萬級數據的常規分頁場景，它是一個開箱即用的可靠方案。隨著 SQL Server 版本迭代，也可考慮遷移至原生 OFFSET ... FETCH 語法以簡化維護。