搞隨機查詢這種需求估計大多數(shù)SQL Server開發(fā)都寫過。一句ORDER BY NEWID()下去看似輕松搞定真正上線后遇到大表慢、抽樣不準、腳本重復維護這些問題才是最磨人的。最近項目里有個抽獎節(jié)奏的需求要從用戶表里隨機撈一條記錄做活動推薦順帶把這塊邏輯用自定義函數(shù)重新封裝了一遍。這篇文章就把隨機查詢的幾種思路、各自原理和性能差異、函數(shù)封裝的邊界與實操以及我實際踩過的坑一并整理出來。1. 隨機查詢一條記錄別只盯著ORDER BY NEWID()先說結論隨機查詢沒有銀彈不同數(shù)據(jù)量、不同業(yè)務場景選型差別很大。我見過不少小伙伴寫隨機查詢就是固定一套ORDER BY NEWID()換到百萬級表就卡得懷疑人生。原因很簡單NEWID()會對表的每一行生成一個 GUID然后SQL Server得對整個結果集做一次排序才能取第一行。表越大排序開銷越離譜。1.1 三種主流寫法和原理差異寫法一ORDER BY NEWID()SELECT TOP 1 * FROM dbo.Users ORDER BY NEWID();這是最直觀的寫法。NEWID()是SQL Server內(nèi)置的GUID生成函數(shù)每一行調(diào)用一次生成一個全局唯一且隨機性很強的值。排序后取第一條每條記錄被抽中的概率基本均等隨機性非常好。缺點就是全表掃描 全量排序表一旦達到幾十萬上百萬行響應時間會肉眼可見地惡化。寫法二TABLESAMPLESELECT TOP 1 * FROM dbo.Users TABLESAMPLE (1000 ROWS) ORDER BY NEWID();TABLESAMPLE是SQL Server 2005之后引入的物理抽樣機制它直接從數(shù)據(jù)頁層面抽取數(shù)據(jù)不需要遍歷每一行。這種方式在大表上的性能優(yōu)勢非常明顯耗時可能從幾百毫秒直接降到幾十毫秒甚至更低。但它有兩個特點一是抽樣結果基于數(shù)據(jù)頁不是基于數(shù)據(jù)行如果數(shù)據(jù)在物理存儲上分布不均衡抽樣偏差很嚴重二是小表可能抽不出任何數(shù)據(jù)返回空結果集。所以它適合大表、對隨機性要求不是極端嚴格的場景。寫法三行號加隨機偏移SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum, * FROM dbo.Users ) AS t WHERE t.RowNum CAST(RAND() * (SELECT COUNT(*) FROM dbo.Users) AS INT) 1;先給每一行編一個行號再隨機算一個偏移量取對應的那一行。這種方式避免了全量排序但要計算總行數(shù)也要全表掃描一次生成行號。它在數(shù)據(jù)量中等時表現(xiàn)還行但遇到高頻并發(fā)調(diào)用COUNT(*)和子查詢重復執(zhí)行性能同樣上不去。1.2 性能實測對比我拿一張128萬行的訂單明細表做了個簡單對比統(tǒng)計平均耗時環(huán)境是SQL Server 2019標準版8核16G的測試機寫法平均耗時隨機性適用場景ORDER BY NEWID()約450ms極好小表、數(shù)據(jù)量在幾萬行以內(nèi)TABLESAMPLE TOP 1約15ms中等大表抽樣、對隨機性要求不高ROW_NUMBER 隨機偏移約380ms好中等表、避免排序開銷這個數(shù)據(jù)僅供參考不同硬件、不同表結構差距很大。但趨勢很明確ORDER BY NEWID()的性能瓶頸不在取數(shù)而在排序。TABLESAMPLE 走的是頁級抽樣所以快得明顯但要接受它的抽樣偏差。1.3 幾個容易翻車的隨機寫法有一種寫法看似是隨機實際是有問題的SELECT TOP 1 * FROM dbo.Users WHERE Id CAST(RAND() * 100000 AS INT);這個寫法有兩個致命傷。第一RAND()在WHERE子句中會被逐行評估同一句查詢里不同行拿到的隨機值可能都不一樣邏輯上根本不可控。第二如果表的主鍵不是連續(xù)的自增序列中間有刪除留下的空洞隨機算出來的Id很可能落在空洞上導致查詢返回空結果。我曾經(jīng)在線上見過類似的代碼抽獎活動上線第一天就出現(xiàn)中獎用戶為空的投訴查了半天才發(fā)現(xiàn)是主鍵不連續(xù)惹的禍。另外還有一種常見做法先把全表數(shù)據(jù)拉到應用程序里再用C#、Java的Random類隨機取一條。這種方案在小數(shù)據(jù)量下沒問題但表一大人就傻了幾百萬行全部拉到內(nèi)存不僅慢還白白占用大量網(wǎng)絡和內(nèi)存資源。正確方向是盡量讓SQL Server在服務端完成隨機取數(shù)的邏輯。2. 為什么要封裝從復制粘貼到函數(shù)復用隨機查詢這個需求最大的問題不是怎么寫而是寫完之后怎么復用。我見過很多項目的SQL腳本都是散落在各處的這個頁面需要隨機推薦就粘貼一份那個后臺需要隨機抽檢又復制一份。表面上看省了事實際上隱患很大。2.1 重復腳本的三個隱患第一口徑不一致。同一套業(yè)務規(guī)則A處腳本寫了WHERE Status 1B處漏寫了這個條件抽出來的數(shù)據(jù)就可能包含已注銷用戶線上效果和預期完全對不上。第二維護成本高。一旦表結構變更比如字段改名你得跑到所有腳本里去替換漏一個就是線上事故。第三SQL注入風險。有些腳本里拼接了用戶輸入的篩選條件沒有用參數(shù)化查詢被惡意調(diào)用時容易出事。所以說封裝不只是代碼好看的問題是實打?qū)嵉纳a(chǎn)安全需求。把隨機查詢的邏輯收斂到一個函數(shù)或存儲過程里所有調(diào)用方只面向這個統(tǒng)一入口改邏輯只改一處安全性、可維護性都提升一個檔次。2.2 SQL Server自定義函數(shù)的分類和限制SQL Server里自定義函數(shù)UDF主要分三類標量函數(shù)、內(nèi)聯(lián)表值函數(shù)、多語句表值函數(shù)。標量函數(shù)返回單個值比如SELECT dbo.GetRandomNumber()。適合封裝一些簡單的計算規(guī)則但它在SQL語句里逐行調(diào)用時性能不好尤其在大數(shù)據(jù)集上容易被反復調(diào)用拖垮查詢。內(nèi)聯(lián)表值函數(shù)函數(shù)體只包含一條SELECT返回一個表。它最大的優(yōu)勢是能被查詢優(yōu)化器展開像視圖一樣參與到外層查詢的計劃生成里性能表現(xiàn)好是我日常封裝首選。多語句表值函數(shù)函數(shù)體里可以有多個語句先聲明表變量再往里插入數(shù)據(jù)最后返回。寫法靈活但優(yōu)化器對它內(nèi)部的行數(shù)預估常常不準性能隱患多能不用盡量不用。自定義函數(shù)還有幾個硬性限制必須提前知道函數(shù)內(nèi)不能執(zhí)行動態(tài)SQLsp_executesql不能在UDF里直接用。函數(shù)不能修改數(shù)據(jù)庫狀態(tài)不能有INSERT、UPDATE、DELETE這類副作用。不確定函數(shù)的使用要當心。GETDATE()、RAND()這些在UDF里有嚴格限制擅自使用會導致創(chuàng)建失敗或引起性能問題。NEWID()相對特殊在內(nèi)聯(lián)表值函數(shù)里可以使用這也是隨機查詢可以封裝成函數(shù)的基礎。2.3 封裝設計先分清固定表還是多表通用這是封裝時最容易犯迷糊的地方。如果隨機查詢的業(yè)務對象是固定的比如就是用戶表、訂單表這種明確到具體表的場景內(nèi)聯(lián)表值函數(shù)足夠。但如果需求是傳入任意表名、動態(tài)拼篩選條件函數(shù)就做不到了因為函數(shù)內(nèi)不能用動態(tài)SQL。這種多表通用的需求正確的落地方案是存儲過程。我的建議很簡單業(yè)務表固定用函數(shù)表名和條件都不固定用存儲過程。兩者不沖突也各有清晰的適用邊界。3. 實操把隨機查詢封裝成函數(shù)并調(diào)用下面進入重點環(huán)節(jié)我把這次項目的封裝過程完整走一遍。目標是兩個固定用戶表隨機取一條記錄以及按狀態(tài)條件隨機取一條記錄。3.1 固定業(yè)務表的封裝內(nèi)聯(lián)表值函數(shù)針對用戶表隨機取一條這個最常見的場景我創(chuàng)建了一個內(nèi)聯(lián)表值函數(shù)CREATE FUNCTION dbo.GetRandomUser() RETURNS TABLE AS RETURN ( SELECT TOP 1 * FROM dbo.Users WITH (TABLOCK) ORDER BY NEWID() ); GO調(diào)用方式非常簡潔SELECT * FROM dbo.GetRandomUser();注意函數(shù)體里我加了一個WITH (TABLOCK)提示。這個提示的作用是讓整張表以表級鎖參與查詢避免函數(shù)內(nèi)部對每一行單獨加鎖導致額外的鎖開銷同時可以讓優(yōu)化器更早決定表掃描路徑。在隨機查詢這種必須全表讀的場景下TABLOCK通常是有利的。當然表級鎖意味著并發(fā)寫入會被阻塞如果業(yè)務不允許去掉這個提示即可。內(nèi)聯(lián)表值函數(shù)的好處是它本質(zhì)上是參數(shù)化的視圖查詢優(yōu)化器會把函數(shù)內(nèi)的SELECT和外層查詢合并成一條整體語句來優(yōu)化不存在額外的函數(shù)調(diào)用開銷。這也是我堅持選內(nèi)聯(lián)表值函數(shù)而非標量函數(shù)的原因。標量函數(shù)如果寫成SELECT dbo.GetRandomUserId()每次調(diào)用都得單獨執(zhí)行函數(shù)體性能反而不如直接寫在語句里。3.2 帶篩選條件的封裝業(yè)務不會停留在無腦隨機這個階段。很快就有個需求只抽已激活的用戶不要已注銷的。這時候就給函數(shù)加一個狀態(tài)參數(shù)CREATE FUNCTION dbo.GetRandomUserByStatus(Status INT) RETURNS TABLE AS RETURN ( SELECT TOP 1 * FROM dbo.Users WITH (TABLOCK) WHERE Status Status ORDER BY NEWID() ); GO調(diào)用SELECT * FROM dbo.GetRandomUserByStatus(1);如果篩選條件不止一個比如既要狀態(tài)又限定會員等級方法是一樣的加參數(shù)就行。但這里要有一個意識每增加一個參數(shù)函數(shù)內(nèi)部就多一層條件分支條件組合一旦多了函數(shù)簽名會變得很難看。我見過有人封裝了七八個參數(shù)的隨機查詢函數(shù)調(diào)用方根本搞不清楚每個參數(shù)該傳什么最后反而棄用。面對這種復雜組合條件我更推薦的做法是把函數(shù)拆成幾個語義清晰的子函數(shù)或者干脆用存儲過程配合CASE語句拼過濾條件。拆函數(shù)的好處是每個函數(shù)職責單一調(diào)用方只看函數(shù)名和參數(shù)就能理解邏輯。存儲過程則更靈活適合組合條件非常多的場景。3.3 多表通用封裝為什么函數(shù)做不到存儲過程可以如果需求變成任意表隨機取一條記錄比如不定哪天需要從日志表抽一條過幾天又要從商品表抽一條這時候動態(tài)SQL就繞不開了。前面提過函數(shù)內(nèi)不能跑動態(tài)SQL所以這個場景必須用存儲過程。CREATE PROCEDURE dbo.GetRandomRow TableName NVARCHAR(128), FilterClause NVARCHAR(MAX) NULL AS BEGIN SET NOCOUNT ON; DECLARE Sql NVARCHAR(MAX); SET Sql NSELECT TOP 1 * FROM QUOTENAME(TableName) CASE WHEN FilterClause IS NOT NULL AND FilterClause THEN N WHERE FilterClause ELSE N END N ORDER BY NEWID();; EXEC sp_executesql Sql; END GO調(diào)用示例EXEC dbo.GetRandomRow TableName Ndbo.Users; EXEC dbo.GetRandomRow TableName Ndbo.Orders, FilterClause NAmount 100;這段存儲過程有兩點務必注意。第一TableName必須用QUOTENAME包裹防止SQL注入絕不能信任外部直接傳入的表名。第二FilterClause是原生拼接條件風險等級很高生產(chǎn)環(huán)境使用必須經(jīng)過嚴格白名單驗證最好只允許傳預先定義好的條件名稱而不是直接接受任意SQL片段。我在項目里通常的做法是預定義幾個公開的過濾條件常量存儲過程內(nèi)部根據(jù)常量映射到安全條件從根上杜絕注入風險。另外這個存儲過程有個潛在的性能問題就是每次調(diào)用都要編譯一次動態(tài)SQL。如果調(diào)用頻率很高可以考慮讓TableName通過參數(shù)化方式來換取計劃重用但表名參數(shù)化后SQL Server不會自動重用計劃這個問題比較復雜。一般這種通用隨機查詢本身頻率不高每次編譯一次完全可以接受。4. 常見問題與排查技巧實錄這個項目做完之后我把過程中遇到的問題和排查思路整理成了一個小冊子很多都是平時文檔里不會明說但實際必然碰到的。4.1 大表隨機查詢慢怎么優(yōu)化前面實測里ORDER BY NEWID()在128萬行表上跑了450多毫秒這個結果換到業(yè)務高峰期可能被放大好幾倍。如果確實要在比較大的表上隨機取一條且不能接受這么高的延遲可以試試在兩階段查詢的思路。對于有自增主鍵、且刪除不頻繁的表可以這樣優(yōu)化DECLARE MinId INT, MaxId INT, TargetId INT; SELECT MinId MIN(Id), MaxId MAX(Id) FROM dbo.Users; SET TargetId MinId CAST(RAND() * (MaxId - MinId) AS INT); SELECT TOP 1 * FROM dbo.Users WHERE Id TargetId ORDER BY Id;這個方法的本質(zhì)是先用ID范圍算出隨機目標再找一個大于等于目標ID的第一條記錄。由于主鍵通常有聚集索引ORDER BY Id可以直接走索引避免全表排序。缺點是Id空洞多時隨機性會向空洞后的記錄傾斜如果表頻繁刪除空洞可能讓某些區(qū)間的記錄永遠抽不到。另一個激進方案是給表增加一個隨機數(shù)列插入數(shù)據(jù)時填充NEWID()并建索引查詢時隨機生成一個GUID找大于該GUID的那一行。這個方案隨機性好、性能也高但GUID列索引頁碎片多寫入性能受影響屬于典型的以寫換讀。4.2 NEWID()在函數(shù)和視圖里的行為NEWID()在函數(shù)里的行為很多人搞不清楚。它和GETDATE()這類不確定函數(shù)不一樣GETDATE()在UDF里沒法直接用但NEWID()在內(nèi)聯(lián)表值函數(shù)里是可以用的這是我多次測試確認過的。原因在于內(nèi)聯(lián)表值函數(shù)會被優(yōu)化器展開函數(shù)體里的表達式最終會融入外層查詢計劃相當于一條普通查詢所以NEWID()的限制在這里被繞過去了。視圖里用NEWID()也是同理只要最終執(zhí)行的查詢里包含NEWID()每一行都會重新生成一個GUID。這一點既是隨機性的來源也是性能開銷的來源。如果你發(fā)現(xiàn)視圖里加了個NEWID()列外層查詢突然變慢別懷疑就是它把視圖變成了一張逐行生成GUID再排序的表。4.3 權限和查詢提示的影響封裝完函數(shù)一定會遇到權限問題。默認情況下普通用戶要能執(zhí)行SELECT * FROM dbo.GetRandomUser()需要對該函數(shù)有執(zhí)行權限。單獨授權可以在架構層面統(tǒng)一處理比如給應用賬號授予整個dbo架構的EXECUTE權限避免每個函數(shù)都要單獨授權。具體操作是GRANT EXECUTE ON SCHEMA :: dbo TO app_user;另外要注意函數(shù)和存儲過程的執(zhí)行上下文。如果函數(shù)內(nèi)部訪問的表和函數(shù)本身不在同一個架構下或者有跨數(shù)據(jù)庫訪問要提前確認賬號是否有相應權限。我這里沒有特別處理因為項目里所有對象都在同一個dbo架構下權限關系比較簡單。如果你們庫比較復雜記得檢查EXECUTE AS的配置。4.4 問題排查速查表直接整理成一個表方便日常參考?，F(xiàn)象原因解決辦法隨機查詢返回空結果主鍵不連續(xù)隨機Id落在空洞上改用 NEWID() 排序或基于最大值最小值做范圍隨機大表隨機查詢極慢NEWID() 全量排序開銷大改用 TABLESAMPLE或基于主鍵范圍取數(shù)TABLESAMPLE 返回0行表太小抽樣頁數(shù)為0加重復試邏輯或直接改用 NEWID() 方案函數(shù)創(chuàng)建時報錯RAND() 不允許UDF中不允許使用無參 RAND()換 NEWID()或顯式傳入種子標量函數(shù)在查詢中被反復調(diào)用性能差標量函數(shù)逐行執(zhí)行改成內(nèi)聯(lián)表值函數(shù)或直接在查詢里展開邏輯動態(tài)SQL函數(shù)創(chuàng)建失敗UDF內(nèi)不能使用 sp_executesql改用存儲過程實現(xiàn)多表動態(tài)查詢結果總偏向某一條記錄主鍵空洞導致范圍隨機不均勻改用 NEWID() 或隨機數(shù)列索引方案排查時有一個原則先看執(zhí)行計劃里的排序運算符再看表掃描的預估行數(shù)基本能定位80%以上的隨機查詢問題。我之前有一次排查線上慢查詢打開執(zhí)行計劃一看排序操作占了整個查詢代價的91%問題一目了然。5. 封裝后的調(diào)用體驗和擴展方向這次封裝完成之后業(yè)務方調(diào)用變得異常簡單。前端要做抽獎后端只需要執(zhí)行EXEC dbo.GetRandomRow TableName Ndbo.Users, FilterClause NStatus 1一條語句拿返回的結果就行。后續(xù)再遇到從訂單表抽明細做抽樣審計或者從商品表抽一款做每日推薦完全不用寫新邏輯傳參調(diào)用同一個存儲過程就解決了。如果項目里用的是Entity Framework或者其他ORM函數(shù)和存儲過程同樣可以通過映射方式調(diào)用。比如EF Core里用FromSqlInterpolated直接調(diào)用表值函數(shù)返回結果就能映射成實體對象開發(fā)體驗很順。還有一個小技巧可以擴展如果希望隨機結果每次帶上一個隨機的序號方便前端做展示排序可以在函數(shù)返回結果里加一列SELECT TOP 1 *, NEWID() AS RandomSort FROM dbo.Users WITH (TABLOCK) ORDER BY NEWID();這樣調(diào)用方拿到的是完整記錄外加一個隨機列用于后續(xù)打亂顯示順序很方便。有些人擔心這樣多調(diào)用了一次NEWID()有沒有額外開銷。實際上排序用的NEWID()已經(jīng)對每行生成過了多選一個列并不會顯著增加成本除非結果集特別大。最后再分享一點我個人的實操體會隨機查詢雖小但翻車概率一點都不低。不要因為看到一句ORDER BY NEWID()很簡單就輕視它到了大表、并發(fā)、動態(tài)條件的場景各種邊界問題都會冒出來。封裝的意義不只是減少重復代碼更是把隨機邏輯這個容易出錯的東西收斂在一個統(tǒng)一入口里做到可控、可測、可維護。如果你在現(xiàn)有項目里看到那種散落各處的隨機查詢腳本強烈建議按這篇文章的思路收斂一下前面的投入會在后面的線上穩(wěn)定上賺回來。