blocks|key|988208|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|988209|SELECT++*
FROM++++(
++++++++SELECT++@cnt+:=+COUNT(*)+%2B+1,
++++++++++++++++@lim+:=+10
++++++++FROM++++t_random
++++++++)+vars
STRAIGHT_JOIN
++++++++(
++++++++SELECT++r.*,
++++++++++++++++@lim+:=+@lim+-+1
++++++++FROM++++t_random+r
++++++++WHERE+++(@cnt+:=+@cnt+-+1)
++++++++++++++++AND+RAND(20090301)+<+@lim+/+@cnt
++++++++)+i|code-block|syntax|javascript|988210|这在MyISAM上尤其有效(因为COUNT(*)是即时的)，但即使在InnoDB中，10的效率也比ORDER+BY+RAND()高出一倍。|offset|length|style|CODE|988211|这里的主要思想是，我们不进行排序，而是保留两个变量，并计算要在当前步骤中选择的行的running+probability。|988212|有关更多细节，请参阅我博客中的这篇文章：|988213|选择随机行|unordered-list-item|988214|更新：|BOLD|988215|如果您只需要选择一个随机记录，请尝试如下：|988216|SELECT++aco.*
FROM++++(
++++++++SELECT++minid+%2B+FLOOR((maxid+-+minid)+*+RAND())+AS+randid
++++++++FROM++++(
++++++++++++++++SELECT++MAX(ac_id)+AS+maxid,+MIN(ac_id)+AS+minid
++++++++++++++++FROM++++accomodation
++++++++++++++++)+q
++++++++)+q2
JOIN++++accomodation+aco
ON++++++aco.ac_id+=
++++++++COALESCE
++++++++(
++++++++(
++++++++SELECT++accomodation.ac_id
++++++++FROM++++accomodation
++++++++WHERE+++ac_id+>+randid
++++++++++++++++AND+ac_status+!=+'draft'
++++++++++++++++AND+ac_images+!=+'b:0;'
++++++++++++++++AND+NOT+EXISTS
++++++++++++++++(
++++++++++++++++SELECT++NULL
++++++++++++++++FROM++++accomodation_category
++++++++++++++++WHERE+++acat_id+=+ac_category
++++++++++++++++++++++++AND+acat_slug+=+'vendeglatohely'
++++++++++++++++)
++++++++ORDER+BY
++++++++++++++++ac_id
++++++++LIMIT+++1
++++++++),
++++++++(
++++++++SELECT++accomodation.ac_id
++++++++FROM++++accomodation
++++++++WHERE+++ac_status+!=+'draft'
++++++++++++++++AND+ac_images+!=+'b:0;'
++++++++++++++++AND+NOT+EXISTS
++++++++++++++++(
++++++++++++++++SELECT++NULL
++++++++++++++++FROM++++accomodation_category
++++++++++++++++WHERE+++acat_id+=+ac_category
++++++++++++++++++++++++AND+acat_slug+=+'vendeglatohely'
++++++++++++++++)
++++++++ORDER+BY
++++++++++++++++ac_id
++++++++LIMIT+++1
++++++++)
++++++++)|988217|这假设您的ac_id的分布或多或少是均匀的。|988218|entityMap|0|LINK|mutability|MUTABLE|url|http://explainextended.com/2009/03/01/selecting-random-rows/^0|0|0|2|6|G|8|Y|6|16|2|1D|F|0|15|J|0|0|0|5|0|0|0|3|0|0|0|5|5|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1B|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1C|8|@$I|1D|J|1E|K|L]|$I|1F|J|1G|K|L]|$I|1H|J|1I|K|L]|$I|1J|J|1K|K|L]|$I|1L|J|1M|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|1N|8|@$I|1O|J|1P|K|L]]|9|@]|A|$]]|$1|O|3|P|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|Q|3|R|5|S|7|1R|8|@]|9|@$I|1S|J|1T|1|1U]]|A|$]]|$1|T|3|U|5|6|7|1V|8|@$I|1W|J|1X|K|V]]|9|@]|A|$]]|$1|W|3|X|5|6|7|1Y|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|D|7|1Z|8|@]|9|@]|A|$E|F]]|$1|10|3|11|5|6|7|20|8|@$I|21|J|22|K|L]]|9|@]|A|$]]|$1|12|3|-4|5|6|7|23|8|@]|9|@]|A|$]]]|13|$14|$5|15|16|17|A|$18|19]]]]

