SQL Server 索引結構與分頁查詢優化（三）：百萬級資料高效分頁存儲過程實戰

分頁查詢演進史

1. ADO Recordset 分頁：客戶端游標，載入全量資料到記憶體，僅適用小資料量。
2. 「俄羅斯存儲過程」：游標法，同樣受限於記憶體與鎖定。
3. 臨時表/表變量 + IDENTITY：先寫入暫存表產生流水號，再篩選頁碼範圍。表變量優於臨時表（較少重編譯、較少鎖定）。
4. NOT IN / NOT EXISTS 子查詢：SELECT TOP n * FROM t WHERE id NOT IN (SELECT TOP m id FROM t ORDER BY id)。缺點：NOT IN 遇 NULL 值語意陷阱、子查詢重複掃描、深分頁效能急劇下降。
5. MAX/MIN 鍵值定位法（最佳）：利用遞增主鍵的 MAX(id) 或 MIN(id) 作為分頁邊界，配合 SEEK 運算子，實現 O(1) 定位。

三種分頁方案效能實測（1000 萬筆測試表，主鍵 GID 非叢集索引）

結論：方案三（MAX/MIN 定位）在深分頁（>1000 頁）效能穩定優異，後勁十足。

方案三核心邏輯

-- 第 @PageIndex 頁，每頁 @PageSize 筆，依 id 遞增
DECLARE @PageSize INT = 20, @PageIndex INT = 50000;

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

通用分頁預存程序（支援條件、排序、總筆數）

CREATE PROCEDURE dbo.Pagination_Fast
    @tblName      NVARCHAR(255),      -- 資料表名
    @strGetFields NVARCHAR(1000) = N'*',  -- 回傳欄位
    @fldName      NVARCHAR(255),      -- 排序欄位（需唯一、有索引）
    @PageSize     INT = 20,
    @PageIndex    INT = 1,
    @doCount      BIT = 0,            -- 1=回傳總筆數
    @OrderType    BIT = 0,            -- 0=ASC, 1=DESC
    @strWhere     NVARCHAR(1500) = N''  -- WHERE 條件（不含 WHERE 關鍵字）
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX),
            @order NVARCHAR(100),
            @bound NVARCHAR(200);

    -- 排序方向
    IF @OrderType = 0
    BEGIN
        SET @order = N' ORDER BY [' + @fldName + N'] ASC';
        SET @bound = N'> (SELECT MAX([' + @fldName + N']) FROM (SELECT TOP ((@PageIndex - 1) * @PageSize) [' + @fldName + N'] FROM [' + @tblName + N']' + @order + N') AS t)';
    END
    ELSE
    BEGIN
        SET @order = N' ORDER BY [' + @fldName + N'] DESC';
        SET @bound = N'< (SELECT MIN([' + @fldName + N']) FROM (SELECT TOP ((@PageIndex - 1) * @PageSize) [' + @fldName + N'] FROM [' + @tblName + N']' + @order + N') AS t)';
    END

    -- 總筆數查詢
    IF @doCount = 1
    BEGIN
        SET @sql = N'SELECT COUNT(*) AS Total FROM [' + @tblName + N']' +
                   CASE WHEN @strWhere <> N'' THEN N' WHERE ' + @strWhere ELSE N'' END;
        EXEC sp_executesql @sql;
    END

    -- 第 1 頁直接 TOP 查詢（最快）
    IF @PageIndex = 1
    BEGIN
        SET @sql = N'SELECT TOP (@PageSize) ' + @strGetFields + N' FROM [' + @tblName + N']' +
                   CASE WHEN @strWhere <> N'' THEN N' WHERE ' + @strWhere ELSE N'' END + @order;
        EXEC sp_executesql @sql, N'@PageSize INT', @PageSize;
        RETURN;
    END

    -- 一般頁：鍵值定位
    SET @sql = N'SELECT TOP (@PageSize) ' + @strGetFields + N' FROM [' + @tblName + N']' +
               N' WHERE [' + @fldName + N']' + @bound +
               CASE WHEN @strWhere <> N'' THEN N' AND ' + @strWhere ELSE N'' END + @order;

    EXEC sp_executesql @sql,
        N'@PageSize INT, @PageIndex INT',
        @PageSize = @PageSize,
        @PageIndex = @PageIndex;
END
GO

呼叫範例

-- 查詢第 3 頁，每頁 20 筆，依 CreateDate 遞減，回傳總筆數
EXEC dbo.Pagination_Fast
    @tblName = N'Orders',
    @strGetFields = N'OrderID, CustomerID, OrderDate, TotalAmount',
    @fldName = N'OrderDate',
    @PageSize = 20,
    @PageIndex = 3,
    @doCount = 1,
    @OrderType = 1,
    @strWhere = N'CustomerID = ''ALFKI'' AND OrderDate >= ''2023-01-01''';

關鍵設計細節

• @fldName 必須唯一且有索引：主鍵、唯一索引、或 IDENTITY 欄位。非唯一欄位會導致邊界定位錯誤（同值列跨頁）。
• 動態 SQL 參數化：使用 sp_executesql 避免 SQL 注入，執行計畫可重用。
• 第 1 頁特例優化：直接 TOP N ... ORDER BY，省去子查詢開銷。
• 條件下推：@strWhere 同時套用於子查詢與外層，確保邊界計算與最終結果一致。
• 深分頁極限：百萬頁以上仍可能因子查詢 TOP (巨大數字) 掃描過多列而變慢。極端深分頁建議改用「游標式分頁」（前端傳上一頁最大/最小鍵值）或「預先計算頁碼對應鍵值區間」快取表。

效能調優檢核清單

1. 確認 @fldName 有**覆蓋索引**（含 @strGetFields 所有欄位）或至少叢集索引。
2. @strWhere 條件欄位有合適索引，避免掃描。
3. 使用 SET STATISTICS IO, TIME ON 觀察邏輯讀與耗時。
4. 大資料量考慮 READ UNCOMMITTED（NOLOCK）或快照隔離級別減少封鎖。