mysql – 优化慢速ORDER BY RAND()查询教程

时间: 2019-10-07

阅读: 157

我有一个使用ORDER BY RAND()的查询,但它需要太长时间,并且随着数据的增长而变得越来越糟糕.

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

表1 – 产品

product_id - pk auto-inc
name 
description

数据

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

表2 – ProductImages

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,但找不到优化查询的方法,所以我正在寻求帮助.
提前致谢.

解决方法:

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

没有办法优化它.

但是有其他选择：

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

获取表中的行数(可以缓存此行)
使用OFFSET<随机偏移从0到$number\_of\_rows-1>进行5次查询LIMIT 1(即从某个随机偏移量中读取并返回一行)

例如：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

内部查询仍然很快,外部只排序几行(假设每个产品的图像很少),因此仍然可以使用rand()的顺序.