SQL Server 分頁查詢優化實戰：從游標到高效存儲過程的演進

大數據量分頁查詢優化：三代方案演進與最佳實踐

Web 應用中分頁瀏覽是核心功能，但傳統 ADO 記錄集分頁（基於游標）在大數據量下存在嚴重效能瓶頸：游標佔用大量記憶體、鎖定記錄、多表大表查詢易導致程式無回應。現代分頁核心理念是「只檢索當前頁面大小的資料塊，而非載入全部資料」。

第一代：俄羅斯存儲過程（游標基礎版）

早期較好的實現方式，但受限於游標缺點，未獲廣泛認可。

第二代：表變量優化版

CREATE PROCEDURE pagination1
    @pagesize INT,   -- 頁面大小
    @pageindex INT   -- 當前頁碼
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @indextable TABLE(id INT IDENTITY(1,1), nid INT)  -- 表變量
    DECLARE @PageLowerBound INT, @PageUpperBound INT
    
    SET @PageLowerBound = (@pageindex - 1) * @pagesize
    SET @PageUpperBound = @PageLowerBound + @pagesize
    
    SET ROWCOUNT @PageUpperBound
    INSERT INTO @indextable(nid) 
    SELECT gid FROM TGongwen 
    WHERE fariqi > DATEADD(day, -365, GETDATE()) 
    ORDER BY fariqi DESC
    
    SELECT O.gid, O.mid, O.title, O.fadanwei, O.fariqi 
    FROM TGongwen O, @indextable t 
    WHERE O.gid = t.nid 
      AND t.id > @PageLowerBound AND t.id <= @PageUpperBound 
    ORDER BY t.id
    
    SET NOCOUNT OFF
END

優點：使用表變量優於暫存表，速度優於 ADO 方式。但後續發現更優方案。

第三代：NOT IN 子查詢優化版（網路流行寫法）

核心邏輯源於「從資料表取出第 n 到第 m 條記錄」：

SELECT TOP m-n+1 * 
FROM publish 
WHERE id NOT IN (SELECT TOP n-1 id FROM publish)

改造為分頁存儲過程：

CREATE PROCEDURE pagination2
    @SQL NVARCHAR(4000),   -- 不含 ORDER BY 的 SQL
    @Page INT,             -- 頁碼
    @RecsPerPage INT,      -- 每頁筆數
    @ID VARCHAR(255),      -- 排序用不重複 ID
    @Sort VARCHAR(255)     -- 排序欄位與規則
AS
BEGIN
    DECLARE @Str NVARCHAR(4000)
    SET @Str = 'SELECT TOP ' + CAST(@RecsPerPage AS VARCHAR(20)) + 
               ' * FROM (' + @SQL + ') T ' +
               'WHERE T.' + @ID + ' NOT IN (' +
               'SELECT TOP ' + CAST((@RecsPerPage * (@Page - 1)) AS VARCHAR(20)) + 
               ' ' + @ID + ' FROM (' + @SQL + ') T9 ORDER BY ' + @Sort + 
               ') ORDER BY ' + @Sort
    PRINT @Str
    EXEC sp_ExecuteSql @Str
END

簡化邏輯：

SELECT TOP 頁大小 * 
FROM Table1 
WHERE ID NOT IN (SELECT TOP 頁大小*頁數 ID FROM 表 ORDER BY ID) 
ORDER BY ID

關鍵缺陷與改進方向

• 致命缺點：含 NOT IN，大數據量下效能極差
• 改造嘗試：用 NOT EXISTS 替代，但實測效能無差異
• 核心洞見：分頁優化最終目的是避免產生過大記錄集，TOP 關鍵字可有效控制資料量
• 突破口：利用 MAX/MIN 配合 >/< 運算子，使查詢符合 SARG 形式，啟用索引求值

第四代：MAX/MIN 邊界值分頁（高效方案）

SELECT TOP 頁大小 * 
FROM table1 
WHERE id > (SELECT MAX(id) FROM (SELECT TOP ((頁碼-1)*頁大小) id FROM table1 ORDER BY id) AS T) 
ORDER BY id

效能實測對比（1000萬筆測試資料，單位：毫秒）

通用分頁存儲過程 pagination3（生產級）

CREATE PROCEDURE pagination3
    @tblName VARCHAR(255),       -- 表名
    @strGetFields VARCHAR(1000) = '*',  -- 回傳欄位
    @fldName VARCHAR(255) = '',  -- 排序欄位
    @PageSize INT = 10,          -- 頁大小
    @PageIndex INT = 1,          -- 頁碼
    @doCount BIT = 0,            -- 是否回傳總筆數
    @OrderType BIT = 0,          -- 排序類型 0升序/1降序
    @strWhere VARCHAR(1500) = '' -- 查詢條件（勿加 WHERE）
AS
BEGIN
    DECLARE @strSQL VARCHAR(5000), @strTmp VARCHAR(110), @strOrder VARCHAR(400)
    
    IF @doCount != 0
    BEGIN
        IF @strWhere != ''
            SET @strSQL = "SELECT COUNT(*) AS Total FROM [" + @tblName + "] WHERE " + @strWhere
        ELSE
            SET @strSQL = "SELECT COUNT(*) AS Total FROM [" + @tblName + "]"
    END
    ELSE
    BEGIN
        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
        
        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(@PageSize) + " " + @strGetFields + 
                          " FROM [" + @tblName + "] WHERE [" + @fldName + "]" + @strTmp + 
                          "([" + @fldName + "]) FROM (SELECT TOP " + 
                          STR((@PageIndex-1)*@PageSize) + " [" + @fldName + "] FROM [" + 
                          @tblName + "]" + @strOrder + ") AS tblTmp)" + @strOrder
            
            IF @strWhere != ''
                SET @strSQL = "SELECT TOP " + STR(@PageSize) + " " + @strGetFields + 
                              " FROM [" + @tblName + "] WHERE [" + @fldName + "]" + @strTmp + 
                              "([" + @fldName + "]) FROM (SELECT TOP " + 
                              STR((@PageIndex-1)*@PageSize) + " [" + @fldName + "] FROM [" + 
                              @tblName + "] WHERE " + @strWhere + " " + @strOrder + ") AS tblTmp) " +
                              "AND " + @strWhere + " " + @strOrder
        END
    END
    EXEC(@strSQL)
END

核心優勢

• 大數據量下查詢最後幾頁通常不超過 9 秒，避免逾時
• 支援動態表名、欄位、條件、排序，通用性極強
• 可選擇性回傳總筆數，減少額外 COUNT(*) 查詢
• 升降序切換邏輯完善，首頁走簡化路徑效能最佳

關鍵結論

1. 分頁演進本質：從「載入全量再過濾」→「只取需要的頁面資料」
2. 索引是前提：排序欄位必須有索引，最好是聚集索引
3. 避免 NOT IN/EXISTS：改用邊界值（MAX/MIN）配合比較運算子
4. TOP 是關鍵：有效限制中間結果集大小
5. 存儲過程預編譯：執行效率優於動態 SQL