带选择概率权重的T-SQL随机查询性能优化求解

原方案核心问题 #

• 原方案通过UNION ALL多次查询同一张表，至少执行3次表扫描，数据量大时会产生大量冗余IO，性能较差
• 冗余的重复记录会提升排序阶段的计算量，还可能返回重复的同用户记录，不符合返回2条不同用户的需求规则

优化方案 #

你的权重规则可以直接转化为公式 权重 = 1 + param1 + param2，和原方案中每条记录在UNION ALL结果中出现的次数完全一致，不需要多次查询，一次扫描即可实现完全相同的概率分布，优化后代码如下（适配SQL Server语法）：

SELECT TOP 2 EMail
FROM dbo.Benutzer
ORDER BY ABS(CHECKSUM(NEWID())) * (1 + param1 + param2) DESC

方案说明 #

• ABS(CHECKSUM(NEWID())) 会为每行生成一个独立的正整数随机值，避免了直接用RAND()仅生成全局单次随机值的问题
• 随机值乘以当前记录的权重后降序排序，权重越高的记录排序越靠前，被选中的概率和权重成正比，和原方案的概率分布完全一致
• 单表查询仅执行一次扫描，没有冗余数据，且每条记录仅出现一次，返回的TOP 2天然就是不同的用户，不需要额外去重逻辑

额外性能优化建议 #

• 如果表数据量较大，可以为EMail, param1, param2创建覆盖索引，查询可以直接走索引获取所有需要的字段，不需要回表访问主数据，性能提升更明显
• 若表数据量达到百万级以上，还可以先根据权重阈值过滤掉低权重的极小部分记录，再执行排序，进一步减少排序计算量

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