logo标签列表

MySQL 从 60 万行中快速选择 10 行随机行

mysqlsqlrandom
552

如何编写最佳查询，从总数为 600k 的数据中随机选择 10 行？

19
这里有8种技巧，也许其中一种适合你的情况。请点击链接查看具体内容：http://mysql.rjweb.org/doc.php/random - Rick James
（实际上有5种技术--其中一些并不是改进。） - Rick James
29个回答
448

这篇文章处理了几种情况，从简单到间隙再到不均匀的间隙。

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列表中可能存在间隙。有关更高级的示例，请参阅文章。

58
如果你的ID之间可能存在较大的空隙，那么最低的ID被随机选择的概率要比高ID低得多。实际上，最大间隔后的第一个ID被选中的概率是最高的。因此，这并不符合随机的定义。 - lukeocodes
8
你如何获取10个不同的随机行？你需要将限制设置为10，然后使用mysqli_fetch_assoc($result)迭代10次吗？或者这10个结果不一定是可区分的吗？ - Adam
17
我理解“随机”是指每个结果有相等的机会。 ;) - lukeocodes
4
全文探讨了不平等分配和重复结果等问题。 - Bradd Szonye
2
具体而言，如果您在ID的开头有一个间隙，则第一个（最小/最大-最小）将被选中。对于这种情况，一个简单的调整是MAX()-MIN() * RAND + MIN()，速度不会太慢。 - Code Abominator
显示剩余11条评论
429
SELECT column FROM table
ORDER BY RAND()
LIMIT 10

这不是最高效的解决方案，但可行

173
ORDER BY RAND() 相对较慢。 - Mateusz Charytoniuk
7
Mateusz - 请证明一下，SELECT words, transcription, translation, sound FROM vocabulary WHERE menu_id=$menuId ORDER BY RAND() LIMIT 10 的时间是0.0010，在不加限制的情况下，它需要0.0012的时间（在该表中有3500个单词）。 - Arthur Kushman
38
3500个单词并不算太多；问题在于一旦MySQL读取每个记录后，必须实际排序所有记录，此操作在突破某个点后会快速增长。一旦此操作涉及到硬盘，你就能感受到差别。 - Ja͢ck
21
我不想重复，但是那是全表扫描。在大型表格中，这需要很多时间和内存，并且可能导致在磁盘上创建临时表格的 & 操作，这是非常慢的。 - matt
14
当我在2010年参加Facebook的面试时，他们问我如何从一个未知大小的大文件中一次性读取并选择一条随机记录。一旦你想出了一个方法，很容易将其推广到选择多条记录。因此，对整个文件进行排序是荒谬的。与此同时，它非常方便。我刚刚使用这种方法从一个包含100万+行的表中选出了10行随机记录。确实，我必须等待一段时间；但我只是想得到一个想法，这个表中典型的行是什么样子的... - Sergey Orshanskiy
显示剩余2条评论
72

具有优越性能并适用于间隔的简单查询：

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： Simple Explain

SELECT * FROM tbl AS t1 JOIN (SELECT id FROM tbl ORDER BY RAND() LIMIT 10) as t2 ON t1.id=t2.id enter image description here

加权版本: https://dev59.com/rHE95IYBdhLWcg3wMa91#41577458

5
衍生表仍需扫描和排序整张表。 - Rick James
17

我在一个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);
17
鉴于我的数据表拥有超过1400万条记录，使用ORDER BY RAND()的查询速度非常慢。 - Fabrizio
6
针对你的情况 - 40万行数据，你可以使用简单的 "ORDER BY rand()"。你的三个查询方法是无用的。你可以将其重写为 "SELECT id, url FROM pages WHERE id IN (SELECT id FROM pages ORDER BY rand() LIMIT 10)"。 - Roman Podlinov
4
你的技术仍在执行表扫描。使用“FLUSH STATUS; SELECT...; SHOW SESSION STATUS LIKE 'Handler％';”来查看它。 - Rick James
4
请尝试在每秒200个请求的网页中运行该查询。并发会让你崩溃。 - Marki555
@RomanPodlinov 相对于普通的 ORDER BY RAND()，这种方法的好处在于它仅对 ID 进行排序（而不是整行），因此临时表更小，但仍需要对所有 ID 进行排序。 - Marki555
显示剩余3条评论
14

来自书籍：

使用偏移量选择随机行

另一种避免前面提到的问题的技术是计算数据集中的行数并返回介于 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();

当您无法假定连续键值且需要确保每一行被选择的机会相等时，请使用此解决方案。

2
对于非常大的表，SELECT count(*) 变得很慢。 - Hans Z
OFFSET 必须跳过这么多行。因此，这个“解决方案”平均花费 1.5*N，其中 N 是表中的行数。 - Rick James
13

这是一个非常简单和单行的查询。

SELECT * FROM Table_Name ORDER BY RAND() LIMIT 0,10;
29
若表格庞大，使用 order by rand() 会导致速度非常慢。 - evilReiko
8
有时候，如果我想保持简单，就会接受缓慢。 - user285594
如果表很大，应该在表上应用索引。 - Muhammad Azeem
5
这里索引是无法帮助的。索引对于某些特定的操作非常有用，但此查询并不适用于索引优化。 - Andrew
11

如果你的键没有间隙并且全部都是数字，那么可以计算随机数并选择这些行。但这种情况可能不会发生。

所以一种解决方案是：

