SQL 分頁查詢效能大比拼：NOT IN、MAX/TOP、ROW_NUMBER() 實測與存儲過程封裝

分頁是面試必考、業務高頻的 SQL 技術難點。本文在 SQL Server 環境下，對五種經典分頁寫法進行 1萬/10萬/100萬三級資料量實測，量化效能差異，並提供生產級存儲過程封裝代碼。

測試環境與資料準備

CREATE DATABASE DBTest; USE DBTest;
CREATE TABLE pagetest (
  id INT IDENTITY(1,1) NOT NULL,
  col01 INT NULL,
  col02 NVARCHAR(50) NULL,
  col03 DATETIME NULL
);
-- 插入 100 萬測試資料
DECLARE @i INT = 0;
WHILE @i < 1000000 BEGIN
  INSERT INTO pagetest VALUES (RAND()*10000, LEFT(NEWID(),10), GETDATE());
  SET @i += 1;
END

測試場景：每頁 50 筆，取第 199 頁（OFFSET 9900, LIMIT 50）。

五種分頁寫法對比

實測結果（毫秒，越低越好）

關鍵發現

深度分析

1. MAX/TOP 與 ROW_NUMBER() 雙雄並立： 兩者執行計畫相近，均利用索引有序掃描 + 書籤查找，效能穩健。
2. NOT EXISTS 略優於 NOT IN： NOT IN 對 NULL 值語義複雜，優化器較難下推；NOT EXISTS 邏輯更清晰。
3. ROW_NUMBER() 三種寫法等效： WHERE rn > ... AND rn < ...、WHERE rn BETWEEN ...、WHERE rn > ... 優化器生成相同計畫。
4. ROW_NUMBER() 變體為反模式： 強制先 TOP 9950 再排序，破壞索引有序性，導致大量排序溢出到 tempdb。
5. 可移植性： MAX/TOP 相容 SQL Server 2000/Access；ROW_NUMBER() 需 2005+。

生產級存儲過程封裝

通用參數設計

• @tbName：表名（防注入需白名單驗證）
• @tbFields：回傳欄位（* 或逗號分隔）
• @PageSize、@PageIndex：分頁參數
• @strWhere：WHERE 條件（不含 WHERE 關鍵字）
• @strOrder：ORDER BY 子句（含 ORDER BY 關鍵字）
• @Total OUTPUT：總筆數回傳

ROW_NUMBER() 版本（SQL Server 2005+）

CREATE PROC [dbo].[spSqlPageByRownumber]
  @tbName VARCHAR(255), @tbFields VARCHAR(1000),
  @PageSize INT, @PageIndex INT,
  @strWhere VARCHAR(1000), @strOrder VARCHAR(255),
  @Total INT OUTPUT
AS
BEGIN
  DECLARE @sql NVARCHAR(MAX), @sqlCount NVARCHAR(MAX);
  -- 總筆數
  SET @sqlCount = 'SELECT @Total = COUNT(*) FROM ' + @tbName + 
    CASE WHEN @strWhere <> '' THEN ' WHERE ' + @strWhere ELSE '' END;
  -- 分頁查詢
  SET @sql = 'SELECT * FROM (SELECT ROW_NUMBER() OVER(' + @strOrder + ') rn, ' + @tbFields + 
    ' FROM ' + @tbName + CASE WHEN @strWhere <> '' THEN ' WHERE ' + @strWhere ELSE '' END + ') t ' +
    'WHERE t.rn > ' + CAST((@PageIndex-1)*@PageSize AS VARCHAR) + 
    ' AND t.rn <= ' + CAST(@PageIndex*@PageSize AS VARCHAR);
  EXEC sp_executesql @sqlCount, N'@Total INT OUTPUT', @Total OUTPUT;
  EXEC sp_executesql @sql;
END

MAX/TOP 版本（相容舊版，假設 PK 為 id）

CREATE PROC [dbo].[spSqlPageByMaxTop]
  @tbName VARCHAR(255), @tbFields VARCHAR(1000),
  @PageSize INT, @PageIndex INT,
  @strWhere VARCHAR(1000), @strOrder VARCHAR(255),
  @Total INT OUTPUT
AS
BEGIN
  DECLARE @sql NVARCHAR(MAX), @sqlCount NVARCHAR(MAX);
  SET @sqlCount = 'SELECT @Total = COUNT(*) FROM ' + @tbName + 
    CASE WHEN @strWhere <> '' THEN ' WHERE ' + @strWhere ELSE '' END;
  SET @sql = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' * FROM ' + @tbName +
    ' WHERE id > (SELECT MAX(id) FROM (SELECT TOP ' + CAST((@PageIndex-1)*@PageSize AS VARCHAR) + 
    ' id FROM ' + @tbName + ' ' + @strOrder + ') a) ' + @strOrder;
  EXEC sp_executesql @sqlCount, N'@Total INT OUTPUT', @Total OUTPUT;
  EXEC sp_executesql @sql;
END

進階優化建議

• Keyset Pagination (Seek Method)： WHERE id > @last_seen_id ORDER BY id LIMIT 50，無 OFFSET 掃描，極致效能，適合無限滾動/翻頁。
• 覆蓋索引： INDEX idx_cover (id) INCLUDE (col01, col02, col03) 避免書籤查找。
• 快取總筆數： 總筆數變化低頻時，Redis 快取 COUNT(*) 結果。
• 避免 SELECT *： 僅選擇必要欄位，減少 I/O 與網路傳輸。

分頁無銀彈，**MAX/TOP 與 ROW_NUMBER() 是關係型資料庫的標準解**；百萬級以上建議改用 Keyset Pagination 或引入 Elasticsearch 等搜尋引擎分擔查詢壓力。