MySQL ORDER BY RAND()函数何时排序? [英] When does MySQL ORDER BY RAND() function order?
问题描述
我已经了解了ORDER BY RAND()及其性能问题-这些仅适用于返回大型数据集的查询吗?例如，如果我有一个包含100,000行的表，并使用WHERE子句返回包含10条记录的数据集，然后使用ORDER BY RAND()LIMIT 1，那么在我的表被过滤为记录与WHERE子句匹配，从而具有可忽略的性能问题?
I've read about the ORDER BY RAND() and its performance problems -- do these only apply to queries that return large datasets? For example, if I have a table with 100,000 rows and return a dataset with 10 records using a WHERE clause and then use ORDER BY RAND() LIMIT 1, will this ORDER BY RAND() be applied AFTER my table has been filtered down to records matching the WHERE clause, and thus have negligible performance issues?
推荐答案
您是正确的，它将在使用WHERE，GROUP BY和HAVING减少行数之后应用ORDER BY.但是它将在LIMIT之前应用ORDER BY.
You're right, it will apply the ORDER BY after reducing the number of rows with WHERE, GROUP BY, and HAVING. But it will apply ORDER BY before LIMIT.
因此，如果您对行数进行了足够的过滤，那么可以，那么ORDER BY RAND()可能会达到您想要的效果，而不会影响性能.简单易读的代码有一个合法的好处.
So if you filter the number of rows down sufficiently, then yes, the ORDER BY RAND() may achieve what you want without a great performance impact. There's a legitimate benefit to code that is simple and easily readable.
当您思考时，问题就来了，查询应该将行减少到较小的数目，但是随着时间的流逝，随着数据的增长，需要排序的行数会再次变大.由于查询随后对排序结果执行LIMIT 10，因此隐藏了您要对500k行执行ORDER BY RAND()的事实.您只是发现性能神秘地变差.
The trouble comes when you think your query should reduce the rows to something small, but over time as your data grows, the number of rows it needs to sort becomes large again. Since your query then does LIMIT 10 on the sorted result hides the fact that you're performing ORDER BY RAND() on 500k rows. You just see performance mysteriously getting worse.
在我的书 SQL反模式中，我已经写了关于选择随机行的替代方法:避免数据库编程的陷阱，或者在其他有关堆栈溢出的答案中
I have written about alternative methods for picking a random row in my book SQL Antipatterns: Avoiding the Pitfalls of Database Programming, or in other answers here on Stack Overflow:
- Selecting random rows with MySQL
- randomizing large dataset
- quick selection of a random row from a large table in mysql
这篇关于MySQL ORDER BY RAND()函数何时排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
