从两个表中随机选择一行MySQL记录并进行JOIN

Question

我已经搜索了几天，但无法找到任何可以缩短运行查询所需时间的解决方案。

我有两张表：

"product_db":  
unique_id - [index] 
image 
url_title 
status - [index]

"product_page"
id
product_unique_id - [index]
page_id - [index]

我要选择的是product_db中状态为“在线”的产品中随机的一张图片，并且该产品必须在页面ID = 3中。product_db中有超过90,000个产品，而product_page中有超过150,000行。

我现在使用的查询是：

SELECT image FROM product_db a, product_page b WHERE b.page_id = 3 AND a.status = 'Online' AND a.unique_id = b.product_unique_id ORDER BY RAND() LIMIT 1

这个查询大约需要2.3秒才能运行。对于一个网页来说，这是相当长的时间。

我尝试了一些其他的查询，先从页面ID = 3的product_page中返回一个随机的行，然后再查询product_db（这确实减少了它所需的时间），但问题是我无法比较该产品是否“在线”。

- James

Answer 1

你的问题在于排序会拖慢速度。不要使用随机排序，而是选择一个随机的product_db.unique_id。

在你的查询中，用以下语句替换ORDER BY RAND()：

AND product_db.unique_id >= ROUND(RAND()*(SELECT MAX(unique_id) FROM product_db))

如果unique_id已从数据库中删除，则使用>=而不是=。这种方法得到的结果不像使用rand函数随机，但查询速度更快。如果需要的话，可以连续运行多个=查询直到找到结果，这仍然比对所有结果进行排序要快得多。

使用显式JOIN语句：

SELECT product_db.image
FROM product_db
JOIN product_page ON product_db.unique_id = product_page.product_unique_id
WHERE product_page.page_id = 3 
AND product_db.status = 'Online' 
AND product_db.unique_id >= ROUND(RAND()*(SELECT MAX(unique_id) FROM product_db))
LIMIT 1

- webbiedave

---

SELECT image FROM product_db a, product_page b WHERE b.page_id = 3 AND a.status = '在线' AND a.unique_id = b.product_unique_id AND a.unique_id >= ROUND(RAND()*MAX(a.id)) - James

#1111 - 使用组函数的方式无效。 我有什么遗漏吗？ - James

我得到了与之前相同的错误：#1111 - 无效的分组函数使用。 我在某个地方读到过，mysql会生成此错误，因为“聚合函数（如COUNT、MAX等）实际上直到查询的其余部分被评估之前才会计算”。 http://bytes.com/topic/mysql/answers/691615-invalid-use-group-function#post2749228 - James

詹姆斯，我已編輯，因為聚合函數需要放在單獨的查詢中。 - webbiedave

Answer 2

问题在于MySQL没有办法选择随机行，因此它会检索所有产品并对它们进行排序（没有必要）。

您可以编写一个存储过程，在MIN和MAX之间选择一个随机的unique_id，然后尝试获取该产品，直到找到为止。您可以限制尝试次数。

- AndreKR

---

嗨，感谢您的建议。我对存储过程不是很熟悉，但我一定会去了解一下！:) 再次感谢 - James

不要， 请使用Alex和Webbiedave指导您的方法。这样更容易，只需要确定性时间。 - AndreKR