MySQL:选择多个随机行的最有效方法是什么 [英] MySQL: what's the most efficient way to select multiple random rows

问题描述

我有一个相当大的数据集和一个需要两个联接的查询，因此查询的效率对我来说非常重要.我需要根据联接的结果从数据库中检索3个满足条件的随机行.指出了最明显的解决方案效率不高此处，因为

I have a fairly large dataset and a query that requires two joins, so efficiency of the query is very important to me. I need to retrieve 3 random rows from the database that meet a condition based on the result of a join. Most obvious solution is pointed out as inefficient here, because

[这些解决方案]需要对所有表进行顺序扫描(因为需要计算与每一行关联的随机值-这样才能确定最小的值)，即使对于中等大小的表也可能相当慢.

[these solutions] need a sequential scan of all the table (because the random value associated with each row needs to be calculated - so that the smallest one can be determined), which can be quite slow for even medium sized tables.

但是，作者建议的方法(SELECT * FROM table WHERE num_value >= RAND() * (SELECT MAX(num_value) FROM table) LIMIT 1，其中num_value是ID)对我不起作用，因为某些ID可能会丢失(因为某些行可能已被用户删除).

However, the method suggested by the author there (SELECT * FROM table WHERE num_value >= RAND() * (SELECT MAX(num_value) FROM table) LIMIT 1 where num_value is ID) doesn't work for me because some IDs might be missing (because some rows may have been been deleted by users).

那么，在我的情况下，检索3个随机行的最有效方法是什么?

So, what would be the most efficient way to retrieve 3 random rows in my situation?

解决方案不必是纯SQL.我也使用PHP.

the solution does not need to be pure SQL. I also use PHP.

推荐答案

将您的RAND()调用添加到ORDER BY子句中将使您忽略ID.试试这个:

Adding your RAND() call into the ORDER BY clause should allow you to ignore the ID. Try this:

SELECT * FROM table WHERE ... ORDER BY RAND() LIMIT 3;

指出了性能问题后，最好的选择是遵循以下原则(使用PHP):

After having performance issues pointed out, your best bet may be something along these lines (utilizing PHP):

$result = PDO:query('SELECT MAX(id) FROM table');
$max    = $result->fetchColumn();
$ids    = array();
$rows   = 5;

for ($i = 0; $i < $rows; $i++) {
    $ids[] = rand(1, $max);
}

$ids     = implode(', ', $ids);
$query   = PDO::prepare('SELECT * FROM table WHERE id IN (:ids)');
$results = $query->execute(array('ids' => $ids));

这时，您应该可以选择前3个结果.这种方法的唯一问题是处理已删除的行，如果您没有收到至少3个结果，则可能必须撞掉$ rows var或添加一些逻辑以执行另一个查询.

At this point you should be able to select the first 3 results. The only issue with this approach is dealing with deleted rows and you might have to either bump the $rows var or add some logic to do another query in case you didn't receive at least 3 results back.

这篇关于MySQL:选择多个随机行的最有效方法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！