測試環境
- 表
ARTICLE:53,210 筆真實客戶資料
- 欄位:
ID (叢集索引)、YEAR (無索引)
- 分頁大小:30 筆
- SQL Server 2008 R2
五種方案與實測結果 (平均 100 次執行時間)
場景 A:查詢第 1500 頁 (第 45001-45030 筆) - 深分頁
| 方案 | SQL 核心邏輯 | 耗時 | 排名 |
| 1. NOT IN | TOP 30 ... WHERE ID NOT IN (TOP 45000 ...) | 45 秒 | 4 |
| 2. 雙重 TOP 正反排序 | TOP 30 FROM (TOP 45030 ... ORDER BY DESC) ORDER BY ASC | 138 秒 | 5 (最差) |
| 3. 關聯子查詢 (JOIN) | JOIN (TOP 30 ID FROM (TOP 45030 ...) ORDER BY ASC) | 21 秒 | 2 |
| 4. IN 子查詢 | WHERE ID IN (TOP 30 FROM (TOP 45030 ...) ORDER BY ASC) | 20 秒 | 1 (最佳) |
| 5. ROW_NUMBER | ROW_NUMBER() OVER(...) n, WHERE n > 45000 AND n <= 45030 | 15 秒 | 1 (最佳) |
場景 B:查詢第 34 頁 (第 1001-1030 筆) - 淺分頁
| 方案 | 耗時 | 排名 |
| 1. NOT IN | 80 秒 | 5 |
| 2. 雙重 TOP | 30 秒 | 4 |
| 3. JOIN 子查詢 | 12 秒 | 2 |
| 4. IN 子查詢 | 13 秒 | 3 |
| 5. ROW_NUMBER | 14 秒 | 1 |
方案程式碼完整版
方案 4:IN 子查詢 (深分頁最佳)
SELECT * FROM ARTICLE w1
WHERE ID IN (
SELECT TOP 30 ID FROM (
SELECT TOP 45030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
) w ORDER BY w.YEAR ASC, w.ID ASC
)
ORDER BY w1.YEAR DESC, w1.ID DESC;
方案 5:ROW_NUMBER (通用最佳、語法最清晰)
SELECT w2.n, w1.*
FROM ARTICLE w1,
(SELECT TOP 45030 ROW_NUMBER() OVER (ORDER BY YEAR DESC, ID DESC) AS n, ID
FROM ARTICLE) w2
WHERE w1.ID = w2.ID AND w2.n > 45000
ORDER BY w2.n ASC;
通用分頁儲存程序 (基於方案 4)
CREATE PROCEDURE [dbo].[sys_Page_v2]
@PCount INT OUTPUT, -- 總頁數
@RCount INT OUTPUT, -- 總筆數
@sys_Table NVARCHAR(100), -- 表名
@sys_Key VARCHAR(50), -- 主鍵
@sys_Fields NVARCHAR(500),-- 查詢欄位
@sys_Where NVARCHAR(3000),-- WHERE 條件
@sys_Order NVARCHAR(100), -- ORDER BY
@sys_Begin INT, -- 起始位移
@sys_PageIndex INT, -- 頁碼
@sys_PageSize INT -- 頁大小
AS
BEGIN
SET NOCOUNT ON; SET ANSI_WARNINGS ON;
IF @sys_PageSize < 1 OR @sys_PageIndex < 1 RETURN;
DECLARE @new_where NVARCHAR(3000), @new_order1 NVARCHAR(100), @new_order2 NVARCHAR(100);
DECLARE @Sql NVARCHAR(MAX), @SqlCount NVARCHAR(MAX), @Top INT;
SET @new_where = CASE WHEN ISNULL(@sys_Where,'')='' THEN '' ELSE ' WHERE '+@sys_Where END;
IF ISNULL(@sys_Order,'') <> ''
BEGIN
SET @new_order1 = ' ORDER BY ' + REPLACE(REPLACE(@sys_Order,'desc',''),'asc','') + ' DESC';
SET @new_order2 = ' ORDER BY ' + @sys_Order;
END
ELSE BEGIN
SET @new_order1 = ' ORDER BY ID DESC';
SET @new_order2 = ' ORDER BY ID ASC';
END
-- 統計總筆數/頁數
SET @SqlCount = 'SELECT @RCount=COUNT(1), @PCount=CEILING((COUNT(1)+0.0)/'+CAST(@sys_PageSize AS NVARCHAR)+') FROM '+@sys_Table+@new_where;
EXEC sp_executesql @SqlCount, N'@RCount INT OUTPUT,@PCount INT OUTPUT', @RCount OUTPUT, @PCount OUTPUT;
-- 修正頁碼
IF @sys_PageIndex > @PCount SET @sys_PageIndex = @PCount;
-- 動態分頁查詢
SET @Sql = 'SELECT '+@sys_Fields+' FROM '+@sys_Table+' w1 WHERE '+@sys_Key+' IN ('
+ 'SELECT TOP '+LTRIM(STR(@sys_PageSize))+' '+@sys_Key+' FROM ('
+ 'SELECT TOP '+LTRIM(STR(@sys_PageSize*@sys_PageIndex+@sys_Begin))+' '+@sys_Key+' FROM '+@sys_Table+@new_where+@new_order2
+') w '+@new_order1+') '+@new_order2;
PRINT @Sql; -- 除錯用
EXEC(@Sql);
END
結論與建議
- SQL 2012+:直接使用
OFFSET ... ROWS FETCH NEXT ... ROWS ONLY,最佳化器原生支援,最簡潔高效
- SQL 2005-2008 R2:ROW_NUMBER 方案 (5) 為首選,語法標準、效能穩定、深淺分頁表現均衡
- 深分頁極端案例:方案 4 (IN 子查詢) 略勝一籌,但可讀性較差
- 避免方案 1 (NOT IN):深分頁效能崩潰,且
NOT IN 含 NULL 時語意陷阱
- 避免方案 2 (雙重 TOP):排序反轉導致大量排序操作,效能最差
總結
分頁查詢無銀彈,需根據 SQL Server 版本、資料量、分頁深度、排序複雜度綜合選擇。現代版本請直接使用 OFFSET/FETCH;舊版本以 ROW_NUMBER 為標準解法,配合適當索引 (含排序欄位) 可獲得最佳效能。