MySQL 使用 WHERE 子句平均分布随机行

Question

我有这张 table ，

person_id   int(10) pk
points      int(6) index
other columns not very important

我有这个随机函数，它在具有 1000 万行的表上非常快:

SELECT person_id
  FROM persons AS r1 JOIN
       (SELECT (RAND() *
                     (SELECT MAX(person_id)
                        FROM persons)) AS id)
        AS r2
 WHERE r1.person_id >= r2.id
 ORDER BY r1.person_id ASC
 LIMIT 1

一切都很好，但现在我希望只显示积分 > 0 的人。示例表:

PERSON_ID      POINTS
1              4
2              6
3              0
4              3

当我将 AND points > 0 附加到 where 子句时，无法选择 person_id 3，因此会产生间隙，当随机选择 person_id 3 时，将选择 person_id 4。这给了第 4 个人更大的机会被选中。任何人都得到了如何调整查询以使其与 where 子句一起工作并为所有行提供相同百分比的选择机会的建议。

信息表:表是统一的，person_id之间没有空隙。大约 90% 的分数为 0。我想查询 where points = 0 and points > 0。

在之前有人会说，使用rand():对于超过100k 行的表，这不是解决方案。

奖励问题:是否可以在 1 个查询中选择 x 个随机行，这样当我想要更多随机行时就不必多次调用此查询？

重要提示:性能是关键，1000 万行以上的查询可能不会比当前查询花费的时间长很多，后者需要 0.0005 秒，我希望保持在 0.05 秒以下。

最后说明:如果您认为满足上述要求的查询永远不会这么快，但另一种解决方案是可能的(例如获取 100 行并显示 x 随机，其中点数大于 0)，请告诉:)

非常感谢您的帮助，欢迎所有帮助:)

Answer 1

您可以为您真正想要使用的记录生成内联无间隙 ID，然后使用可用记录总数生成随机选择器。

试试这个(为 row_number 生成器选择答案 here 的 Prop ):

    SELECT r1.*
    FROM
        (SELECT  person_id,
                 @curRow := @curRow + 1 AS row_number
        FROM persons as p,
             (SELECT @curRow := 0) r0
        WHERE points>0) r1
    , (SELECT COUNT(1) * RAND() id
       FROM persons
       WHERE points>0) r2
    WHERE r1.person_id>=r2.id
    ORDER BY r1.person_id ASC
    LIMIT 1;

你可以在this sqlfiddle中搞砸它.