mysql - 如何优化 MySQL 查询以便 WHERE 子句中的选定值仅计算一次？

Question

我需要以高效的方式从我的表中随机选择 10 行。

我发现以下内容效果很好(在查询之后，我只是从查询中获得的 10 到 30 个随机元素中选择 10 个 PHP):

   SELECT * FROM product WHERE RAND() <= (SELECT 20 / COUNT(*) FROM product)

然而，子查询虽然相对便宜，但它是为表中的每一行计算的。我怎样才能防止这种情况发生？带变量？加入？

谢谢!

Answer 1

一个变量就可以做到。像这样:

SELECT @myvar := (SELECT 20 / COUNT(*) FROM product);
SELECT * FROM product WHERE RAND() <= @myvar;

或者，来自 MySql math functions doc :

You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. However, you can retrieve rows in random order like this:

mysql> SELECT * FROM tbl_name ORDER BY
> RAND();

ORDER BY RAND() combined with LIMIT is useful for selecting a random sample from a set of rows:

mysql> SELECT * FROM table1, table2
> WHERE a=b AND c<d -> ORDER BY RAND()
> LIMIT 1000;

RAND() is not meant to be a perfect random generator. It is a fast way to generate random numbers on demand that is portable between platforms for the same MySQL version.