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