如何优化由于ORDER BY RAND（）导致的慢速MySQL查询？ [重复]

This question already has an answer here:

这个问题在这里已有答案:

Fetching RAND() rows without ORDER BY RAND() in just one query 3 answers

在一个查询3个答案中获取没有ORDER BY RAND()的RAND()行

So I have this relatively simple query to pull a random row based on a few criteria from two different MyISAM tables:

所以我有这个相对简单的查询来根据两个不同的MyISAM表中的一些条件来拉随机行:

SELECT 
    table1 . * , table2 . * 
FROM 
    Table1 AS table1
LEFT JOIN 
    Table2 AS table2
USING (
    col1
)
WHERE 
    table1.col1 !=  '5324372'
AND 
    table1.col2 LIKE  'S'
AND (
    table1.col3 LIKE  'I'
    OR table1.col3 LIKE  'V-G'
)
AND (
    table2.col1 =  'A'
    OR table2.col2 =  'B'
)
ORDER BY RAND( ) 
LIMIT 1

...which at the beginning worked fine, but as my Database began to grow, now takes several seconds to execute.

...一开始工作正常,但随着我的数据库开始增长,现在需要几秒钟才能执行。

All columns used above are indexed, so it's not an indexing problem.

上面使用的所有列都是索引的,因此它不是索引问题。

From what I've researched, it's due to the ORDER BY RAND() line which apparently runs very slow on large tables.

根据我的研究,这是由于ORDER BY RAND()线显然在大型桌子上运行得很慢。

I've seen some potential solutions to this problem, but they are quite cryptic and I could not wrap my head around any of them in relation to my query above.

我已经看到了这个问题的一些潜在的解决方案,但它们非常神秘,我无法围绕上面的查询来解决任何问题。

I'm actually surprised that MySQL does not have a built-in solution to this very slow ORDER BY RAND() problem as it would seem to me like it is a very basic need for many types of applications needing to select a random row.

我真的很惊讶MySQL没有这个非常慢的ORDER BY RAND()问题的内置解决方案,因为在我看来,对于需要选择随机行的许多类型的应用程序来说,这是一个非常基本的需求。

So how can I optimize my query above so that I get the same/similar effect without the long execution times?

那么我怎样才能优化上面的查询,以便在没有长执行时间的情况下获得相同/相似的效果?

2 个解决方案

#1

You are choosing only one row , I think you can choose one row randomly by LIMIT randomly. Example:

您只选择一行,我认为您可以随机选择LIMIT随机选择一行。例:

If you have:

如果你有:

SELECT * table  ORDER BY RAND( )  LIMIT 1;

change it to

改为

SELECT * table    LIMIT $randomvalue  ,$randomvalue + 1;

$randomvalue is a random value chosen from the application level.

$ randomvalue是从应用程序级别中选择的随机值。

update: the following answer is more clear than the above one.

更新:以下答案比上面的答案更清楚。

//get the total number of rows
$result= mysql_query(" SELECT  COUNT(*) AS total FROM `table` ");
$row = mysql_fetch_array($result);
$total=$row['total'];


//create random value from 1 to the total of rows 
$randomvalue =rand(1,$total);


//get the random row

$result= mysql_query(" SELECT  * FROM `table` limit $randomvalue,1");

#2

Sorting is typically an N*log(N) operation. Normally, sorting is avoided as much as possible by the query planner / generator / optimiser, by combining it with indexes, or the order that naturally arises from subqueries. Sorting by random cannot be "optimised out" in any way. The solution: don't do it.

排序通常是N * log(N)操作。通常,查询规划器/生成器/优化器通过将其与索引或子查询自然产生的顺序相结合,尽可能地避免排序。随机排序不能以任何方式“优化”。解决方案:不要这样做。

#1