Try this:

<pre><code>SELECT *
FROM (
 SELECT @cnt := COUNT(*) + 1,
 @lim := 10
 FROM t_random
 ) vars
STRAIGHT_JOIN
 (
 SELECT r.*,
 @lim := @lim - 1
 FROM t_random r
 WHERE (@cnt := @cnt - 1)
 AND RAND(20090301) &lt; @lim / @cnt
 ) i
</code></pre>

This is especially efficient on <code>MyISAM</code> (since the <code>COUNT(*)</code> is instant), but even in <code>InnoDB</code> it's <code>10</code> times more efficient than <code>ORDER BY RAND()</code>.

The main idea here is that we don't sort, but instead keep two variables and calculate the <code>running probability</code> of a row to be selected on the current step.

See this article in my blog for more detail:

<ul>
<li><a href="http://explainextended.com/2009/03/01/selecting-random-rows/" rel="noreferrer">Selecting random rows</a></li>
</ul>

Update:

If you need to select but a single random record, try this:

<pre><code>SELECT aco.*
FROM (
 SELECT minid + FLOOR((maxid - minid) * RAND()) AS randid
 FROM (
 SELECT MAX(ac_id) AS maxid, MIN(ac_id) AS minid
 FROM accomodation
 ) q
 ) q2
JOIN accomodation aco
ON aco.ac_id =
 COALESCE
 (
 (
 SELECT accomodation.ac_id
 FROM accomodation
 WHERE ac_id &gt; randid
 AND ac_status != 'draft'
 AND ac_images != 'b:0;'
 AND NOT EXISTS
 (
 SELECT NULL
 FROM accomodation_category
 WHERE acat_id = ac_category
 AND acat_slug = 'vendeglatohely'
 )
 ORDER BY
 ac_id
 LIMIT 1
 ),
 (
 SELECT accomodation.ac_id
 FROM accomodation
 WHERE ac_status != 'draft'
 AND ac_images != 'b:0;'
 AND NOT EXISTS
 (
 SELECT NULL
 FROM accomodation_category
 WHERE acat_id = ac_category
 AND acat_slug = 'vendeglatohely'
 )
 ORDER BY
 ac_id
 LIMIT 1
 )
 )
</code></pre>

This assumes your <code>ac_id</code>'s are distributed more or less evenly.

