SQL查询中的RAND()是如何工作的

How does the following query actually work?

下面的查询是如何工作的?

SELECT * FROM t ORDER BY RAND() LIMIT 1;

Does it sort all records in the source database first and just truncate it to get 1 row then?

它是否先对源数据库中的所有记录进行排序，然后将其截断为1行?

2 个解决方案

#1

This question has more to do with LIMIT query optimization than how does RAND() work.

这个问题更多的是关于限制查询优化，而不是RAND()如何工作。

From manual (bold emphasis mine):

从手册(大胆的强调我的):

If you combine LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result.

如果您将LIMIT row_count与ORDER BY组合在一起，那么MySQL会在找到排序结果的第一个row_count行之后就结束排序，而不是对整个结果进行排序。

If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set.

如果排序是通过使用索引完成的，那么这是非常快的。如果必须完成文件排序，那么在第一个row_count被找到之前，所有匹配查询的行都被选中，并且大多数或全部都已排序。找到初始行之后，MySQL不会对结果集的其余部分进行排序。

#2

Yes. Order by operation is performed before limiting the result.

是的。在限制结果之前，按操作进行排序。

To get a random record in good performance, you must use another approach.

要获得性能良好的随机记录，您必须使用另一种方法。

A solution is : If you have an id that is sequential you can create a random number and fetch that record only by where clause:

一个解决方案是:如果你有一个连续的id，你可以创建一个随机数，只根据where子句获取记录:

Select * from t 
where id>(select * from 
  (select rand()*10000)t1
 ) limit 1;

Where 10000 is the biggest id in your table.

这里10000是表格中最大的id。

To making the query more dynamic we can use:

为了使查询更有活力，我们可以使用:

SET @m:=rand()*(select max(id) from t); 
SELECT * FROM t WHERE id > @m LIMIT 1;

And also this works:

也是如此:

Select * from t 
where 
  id>(select * from(select rand()*max(id) from t) t1)
limit 1;

But following query is incorrect and shows only approximately 0.5 percent of records in the beginning. And I don't know why:

但是下面的查询是不正确的，并且在开始时只显示了大约0.5%的记录。我不知道为什么:

Select * from t 
where 
  id>rand()*(select max(id) from t) 
limit 1;

#1