面對 200 萬+ 測試資料集,本文實測五種經典分頁算法在不同頁碼 (第 2/1000/10000/100000/200000 頁) 的耗時表現,並引入**二分法優化**將深分頁效能提升 4-5 倍,給出生產級通用存儲過程完整代碼。
CREATE DATABASE Data_Test;
CREATE TABLE tb_TestTable (
ID INT IDENTITY(1,1) PRIMARY KEY,
UserName NVARCHAR(20) NOT NULL,
UserPWD NVARCHAR(20) NOT NULL,
UserEmail NVARCHAR(40) NULL
);
-- 插入 2,000,000 筆測試資料
SET IDENTITY_INSERT tb_TestTable ON;
DECLARE @i INT = 1;
WHILE @i <= 2000000 BEGIN
INSERT tb_TestTable(ID, UserName, UserPWD, UserEmail)
VALUES (@i, N'admin', N'admin888', N'test@yahoo.com.cn');
SET @i += 1;
END
SET IDENTITY_INSERT tb_TestTable OFF;
| 算法 | 第 2 頁 | 第 1,000 頁 | 第 10,000 頁 | 第 100,000 頁 | 第 200,000 頁 | 排名 |
|---|---|---|---|---|---|---|
| 1. TOP + NOT IN | 0 | 16 | 47 | 475 | 953 | 3 |
| 2. TOP + MAX(ID) | 5 | 16 | 35 | 325 | 623 | 1 |
| 3. 中間變量法 | 966 | 970 | 960 | 945 | 933 | 5 |
| 4. ROW_NUMBER() | 0 | 0 | 34 | 365 | 710 | 2 |
| 5. CTE + ROW_NUMBER | 780 | 796 | 798 | 780 | 805 | 4 |
**結論**:`TOP + MAX(ID)` (關鍵欄位有叢集索引) 效能最穩;`ROW_NUMBER()` 寫法現代、可維護、效能次之;`NOT IN` 深分頁劇烈劣化;中間變量/CTE 完全不可用。
核心思想:根據頁碼位於前半段或後半段,動態選擇**正序向後求**或**倒序向前求**,再反轉結果,使首尾頁效能對稱。
CREATE PROCEDURE dbo.usp_PagedQuery_2Part
@TblName NVARCHAR(200), -- 目標表/連接視圖
@FldName NVARCHAR(500) = N'*', -- 選取欄位
@PageSize INT = 10,
@PageIndex INT = 1,
@FldSort NVARCHAR(200) = NULL, -- 排序欄位,如 'ID ASC, CreateTime DESC'
@SortDir BIT = 0, -- 0=ASC(預設), 1=DESC (最後一欄方向)
@StrCondition NVARCHAR(1000) = NULL, -- WHERE 條件 (不含 WHERE)
@PK NVARCHAR(150), -- 主鍵/唯一鍵 (雙向定位用)
@Distinct BIT = 0, -- 是否 DISTINCT
@TotalPages INT OUTPUT,
@TotalRows BIGINT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX), @SQLCnt NVARCHAR(MAX), @OrderBy NVARCHAR(200);
DECLARE @StartRow BIGINT, @EndRow BIGINT, @Reverse BIT;
-- 1. 總筆數
SET @SQLCnt = N'SELECT @TotalRows = COUNT_BIG(' + CASE WHEN @Distinct=1 THEN N'DISTINCT ' + @PK ELSE N'*' END + N') FROM ' + @TblName;
IF @StrCondition IS NOT NULL SET @SQLCnt += N' WHERE ' + @StrCondition;
EXEC sp_executesql @SQLCnt, N'@TotalRows BIGINT OUTPUT', @TotalRows OUTPUT;
SET @TotalPages = CEILING(@TotalRows * 1.0 / @PageSize);
IF @PageIndex > @TotalPages SET @PageIndex = @TotalPages;
IF @PageIndex < 1 SET @PageIndex = 1;
-- 2. 判斷正/反向
SET @Reverse = CASE WHEN @PageIndex > @TotalPages / 2 THEN 1 ELSE 0 END;
SET @StartRow = CASE WHEN @Reverse = 1 THEN (@TotalPages - @PageIndex) * @PageSize + 1 ELSE (@PageIndex - 1) * @PageSize + 1 END;
SET @EndRow = CASE WHEN @Reverse = 1 THEN (@TotalPages - @PageIndex + 1) * @PageSize ELSE @PageIndex * @PageSize END;
-- 3. 構建 ORDER BY (支援多欄位、最後一欄方向由 @SortDir 控制)
SET @OrderBy = COALESCE(@FldSort, @PK + N' ASC');
IF @Reverse = 1
SET @OrderBy = REPLACE(@OrderBy + N',', N' ASC,', N' DESC,') -- 簡易反轉,生產建議解析重組
-- 4. 動態 SQL (ROW_NUMBER 視窗)
SET @SQL = N'
;WITH Numbered AS (
SELECT ' + @FldName + N', ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + N') AS RN
FROM ' + @TblName + CASE WHEN @StrCondition IS NOT NULL THEN N' WHERE ' + @StrCondition ELSE N'' END + N'
)
SELECT ' + @FldName + N' FROM Numbered WHERE RN BETWEEN ' + CAST(@StartRow AS NVARCHAR) + N' AND ' + CAST(@EndRow AS NVARCHAR) + N'
ORDER BY ' + CASE WHEN @Reverse = 1 THEN N'RN DESC' ELSE N'RN ASC' END + N';';
EXEC sp_executesql @SQL;
END
| 頁碼 | 原 ROW_NUMBER | 二分法 | 提升 |
|---|---|---|---|
| 第 2 頁 | 0 ms | 156 ms | - (首頁無需優化) |
| 第 100,000 頁 | 365 ms | 470 ms | 略增 (中間地帶) |
| 第 199,999 頁 | 710 ms | 156 ms | 4.5 倍 |
| 第 159,999 頁 | ~550 ms | 296 ms | 近 2 倍 |
-- 客戶端記住上一頁最後一筆的排序鍵值
SELECT TOP (@PageSize) * FROM dbo.BigTable
WHERE (SortCol1, PK) > (@LastSortCol1, @LastPK) -- 行值比較 (2008+ 支援)
ORDER BY SortCol1, PK;
二分法 `ROW_NUMBER` 方案兼顧跳頁、總頁數、任意排序與深分頁效能,是傳統 Web 分頁的最佳選擇;新架構優先考慮 Keyset Pagination。