blocks|key|2440790|text|我是这样做的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2440791|SET+@r+:=+(SELECT+ROUND(RAND()+*+(SELECT+COUNT(*)
++FROM++++accomodation+a
++JOIN++++accomodation_category+c
++++ON+(a.ac_category+=+c.acat_id)
++WHERE+++a.ac_status+!=+'draft'
++++++++AND+c.acat_slug+!=+'vendeglatohely'
++++++++AND+a.ac_images+!=+'b:0;';

SET+@sql+:=+CONCAT('
++SELECT++a.ac_id,
++++++++a.ac_status,
++++++++a.ac_name,
++++++++a.ac_status,
++++++++a.ac_images
++FROM++++accomodation+a
++JOIN++++accomodation_category+c
++++ON+(a.ac_category+=+c.acat_id)
++WHERE+++a.ac_status+!=+''draft''
++++++++AND+c.acat_slug+!=+''vendeglatohely''
++++++++AND+a.ac_images+!=+''b:0;''
++LIMIT+',+@r,+',+1');

PREPARE+stmt1+FROM+@sql;

EXECUTE+stmt1;|code-block|syntax|javascript|2440792|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here's how I'd do it:

<pre><code>SET @r := (SELECT ROUND(RAND() * (SELECT COUNT(*)
 FROM accomodation a
 JOIN accomodation_category c
 ON (a.ac_category = c.acat_id)
 WHERE a.ac_status != 'draft'
 AND c.acat_slug != 'vendeglatohely'
 AND a.ac_images != 'b:0;';

SET @sql := CONCAT('
 SELECT a.ac_id,
 a.ac_status,
 a.ac_name,
 a.ac_status,
 a.ac_images
 FROM accomodation a
 JOIN accomodation_category c
 ON (a.ac_category = c.acat_id)
 WHERE a.ac_status != ''draft''
 AND c.acat_slug != ''vendeglatohely''
 AND a.ac_images != ''b:0;''
 LIMIT ', @r, ', 1');

PREPARE stmt1 FROM @sql;

EXECUTE stmt1;
</code></pre>

blocks|key|914871|text|这取决于你需要的随机性。你联系的解决方案很管用，海事组织。除非您在ID字段中有很大的空白，否则仍然是随机的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|914872|但是，您应该能够在一个查询中使用以下方法(用于选择单个值)：|914873|SELECT+[fields]+FROM+[table]+WHERE+id+>=+FLOOR(RAND()*MAX(id))+LIMIT+1|code-block|syntax|javascript|914874|其他解决办法：|914875|向表中添加一个名为random的永久浮点数字段，并将其填充为随机数。然后，您可以在PHP中生成一个随机数并执行"SELECT+...+WHERE+rnd+>+$random"。|unordered-list-item|offset|length|style|CODE|914876|获取整个in列表并将其缓存在文本文件中。读取文件并从中选择一个随机ID。|914877|将查询结果缓存为HTML，并将其保存几个小时。|914878|entityMap^0|0|0|0|0|9|6|1J|W|0|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Z|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|10|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|11|8|@$N|12|O|13|P|Q]|$N|14|O|15|P|Q]]|9|@]|A|$]]|$1|R|3|S|5|M|7|16|8|@]|9|@]|A|$]]|$1|T|3|U|5|M|7|17|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|W|$]]

It depends on how random you need to be. The solution you linked works pretty well IMO. Unless you have large gaps in the ID field, it's still pretty random.

However, you should be able to do it in one query using this (for selecting a single value):

<pre><code>SELECT [fields] FROM [table] WHERE id &gt;= FLOOR(RAND()*MAX(id)) LIMIT 1
</code></pre>

Other solutions:

<ul>
<li>Add a permanent float field called <code>random</code> to the table and fill it with random numbers. You can then generate a random number in PHP and do <code>"SELECT ... WHERE rnd &gt; $random"</code></li>
<li>Grab the entire list of IDs and cache them in a text file. Read the file and pick a random ID from it.</li>
<li>Cache the results of the query as HTML and keep it for a few hours.</li>
</ul>

blocks|key|2440846|text|这将为您提供使用索引获取随机id的单个子查询，然后另一个查询将触发获取您的联接表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2440847|SELECT++accomodation.ac_id,
++++++++accomodation.ac_status,
++++++++accomodation.ac_name,
++++++++accomodation.ac_status,
++++++++accomodation.ac_images
FROM++++accomodation,+accomodation_category
WHERE+++accomodation.ac_status+!=+'draft'
++++++++AND+accomodation.ac_category+=+accomodation_category.acat_id
++++++++AND+accomodation_category.acat_slug+!=+'vendeglatohely'
++++++++AND+ac_images+!=+'b:0;'
AND+accomodation.ac_id+IS+IN+(
++++++++SELECT+accomodation.ac_id+FROM+accomodation+ORDER+BY+RAND()+LIMIT+1
)|code-block|syntax|javascript|2440848|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This will give you single sub query that will use the index to get a random id then the other query will fire getting your joined table.

