mysql - SELECT 随机 id 的 SQL 优化(带 WHERE 子句)

Question

我目前正在开发一个多线程程序(使用 Java)，该程序需要在数据库中选择随机行，以便更新它们。这运行良好，但我开始遇到一些关于我的 SELECT 请求的性能问题。

在找到这个网站之前，我尝试了多种解决方案:

http://jan.kneschke.de/projects/mysql/order-by-rand/

我尝试了以下解决方案:

SELECT * FROM Table 
JOIN (SELECT FLOOR( COUNT(*) * RAND() ) AS Random FROM Table) 
AS R ON Table.ID > R.Random 
WHERE Table.FOREIGNKEY_ID IS NULL 
LIMIT 1;

它只选择生成的随机 ID 号下方的一行。这工作得很好(15 万行的每个请求平均不到 100 毫秒)。但是在我的程序处理之后，FOREIGNKEY_ID 将不再为 NULL(它将更新为一些值)。

问题是，我的 SELECT 会“忘记”一些行，因为 ID 低于随机生成的 ID，我将无法处理它们。

所以我尝试调整我的要求，这样做:

SELECT * FROM Table 
JOIN (SELECT FLOOR( 
(SELECT COUNT(id) FROM Table WHERE FOREIGNKEY_ID IS NULL) * RAND() ) 
AS Random FROM Table) 
AS R ON Table.ID > R.Random
WHERE Table.FOREIGNKEY_ID IS NULL 
LIMIT 1;

有了这个请求，不再有跳过某些行的问题，但性能却急剧下降(在 150k 行上每个请求平均 1s)。

当我还有很多行要处理时，我可以简单地执行快速的，当它只剩下几行时切换到慢速的，但这将是代码中的一个“脏”修复，我更喜欢可以完成这项工作的优雅 SQL 请求。

感谢您的帮助，如果我不清楚或者您需要更多详细信息，请告诉我。

Answer 1

为了使您的方法更通用，您需要 max(id) 而不是 count(*):

SELECT t.*
FROM Table t JOIN
     (SELECT FLOOR(MAX(id) * RAND() ) AS Random FROM Table) r
     ON t.ID > R.Random 
WHERE t.FOREIGNKEY_ID IS NULL 
ORDER BY t.ID
LIMIT 1;

通常添加 ORDER BY 以确保返回“下一个”id。理论上，MySQL 总是可以返回表中的最大 id。

问题是 ID 中的间隙。而且，很容易创建您永远不会获得随机数的分布。 . .假设这四个 id 是 1、2、3、1000。您的方法永远不会得到 1000000。以上几乎总能搞定。

也许解决您的问题的最简单方法是多次运行第一个查询，直到它获得有效的行。下一个建议是 (FOREIGNKEY_ID, ID) 上的索引，子查询可以使用它。这可能会加快查询速度。

我更喜欢这样的东西:

SELECT t.id
FROM Table t 
WHERE t.FOREIGNKEY_ID IS NULL AND
      RAND() < 1.0 / 1000
ORDER BY RAND()
LIMIT 1;

WHERE 子句的目的是大大减少体积，因此 ORDER BY 不会花费太多时间。

不幸的是，这将需要扫描表格，因此您可能不会在 150k 表格上获得 100 毫秒范围内的响应。您可以将其简化为使用 t(FOREIGNKEY_ID, ID) 上的索引进行索引扫描。

编辑:

如果您想要一个合理的机会实现均匀分布并且性能不会随着表变大而增加，这是另一个想法，唉，它需要一个触发器。

向表中添加一个名为 random 的新列，该列使用 rand() 进行初始化。在 random` 上建立索引。然后运行查询，例如:

select t.*
from ((select t.*
       from t
       where random >= @random
       order by random
       limit 10 
      ) union all
      (select t.*
       from t
       where random < @random
       order by random desc
       limit 10 
      )
     ) t
order by rand();
limit 1;

这个想法是，子查询可以使用索引来选择一组 20 行，这些行是非常任意的——在所选点之前和之后各 10 行。然后对行进行排序(一些开销，您可以使用 limit 数字来控制)。这些是随机返回的。

想法是，如果您选择随机数，就会存在任意间隙，这些间隙会使所选数字不太统一。然而，通过在该值周围抽取更大的样本，那么选择任何一个值的概率应该接近均匀分布。均匀性仍然会有边缘效应，但这些在大量数据上应该是次要的。