SELECT * FROM table WHERE key >= FLOOR(RAND()*MAX(id)) LIMIT 1

该方法基本上能够确保你在键的范围内获得一个随机数，然后选择下一个最接近的大于此数的键。你需要执行此操作10次。

但是这并不是真正的随机数生成，因为你的键很可能分布不均匀。

这是一个非常棘手的问题，要满足所有要求并不容易解决。如果你真的需要获取10个随机行，MySQL的rand()函数是最好的选择。

不过，还有另一种解决方案，速度很快，但在随机性方面存在一些权衡，但也许更适合你。在这里阅读相关内容：如何优化MySQL的ORDER BY RAND()函数？

问题在于你需要多么随机。如果你能详细说明，我可以给出一个好的解决方案。

例如，我曾经和一家公司合作，他们需要极快的绝对随机性。他们最终选择了预先用降序选取的随机值填充数据库，并再次设置为不同的随机值。

如果你几乎不更新，你也可以填写一个递增的id，这样就没有间隙，只需在选择前计算随机键即可...具体情况取决于使用场景！

嗨，乔。在这种情况下，键不应该缺少间隙，但随着时间的推移，这可能会改变。虽然你的答案是有效的，但它将生成随机的10行（如果我写了limit 10），这些行是连续的，而我想要更多的随机性。:)谢谢。 - Francisc
如果您需要10个，请使用某种联合方式生成10个唯一的行。 - johno
那就是我说的，你需要执行它10次。将其与union合并是将其放入一个查询中的一种方法。请看我2分钟前的补充说明。 - The Surrican
1
@TheSurrican，这个解决方案看起来很酷，但是极其有缺陷。只要插入一个非常大的“Id”，所有随机查询都会返回那个“Id”。 - Pacerier
1
FLOOR(RAND()*MAX(id)) 偏向于返回较大的 id。 - Rick James
上一个建议很好。我们使用递增的ID，因此在选择之前计算随机键实际上对我们有用。很容易忘记SQL查询可能非常大，因为我们经常努力使它们变小。 - Praxiteles
10

如何从表格中选择随机行：

参考链接： 在MySQL中选择随机行

相较于“全表扫描”，一个快速的提升方法是使用索引来获取随机 id。

SELECT *
FROM random, (
        SELECT id AS sid
        FROM random
        ORDER BY RAND( )
        LIMIT 10
    ) tmp
WHERE random.id = tmp.sid;
1
这对于MyISAM有所帮助，但对于InnoDB没有帮助（假设id是聚集的“PRIMARY KEY”）。 - Rick James
1
内部查询执行了全表扫描并对结果进行排序。实际上，那个链接中的大多数（也许全部）技术都涉及全表扫描。 - Rick James
6

所有最好的答案已经发布了（主要是那些引用链接http://jan.kneschke.de/projects/mysql/order-by-rand/的答案）。

我想强调另一个加速可能性-缓存。想想为什么你需要获取随机行。可能你想在网站上显示一些随机帖子或随机广告。如果你每秒获得100个请求，真的需要每个访问者都获得随机行吗？通常将这些X个随机行缓存1秒钟（甚至10秒钟）完全没有问题。如果在同一秒内有100个独立的访问者获得相同的随机帖子，也没有关系，因为下一秒会有另外100个访问者获得不同的帖子。

使用这种缓存时，您可以使用一些较慢的解决方案来获取随机数据，因为它只会从MySQL中检索一次，而不管您的请求数。

6

我改进了@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)

让我解释一下正在发生的事情。
1. @max := (SELECT MAX(id) FROM table) 我正在计算并保存最大值。对于非常大的表格，在需要行时每次计算MAX(id)会有轻微的开销。
2. SELECT FLOOR(rand() * @max) + 1 as rand) 获取一个随机id。
3. SELECT id FROM table INNER JOIN (...) on id > rand LIMIT 1 这填补了间隙。基本上，如果在间隙中随机选择一个数字，它将只选择下一个id。假设间隙是均匀分布的，这不应该是一个问题。
使用union可以帮助您将所有内容放入一个查询中，以便避免执行多个查询。它还可以让您节省计算MAX(id)的开销。根据您的应用程序，这可能非常重要或非常不重要。
请注意，这仅获取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
我需要30个随机记录，所以我应该在查询中将“LIMIT 1”改为“LIMIT 30”吗？ - Muhammad Hassaan
@Hassaan，你不应该这样做，将LIMIT 1更改为LIMIT 30会从表中的随机点连续获取30条记录。相反，你应该在中间部分有30个(SELECT id FROM ....的副本。 - Hans Z
我已经尝试过，但似乎没有 Riedsio 的答案更有效率。我使用 PHP 7.0.22 和 MariaDB 在 CentOS 7 上进行了每秒 500 次的页面访问测试，使用 Riedsio 的答案比你的答案多获得了 500 多个成功响应。 - Muhammad Hassaan
1
@Hassaan riedsio的答案只提供了1行，而这个答案可以给你n行，并且减少了查询时的I/O开销。你可能能够更快地获取行，但会增加系统负载。 - Hans Z
选择FLOOR(rand() * @max) + 1 as rand，如果表中包含一个id为1的记录。Floor将是0，rand将是0+1=1。id > rand将不会返回任何结果（1>1）如果随机数返回0.999*****。例如表中的最大id为100。Floor将是99，rand将是99+1=100。id > rand将不会返回任何结果（100>100） - Einārs Ozols
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接