<pre><code>SELECT accomodation.ac_id,
 accomodation.ac_status,
 accomodation.ac_name,
 accomodation.ac_status,
 accomodation.ac_images
FROM accomodation, accomodation_category
WHERE accomodation.ac_status != 'draft'
 AND accomodation.ac_category = accomodation_category.acat_id
 AND accomodation_category.acat_slug != 'vendeglatohely'
 AND ac_images != 'b:0;'
AND accomodation.ac_id IS IN (
 SELECT accomodation.ac_id FROM accomodation ORDER BY RAND() LIMIT 1
)
</code></pre>

blocks|key|2440853|text|您的虚拟示例的解决方案是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2440854|SELECT++accomodation.ac_id,
++++++++accomodation.ac_status,
++++++++accomodation.ac_name,
++++++++accomodation.ac_status,
++++++++accomodation.ac_images
FROM++++accomodation,
++++++++JOIN+
++++++++++++accomodation_category+
++++++++++++ON+accomodation.ac_category+=+accomodation_category.acat_id
++++++++JOIN+
++++++++++++(+
+++++++++++++++SELECT+CEIL(RAND()*(SELECT+MAX(ac_id)+FROM+accomodation))+AS+ac_id
++++++++++++)+AS+Choices+
++++++++++++USING+(ac_id)
WHERE+++accomodation.ac_id+>=+Choices.ac_id+
++++++++AND+accomodation.ac_status+!=+'draft'
++++++++AND+accomodation_category.acat_slug+!=+'vendeglatohely'
++++++++AND+ac_images+!=+'b:0;'
LIMIT+1|code-block|syntax|javascript|2440855|要阅读更多关于ORDER+BY+RAND()的替代方案，您应该阅读这篇文章。|offset|length|style|CODE|2440856|entityMap|0|LINK|mutability|MUTABLE|url|http://jan.kneschke.de/projects/mysql/order-by-rand/^0|0|0|7|F|X|4|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@$I|X|J|Y|K|L]]|9|@$I|Z|J|10|1|11]]|A|$]]|$1|M|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

The solution for your dummy-example would be:

<pre><code>SELECT accomodation.ac_id,
 accomodation.ac_status,
 accomodation.ac_name,
 accomodation.ac_status,
 accomodation.ac_images
FROM accomodation,
 JOIN 
 accomodation_category 
 ON accomodation.ac_category = accomodation_category.acat_id
 JOIN 
 ( 
 SELECT CEIL(RAND()*(SELECT MAX(ac_id) FROM accomodation)) AS ac_id
 ) AS Choices 
 USING (ac_id)
WHERE accomodation.ac_id &gt;= Choices.ac_id 
 AND accomodation.ac_status != 'draft'
 AND accomodation_category.acat_slug != 'vendeglatohely'
 AND ac_images != 'b:0;'
LIMIT 1
</code></pre>

To read more about alternatives to <code>ORDER BY RAND()</code>, you should read <a href="http://jan.kneschke.de/projects/mysql/order-by-rand/" rel="nofollow">this article</a>.

