mysql - 优化慢速 ORDER BY RAND() 查询

Question

我有一个使用 ORDER BY RAND() 的查询，但它花费的时间太长，而且随着数据的增长，情况变得更糟。

查询连接两个表并返回 5 个随机产品和每个产品的随机图像

表 1 - 产品

product_id - pk auto-inc
name 
description

数据

1 - product 1 - description
2 - product 2 - description

表 2 - 产品图片

image_id   - pk auto-inc
product_id - fk index
filename

数据

1 - 1 - product 1 image
2 - 1 - product 1 image
3 - 1 - product 1 image
4 - 2 - product 2 image

...

我读过 this和 this但找不到优化查询的方法，所以我寻求帮助。提前致谢。

Answer 1

ORDER BY RAND()很慢，因为 DBMS 必须读取所有行，对它们进行排序，只保留几行。因此，此查询的性能在很大程度上取决于表中的行数，并随着行数的增加而降低。

没有办法对其进行优化。

但是还有其他选择:

您可以通过执行 6 个查询来实现“获取 5 个随机行”:

• 获取表中的行数(你可以缓存这个)

• 使用 OFFSET <random offset from 0 to $number_of_rows-1> LIMIT 1 进行 5 次查询(即从某个随机偏移量读取并仅返回一行)

  例如:SELECT * FROM Products OFFSET 42 LIMIT 1 (注:暂不加入)

  此类查询速度非常快，运行时间几乎与表大小无关。

这应该比 ORDER BY RAND() 快很多 .

---

现在，为每个随机产品获取随机图像:

SELECT *
FROM (
    SELECT *
    FROM Products
    OFFSET 42 LIMIT 1
) p
JOIN ProductImages pi
ON   pi.product_id = p.id
ORDER BY RAND()
LIMIT 1

内部查询仍然很快，外部只对几行进行排序(假设每个产品的图片很少)，因此仍然可以使用 order by rand()。