您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

SQL Server 分頁存儲過程優化演進：從游標到高效集合操作

編輯：MySQL 資料庫相關知識

前言：分頁查詢的挑戰與演進

在 Web 應用開發中，分頁瀏覽是剛性需求。早期常用 ADO Recordset 分頁（基於游標），但游標佔用大量記憶體、鎖定資源，且在大數據量、多表聯查場景下效能極差，甚至導致程式假死。隨著資料規模增長，業界逐漸轉向「只取當前頁所需資料塊」的集合式分頁思路。

一、經典「俄羅斯存儲過程」與表變量改良版

早期流行的「俄羅斯存儲過程」雖實現了按頁碼、頁面大小取數據，但仍依賴游標，局限性未解。後來有開發者利用 SQL Server 的表變量改良，範例如下：

CREATE PROCEDURE pagination1
    @pagesize INT,   -- 每頁筆數
    @pageindex INT   -- 當前頁碼
AS
SET NOCOUNT ON
BEGIN
    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;
END
SET NOCOUNT OFF

此版本利用表變量替代臨時表，減少 I/O 開銷，效能明顯優於游標法。但隨著資料量進一步增長，仍存在全表掃描、排序開銷大等問題。

二、突破性思路：NOT IN 子查詢分頁法

網路上一篇短文《從數據表中取出第 n 條到第 m 條的記錄的方法》啟發了新思路：

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

核心邏輯：先排除前 n-1 筆，再取所需筆數。這避免了游標與臨時表，直接利用索引定位。

三、通用動態分頁存儲過程 pagination2

基於上述思路，結合動態 SQL 實現通用分頁：

CREATE PROCEDURE pagination2
    @SQL NVARCHAR(4000),   -- 不含 ORDER BY 的查詢語句
    @Page INT,             -- 頁碼
    @RecsPerPage INT,      -- 每頁筆數
    @ID VARCHAR(255),      -- 唯一排序鍵
    @Sort VARCHAR(255)     -- 排序欄位與規則
AS
DECLARE @Str NVARCHAR(4000)
-- 動態構建 SQL，利用 NOT IN 跳過前幾頁
SET @Str = 'SELECT TOP ' + CAST(@RecsPerPage AS VARCHAR) + ' * FROM (' + @SQL + ') AS T
WHERE ' + @ID + ' NOT IN (
    SELECT TOP ' + CAST((@Page-1)*@RecsPerPage) AS VARCHAR) + ' ' + @ID + ' FROM (' + @SQL + ') AS T2
    ORDER BY ' + @Sort + '
) ORDER BY ' + @Sort
EXEC sp_executesql @Str

四、現代最佳實踐：OFFSET-FETCH (SQL Server 2012+)

SQL Server 2012 引入 ANSI 標準分頁語法，語法簡潔、優化器支援度高：

SELECT gid, mid, title, fadanwei, fariqi
FROM TGongwen
WHERE fariqi > DATEADD(day, -365, GETDATE())
ORDER BY fariqi DESC
OFFSET (@pageindex-1)*@pagesize ROWS
FETCH NEXT @pagesize ROWS ONLY;

優點：無需動態 SQL、計畫重用率高、可讀性強、配合索引效能最優。