blocks|key|2440891|text|我正在优化我的项目中的许多现有查询。Quassnoi的解决方案帮助我加快了查询的速度！但是，我发现很难将上述解决方案集成到所有查询中，特别是对于涉及多个大型表上的许多子查询的复杂查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2440892|因此，我使用的是一个较不优化的解决方案。从根本上说，它的工作方式与Quassnoi的解决方案相同。|2440893|SELECT++accomodation.ac_id,
++++++++accomodation.ac_status,
++++++++accomodation.ac_name,
++++++++accomodation.ac_status,
++++++++accomodation.ac_images
FROM++++accomodation,+accomodation_category
WHERE+++accomodation.ac_status+!=+'draft'
++++++++AND+accomodation.ac_category+=+accomodation_category.acat_id
++++++++AND+accomodation_category.acat_slug+!=+'vendeglatohely'
++++++++AND+ac_images+!=+'b:0;'
++++++++AND+rand()+<=+$size+*+$factor+/+[accomodation_table_row_count]
LIMIT+$size|code-block|syntax|javascript|2440894|$size+*+$factor+/+[accomodation_table_row_count]计算出选择随机行的概率。rand()将生成一个随机数。如果rand()较小或等于概率，则将选择该行。这有效地执行了随机选择以限制表的大小。因为它有可能返回小于定义的限制计数，我们需要增加概率，以确保我们选择了足够多的行。因此，我们将$size乘以一个$factor+(通常设置$factor+=+2，在大多数情况下有效)。最后，我们做了limit+$size|offset|length|style|BOLD|CODE|2440895|现在的问题是解决accomodation_table_row_count.如果我们知道表的大小，就可以硬编码表的大小。这将是跑得最快的，但显然这并不理想。如果您正在使用Myisam，获取表计数是非常有效的。由于我使用的是innodb，所以我只是在做一个简单的count%2Bselection。在你的例子中，它看起来是这样的：|2440896|SELECT++accomodation.ac_id,
++++++++accomodation.ac_status,
++++++++accomodation.ac_name,
++++++++accomodation.ac_status,
++++++++accomodation.ac_images
FROM++++accomodation,+accomodation_category
WHERE+++accomodation.ac_status+!=+'draft'
++++++++AND+accomodation.ac_category+=+accomodation_category.acat_id
++++++++AND+accomodation_category.acat_slug+!=+'vendeglatohely'
++++++++AND+ac_images+!=+'b:0;'
++++++++AND+rand()+<=+$size+*+$factor+/+(select+(SELECT+count(*)+FROM+`accomodation`)+*+(SELECT+count(*)+FROM+`accomodation_category`))
LIMIT+$size|2440897|棘手的部分是计算出正确的概率。如您所见，下面的代码实际上只计算粗略的临时表大小(事实上，太粗糙了！)：(select+(SELECT+count(*)+FROM+accomodation)+*+(SELECT+count(*)+FROM+accomodation_category))，但是您可以改进这个逻辑，以提供更接近的表大小近似。请注意，过度选择要比选择较少的行好。也就是说，如果设置的概率太低，就有可能没有选择足够的行。|2440898|这个解决方案比Quassnoi的解决方案运行得慢，因为我们需要重新计算表的大小。但是，我发现这种编码更易于管理。这是准确性%2B性能与编码复杂度之间的折衷。尽管如此，在大型表上，这仍然比Rand()的订单要快得多。|2440899|注意:如果查询逻辑允许，请在任何联接操作之前尽早执行随机选择。|2440900|entityMap^0|0|0|0|0|1C|0|1C|60|B|0|8|S|0|0|1F|2J|0|1M|6|1T|5|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|12|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|13|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|14|8|@$K|15|L|16|M|N]|$K|17|L|18|M|O]|$K|19|L|1A|M|O]]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1B|8|@$K|1C|L|1D|M|N]]|9|@]|A|$]]|$1|R|3|S|5|F|7|1E|8|@]|9|@]|A|$G|H]]|$1|T|3|U|5|6|7|1F|8|@$K|1G|L|1H|M|O]]|9|@]|A|$]]|$1|V|3|W|5|6|7|1I|8|@$K|1J|L|1K|M|N]|$K|1L|L|1M|M|N]]|9|@]|A|$]]|$1|X|3|Y|5|6|7|1N|8|@]|9|@]|A|$]]|$1|Z|3|-4|5|6|7|1O|8|@]|9|@]|A|$]]]|10|$]]

I am optimizing a lot of existing queries in my project. Quassnoi's solution has helped me speed up the queries a lot! However, I find it hard to incorporate the said solution in all queries, especially for complicated queries involving many subqueries on multiple large tables.

