MySQL快速从600K行中随机选择10行

我如何才能最好地编写一个查询，从总共600k中随机选择10行?

551368 次浏览
小开
最佳答案

一个伟大的职位处理几个情况，从简单，到差距，到不均匀与差距。

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

小开

如果键之间没有间隙而且都是数字你可以计算随机数然后选择这些行。但事实可能并非如此。

所以一种解决方案是:

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

这将确保你在键的范围内得到一个随机数然后你选择下一个更大的最佳值。 你必须这样做10次。

然而，这并不是随机的，因为你的钥匙很可能不是均匀分布的。

这真的是一个大问题，不容易解决满足所有的要求，MySQL的rand()是最好的，如果你真的想要10个随机行。

然而，还有另一种解决方案，它速度很快，但涉及到随机性时也需要权衡，但可能更适合你。点击这里阅读:如何优化mysql的ORDER BY RAND()函数?

问题是你需要它有多随机。

你能多解释一下吗?这样我才能给你一个好的解决办法。

例如，我合作的一家公司有一个解决方案，他们需要非常快的绝对随机性。最后，他们用随机值预填充数据库，这些随机值是从降序选择的，然后再次设置为不同的随机值。

如果你几乎没有更新，你也可以填充一个递增的id，这样你就没有间隙，只是可以在选择之前计算随机键…这取决于用例!

小开

我得到快速查询(大约0.5秒)与缓慢的cpu，在一个400K寄存器MySQL数据库非缓存2Gb大小中选择10个随机行。看这里我的代码:快速选择随机行在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);
小开

我使用了Riedsio发布的http://jan.kneschke.de/projects/mysql/order-by-rand/(我使用了返回一个或多个随机值的存储过程的情况):

   DROP TEMPORARY TABLE IF EXISTS rands;
CREATE TEMPORARY TABLE rands ( rand_id INT );


loop_me: LOOP
IF cnt < 1 THEN
LEAVE loop_me;
END IF;


INSERT INTO rands
SELECT r1.id
FROM random AS r1 JOIN
(SELECT (RAND() *
(SELECT MAX(id)
FROM random)) AS id)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1;


SET cnt = cnt - 1;
END LOOP loop_me;
在文章中，他解决了缺口问题在ids导致不是随机的结果维护表(使用触发器等…参见文章); 我通过向表中添加另一列来解决这个问题，用连续的数字填充，从1开始(< em >编辑:< / em >此列被添加到运行时由子查询创建的临时表中，不影响永久表):

   DROP TEMPORARY TABLE IF EXISTS rands;
CREATE TEMPORARY TABLE rands ( rand_id INT );


loop_me: LOOP
IF cnt < 1 THEN
LEAVE loop_me;
END IF;


SET @no_gaps_id := 0;


INSERT INTO rands
SELECT r1.id
FROM (SELECT id, @no_gaps_id := @no_gaps_id + 1 AS no_gaps_id FROM random) AS r1 JOIN
(SELECT (RAND() *
(SELECT COUNT(*)
FROM random)) AS id)
AS r2
WHERE r1.no_gaps_id >= r2.id
ORDER BY r1.no_gaps_id ASC
LIMIT 1;


SET cnt = cnt - 1;
END LOOP loop_me;

在文章中，我可以看到他花了很大的精力来优化代码;我不知道我的改变是否/有多大影响性能，但对我来说非常好。

小开 
SELECT column FROM table
ORDER BY RAND()
LIMIT 10

这不是有效的解决方案，但确实有效

小开

我是这样做的:

select *
from table_with_600k_rows
where rand() < 10/600000
limit 10

我喜欢它，因为它不需要其他表，写起来很简单，执行起来非常快。

小开

我想这是最好的办法了。

SELECT id, id * RAND( ) AS random_no, first_name, last_name
FROM user
ORDER BY random_no
小开

如何从表中随机选择行:

从这里< p >: # EYZ0 < / p >

对“表扫描”的快速改进是使用索引来获取随机id。

SELECT *
FROM random, (
SELECT id AS sid
FROM random
ORDER BY RAND( )
LIMIT 10
) tmp
WHERE random.id = tmp.sid;
小开

如果你只有一个读请求

将@redsio的答案与一个临时表结合起来(600K并不是很多):

DROP TEMPORARY TABLE IF EXISTS tmp_randorder;
CREATE TABLE tmp_randorder (id int(11) not null auto_increment primary key, data_id int(11));
INSERT INTO tmp_randorder (data_id) select id from datatable;

然后用一个@redsios的版本回答:

