说到SQL里的随机函数很多人第一反应就是ORDER BY RAND()然后数据库就慢得像老牛拉车。做后端开发的这些年我处理过不少和随机抽取、随机排序相关的需求比如抽奖活动、随机题库、AB测试分组、数据脱敏抽样背后都离不开这个不起眼的函数。它看起来简单用起来却藏着不少细节和坑。这篇笔记就当是给自己做的一个系统整理随机函数在不同数据库里的写法、随机抽取的正确姿势、性能优化思路、常见问题排查以及怎么用它来生成测试数据。内容不挑数据库MySQL、SQL Server、PostgreSQL、Oracle 我都会提到新手可以照着抄老手也可以看看有没有踩过同样的坑。1. 随机函数的核心概念与各数据库实现差异1.1 随机函数到底是什么业务上为什么要用它SQL 的随机函数本质上就是一个返回伪随机数的内置函数。所谓伪随机指的是它由一个初始种子Seed作为起点通过一定的数学算法计算出一串看起来杂乱无章的数字。只要种子相同、算法相同生成的序列就完全一致。这个特点既带来了麻烦也可以被利用来复现实验结果。业务上我们用到随机函数的地方非常多。最常见的是随机抽取比如线上活动要随机抽一批用户发优惠券、考试系统要随机抽题、视频平台要做随机推荐。其次是随机排序比如把首页内容“洗牌”一下让不同用户看到不同顺序。再就是测试数据填充没有随机函数的话你想造一批模拟订单、模拟用户信息那得靠手写一大堆不重样的数据想想都头大。我印象很深的一次项目经历是给一个营销系统做“转盘抽奖”奖品池有几十个档位概率还不一样。当时如果直接ORDER BY RAND()然后取第一条数据量一大就撑不住最后不得不换方案。这件事也让我明白随机函数本身只是工具怎么把它用好、用对才是真正的门道。1.2 主流数据库的随机函数对照表不同数据库的随机函数名称、返回类型、用法差别很大。这里我整理了一份常用的对照方便大家查表数据库随机函数返回类型示例备注MySQLRAND()0到1之间的浮点数SELECT RAND();可传种子RAND(seed)SQL ServerRAND()0到1之间的浮点数SELECT RAND();可传种子每行相同SQL ServerNEWID()GUIDSELECT NEWID();常用于随机排序PostgreSQLRANDOM()0到1之间的浮点数SELECT RANDOM();无种子参数PostgreSQLSETSEED()voidSELECT SETSEED(0.5);设置随机种子OracleDBMS_RANDOM.VALUENUMBERSELECT DBMS_RANDOM.VALUE FROM DUAL;需有执行权限OracleDBMS_RANDOM.STRINGVARCHAR2SELECT DBMS_RANDOM.STRING(A, 10) FROM DUAL;生成随机字符串SQLiteRANDOM()64位整数SELECT RANDOM();范围很大这里有个特别容易踩的坑在 SQL Server 里RAND()在同一查询中只计算一次每一行返回的是同一个值而NEWID()是每行生成一个 GUID所以你要是想对每行产生不同的随机值用NEWID()更靠谱。PostgreSQL 的RANDOM()则是每行都会重新计算一次行为又不一样。这些差异直接决定了你在写 SQL 时能不能多放几个随机字段。1.3 随机数是怎么生成的了解原理之后好排错我承认刚开始写 SQL 的时候根本不关心随机函数内部原理遇到问题只会换函数。但后来因为一个“随机结果不随机”的 bug 排查了很久才意识到原理很重要。大多数数据库的随机函数用的是线性同余生成器LCG或者梅森旋转算法。拿最简单的 LCG 来说递推公式是X(n1) (a * X(n) c) mod m只要初始值 X(0)也就是种子确定了整条序列就确定了。这也是为什么 SQL Server 的RAND(1)每次返回的都是同一个数。MySQL 的RAND(N)同理传了种子之后结果就可复现了。理解了这一点你就能解释很多怪现象为什么同一句 SQL 跑两次结果不一样因为你没给种子数据库用当前时间戳或系统状态当种子。为什么加了种子之后每次跑都一样因为序列是固定的。为什么有时候随机数看起来“分布不均匀”因为底层算法的统计特性以及你可能只取了小数的一部分比如CAST(RAND() * 100 AS INT)在边缘值上偶尔会出现偏斜。搞懂了原理排查问题的时候就能往正确的方向想而不是瞎猜。2. 用随机函数做随机排序与随机抽取2.1 随机排序的几种常见写法随机排序的需求很常见做法也最直观在ORDER BY后面放一个随机函数。MySQL 写作SELECT user_id, nickname FROM users ORDER BY RAND();PostgreSQL 写作SELECT user_id, nickname FROM users ORDER BY RANDOM();SQL Server 的经典写法是SELECT TOP 10 user_id, nickname FROM users ORDER BY NEWID();Oracle 可以这么写SELECT * FROM ( SELECT user_id, nickname FROM users ORDER BY DBMS_RANDOM.VALUE ) WHERE ROWNUM 10;但请注意这三种数据库的“随机函数作为排序键”的语义不完全一样。MySQL 和 PostgreSQL 是每一行都计算一次随机值然后按计算结果排序整体确实能打乱。SQL Server 的RAND()就不行同一语句只返回一个恒定值所有行的排序键都一样等于没排序必须换用NEWID()。这一点是最常见的误解之一。2.2 随机抽取 N 条记录的完整 SQL随机抽取通常有两种思路先随机排序再取前 N 条或者先算出一个随机 ID 范围再直接取。第二种我放到性能优化部分详细说这里先说第一种。以业务中很常见的“考试系统随机抽 10 道题”为例。假设题目表questions结构大致是CREATE TABLE questions ( id INT PRIMARY KEY, body TEXT, category_id INT );MySQL 实现随机抽题SELECT id, body FROM questions WHERE category_id 1 ORDER BY RAND() LIMIT 10;SQL Server 实现SELECT TOP 10 id, body FROM questions WHERE category_id 1 ORDER BY NEWID();PostgreSQL 实现SELECT id, body FROM questions WHERE category_id 1 ORDER BY RANDOM() LIMIT 10;Oracle 实现SELECT * FROM ( SELECT id, body FROM questions WHERE category_id 1 ORDER BY DBMS_RANDOM.VALUE ) FETCH FIRST 10 ROWS ONLY;看起来都不复杂但要注意这样写在小数据量的表上完全没问题比如几千条、几万条。可如果表数据量到了百万级ORDER BY RAND()就要把整张表的数据载入内存做排序性能会非常差。具体怎么解决下面专门讲。2.3 带权重的随机抽取怎么实现很多时候我们需要“概率不均等”的随机比如抽奖活动中一等奖概率 1%二等奖 10%三等奖 89%。这时候直接ORDER BY RAND()就没办法了因为每个奖品的权重不同。我的常用做法是先给每个奖品分配一个权重区间然后生成随机数落在哪个区间。具体 SQL 逻辑可以这样拆从奖品表中按权重累加出一个end_range字段。随机生成一个 0 到总权重之间的数。找到第一条end_range 随机数的记录。MySQL 里可以这样写假设权重表prizesSELECT prize_id, prize_name FROM ( SELECT prize_id, prize_name, SUM(weight) OVER (ORDER BY prize_id) AS end_range, SUM(weight) OVER () AS total_weight FROM prizes ) t WHERE end_range RAND() * total_weight ORDER BY end_range LIMIT 1;这种写法在数据量不大的场景下很好使。如果奖品数量特别多你也可以在业务代码里做二分查找把区间表加载到内存然后用一个随机数去定位。这个方法我实际项目里用过稳定而且方便做概率验证。3. 随机函数的性能分析与优化3.1 为什么ORDER BY RAND()在大表上慢得离谱很多刚接触随机查询的同学不理解明明只有几万行为什么查询要好几秒原因很简单ORDER BY RAND()的工作过程分为三步。第一步数据库必须读取所有满足WHERE条件的行。第二步对每一行调用随机函数生成一个随机数作为排序键。第三步按照这些随机数进行全量排序。排序的时间复杂度大约是 O(n log n)而且必须把中间结果放到临时文件里。数据量越大临时文件越大磁盘 I/O 越高当然就越慢。我拿一个具体的表做过测试一张单表 500 万行的用户表在普通服务器上执行SELECT user_id FROM users ORDER BY RAND() LIMIT 1;冷启动跑了接近 8 秒。而如果只是查一条主键再用主键检索连 0.01 秒都用不到。8 秒和 0.01 秒的区别在线上接口里就是超时和不超时的区别。3.2 基于自增 ID 区间的优化方案如果你的表有自增主键而且足够连续那么随机抽一条的最优解法是生成一个随机 ID再按主键去取。MySQL 写法SELECT user_id, nickname FROM users WHERE id ( SELECT ROUND(RAND() * (SELECT MAX(id) FROM users)) ) ORDER BY id LIMIT 1;这个方案的前提是主键不能有大量空洞。比如你删了几万条数据MAX(id)可能很大但中间很多 id 不存在这样随机出来的 id 大概率是空的最终结果就会集中在表的尾部或者查不到记录。这种情况下可以换用“随机偏移量”方式SELECT user_id, nickname FROM users LIMIT 1 OFFSET ( SELECT FLOOR(RAND() * (SELECT COUNT(*) FROM users)) );OFFSET 方式在 MySQL 里对于大偏移量依旧偏慢因为它要扫描掉前面的所有行。但如果只是抽取少量记录而且表有索引也比ORDER BY RAND()好很多。还有一种更稳的做法先随机查出一条主键 ID再用主键查全行。配合两个查询分开执行性能非常理想。参考方案得到表的MIN(id)和MAX(id)。在应用层生成一个随机整数target_id。执行SELECT ... FROM users WHERE id target_id ORDER BY id LIMIT 1;如果结果为空再取id target_id的最接近一条。这个方案对主键空洞不敏感也能保持较好的随机性。项目里我用的最多的就是这一套。3.3 大数据量下的其他优化套路不能只靠一种方案打天下数据量大到一定程度后我还会考虑以下几种优化。第一种是“预先生成随机序列表”。给业务表维护一个random_key字段提前用批处理任务给每行写入一个随机数。查询时直接SELECT user_id, nickname FROM users ORDER BY random_key LIMIT 20;random_key上建好索引随机排序就变成了一次普通的索引扫描速度非常快。缺点是数据新增时要有机制补充随机数而且随机性在插入后一段时间内会“固化”但大多数应用场景完全够用。第二种是“分批抽取”。需要随机展示 100 条可以按某个业务键分桶比如按user_id % 10把数据拆成 10 个桶然后每桶随机取 10 条最后合并乱序。这样虽然每桶的数据量小了但整体集合的随机性依然能保证。第三种是“用 JOIN 随机数集合”。先生成一张包含若干随机数的临时表再和目标表 JOINSELECT t.*, r.seq FROM temp_random r JOIN users t ON t.user_id r.user_id;前提是提前把候选 id 集合抽出来。这种方案适合“从几十万 userId 中随机抽 5000 个”的场景比ORDER BY RAND()快很多。3.4 不同数据库的优化侧重点MySQL 和 PostgreSQL 里ORDER BY RAND()/ORDER BY RANDOM()在小结果集上没有太大问题主要限制在LIMIT之前的扫描量。SQL Server 里NEWID()的排序也是同理大数据集下同样会遇到排序压力。Oracle 的ORDER BY DBMS_RANDOM.VALUE也不例外。不过有些数据库提供了专门的采样语法比如 SQL Server 的TABLESAMPLE、PostgreSQL 的TABLESAMPLE SYSTEM或TABLESAMPLE BERNOULLI。它们不是对每行算随机数再排序而是在存储引擎层面直接随机抽样页或行性能好很多。缺点是随机性不是完全均匀的而且在数据量小的时候可能抽不到足够的行。用它们做分析和抽样是可以的但做线上精确抽取活动用户要谨慎。4. 实战经验常见问题与排查技巧4.1 每次查询结果不随机或者重复出现遇到过好几次这样的反馈“我用ORDER BY RAND()怎么感觉抽出来的老是同一批人” 先别急着怀疑数据库大概率是表里主键不连续的问题。比如业务逻辑删除数据比较频繁自增主键一直往后涨剩下有效数据的 id 分布在 1000 到 1000 万之间但有效行只有几百条。如果用的是基于MAX(id)的随机方式那产生大的随机 id 的概率高结果总是落在尾部有数据的区域自然感觉不够随机。解决办法是不要直接对 id 范围随机而应该先估算总行数COUNT(*)再用LIMIT 1 OFFSET FLOOR(RAND() * count)或者用id 随机id后补齐的方式。这样才能让每一条有效记录有接近相同的概率被选中。还有一种情况是查询结果看起来“随机”但和预期的分布不一样。比如你随机抽了 100 条发现某个分类的占比过高。这和随机函数本身没关系而是你的数据源分布不均匀。数据库只会按现有数据行做均匀随机如果你的表里 A 分类有 9000 行、B 分类只有 100 行那抽 100 条出来大概率 90 条是 A。想在分类内均匀随机就得先分组随机。4.2 RAND() 和种子值的正确使用方式SQL Server 开发者应该都踩过这个坑在 SELECT 列表里写RAND()然后发现同一行里多个字段的随机数一模一样。为什么因为RAND()在语句级别只计算一次而不是每行计算一次。如果你想每行得到不同的随机值必须想办法让它每行都重新触发。一个常见的变通方法是RAND() CHECKSUM(NEWID())作为种子比如SELECT *, RAND(CHECKSUM(NEWID())) AS random_value FROM users;这样每行因为NEWID()不同CHECKSUM也不同RAND()以不同的种子初始化自然会得到不同的值。MySQL 的RAND()是每行独立计算的所以不存在这个问题。PostgreSQL 也一样。种子值另一个用途是做“可复现的随机”。比如运营要求每天随机推荐的内容在当天保持稳定同一个用户刷新不要每次都变那就可以用“用户 ID 日期”作为种子比如 MySQLSELECT * FROM contents ORDER BY RAND(UNIX_TIMESTAMP(CURDATE()) user_id);这里我省略了参数的具体拼接但思路就是用RAND(seed)中的 seed 来固定一天的随机序列。这样用户当天看到的顺序是一致的第二天又会变。这个方法在个性化推荐、每日推荐位上都很好用。4.3 多个随机列互相影响的问题有时候你想在一行里生成好几个随机数用来做不同用途。比如既要随机排序又要生成一个随机数值作为流量分组依据。如果你图省事直接写SELECT RAND() AS r1, RAND() AS r2 FROM users;在 MySQL 里通常没问题RAND()每次调用都会更新内部状态所以 r1 和 r2 不一样。但在 SQL Server 里因为你用的是同一个RAND()又没有触发重新计算的机制两个列的值几乎是一样的。这个问题很容易被测试数据误导因为在一个单行查询里RAND()可能确实返回不同的数但放到整个表查询里就固定了。更隐蔽的问题是 ORDER BY 和 SELECT 中的随机函数同时存在。有些数据库优化器可能只计算一遍随机值也可能计算两遍导致排序结果和你预想的完全不一样。遇到这种复杂业务我建议把随机逻辑拆到子查询里明确算一次随机值再在外层使用SELECT r1, r2 FROM ( SELECT id, RAND() AS r1, RAND() AS r2 FROM users ) t ORDER BY t.r1;这样至少你能知道每一列到底是怎么算出来的不会依赖数据库优化器的行为。4.4 在分页场景下做随机排序的坑很多分页接口会在 SQL 里写ORDER BY RAND() LIMIT ?, 20。结果一旦用户翻到第二页前面出现过的数据可能再次出现后面再怎么翻也看不到一些数据。因为每次请求都重新随机排序了分页毫无意义。最笨的办法是在业务层先把整个随机列表抽出来再按固定顺序分页。比如一次性随机抽取 500 条 ID缓存到一个临时集合然后按这个集合顺序做分页。500 条以内基本没问题超过的话可以用random_key字段提前在表里维护好随机排序值分页就在固定排序下进行。这里还有个细节如果你用OFFSET深分页性能会很差。通常我会建议产品限制随机分页的深度比如最多翻 10 页或者把随机列表限制在前 2000 条内。毕竟用户真的会在随机推荐列表里翻到第 50 页吗大概率不会。4.5 常见问题速查表现象可能原因解决办法多个RAND()列结果相同SQL Server 语句级只计算一次用RAND(CHECKSUM(NEWID()))或改用NEWID()大表随机查询很慢全表扫描 全量排序使用主键随机抽或预生成随机序随机抽到的数据集中在某一段主键空洞或MAX(id)过大改为基于COUNT(*)的 OFFSET 方式当天随机结果会变无种子用日期用户ID 作为种子随机排序分页内容重复每次请求重新随机增加random_key字段固化排序值随机结果分布不均匀源数据本身分布不均分组随机或按权重处理查不到随机记录随机 id 落在空洞上使用id 随机值再LIMIT 15. 扩展应用随机数据生成与抽样分析5.1 用随机函数快速生成测试数据我们做开发的时候经常需要一大把模拟数据来验证功能。比如要建 10 万条模拟订单靠手写脚本当然能写但 SQL 内置随机函数配合系统函数能省不少事。MySQL 生成随机用户名INSERT INTO test_users (username, email, age) SELECT CONCAT(user_, FLOOR(RAND() * 1000000)), CONCAT(user_, FLOOR(RAND() * 1000000), example.com), FLOOR(RAND() * 60 18) FROM information_schema.tables LIMIT 100;这里利用了information_schema.tables作为行数来源容易受源表行数限制。更通用的是用递归 CTEWITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n 1 FROM seq WHERE n 100 ) SELECT CONCAT(user_, FLOOR(RAND() * 1000000)), FLOOR(RAND() * 60 18) FROM seq;PostgreSQL 则更简单SELECT user_ || floor(random() * 1000000)::int, floor(random() * 60 18)::int FROM generate_series(1, 100);随机测试数据除了要“随机”还要注意取值范围和业务规则。比如年龄就不能小于 0月份不能超过 12状态字段不能乱枚举。所以我会在生成时候写清楚边界顺手做一层CHECK约束来兜底。5.2 用随机函数做数据抽样与 AB 测试分组数据分析中经常要做随机抽样。如果只是想看看某张表的特征完全没必要全表分析抽个 1% 出来跑一遍就行。MySQL 可以这么抽SELECT * FROM orders WHERE RAND() 0.01;PostgreSQL 的RANDOM()也一样。这样可以保证每行有 1% 的概率被选中不需要额外排序。AB 测试分组则是更常见的一个应用。要给用户随机分成 A、B 两组一般不建议直接在用户表上加随机数然后去不停更新而是在注册或首次访问时算好组别。比如用用户 ID 的 HASH 取模分组SELECT user_id, CASE WHEN HASHBYTES(MD5, CAST(user_id AS VARCHAR)) % 100 50 THEN A ELSE B END AS group_id FROM users;这种方式的优点是可复现、且分组稳定。而如果直接用RAND()存到字段里一旦字段丢失或者重新计算分组就乱了。所以 AB 测试的分组优先级能用哈希就用哈希能存下来就存下来。5.3 随机函数在脱敏和数据模拟中的注意事项数据脱敏有时候也会用到随机函数比如把真实用户名替换成“用户12345”把手机号替换成“138xxxx1234”。但这里有一个非常重要的安全要求脱敏后的数据不能让外部通过随机规则反推出真实信息。直接使用RAND()生成随机替换值是安全的因为随机出来的内容必须不携带原始数据的任何信息。不过要注意的是如果只是简单地把手机号中间四位随机掉其他位保留原样那么攻击者可以做号码段扫描仍然会暴露部分真实信息。所以在脱敏时我一般会把敏感字段整体替换成随机值而不是局部打码。还有一点测试环境如果和生产环境数据混用且脱敏规则不够强可能引发数据泄露。这在公司里是个严肃的合规问题。随机函数本身只是工具但使用它的人得想清楚边界。6. 结尾附一些个人心得最后说点实操下来的体会吧。随机函数在 SQL 里虽然只是个基础函数但它最容易暴露出我们对数据库执行计划的理解程度。不同数据库行为不同同一数据库不同版本也可能会出现细微差异所以写之前顺手查一下官方文档比啥都强。我自己的习惯是所有“随机”需求先问自己三个问题数据量有多大随机性是每次都要变还是可以维持一段时间是否需要可复现这三个问题想清楚了方案基本就浮现出来了。小表无脑ORDER BY RAND()大表老老实实用主键随机或预生成随机键别偷懒。如果你正在做活动抽奖或者推荐系统建议多花点时间做随机结果的概率验证。随机函数输出是否符合预期不只看代码逻辑还要看实际运行分布。写个脚本统计几千次结果如果有明显偏差多半是权重计算或行数统计那一步出了岔子。踩过一次坑之后你对随机函数的理解就能上一个台阶。