MSSQL 基於 ID 的高效分頁查詢實現方案

核心思路

利用 ID 欄位的單調遞增特性，結合 TOP 子句與子查詢，避免傳統 OFFSET/FETCH（SQL Server 2012+）或雙重排序的效能開銷。適用於 SQL Server 2000/2005/2008 等舊版本環境。

儲存過程完整代碼

CREATE PROCEDURE dbo.usp_GetPagedInviteList
    @uid        INT,        -- 目標用戶 ID
    @pageIndex  INT,        -- 頁碼（從 1 開始）
    @pageSize   INT         -- 每頁筆數
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startRow INT = (@pageIndex - 1) * @pageSize;

    IF @pageIndex = 1
    BEGIN
        -- 首頁直接取前 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
        -- 非首頁：先取前 (pageIndex-1)*pageSize 筆的最小 ID，再取比該 ID 小的前 pageSize 筆
        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

關鍵點解析

• 參數化查詢：使用變數而非字串拼接，防止 SQL 注入並利用執行計劃快取。
• 首頁優化：第一頁直接 TOP N ORDER BY id DESC，避免子查詢開銷。
• 索引建議：在 touid, id DESC 上建立複合非叢集索引，覆蓋查詢欄位可進一步消除書籤查找。

效能對比

擴展建議

• 若 ID 不連續或有刪除，邏輯仍正確，因依賴「小於上一頁最小 ID」而非行號。
• 需支援正向/反向翻頁時，可新增 @direction 參數動態切換 >/< 與排序方向。
• 大數據量（千萬級）建議改用 ROW_NUMBER() OVER (ORDER BY id DESC) 搭配 CTE，配合索引效能更穩定。