SQL Server 高效分頁存儲程序演進：從游標到 TOP NOT IN 再到叢集索引鍵定位的極致優化

分頁技術演進路線

1. ADO Recordset 分頁：游標駐留記憶體、鎖定資源、大資料量極易 OOM/死鎖，已淘汰。
2. 俄羅斯分頁存儲程序：游標 + 暫存表，同樣受限於游標缺點。
3. 表變量分頁 (方案一)：以 TABLE 變量儲存主鍵 + Identity 排序，再 Join 取資料。無統計資訊但避免重編譯，中等資料量表現優異。
4. TOP + NOT IN (方案二)：SELECT TOP page_size * FROM t WHERE id NOT IN (SELECT TOP (page_size*(page-1)) id FROM t ORDER BY id) ORDER BY id。邏輯簡潔但 NOT IN 效能差，深分頁極慢。
5. 叢集索引鍵定位 (方案三，極致優化)：利用 MAX/MIN 於叢集索引鍵定位分頁邊界，配合 >/< 範圍查詢，符合 SARG 形式，深分頁效能穩定。

方案三核心邏輯

SELECT TOP @PageSize *
FROM table1
WHERE id > (
    SELECT MAX(id) FROM (
        SELECT TOP ((@PageIndex-1)*@PageSize) id 
        FROM table1 ORDER BY id
    ) AS T
)
ORDER BY id;

原理：子查詢取前 (頁碼-1)*頁大小 筆的最大 id 作為分水嶺，外層以 id > 分水嶺 直接定位下一頁起始點，避免掃描前 N 頁資料。

實測效能對比 (1000 萬筆測試表，GID 主鍵非叢集索引)

結論：方案三在深分頁 (＞1000頁) 效能優勢壓倒性，且隨頁碼增長曲線平緩。

通用分頁存儲程序 (方案三封裝)

CREATE PROCEDURE dbo.Pagination_V3
    @tblName       VARCHAR(255),
    @strGetFields  VARCHAR(1000) = '*',
    @fldName       VARCHAR(255) = '',
    @PageSize      INT = 10,
    @PageIndex     INT = 1,
    @doCount       BIT = 0,
    @OrderType     BIT = 0,      -- 0=ASC, 1=DESC
    @strWhere      VARCHAR(1500) = ''  -- 不含 WHERE 關鍵字
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @strSQL VARCHAR(5000), @strTmp VARCHAR(110), @strOrder VARCHAR(400);

    -- 總筆數統計
    IF @doCount <> 0
    BEGIN
        SET @strSQL = 'SELECT COUNT(*) AS Total FROM [' + @tblName + ']' 
                    + CASE WHEN @strWhere <> '' THEN ' WHERE ' + @strWhere ELSE '' END;
        EXEC(@strSQL);
    END

    -- 分頁查詢
    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
        SET @strSQL = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' ' + @strGetFields 
                    + ' FROM [' + @tblName + ']' 
                    + CASE WHEN @strWhere <> '' THEN ' WHERE ' + @strWhere ELSE '' END
                    + ' ' + @strOrder;
    END
    ELSE
    BEGIN
        SET @strSQL = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' ' + @strGetFields
                    + ' FROM [' + @tblName + ']'
                    + ' WHERE [' + @fldName + ']' + @strTmp + '([' + @fldName + '])'
                    + ' FROM (SELECT TOP ' + CAST((@PageIndex-1)*@PageSize AS VARCHAR) 
                    + ' [' + @fldName + '] FROM [' + @tblName + '] ' + @strOrder + ') AS tblTmp)'
                    + ' ' + @strOrder;
        IF @strWhere <> ''
            SET @strSQL = @strSQL + ' AND ' + @strWhere; -- 簡化，實際需置於子查詢與外層
    END

    EXEC(@strSQL);
END;

關鍵優化洞見：叢集索引的雙重角色

• 查詢優化：叢集索引鍵為最常用範圍查詢欄位（如日期），可快速縮小掃描範圍。
• 分頁排序：叢集索引鍵為唯一且有序（如 IDENTITY 或 高精度時間戳），可支援 MAX/MIN 定位分頁邊界。

矛盾：業務常用範圍欄位（日期）常有重複值，無法作為分頁定位鍵；唯一鍵（ID）又浪費寶貴的叢集索引位置。

解決方案：新增高精度時間戳欄位（DEFAULT GETDATE()，精度至毫秒 + UNIQUE 約束），作為叢集索引鍵。同時滿足：

1. 按時間範圍查詢極快（叢集索引特性）。
2. 唯一有序，支援分頁定位（MAX/MIN）。

優化後，無論大小資料量、首頁或尾頁，分頁耗時穩定在 幾十毫秒甚至 0 毫秒。

叢集索引選擇黃金法則

1. 建立在 最頻繁用於縮小查詢範圍 的欄位上。
2. 建立在 最頻繁用於排序/分頁 的欄位上。

若單一欄位無法同時滿足，尋找或建立兼具兩特性的替代鍵（如高精度時間戳）。