JOBCN 簡歷庫 chineseresume 表超 100 萬行,需按 ReferenceID 或 updatedate 分頁查詢(每頁 20 行),第 3 頁(FID 41~60)。
DECLARE @local_variable TABLE (FID INT IDENTITY(1,1), ReferenceID VARCHAR(20));
INSERT INTO @local_variable (ReferenceID)
SELECT TOP 100000 ReferenceID FROM chineseresume ORDER BY ReferenceID; -- 聚簇索引順序掃描
SELECT * FROM @local_variable WHERE Fid > 40 AND Fid <= 60;
特點:
TOP 100000 順序掃描聚簇索引,無排序開銷。DECLARE @local_variable TABLE (FID INT IDENTITY(1,1), ReferenceID VARCHAR(20));
INSERT INTO @local_variable (ReferenceID)
SELECT TOP 100000 ReferenceID FROM chineseresume ORDER BY updatedate; -- 需排序,溢出 tempdb
SELECT * FROM @local_variable WHERE Fid > 40 AND Fid <= 60;
差異:ORDER BY updatedate 觸發排序操作,可能溢出到 tempdb,產生 PAGELATCH 爭用,耗時顯著增加。
#temp(大數據量/需索引/並行)CREATE TABLE #temp (FID INT IDENTITY(1,1), ReferenceID VARCHAR(20));
INSERT INTO #temp (ReferenceID)
SELECT TOP 100000 ReferenceID FROM chineseresume ORDER BY updatedate;
CREATE INDEX idx_fid ON #temp(Fid); -- 可建索引、有統計信息、支持並行
SELECT * FROM #temp WHERE Fid > 40 AND Fid <= 60;
DROP TABLE #temp;
優勢:支持索引、統計信息、並行執行、大數據量穩定;劣勢:系統表鎖、日誌開銷、需顯式 DROP。
| 方案 | 排序列 | 耗時 | tempdb 爭用 | 推薦場景 |
|---|---|---|---|---|
| A(表變量) | 聚簇索引鍵 | 最快(~200ms) | 極低 | 小結果集(<10萬)、單會話、無後續複雜關聯 |
| B(表變量) | 非索引列 | 中等(~1.2s) | 中(排序溢出) | 臨時嘗試,非核心路徑 |
| C(臨時表) | 非索引列 | 較快(~800ms) | 可控(可建索引) | 大結果集、需後續 JOIN、並行、統計信息依賴 |
棄用臨時表分頁,直接使用 OFFSET ... FETCH:
DECLARE @PageSize INT = 20, @PageNum INT = 3; -- 第 3 頁
SELECT ReferenceID
FROM chineseresume
ORDER BY updatedate DESC
OFFSET (@PageNum - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;
優勢:無臨時對象、優化器可推遲排序、支持索引下推、計劃緩存復用。
-- 先只取主鍵分頁,再回表取明細(避免排序時攜帶大列)
SELECT t.*
FROM (
SELECT id FROM chineseresume ORDER BY updatedate DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY
) p
JOIN chineseresume t ON t.id = p.id;
IF 結果集 < 5萬 行 AND 無後續複雜 JOIN
AND 排序列有索引
→ 表變量 @table (方案 A)
ELSE IF 需索引/統計/並行 OR 結果集 > 5萬
→ 臨時表 #temp (方案 C)
ELSE
→ 直接 OFFSET/FETCH (現代標準)
END IF
分頁優化的本質是減少排序開銷、避免大列參與排序、利用索引順序。表變量/臨時表是 SQL Server 2005 時代的產物,現代版本請優先擁抱
OFFSET/FETCH+ 延遲關聯 模式。