So I am using a less optimized solution. Fundamentally it works the same way as Quassnoi's solution.

<pre><code>SELECT accomodation.ac_id,
 accomodation.ac_status,
 accomodation.ac_name,
 accomodation.ac_status,
 accomodation.ac_images
FROM accomodation, accomodation_category
WHERE accomodation.ac_status != 'draft'
 AND accomodation.ac_category = accomodation_category.acat_id
 AND accomodation_category.acat_slug != 'vendeglatohely'
 AND ac_images != 'b:0;'
 AND rand() &lt;= $size * $factor / [accomodation_table_row_count]
LIMIT $size
</code></pre>

<code>$size * $factor / [accomodation_table_row_count]</code> works out the probability of picking a random row. The rand() will generate a random number. The row will be selected if rand() is smaller or equals to the probability. This effectively performs a random selection to limit the table size. Since there is a chance it will return less than the defined limit count, we need to increase probability to ensure we are selecting enough rows. Hence we multiply $size by a $factor (I usually set $factor = 2, works in most cases). Finally we do the <code>limit $size</code>

The problem now is working out the accomodation_table_row_count.
If we know the table size, we COULD hard code the table size. This would run the fastest, but obviously this is not ideal. If you are using Myisam, getting table count is very efficient. Since I am using innodb, I am just doing a simple count+selection. In your case, it would look like this:

<pre><code>SELECT accomodation.ac_id,
 accomodation.ac_status,
 accomodation.ac_name,
 accomodation.ac_status,
 accomodation.ac_images
FROM accomodation, accomodation_category
WHERE accomodation.ac_status != 'draft'
 AND accomodation.ac_category = accomodation_category.acat_id
 AND accomodation_category.acat_slug != 'vendeglatohely'
 AND ac_images != 'b:0;'
 AND rand() &lt;= $size * $factor / (select (SELECT count(*) FROM `accomodation`) * (SELECT count(*) FROM `accomodation_category`))
LIMIT $size
</code></pre>

The tricky part is working out the right probability. As you can see the following code actually only calculates the rough temp table size (In fact, too rough!): <code>(select (SELECT count(*) FROM accomodation) * (SELECT count(*) FROM accomodation_category))</code> But you can refine this logic to give a closer table size approximation. Note that it is better to OVER-select than to under-select rows. i.e. if the probability is set too low, you risk not selecting enough rows.

This solution runs slower than Quassnoi's solution since we need to recalculate the table size. However, I find this coding a lot more manageable. This is a trade off between accuracy + performance vs coding complexity. Having said that, on large tables this is still by far faster than Order by Rand().

Note: If the query logic permits, perform the random selection as early as possible before any join operations.

blocks|key|988749|text|(是的，我会因为这里没有足够的肉而受到责备，但你就不能做一天的素食主义者吗？)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|988750|案例:连续的AUTO_INCREMENT没有空白，返回1行|988751|案例:连续无间隙的AUTO_INCREMENT，10行|988752|案例:带间隙的AUTO_INCREMENT，返回1行|988753|Case:用于随机化的额外浮动列|988754|案例:+UUID或MD5列|988755|这5种情况对于大桌子来说是非常有效的。详细信息请参见http://mysql.rjweb.org/doc.php/random。|offset|length|988756|entityMap|0|LINK|mutability|MUTABLE|url|http://mysql.rjweb.org/doc.php/random^0|0|0|0|0|0|0|Q|11|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Z|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|10|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|11|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|12|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|13|8|@]|9|@$N|14|O|15|1|16]]|A|$]]|$1|P|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|Q|$R|$5|S|T|U|A|$V|W]]]]

