MYSQL:通过 rand() 查询订单非常慢 [英] MYSQL: Query order by rand() very slow

问题描述

我必须从表中随机选取 30 条记录，除了查询使用一秒，如果内容被许多用户显示，这会降低 mysql 的速度.这是查询:

I have to pick 30 random records from a table, except that the query uses one second, and this slows mysql if the content is displayed by many users. This is the query:

SELECT relationship, COUNT(id) AS number FROM FR_user_friends GROUP BY relationship ORDER BY rand() LIMIT 30

你知道如何加速这个查询吗?谢谢.

Do you know how to speed up this query? Thank you.

如果我删除 rand() 查询速度很快.我们必须找到 rand() 的替代方案

If I remove rand() the query is fast. We have to find an alternative for rand()

推荐答案

ORDER BY RAND() 使引擎为所有行生成随机值，所以如果你想从一个大表中选择几行，它给出了非常糟糕的表现.

ORDER BY RAND() causes the engine to generate random values for all rows, so if you want to select a few rows from a large table, it gives very bad performance.

例如，您可以在 php 中生成 30 个范围 [1, 最大行 ID] 的随机值，然后选择行 ID 大于或等于 LIMIT 1<随机值的第一行/代码>.

You could for example generate 30 random values in php in the range [1, maximum row-id] and select the first row with a row-id that is bigger or equal to the random value with LIMIT 1.

在 我如何优化 MySQL 的 ORDER BY RAND() 函数?(但有些也不是微不足道的).

SQL-only ways to deal with this you find in How can i optimize MySQL's ORDER BY RAND() function? (but some are not trivial as well).

这篇关于MYSQL:通过 rand() 查询订单非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！