如何编写最佳查询,从总数为 600k 的数据中随机选择 10 行?
这篇文章处理了几种情况,从简单到间隙再到不均匀的间隙。
http://jan.kneschke.de/projects/mysql/order-by-rand/
对于大多数通用情况,以下是实现方法:
SELECT name
FROM random AS r1 JOIN
(SELECT CEIL(RAND() *
(SELECT MAX(id)
FROM random)) AS id)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1
这假设ID的分布是均等的,并且ID列表中可能存在间隙。有关更高级的示例,请参阅文章。
mysqli_fetch_assoc($result)
迭代10次吗?或者这10个结果不一定是可区分的吗? - AdamSELECT column FROM table
ORDER BY RAND()
LIMIT 10
这不是最高效的解决方案,但可行
ORDER BY RAND()
相对较慢。 - Mateusz CharytoniukSELECT words, transcription, translation, sound FROM vocabulary WHERE menu_id=$menuId ORDER BY RAND() LIMIT 10
的时间是0.0010,在不加限制的情况下,它需要0.0012的时间(在该表中有3500个单词)。 - Arthur Kushman具有优越性能并适用于间隔的简单查询:
SELECT * FROM tbl AS t1 JOIN (SELECT id FROM tbl ORDER BY RAND() LIMIT 10) as t2 ON t1.id=t2.id
这个针对一个20万行的表格的查询只需0.08秒,而普通版本(SELECT * FROM tbl ORDER BY RAND() LIMIT 10)在我的电脑上需要0.35秒。
这样快是因为排序阶段只使用了索引ID列。您可以在解释中看到这种行为:
SELECT * FROM tbl ORDER BY RAND() LIMIT 10:
SELECT * FROM tbl AS t1 JOIN (SELECT id FROM tbl ORDER BY RAND() LIMIT 10) as t2 ON t1.id=t2.id
我在一个400K条记录、2GB大小的MySQL数据库中选择10个随机行,即使使用一台慢速CPU,也可以获得快速查询(大约0.5秒)。这是我的代码:MySQL中快速选择随机行
$time= microtime_float();
$sql='SELECT COUNT(*) FROM pages';
$rquery= BD_Ejecutar($sql);
list($num_records)=mysql_fetch_row($rquery);
mysql_free_result($rquery);
$sql="SELECT id FROM pages WHERE RAND()*$num_records<20
ORDER BY RAND() LIMIT 0,10";
$rquery= BD_Ejecutar($sql);
while(list($id)=mysql_fetch_row($rquery)){
if($id_in) $id_in.=",$id";
else $id_in="$id";
}
mysql_free_result($rquery);
$sql="SELECT id,url FROM pages WHERE id IN($id_in)";
$rquery= BD_Ejecutar($sql);
while(list($id,$url)=mysql_fetch_row($rquery)){
logger("$id, $url",1);
}
mysql_free_result($rquery);
$time= microtime_float()-$time;
logger("num_records=$num_records",1);
logger("$id_in",1);
logger("Time elapsed: <b>$time segundos</b>",1);
ORDER BY RAND()
的查询速度非常慢。 - FabrizioORDER BY RAND()
,这种方法的好处在于它仅对 ID 进行排序(而不是整行),因此临时表更小,但仍需要对所有 ID 进行排序。 - Marki555来自书籍:
使用偏移量选择随机行
另一种避免前面提到的问题的技术是计算数据集中的行数并返回介于 0 和这个数之间的随机数。然后在查询数据集时使用此数字作为偏移量。
$rand = "SELECT ROUND(RAND() * (SELECT COUNT(*) FROM Bugs))";
$offset = $pdo->query($rand)->fetch(PDO::FETCH_ASSOC);
$sql = "SELECT * FROM Bugs LIMIT 1 OFFSET :offset";
$stmt = $pdo->prepare($sql);
$stmt->execute( $offset );
$rand_bug = $stmt->fetch();
当您无法假定连续键值且需要确保每一行被选择的机会相等时,请使用此解决方案。
SELECT count(*)
变得很慢。 - Hans ZOFFSET
必须跳过这么多行。因此,这个“解决方案”平均花费 1.5*N,其中 N 是表中的行数。 - Rick James这是一个非常简单和单行的查询。
SELECT * FROM Table_Name ORDER BY RAND() LIMIT 0,10;
order by rand()
会导致速度非常慢。 - evilReiko如果你的键没有间隙并且全部都是数字,那么可以计算随机数并选择这些行。但这种情况可能不会发生。
所以一种解决方案是:
SELECT * FROM table WHERE key >= FLOOR(RAND()*MAX(id)) LIMIT 1
该方法基本上能够确保你在键的范围内获得一个随机数,然后选择下一个最接近的大于此数的键。你需要执行此操作10次。
但是这并不是真正的随机数生成,因为你的键很可能分布不均匀。
这是一个非常棘手的问题,要满足所有要求并不容易解决。如果你真的需要获取10个随机行,MySQL的rand()函数是最好的选择。
不过,还有另一种解决方案,速度很快,但在随机性方面存在一些权衡,但也许更适合你。在这里阅读相关内容:如何优化MySQL的ORDER BY RAND()函数?
问题在于你需要多么随机。如果你能详细说明,我可以给出一个好的解决方案。
例如,我曾经和一家公司合作,他们需要极快的绝对随机性。他们最终选择了预先用降序选取的随机值填充数据库,并再次设置为不同的随机值。
如果你几乎不更新,你也可以填写一个递增的id,这样就没有间隙,只需在选择前计算随机键即可...具体情况取决于使用场景!
FLOOR(RAND()*MAX(id))
偏向于返回较大的 id。 - Rick James如何从表格中选择随机行:
参考链接: 在MySQL中选择随机行
相较于“全表扫描”,一个快速的提升方法是使用索引来获取随机 id。
SELECT *
FROM random, (
SELECT id AS sid
FROM random
ORDER BY RAND( )
LIMIT 10
) tmp
WHERE random.id = tmp.sid;
所有最好的答案已经发布了(主要是那些引用链接http://jan.kneschke.de/projects/mysql/order-by-rand/的答案)。
我想强调另一个加速可能性-缓存。想想为什么你需要获取随机行。可能你想在网站上显示一些随机帖子或随机广告。如果你每秒获得100个请求,真的需要每个访问者都获得随机行吗?通常将这些X个随机行缓存1秒钟(甚至10秒钟)完全没有问题。如果在同一秒内有100个独立的访问者获得相同的随机帖子,也没有关系,因为下一秒会有另外100个访问者获得不同的帖子。
使用这种缓存时,您可以使用一些较慢的解决方案来获取随机数据,因为它只会从MySQL中检索一次,而不管您的请求数。
我改进了@Riedsio的答案。这是我在一个有间隔的大型均匀分布表上找到的最有效的查询(在从具有> 2.6B行的表中获取1000个随机行进行测试)。
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max := (SELECT MAX(id) FROM table)) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1)
@max := (SELECT MAX(id) FROM table)
我正在计算并保存最大值。对于非常大的表格,在需要行时每次计算MAX(id)
会有轻微的开销。SELECT FLOOR(rand() * @max) + 1 as rand)
获取一个随机id。SELECT id FROM table INNER JOIN (...) on id > rand LIMIT 1
这填补了间隙。基本上,如果在间隙中随机选择一个数字,它将只选择下一个id。假设间隙是均匀分布的,这不应该是一个问题。MAX(id)
的开销。根据您的应用程序,这可能非常重要或非常不重要。SELECT t.id, t.name -- etc, etc
FROM table t
INNER JOIN (
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max := (SELECT MAX(id) FROM table)) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1) UNION
(SELECT id FROM table INNER JOIN (SELECT FLOOR(RAND() * @max) + 1 as rand) r on id > rand LIMIT 1)
) x ON x.id = t.id
ORDER BY t.id
LIMIT 1
更改为LIMIT 30
会从表中的随机点连续获取30条记录。相反,你应该在中间部分有30个(SELECT id FROM ....
的副本。 - Hans ZRiedsio
的答案更有效率。我使用 PHP 7.0.22 和 MariaDB 在 CentOS 7 上进行了每秒 500 次的页面访问测试,使用 Riedsio
的答案比你的答案多获得了 500 多个成功响应。 - Muhammad Hassaan