MSSQL 基於 ID 的高效分頁存儲過程實現

場景與挑戰

SQL Server 早期版本（2008 R2 以前）無 OFFSET ... FETCH 語法，海量數據下傳統 TOP N + NOT IN 或游標分頁性能極差。利用主鍵 ID 有序特性，可構建「鍵集分頁」存儲過程，實現百萬級秒級響應。

存儲過程完整代碼

CREATE PROCEDURE dbo.dnt_getappinvitelist
    @uid        INT,
    @pageindex  INT,
    @pagesize   INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @startRow INT = (@pageindex - 1) * @pagesize;

    IF @pageindex = 1
    BEGIN
        -- 首頁直接 TOP N
        SELECT TOP (@pagesize) 
            [id],[typename],[appid],[type],[fromuid],[touid],[myml],[datetime],[hash]
        FROM [dnt_myinvite]
        WHERE [touid] = @uid
        ORDER BY [id] DESC;
    END
    ELSE
    BEGIN
        -- 非首頁：子查詢取第 @startRow 行的 ID 作為錨點
        SELECT TOP (@pagesize)
            [id],[typename],[appid],[type],[fromuid],[touid],[myml],[datetime],[hash]
        FROM [dnt_myinvite]
        WHERE [touid] = @uid
          AND [id] < (
                SELECT MIN([id]) 
                FROM (
                    SELECT TOP (@startRow) [id]
                    FROM [dnt_myinvite]
                    WHERE [touid] = @uid
                    ORDER BY [id] DESC
                ) AS T
          )
        ORDER BY [id] DESC;
    END
END;

核心原理解析

1. 首頁優化：TOP N ORDER BY id DESC 直接走聚簇索引掃描，極快。
2. 深分頁錨點查詢：
   內層 TOP @startRow ORDER BY id DESC 定位到「上一頁最後一行」的 ID；
   外層 WHERE id < 錨點ID 配合索引直接定位，避免掃描大量無關行。
3. 動態 SQL 注意：示例用 EXEC('...') 拼接字符串，實際生產建議改用參數化 sp_executesql 防注入。

現代 SQL Server (2012+) 更簡潔寫法

SELECT [id],[typename],[appid],[type],[fromuid],[touid],[myml],[datetime],[hash]
FROM [dnt_myinvite]
WHERE [touid] = @uid
ORDER BY [id] DESC
OFFSET (@pageindex - 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY;

OFFSET/FETCH 由優化器原生支持，執行計劃更穩定，推薦優先使用。

性能對比與建議

結論：舊版本務必用「鍵集分頁」；新版本直接用標準語法，代碼更清晰、維護成本更低。