萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

MSSQL 分頁查詢最佳實踐:基於 ID 的高效分頁存儲過程

傳統分頁痛點

  • TOP N + NOT IN (SELECT TOP M ...):子查詢效能差、大偏移量極慢
  • ROW_NUMBER() OVER (ORDER BY ...):SQL 2005+ 標準寫法,但大資料集排序成本高

ID 基礎鍵集分頁法(適用於遞增 ID、無大量刪除間隙)

ALTER PROCEDURE [dbo].[dnt_getappinvitelist]
    @uid INT,
    @pageIndex INT,
    @pageSize INT
AS
BEGIN
    DECLARE @startRow INT, @endRow INT
    SET @startRow = (@pageIndex - 1) * @pageSize

    IF @pageIndex = 1
    BEGIN
        -- 首頁:直接取最新 N 筆
        EXEC('
            SELECT TOP ' + CAST(@pageSize AS VARCHAR) + '
                [id],[typename],[appid],[type],[fromuid],[touid],[myml],[datetime],[hash]
            FROM [dnt_myinvite]
            WHERE [touid] = ' + CAST(@uid AS VARCHAR) + '
            ORDER BY [id] DESC
        ')
    END
    ELSE
    BEGIN
        -- 非首頁:以上一頁最小 ID 為游標向後取
        EXEC('
            SELECT TOP ' + CAST(@pageSize AS VARCHAR) + '
                [id],[typename],[appid],[type],[fromuid],[touid],[myml],[datetime],[hash]
            FROM [dnt_myinvite]
            WHERE [touid] = ' + CAST(@uid AS VARCHAR) + '
              AND [id] < (
                  SELECT MIN([id]) FROM (
                      SELECT TOP ' + CAST(@startRow AS VARCHAR) + ' [id]
                      FROM [dnt_myinvite]
                      WHERE [touid] = ' + CAST(@uid AS VARCHAR) + '
                      ORDER BY [id] DESC
                  ) AS T
              )
            ORDER BY [id] DESC
        ')
    END
END

核心邏輯解析

  1. 首頁:直接 TOP @pageSize ORDER BY ID DESC,利用叢集索引掃描極快
  2. 非首頁:
    • 內層 SELECT TOP @startRow ID ... ORDER BY ID DESC 取得第 @startRow 筆的 ID(即上一頁最後一筆 ID)
    • MIN(ID) 取得該分頁邊界值
    • 外層 WHERE ID < @boundary 直接定位到下一頁起始位置,避免掃描前 N 筆

適用條件與限制

  • ✅ ID 為 遞增身分識別欄(IDENTITY)
  • ✅ 資料以 ID 排序等同業務排序(如按時間倒序、ID 即時間單調遞增)
  • ⚠️ 大量刪除導致 ID 間隙過大時,頁面筆數可能不穩定
  • ❌ 不支援任意排序欄位分頁(需改用 ROW_NUMBER/OFFSET-FETCH)

現代替代方案(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 標準化、可讀性高、優化器支援良好,建議新專案優先採用。

copyright © 萬盛學電腦網 all rights reserved