您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

SQL Server 大量資料分頁優化：ROW_NUMBER() 視窗函數與儲存程序實戰

編輯：MySQL 資料庫相關知識

傳統 TOP N 或 OFFSET/FETCH 在百萬筆資料深分頁時效能急劇下降。本文展示以 ROW_NUMBER() 視窗函數搭配衍生表/儲存程序的優化模式，僅讀取當頁資料，大幅降低 I/O 與 CPU 耗用。

效能痛點分析

SELECT * FROM table ORDER BY id OFFSET 100000 ROWS FETCH NEXT 20 ROWS：引擎需掃描/排序前 100,020 列再捨棄大部分。
無覆蓋索引時觸發 Key Lookup，隨機 I/O 暴增。

優化核心：預先計算序號 + 範圍過濾

步驟 1：建立測試環境

USE TestForPaging;
GO

CREATE TABLE SomeData (
  id INT PRIMARY KEY,
  name VARCHAR(30) NULL,
  description TEXT
);
GO

-- 插入樣本資料
INSERT INTO SomeData VALUES (1,'num1','第1筆'), (2,'num2','第2筆'), ...;

步驟 2：產生帶序號的衍生表

SELECT name, description,
       ROW_NUMBER() OVER (ORDER BY id DESC) AS dataLevel
FROM SomeData;

步驟 3：範圍查詢（核心優化）

SELECT dataLevel, name, description
FROM (
  SELECT name, description,
         ROW_NUMBER() OVER (ORDER BY id DESC) AS dataLevel
  FROM SomeData
) AS datawithlevel
WHERE dataLevel BETWEEN 2 AND 4;  -- 第 2~4 筆

步驟 4：封裝為儲存程序（支援動態排序與分頁參數）

CREATE PROCEDURE GetDataPaged
  @startRowIndex INT,   -- 起始頁索引（0-based）
  @maximumRows INT,     -- 每頁筆數
  @sort VARCHAR(50)     -- 排序欄位
AS
BEGIN
  SET NOCOUNT ON;
  IF LEN(@sort) = 0 SET @sort = 'id';

  -- 動態 SQL 避免 ORDER BY 參數化限制
  DECLARE @sql NVARCHAR(MAX) = N'
    SELECT dataLevel, name, description
    FROM (
      SELECT name, description,
             ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@sort) + ' DESC) AS dataLevel
      FROM SomeData
    ) AS d
    WHERE dataLevel > (@startRowIndex * @maximumRows)
      AND dataLevel <= ((@startRowIndex + 1) * @maximumRows);';

  EXEC sp_executesql @sql,
    N'@startRowIndex INT, @maximumRows INT',
    @startRowIndex, @maximumRows;
END;
GO

關鍵優化細節

技巧	效果
ROW_NUMBER() 只掃描一次	避免重複排序
WHERE 範圍過濾在外層	最小化輸出結果集
動態 SQL + QUOTENAME	防 SQL 注入、支援動態排序
sp_executesql 參數化	計畫重用、避免編譯開銷

進階替代方案（SQL 2012+）

-- OFFSET/FETCH 簡潔語法（內部同樣需掃描前 N 列）
SELECT name, description
FROM SomeData
ORDER BY id DESC
OFFSET @startRowIndex * @maximumRows ROWS
FETCH NEXT @maximumRows ROWS ONLY;

OFFSET/FETCH 適合中小資料量或淺分頁；深分頁仍建議 ROW_NUMBER + 覆蓋索引（INCLUDE description）或 Keyset Pagination（游標式分頁）。

實務部署檢核

[ ] 分頁欄位建立覆蓋索引：CREATE INDEX IX_SomeData_Paging ON SomeData(id DESC) INCLUDE (name, description);
[ ] 避免 SELECT *，僅取必要欄位。
[ ] 監控執行計畫：確認為 Index Seek + Top 操作，而非 Table Scan。

掌握視窗函數分頁模式，並配合索引設計，即能在大量資料環境下實現穩定、低延遲的分頁查詢體驗。