SELECT dt.*
FROM
(SELECT (RAND() *
(SELECT MAX(id)
FROM tmp_randorder)) AS id)
AS rnd
INNER JOIN tmp_randorder rndo on rndo.id between rnd.id - 10 and rnd.id + 10
INNER JOIN datatable AS dt on dt.id = rndo.data_id
ORDER BY abs(rndo.id - rnd.id)
LIMIT 1;

如果表比较大，可以先筛选第一部分:

INSERT INTO tmp_randorder (data_id) select id from datatable where rand() < 0.01;

如果你有很多读请求

  1. 你可以将表tmp_randorder持久化，称它为datatable_idlist。在特定的时间间隔(天，小时)重新创建该表，因为它也会有洞。如果你的桌子真的很大，你还可以把洞补上

    select l.data_id as whole 从datatable_idlist l 左连接数据表dt在dt上。Id = l.data_id dt的地方。Id为null;

  2. 给你的数据集一个random_sortorder列，可以直接在数据表中，也可以在一个持久的额外表datatable_sortorder中。索引该列。在应用程序中生成一个随机值(我将其称为$rand)。

    select l.*
from datatable l
order by abs(random_sortorder - $rand) desc
limit 1;

This solution discriminates the 'edge rows' with the highest and the lowest random_sortorder, so rearrange them in intervals (once a day).

小开

我需要一个查询从一个相当大的表中返回大量随机行。这是我想到的。首先获取最大记录id:

SELECT MAX(id) FROM table_name;

然后将该值代入:

SELECT * FROM table_name WHERE id > FLOOR(RAND() * max) LIMIT n;

其中max是表中的最大记录id, n是结果集中需要的行数。假设在记录id中没有空白，尽管我怀疑它是否会影响结果(虽然还没有尝试过)。我还创建了这个更通用的存储过程;传入要返回的表名和行数。我在Windows 2008上运行MySQL 5.5.38, 32GB，双3GHz E5450，在一个有17,361,264行的表上，它在~是相当一致的。03秒/ ~11秒返回1,000,000行。(时间来自MySQL Workbench 6.1;你也可以在第二个选择语句中使用CEIL而不是FLOOR，这取决于你的偏好)

DELIMITER $$


USE [schema name] $$


DROP PROCEDURE IF EXISTS `random_rows` $$


CREATE PROCEDURE `random_rows`(IN tab_name VARCHAR(64), IN num_rows INT)
BEGIN


SET @t = CONCAT('SET @max=(SELECT MAX(id) FROM ',tab_name,')');
PREPARE stmt FROM @t;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


SET @t = CONCAT(
'SELECT * FROM ',
tab_name,
' WHERE id>FLOOR(RAND()*@max) LIMIT ',
num_rows);


PREPARE stmt FROM @t;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
$$

然后

CALL [schema name].random_rows([table name], n);
小开

使用这个查询:

select floor(RAND() * (SELECT MAX(key) FROM table)) from table limit 10

查询时间:0.016秒

小开

使用下面的简单查询从表中获取随机数据。

SELECT user_firstname ,
COUNT(DISTINCT usr_fk_id) cnt
FROM userdetails
GROUP BY usr_fk_id
ORDER BY cnt ASC
LIMIT 10
小开

这里有一个改变游戏规则的方法，可能对许多人有帮助;

我有一个有200k行，使用顺序id的表，我需要选择N随机行，所以我选择根据表中最大的ID生成随机值，我创建了这个脚本来找出哪个是最快的操作:

logTime();
query("SELECT COUNT(id) FROM tbl");
logTime();
query("SELECT MAX(id) FROM tbl");
logTime();
query("SELECT id FROM tbl ORDER BY id DESC LIMIT 1");
logTime();

结果如下:

  • 计数:36.8418693542479毫秒
  • Max: 0.241041183472毫秒
  • 订购:0.216960906982 ms
根据这个结果，order desc是得到最大id的最快操作，
下面是我对这个问题的回答:

SELECT GROUP_CONCAT(n SEPARATOR ',') g FROM (
SELECT FLOOR(RAND() * (
SELECT id FROM tbl ORDER BY id DESC LIMIT 1
)) n FROM tbl LIMIT 10) a


...
SELECT * FROM tbl WHERE id IN ($result);

供参考:为了从一个200k表中随机获得10行，我花费了1.78 女士 (包括PHP端的所有操作)

小开

从书中:

使用偏移量选择随机行

这是另一种避免前面发现的问题的技术 替代方法是统计数据集中的行数并返回一个随机值 0到计数之间的数字。然后用这个数字作为抵消 查询数据集

. txt 
$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();

当不能假定连续键值和时使用此解决方案

