MySQL/MariaDB嵌套查询中RAND()函数输出分布异常问题

测试环境 #

• 数据库版本：10.5.8-MariaDB-3
• 存储引擎：InnoDB
• 测试表status_log核心结构：

CREATE TABLE `status_log` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `rank` int(11) DEFAULT 50,
 -- 省略其余未涉及字段
)

基线测试（无外层过滤） #

直接查询全量匹配记录并生成随机值，结果符合预期：

SELECT id, `rank` AS rank, (RAND()*100) AS thres 
FROM status_log 
WHERE `rank` = 50;

执行结果共返回66169条独立记录，每条记录的thres字段为独立生成的0-100范围随机数：

| 6575476 |   50 |     34.51090244065123 |
| 6575511 |   50 |     67.84258230388404 |
| 6575589 |   50 |     35.68020727083106 |
| 6575644 |   50 |     74.87329251586766 |
| 6575723 |   50 |     67.32584384020961 |
| 6575771 |   50 |    12.009344726809621 |
| 6575863 |   50 |     58.06919518678374 |
+---------+------+-----------------------+
66169 rows in set (2.502 sec)

异常场景测试 #

子查询外层过滤异常 #

在子查询内生成随机值，在外层对thres字段做大小比较过滤：

1. 测试thres > rank条件：

   SELECT * 
   FROM (
       SELECT id, `rank` AS rank, (RAND()*100) AS thres   
       FROM status_log 
       WHERE `rank` = 50
   ) t 
   WHERE thres > `rank` 
   ORDER BY thres;
   

   • 预期结果：基于0-100均匀随机分布特性，rank=50时满足条件的记录占比约50%，对应约3.3万条
   • 实际结果：仅返回16449条记录

   | 4396732 |   50 |  99.97966075314177 |
   | 4001782 |   50 |  99.98002871869134 |
   | 1788580 |   50 |  99.98064143581375 |
   | 5300286 |   50 |  99.98275954274717 |
   |  146401 |   50 |  99.98552389441573 |
   | 4744748 |   50 |  99.98644758014609 |
   +---------+------+--------------------+
   16449 rows in set (2.188 sec)
   

2. 测试thres < rank条件：

   SELECT * 
   FROM (
       SELECT id, `rank` AS rank, (RAND()*100) AS thres 
       FROM status_log 
       WHERE `rank` = 50
   ) t 
   WHERE thres < `rank` 
   ORDER BY thres;
   

   • 实际结果：仅返回16562条记录

   | 4653786 |   50 |    49.98035016467827 |
   | 6041489 |   50 |   49.980370281245904 |
   | 5064204 |   50 |   49.989308742796354 |
   | 1699741 |   50 |   49.991373205549436 |
   | 3234039 |   50 |    49.99390454030959 |
   |  806791 |   50 |    49.99575274996064 |
   | 3713581 |   50 |    49.99814410693771 |
   +---------+----------------------+
   16562 rows in set (2.373 sec)
   

两个条件的返回结果加总仅3.3万条，占总匹配数据量的50%，与100%的预期覆盖度存在明显偏差。

UNION ALL拼接测试 #

将两个方向的过滤查询通过UNION ALL拼接，验证条件覆盖度：

SELECT * FROM (
    SELECT id, `rank` AS rank, (RAND()*100) AS thres 
    FROM status_log 
    WHERE `rank` = 50
) t WHERE thres < 50
UNION ALL
SELECT * FROM (
    SELECT id, `rank` AS rank, (RAND()*100) AS thres 
    FROM status_log 
    WHERE `rank` = 50
) t WHERE thres >= 50;

• 预期结果：覆盖全部66169条rank=50的记录
• 实际结果：仅返回约3.3万条记录

对照测试结论 #

通过变量控制可确认异常触发边界：

• 异常仅在使用无参、非确定性的RAND()函数时出现
• 改用带id作为绑定种子的RAND(id)生成随机值、或提前将随机值写入表临时字段后再做过滤，结果完全符合预期（各条件返回约3.3万条记录）
• 调整rank阈值至其他值（如rank=30）时，异常依然存在：thres<30返回约6000条、thres>30返回约3.2万条，均不符合0-100均匀分布的概率预期

内容的提问来源于stack exchange，提问作者Diver