优化慢的ORDER BY RAND()查询 [英] Optimizing slow ORDER BY RAND() query

问题描述

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

I have a query that is using ORDER BY RAND() but it takes too long and it's getting worse as data is growing.

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

The query joins two tables and it returns 5 random products and a random image of each product

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

...

我已阅读此和此，但是找不到优化查询的方法，所以我寻求帮助. 预先感谢.

I've read this and this but cannot find a way to optimize the query so i'm asking for help. Thanks in advance.

推荐答案

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

ORDER BY RAND() is slow because the DBMS has to read all rows, sort them all, just to keep only a few rows. So the performance of this query heavily depends on the number of rows in the table, and decreases as the number of rows increase.

没有办法对其进行优化.

There is no way to optimize that.

但是，还有其他选择:

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

You can implement "get 5 random rows" by doing 6 queries:

• 获取表中的行数(您可以缓存此行)

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

• get number of rows in table (you can cache this one)

• do 5 queries with OFFSET <random offset from 0 to $number_of_rows-1> LIMIT 1 (i.e. read and return only one row from some random offset)

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

For example: SELECT * FROM Products OFFSET 42 LIMIT 1 (note: without joining, for now)

这样的查询非常快，并且运行时间几乎与表的大小无关.

Such queries are very fast and run in a time virtually independent from the table size.

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

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

Now, to get a random Image for each random Product:

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()的订单.

The inner query is still fast, and the outer is only sorting few rows (assuming there are few images per product), and so can still use order by rand().

这篇关于优化慢的ORDER BY RAND()查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！