mysql order by rand() 性能问题及解决方案

Question

mysql order by rand() 性能问题及解决方案

7

我曾经使用order by rand()从数据库中生成随机行，没有问题，但是我发现随着数据库大小的增加，这个rand()会导致服务器负载过重，所以我在寻找一种替代方法。我尝试使用php rand()函数生成一个随机数，并将其作为id放入mysql查询中，这样非常快，因为mysql知道行id。但问题在于，在我的表中，并不是所有的数字都可用。例如1、2、5、9、12等。

如果php rand()生成数字3、4等，那么查询结果将为空，因为没有编号为3、4等的id。

最好的方法是什么？最好从php生成可用的随机数，但它必须检查该表以生成可用的编号。请给出建议。

$id23=rand(1,100000000);
    SELECT items FROM tablea where status='0' and id='$id23' LIMIT 1

上述查询速度快，但有时会生成不存在于数据库中的结果。

    SELECT items FROM tablea where status=0 order by rand() LIMIT 1

上述查询速度太慢，会给服务器带来很大的负担。

- raviloves

1

请看这里：https://dev59.com/wXM_5IYBdhLWcg3ww18Q - Sudhir Bastakoti

你是否已经知道行数，还是需要第二个查询来获取它？ - hek2mgl

4个回答

8

使用您的数据库查找表中的最大值，生成一个小于或等于该值的随机数，在 id 大于或等于您的随机数的第一行中抓取第一行。无需 PHP。

SELECT items
FROM tablea
WHERE status = '0' and
      id >= FLOOR(1 + RAND() * (SELECT MAX(id) FROM tablea))
LIMIT 1

- pyrospade

谢谢！主要的好处是不必两次访问数据库，只为确保您没有低估或高估表的最大ID。 - pyrospade

这个特定的解决方案的缺点在于它偏向于表格中早期的行。它会扫描表格，一遍遍地测试id与重新评估的表达式，直到成功为止。 - Rick James

我对MySQL不是很熟悉，但这难道不只是一个索引扫描吗？如果索引在（id、状态、项目）上，则可能甚至是仅索引扫描。 - pyrospade

有两个步骤--评估子查询一次；它需要一个在'id'上的索引（可能是“PRIMARY KEY(id)”）；这非常有效率。然后它会扫描部分表格。 INDEX(status，id，items)，按照_this_的顺序，是最佳且覆盖的。问题在于结果会“偏向”status=0后面的项目或id间隙后面的项目。当RAND()接近1.0时，它还可能什么都不返回。 - Rick James

1

一种可能的解决方案是使用limit：

$id23=rand(1,$numberOfRows);

SELECT items FROM tablea where status='0' LIMIT $id23 1

这不会产生任何遗漏的行（但正如hek2mgl所提到的），需要知道选择中的行数。

- Jim

我也会建议这样做 :) 但是不确定已经有多少行可用。 - hek2mgl

@hek2mgl 我已经清楚地写明我正在使用这个方法，但问题是并非所有行都可用，而且有时此方法会生成不可用的数字，在这种情况下查询将为空。 - raviloves

2

结果证明这并不快，因为它必须扫描$id23行来执行偏移量。而且获取行数的COUNT()也不是一个快速操作。 - Bill Karwin

1

@BillKarwin 哦，我本来以为MySQL会想出一种聪明的方法跳到数据集的中间。这是否意味着对于大型数据集，最好使用ids进行分页？ - Jim

1

@BillKarwin 当然，如果你不知道前四个记录是什么，就没有办法找到第五个记录。 - Jim

显示剩余2条评论

1

你说得对，如果你要处理大型数据集，ORDER BY RAND()不是一个好的解决方案。根据需要随机化的频率，你可以生成一个带有随机数的列，然后在某个预定义的时间间隔内更新该数字。

你可以将该列作为排序索引使用。这对于读取环境较重的情况非常有效，并且在一定时间内产生可预测的随机顺序。

- datasage

我正在考虑像你建议的那样做。我想讨论一下我的想法。我将从大型数据库中生成行ID并存储在另一个表中，最多保留1000个数字，并从该表中生成随机数，在完成工作后删除该行。这个想法怎么样？ - raviloves

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Bill Karwin · Accepted Answer

首先，生成一个从1到MAX(id)的随机值，而不是100000000。

然后有至少两种好的解决方案：

Use > not =
```
SELECT items FROM tablea where status='0' and id>'$id23' LIMIT 1
```
Create an index on (status,id,items) to make this an index-only query.
Use =, but just try again with a different random value if you don't find a hit. Sometimes it will take several tries, but often it will take only one try. The = should be faster since it can use the primary key. And if it's faster and gets it in one try 90% of the time, that could make up for the other 10% of the time when it takes more than one try. Depends on how many gaps you have in your id values.