SQL Server 分頁存儲過程效能大比拚：ROW_NUMBER() vs 傳統 NOT IN — 50 萬筆實測

測試環境與資料

• 硬體：Core 2 Duo 2.20GHz / 1GB RAM
• DB：SQL Server 2008
• 資料表 Xtest：ID BIGINT IDENTITY PK、XName NVARCHAR(50)、XDemo NVARCHAR(500)、XName 有非叢集索引
• 資料量：**50 萬筆**
• 查詢條件：XName LIKE '%222name%' (低選擇性、前導萬用字元，索引無效，全表掃描)

兩種分頁存儲過程實作

A. 傳統 SQL 2000 風格：NOT IN + TOP (sp_Pager2000)

-- 核心邏輯
IF @PageIndex > 1
    SET @sql = 'SELECT TOP ' + @PageSize + ' * FROM ' + @tblName + 
               ' WHERE ' + @PKName + ' NOT IN (SELECT TOP ' + @PageSize*(@PageIndex-1) + ' ' + @PKName + ' FROM ' + @tblName + ' ' + @strWhere + ' ' + @strOrder + ') ' + @strWhere;
ELSE
    SET @sql = 'SELECT TOP ' + @PageSize + ' * FROM ' + @tblName + ' ' + @strWhere;
-- 執行動態 SQL

B. SQL 2005+ 標準：ROW_NUMBER() OVER (sp_Pager2005)

SET @strSQL = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (' + @strOrder + ') AS ROWID, ' + @strGetFields + ' FROM [' + @tblName + '] ' + @strWhere + ') AS sp WHERE ROWID BETWEEN ' + @StartRow + ' AND ' + @EndRow;

實測結果 (單位：毫秒)

深度分析

1. 統計總筆數 (COUNT(*)) 是最大殺手

• 全表掃描 + LIKE '%...%' 過濾，**每次 2.9 秒**。
• **最佳化**：首頁取得總筆數後，**快取起來** (Session、Redis、OutputCache)，後續分頁**完全不再統計**。效能提升 **10-15 倍**。

2. ROW_NUMBER() 完勝 NOT IN (在「僅首頁統計」模式下)

• 第 2 頁：218ms vs 343ms (快 36%)
• 第 6 頁：687ms vs 1281ms (快 46%)
• **原因**：ROW_NUMBER() 由優化器單一掃描 + 排序 + 區段篩選；NOT IN 子查詢隨頁碼增長，TOP N 掃描量線性增大 (第 6 頁掃前 6×PageSize 列)，導致遞增開銷。

3. 每頁統計時兩者差異不大

因 COUNT(*) 佔 90%+ 耗時，分頁邏輯本身差異被掩蓋。

現代最佳分頁寫法 (SQL Server 2012+)

CREATE PROCEDURE dbo.sp_Pager_Modern
    @PageIndex INT = 1,
    @PageSize  INT = 20,
    @TotalRows INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 1. 統計總筆數 (可選快取)
    SELECT @TotalRows = COUNT(*) FROM dbo.Xtest WHERE XName LIKE '%222name%';
    
    -- 2. OFFSET/FETCH 標準語法 (最佳化器支援最好)
    SELECT ID, XName, XDemo
    FROM dbo.Xtest
    WHERE XName LIKE '%222name%'
    ORDER BY ID
    OFFSET (@PageIndex - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END;

• OFFSET/FETCH 內部實作同 ROW_NUMBER()，但語法標準、可讀性高、優化器持續改進。
• **Keyset Pagination (游標分頁)**：若翻頁極深 (第 10000 頁)，改用 WHERE ID > @LastSeenID ORDER BY ID OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY，避免掃描前 N 萬列。

索引策略對分頁的影響

總結

1. ROW_NUMBER() / OFFSET-FETCH > NOT IN：單掃描、無遞增掃描、優化器友善。
2. 快取總筆數是銀彈：分頁查詢 90% 時間花在 COUNT(*)，首頁統計後快取，後續頁亞秒級響應。
3. 深分頁用 Keyset：避免 OFFSET 1000000 掃描百萬列丟棄。
4. 索引設計優於語法選擇：覆蓋索引 + 叢集鍵排序 = 最快分頁。