SQL Server 隨機抽樣查詢：NEWID() 排序原理、性能陷阱與大表優化方案

標準寫法：ORDER BY NEWID()

-- 返回隨機 10 行
SELECT TOP 10 * FROM dbo.Orders ORDER BY NEWID();

-- 隨機排序全表
SELECT * FROM dbo.Orders ORDER BY NEWID();

原理：計算列 + 全表掃描 + 排序

1. 掃描全表 (Clustered Index Scan / Table Scan)。
2. 為每行計算 NEWID() (生成唯一標識符)，作為計算列。
3. 對計算列進行全局排序 (Sort 操作符，可能溢出到 TempDB)。
4. 返回 Top N 或全量結果。

複雜度：O(N log N) 排序 + O(N) NEWID() 計算，隨表增長性能急劇下降。

性能基準 (Orders 表 1000 萬行)

大表優化方案對比

方案 1：TABLESAMPLE (系統採樣，極快但非精確隨機)

-- 系統採樣：按頁採樣，同頁行要么全選要么全不選
SELECT * FROM dbo.Orders TABLESAMPLE SYSTEM (10 ROWS);  -- 近似 10 行
SELECT * FROM dbo.Orders TABLESAMPLE BERNOULLI (0.01); -- 每行獨立 0.01% 概率

• ✅ 秒級響應，不掃描全表，不排序。
• ❌ SYSTEM 非真隨機 (頁級相關)；BERNOULLI 概率性，行數不定。
• ❌ 不支持 WHERE 過濾後採樣 (需子查詢/視圖)。

方案 2：主鍵範圍隨機跳躍 (精確隨機、毫秒級)

-- 前提：主鍵 ID 連續或近似連續 (IDENTITY)
DECLARE @MinId BIGINT, @MaxId BIGINT, @RandomId BIGINT;
SELECT @MinId = MIN(Id), @MaxId = MAX(Id) FROM dbo.Orders;
SET @RandomId = @MinId + CAST(RAND() * (@MaxId - @MinId) AS BIGINT);

SELECT TOP 10 * 
FROM dbo.Orders 
WHERE Id >= @RandomId
ORDER BY Id;
-- 若不足 10 行，可 UNION ALL 再從頭取

• ✅ 索引 Seek 定位，極快；結果近似隨機 (取決於 ID 分佈均勻度)。
• ❌ ID 有大量間隙/刪除時分佈偏斜；需處理邊界環繞。

方案 3：預計算隨機值列 + 索引 (精確隨機、寫入開銷換讀取快)

ALTER TABLE dbo.Orders ADD RandVal BINARY(16) DEFAULT NEWID(); -- 或 CHECKSUM(NEWID())
CREATE INDEX IX_Orders_RandVal ON dbo.Orders (RandVal);

-- 查詢
SELECT TOP 10 * FROM dbo.Orders ORDER BY RandVal; -- 索引順序掃描，無排序！
-- 定期更新 RandVal 刷新隨機性
UPDATE dbo.Orders SET RandVal = NEWID(); -- 大表分批更新

• ✅ 查詢變為 Index Scan + Top，無 Sort，性能穩定 O(log N + K)。
• ✅ 支持 WHERE 過濾後隨機：WHERE Status=1 ORDER BY RandVal。
• ❌ 存儲 16 字節/行 + 索引開銷；寫入/更新需維護隨機值。

方案 4：應用層隨機 ID 列表 (極大表、極高併發)

-- 1. 離線任務：每日生成隨機 ID 列表存入 Redis/配置表
-- SELECT Id FROM dbo.Orders WHERE CreateDate >= '2024-01-01' ORDER BY NEWID() LIMIT 10000;
-- 2. 應用層從列表隨機取 N 個 ID → 批量查詢
-- SELECT * FROM dbo.Orders WHERE Id IN (id1, id2, ...);

• ✅ 數據庫零隨機計算負載，查詢極快 (Index Seek 批量)。
• ✅ 完全可控隨機分佈、業務過濾邏輯。
• ❌ 需額外維護組件、數據有延遲 (T+1)。

選型決策樹

表大小 ≤ 10 萬行          → 直接 ORDER BY NEWID() 簡單可維護
表 10 萬 - 100 萬行       → TABLESAMPLE BERNOULLI / 主鍵隨機跳躍
表 > 100 萬行、需精確隨機  → 預計算 RandVal 列 + 索引 / 離線 ID 列表
表 > 100 萬行、允許近似   → TABLESAMPLE SYSTEM (最快)

避坑提醒

• ❌ RAND() 查詢中只計算一次，全表同一值，**不可用於 ORDER BY RAND()**。
• ❌ NEWID() 非確定性函數，導致計劃無法復用，高併發下編譯鎖爭用。
• ❌ 視圖/CTV 封裝 ORDER BY NEWID() 不改變本質，仍是全掃描排序。
• ✅ 統計信息更新後優化器可能選擇不同計劃，大表隨機查詢**必須測試實際執行計劃**。

隨機抽樣看似簡單，實則是「統計學隨機性」與「工程學性能」的博弈。**大表嚴禁直接 ORDER BY NEWID()**，請根據業務對隨機性精度要求、表規模、寫入頻率，選擇 TABLESAMPLE、主鍵跳躍、預計算列或離線 ID 列表方案，實現毫秒級隨機查詢體驗。