(Yeah, I will get dinged for not having enough meat here, but can't you be a vegan for one day?)

Case: Consecutive AUTO_INCREMENT without gaps, 1 row returned 
Case: Consecutive AUTO_INCREMENT without gaps, 10 rows 
Case: AUTO_INCREMENT with gaps, 1 row returned 
Case: Extra FLOAT column for randomizing 
Case: UUID or MD5 column

Those 5 cases can be made very efficient for large tables. See <a href="http://mysql.rjweb.org/doc.php/random" rel="nofollow">my blog</a> for the details.

blocks|key|914998|text|function+getRandomRow(){
++++$id+=+rand(0,NUM_OF_ROWS_OR_CLOSE_TO_IT);
++++$res+=+getRowById($id);
++++if(!empty($res))
++++return+$res;
++++return+getRandomRow();
}

//rowid+is+a+key+on+table
function+getRowById($rowid=false){

+++return+db+select+from+table+where+rowid+=+$rowid;+
}|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|914999|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>function getRandomRow(){
 $id = rand(0,NUM_OF_ROWS_OR_CLOSE_TO_IT);
 $res = getRowById($id);
 if(!empty($res))
 return $res;
 return getRandomRow();
}

//rowid is a key on table
function getRowById($rowid=false){

 return db select from table where rowid = $rowid; 
}
</code></pre>

blocks|key|2440952|text|我的建议是添加一个具有UUID+(版本4)或其他随机值的列，该列具有唯一的索引(或者仅仅是主键)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2440953|然后，您可以简单地在查询时生成一个随机值，并选择大于生成值的行，按随机列排序。|2440954|确保如果收到的行数少于预期的行数，则重复查询而不使用大于子句(在结果集的“开始”处选择行)。|2440955|uuid+=+generateUUIDV4()

select+*+from+foo
where+uuid+>+:uuid
order+by+uuid
limit+42

if+count(results)+<+42+{
++select+*+from+foo
++order+by+uuid
++limit+:remainingResultsRequired
}|code-block|syntax|javascript|2440956|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

My recommendation is to add a column with a UUID (version 4) or other random value, with a unique index (or just the primary key).
Then you can simply generate a random value at query time and select rows greater than the generated value, ordering by the random column.
Make sure if you receive less than the expected number of rows, you repeat the query without the greater than clause (to select rows at the &quot;beginning&quot; of the result set).
<pre><code>uuid = generateUUIDV4()

select * from foo
where uuid &gt; :uuid
order by uuid
limit 42

if count(results) &lt; 42 {
 select * from foo
 order by uuid
 limit :remainingResultsRequired
}
</code></pre>

I'd like to optimize my queries so I look into <code>mysql-slow.log</code>.

Most of my slow queries contains <code>ORDER BY RAND()</code>. I cannot find a real solution to resolve this problem. Theres is a possible solution at <a href="http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/#comment-2184" rel="noreferrer">MySQLPerformanceBlog</a> but I don't think this is enough. On poorly optimized (or frequently updated, user managed) tables it doesn't work or I need to run two or more queries before I can select my <code>PHP</code>-generated random row.

Is there any solution for this issue?

A dummy example:

<pre><code>SELECT accomodation.ac_id,
 accomodation.ac_status,
 accomodation.ac_name,
 accomodation.ac_status,
 accomodation.ac_images
FROM accomodation, accomodation_category
WHERE accomodation.ac_status != 'draft'
 AND accomodation.ac_category = accomodation_category.acat_id
 AND accomodation_category.acat_slug != 'vendeglatohely'
 AND ac_images != 'b:0;'
ORDER BY
 RAND()
LIMIT 1
</code></pre>

How can i optimize MySQL's ORDER BY RAND() function?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我想优化我的查询，以便查看mysql-slow.log。我的大多数慢速查询都包含ORDER BY RAND()。我无法找到真正的解决办法来解决这个问题。在有一个可能的解决方案，但我认为这还不够。对于优化不佳(或经常更新，用户管理)的表，它不能工作，或者我需要运行两个或多个查询才能选择我的PHP-generated随机行...

问如何通过RAND()函数优化MySQL的顺序？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何通过RAND()函数优化MySQL的顺序？EN