.

.

.

.
小开

所有最好的答案都已经贴出来了(主要是那些引用了http://jan.kneschke.de/projects/mysql/order-by-rand/链接的)。

我想指出另一种加速的可能性——缓存。想想为什么你需要随机的行。也许你想在网站上显示一些随机的帖子或随机的广告。如果你得到100个请求/秒，是否真的需要每个访问者得到随机的行?通常，将这X个随机行缓存1秒(甚至10秒)是完全没问题的。如果在同一秒内有100个访问者获得相同的随机帖子，这并不重要，因为下一秒另100个访问者将获得不同的帖子集。

当使用这种缓存时，你也可以使用一些较慢的解决方案来获取随机数据，因为不管你的req/s如何，它每秒只会从MySQL中获取一次。

小开

另一个简单的解决方案是对行进行排名，并随机获取其中之一，有了这个解决方案，你将不需要在表中有任何基于“Id”的列。

SELECT d.* FROM (
SELECT  t.*,  @rownum := @rownum + 1 AS rank
FROM mytable AS t,
(SELECT @rownum := 0) AS r,
(SELECT @cnt := (SELECT RAND() * (SELECT COUNT(*) FROM mytable))) AS n
) d WHERE rank >= @cnt LIMIT 10;

您可以根据需要更改限制值，以便访问尽可能多的行，但大多数情况下是连续的值。

然而，如果你不想要连续的随机值，那么你可以获取一个更大的样本并从中随机选择。就像……

SELECT * FROM (
SELECT d.* FROM (
SELECT  c.*,  @rownum := @rownum + 1 AS rank
FROM buildbrain.`commits` AS c,
(SELECT @rownum := 0) AS r,
(SELECT @cnt := (SELECT RAND() * (SELECT COUNT(*) FROM buildbrain.`commits`))) AS rnd
) d
WHERE rank >= @cnt LIMIT 10000
) t ORDER BY RAND() LIMIT 10;
小开

它是非常简单的单行查询。

SELECT * FROM Table_Name ORDER BY RAND() LIMIT 0,10;
小开

如果有一个自动生成的id，我发现一个很好的方法是使用模运算符'%'。例如，如果您需要70,000条随机记录中的10,000条，您可以简化为每7行中需要1行。这可以在这个查询中简化:

SELECT * FROM
table
WHERE
id %
FLOOR(
(SELECT count(1) FROM table)
/ 10000
) = 0;

如果目标行除以total available的结果不是一个整数，那么你将得到比你要求的更多的行，所以你应该添加一个LIMIT子句来帮助你像这样修剪结果集:

SELECT * FROM
table
WHERE
id %
FLOOR(
(SELECT count(1) FROM table)
/ 10000
) = 0
LIMIT 10000;

这确实需要一个完整的扫描，但它比ORDER BY RAND更快，在我看来，比本文中提到的其他选项更容易理解。另外，如果写入数据库的系统批量创建了一组行，你可能不会得到你所期望的随机结果。

小开

有出色的工作表现和配合能力的简单查询:

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

在一个200K表上的这个查询使用0.08秒，而普通版本(SELECT * FROM tbl ORDER BY RAND() LIMIT 10)在我的机器上使用0.35秒。

这是快速的，因为排序阶段只使用索引ID列。你可以在解释中看到这种行为:

SELECT * FROM tbl ORDER BY RAND() LIMIT 10: # EYZ0 < / p >

SELECT * FROM tbl AS t1 JOIN (SELECT id FROM tbl ORDER BY RAND() LIMIT 10) AS t2 ON t1.id=t2.id # EYZ0 < / p >

# EYZ0: # EYZ1

小开

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

让我来解释一下发生了什么。

    <李> # EYZ0
    • 我在计算并保存最大值。对于非常大的表，每次需要一行时计算MAX(id)会有轻微的开销
      • 李< / ul > < / > <李> # EYZ0
      • 获取一个随机id
        • 李< / ul > < / > <李> # EYZ0
        • 这就填补了空白。基本上，如果你在间隙中随机选择一个数字，它就会选择下一个id。假设间隙是均匀分布的，这应该不是问题。
          • 李< / ul > < / >

进行联合可以帮助您将所有内容放入一个查询中，从而避免进行多个查询。它还可以节省计算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
小开

如果你想要一个随机记录(不管id之间是否有空隙):

PREPARE stmt FROM 'SELECT * FROM `table_name` LIMIT 1 OFFSET ?';
SET @count = (SELECT
FLOOR(RAND() * COUNT(*))
FROM `table_name`);


EXECUTE stmt USING @count;

来源:# EYZ0

小开

我看了所有的答案，我认为没有人提到这种可能性，我也不知道为什么。

如果你想要最大限度的简单和速度，在一个较小的成本，那么对我来说，它似乎是有意义的存储在DB中的每一行的随机数。只需要创建一个额外的列random_number，并将其默认值设置为RAND()。在此列上创建索引。

然后，当您想检索一行时，在代码(PHP、Perl等)中生成一个随机数，并将其与列进行比较。

SELECT FROM tbl WHERE random_number >= :random LIMIT 1

我想虽然它对于单行来说非常整洁，但是对于像OP要求的十行，你必须分别调用它十次(或者想出一个我立即逃脱的聪明的调整)

小开

这非常快，而且是100%随机的，即使你有间隙。

  1. 计算可用的行数x SELECT COUNT(*) as rows FROM TABLE
  2. 在0和x之间选择10个不同的随机数a_1,a_2,...,a_10
  3. 像这样查询行:SELECT * FROM TABLE LIMIT 1 offset a_i for i=1，…，10

我在比尔Karwin的SQL反模式书中找到了这个hack。

小开

以下内容应该是快速的，公正的，独立于id列的。# EYZ0

SELECT *
FROM t
WHERE RAND() < (SELECT 10 / COUNT(*) FROM t)

解释:假设你想要100行中的10行，那么每一行都有1/10的概率被选中，这可以通过WHERE RAND() < 0.1实现。这种方法不能保证有10行;但是如果查询运行了足够多的次数，那么每次执行的平均行数将在10左右，并且表中的每一行都将被均匀地选择。

小开

您可以轻松地使用带限制的随机偏移量

PREPARE stm from 'select * from table limit 10 offset ?';
SET @total = (select count(*) from table);
SET @_offset = FLOOR(RAND() * @total);
EXECUTE stm using @_offset;

您还可以像这样应用where子句

PREPARE stm from 'select * from table where available=true limit 10 offset ?';
SET @total = (select count(*) from table where available=true);
SET @_offset = FLOOR(RAND() * @total);
EXECUTE stm using @_offset;

在600,000行(700MB)表查询执行上的测试花费了大约0.016秒的硬盘驱动器时间。

编辑:偏移量可能会取一个接近表末尾的值，这将导致select语句返回更少的行(或者可能只有一行)，为了避免这种情况，我们可以在声明offset后再次检查它，如下所示

SET @rows_count = 10;
PREPARE stm from "select * from table where available=true limit ? offset ?";
SET @total = (select count(*) from table where available=true);
SET @_offset = FLOOR(RAND() * @total);
SET @_offset = (SELECT IF(@total-@_offset<@rows_count,@_offset-@rows_count,@_offset));
SET @_offset = (SELECT IF(@_offset<0,0,@_offset));
EXECUTE stm using @rows_count,@_offset;
小开

我认为这是一个简单但更快的方法，我在现场服务器上测试了它，与上面的几个答案相比，它更快。

 SELECT * FROM `table_name` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table_name` ) ORDER BY id LIMIT 30;

//对一个130行的表花费0.0014秒

SELECT * FROM `table_name` WHERE 1 ORDER BY RAND() LIMIT 30

//对130行的表花费0.0042秒

 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 30

//对130行的表花费0.0040秒

小开

我知道这不是您想要的，但我将给您的答案是我在小网站中使用的。

根据您访问随机值的次数，不值得使用MySQL，因为您将无法缓存答案。我们在那里有一个按钮来访问一个随机页面，用户可以每分钟点击几次，如果他愿意的话。这将导致MySQL的大量使用，至少对我来说，MySQL是优化的最大问题。

我会采用另一种方法，你可以把答案存储在缓存中。调用MySQL:

SELECT min(id) as min, max(id) as max FROM your_table

使用min和max Id，您可以在服务器中计算一个随机数。在python中:

random.randint(min, max)

然后，用你的随机数，你可以在你的表中得到一个随机Id:

SELECT *
FROM your_table
WHERE id >= %s
ORDER BY id ASC
LIMIT 1

在这种方法中，对数据库执行两次调用，但是可以缓存它们，并且在很长一段时间内不访问数据库，从而提高性能。注意，如果表中有洞，这不是随机的。有超过一行很容易，因为你可以使用python创建Id，并为每行执行一个请求，但由于它们是缓存的，这是可以的。

小开 
SELECT
*
FROM
table_with_600k_rows
WHERE
RAND( )
ORDER BY
id DESC
LIMIT 30;

id是主键，按id排序， 解释table_with_600k_rows，发现该行不扫描整个表


提交答案