百萬級資料分頁存儲過程：游標法與鍵值法效能實測

需求背景

Web 應用常見分頁需求：百萬級資料表，前端每頁 10-50 筆，需支援首頁、末頁、上一頁、下一頁、跳轉任意頁。傳統 `OFFSET/FETCH` 或 `ROW_NUMBER()` 在深分頁 (如第 10,000 頁) 效能急劇下降。

高效分頁存儲過程：鍵值游標法 (Key-based Pagination)

核心思想：**記住當前頁首尾主鍵值，下一頁直接 `WHERE key > @LastKey`，上一頁 `WHERE key < @FirstKey`**，配合索引求極致效能，與資料總量無關。

CREATE PROCEDURE dbo.usp_PageNavigation
    @FirstKey   NVARCHAR(20) = NULL,  -- 當前頁第一筆排序鍵值
    @LastKey    NVARCHAR(20) = NULL,  -- 當前頁最後一筆排序鍵值
    @IsNext     BIT = NULL,           -- 1=下一頁, 0=上一頁
    @TotalCount INT OUTPUT,           -- 總筆數
    @PageSize   INT OUTPUT,           -- 每頁筆數
    @PageNumber INT                   -- 0=首頁, -1=末頁, >0=指定頁碼 (配合傳統計數)
AS
BEGIN
    SET NOCOUNT ON;

    -- 固定頁大小 (可改為參數)
    SET @PageSize = 10;

    -- 1. 首頁載入
    IF @PageNumber = 0
    BEGIN
        SELECT @TotalCount = COUNT(*) FROM dbo.Product_Test;
        SELECT TOP (@PageSize) ProductId, ProductName, Introduction
        FROM dbo.Product_Test
        ORDER BY ProductId;  -- 必須有叢集索引支援
        RETURN;
    END

    -- 2. 末頁載入 (反向取 TOP N 再正序)
    IF @PageNumber = -1
    BEGIN
        SELECT @TotalCount = COUNT(*) FROM dbo.Product_Test;
        SELECT * FROM (
            SELECT TOP (@PageSize) ProductId, ProductName, Introduction
            FROM dbo.Product_Test
            ORDER BY ProductId DESC
        ) AS t
        ORDER BY ProductId;
        RETURN;
    END

    -- 3. 中間頁：鍵值導航
    IF @IsNext = 1
    BEGIN
        -- 下一頁：大於當前頁最後一筆
        SELECT TOP (@PageSize) ProductId, ProductName, Introduction
        FROM dbo.Product_Test
        WHERE ProductId > @LastKey
        ORDER BY ProductId;
    END
    ELSE
    BEGIN
        -- 上一頁：小於當前頁第一筆，反向取再正序
        SELECT * FROM (
            SELECT TOP (@PageSize) ProductId, ProductName, Introduction
            FROM dbo.Product_Test
            WHERE ProductId < @FirstKey
            ORDER BY ProductId DESC
        ) AS t
        ORDER BY ProductId;
    END
END
GO

前端配合邏輯

• 首頁請求：`@PageNumber=0`，回傳 `@TotalCount`、首頁資料、首尾 `ProductId`。
• 下一頁：`@PageNumber=2, @IsNext=1, @LastKey=上頁最後ProductId`。
3. 上一頁：`@PageNumber=1, @IsNext=0, @FirstKey=當前頁第一ProductId`。 4. 末頁：`@PageNumber=-1` (或前端計算總頁數後直接跳轉)。

關鍵索引需求

-- 排序欄位必須有索引，最佳為叢集索引
CREATE CLUSTERED INDEX CX_Product_Test_ProductId ON dbo.Product_Test(ProductId);
-- 或非叢集覆蓋索引 (含查詢所需所有欄位)
CREATE NONCLUSTERED INDEX IX_Product_Test_Paging 
ON dbo.Product_Test(ProductId) INCLUDE (ProductName, Introduction);

效能對比 (百萬筆測試環境)

進階擴展

1. 複合排序鍵：`WHERE (Col1, Col2) > (@LastCol1, @LastCol2)` (需資料庫支援 tuple 比較，SQL Server 2008+ 支援 `(a,b) > (c,d)` 語法)。
2. 跳轉任意頁：結合傳統 `COUNT(*)` + `OFFSET/FETCH` 僅用於跳轉，常規導航用鍵值法。
3. 快取總筆數：`@TotalCount` 可快取 5-10 分鐘，避免每次 `COUNT(*)` 全表掃描。
4. 軟刪除/過濾：`WHERE IsDeleted=0 AND ProductId > @LastKey`，確保索引包含過濾欄位 (Filtered Index)。

小結

百萬級分頁，**鍵值游標法** 是效能與簡潔度的最佳平衡。前端僅需記住首尾主鍵，後端單一索引求解，深分頁效能與首頁持平。**棄用 OFFSET/FETCH 做高頻深分頁**，轉型為鍵值導航，是資料庫分頁優化的核心模式。