MySQL快速从600K行中选择10个随机行-Java 学习之路

409

如何最好地编写一个从总共600k中随机选择10行的查询？

23 回答

老问题，但这是我今天遇到的，想要选择一个随机页面 . 我选择不使用任何答案，因为担心表现以及他们中的许多人在“随机”中有强烈的偏见 . 这是我的解决方案（使用PHP）：

Pages model:

public static function getIDs() {
    $sql  = "SELECT `id` FROM `pages`;";
    $db   = static::getDB();
    $stmt = $db->query($sql);

    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Pages controller:

public function randomAction() {
    $pages  = Pages::getIDs();
    $random = $pages[rand(0, count($pages))];

    $this->redirect('/' . $random['id'], 307);
}

基本上，它所做的只是从数据库获取一系列页面slug，并使用PHP从返回的数组中选择一个随机的 .

如果您想要10条记录，只需遍历数组并删除选定的数据以避免重复，然后将它们添加到单独的结果数组中 . 像这样的东西：

public static function randomAction() {
    $pages   = Pages::getIDs();
    $count   = count($pages);
    $results = [];

    for($i = 0; $i < 10; $i++) {
        $random = rand(0, $count);
        $count -= 1;

        $results[] = $pages[$random];
        unset($pages[$random]);
    }

    return $results;
}

回复于 2024-05-02T11:40:11+08:00

6

我已经查看了所有的答案，我认为根本没有人提到这种可能性，我不确定为什么 .

如果你想以极小的代价获得最大的简单性和速度，那么对我而言，在DB中的每一行存储一个随机数似乎是有意义的 . 只需创建一个额外的列 random_number ，并将其默认设置为 RAND() . 在此列上创建索引 .

然后，当你想要检索一行时，在代码中生成一个随机数（PHP，Perl，无论如何）并将其与列进行比较 .

SELECT FROM tbl WHERE random_number >= :random LIMIT 1

我想虽然它对于单排来说非常整齐，但像OP这样的十行要求你必须分十次调用它（或者想出一个巧妙的调整让我立刻逃脱）

回复于 2024-05-02T11:40:11+08:00
0
它非常简单和单行查询 .
```
SELECT * FROM Table_Name ORDER BY RAND() LIMIT 0,10;
```
回复于 2024-05-02T11:40:11+08:00
2
如果您只有一个读取请求

将@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;
```
如果 table 很大，你可以在第一部分筛选：
```
INSERT INTO tmp_randorder (data_id) select id from datatable where rand() < 0.01;
```
如果您有许多读取请求
- 版本：你可以保持表 tmp_randorder 持久化，称之为datatable_idlist . 以特定间隔（日，小时）重新创建该表，因为它也会出现漏洞 . 如果你的 table 变得很大，你也可以重新填充洞
从datatable_idlist中选择整个l.data_id l在dt.id = l.data_id上左连接数据表dt其中dt.id为null;
- 版本：直接在数据表或持久性额外表 datatable_sortorder 中为数据集提供random_sortorder列 . 索引该列 . 在您的应用程序中生成一个随机值（我称之为 $rand ） .
```
select l.*
from datatable l 
order by abs(random_sortorder - $rand) desc 
limit 1;
```
该解决方案将“边缘行”与最高和最低的random_sortorder区分开，因此以间隔（每天一次）重新排列它们 .
回复于 2024-05-02T11:40:11+08:00
2
我需要一个查询来从一个相当大的表中返回大量的随机行 . 这就是我提出的 . 首先获取最大记录ID：
```
SELECT MAX(id) FROM table_name;
```
然后将该值替换为：
```
SELECT * FROM table_name WHERE id > FLOOR(RAND() * max) LIMIT n;
```
其中max是表中的最大记录ID，n是结果集中所需的行数 . 假设记录id中没有间隙，尽管我怀疑它会影响结果（如果有的话）（尽管没有尝试过） . 我还创建了这个存储过程更通用;传递表名和要返回的行数 . 我在Windows 2008，32GB，双3GHz E5450上运行MySQL 5.5.38，在一个有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);
```
回复于 2024-05-02T11:40:11+08:00
-4

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

我想确定另一种加速可能性 - caching . 想想你为什么需要获得随机行 . 您可能希望在网站上显示一些随机帖子或随机广告 . 如果你得到100 req / s，是否真的需要每个访问者获得随机行？通常将这些X随机行缓存1秒（甚至10秒）完全没问题 . 如果100个独立访问者在同一个1秒内获得相同的随机帖子并不重要，因为下一秒另外100个访问者将得到不同的帖子 .

使用此缓存时，您还可以使用一些较慢的解决方案来获取随机数据，因为无论您的req / s是什么，它都将每秒从MySQL获取一次 .

回复于 2024-05-02T11:40:11+08:00
1
我是这样做的：
```
select * 
from table_with_600k_rows
where rand() < 10/600000
limit 10
```
我喜欢它，因为它不需要其他表，编写起来很简单，并且执行起来非常快 .
回复于 2024-05-02T11:40:11+08:00
20
```
SELECT column FROM table
ORDER BY RAND()
LIMIT 10
```
不是有效的解决方案，但有效
回复于 2024-05-02T11:40:11+08:00
0
具有 excellent performance 的简单查询（适用于间隙）：
```
SELECT * FROM tbl WHERE id IN 
    (SELECT id FROM (SELECT id FROM tbl ORDER BY RAND() LIMIT 10) t)
```
使用了两个嵌套子查询，因为MySQL尚未在第一个中支持LIMIT .

这很快，因为排序阶段仅使用索引ID列 .

加权版：https://stackoverflow.com/a/41577458/893432
回复于 2024-05-02T11:40:11+08:00
12
如何从表中选择随机行：

从这里：Select random rows in MySQL

对“表扫描”的快速改进是使用索引来获取随机ID .
```
SELECT *
FROM random, (
        SELECT id AS sid
        FROM random
        ORDER BY RAND( )
        LIMIT 10
    ) tmp
WHERE random.id = tmp.sid;
```
回复于 2024-05-02T11:40:11+08:00
348
这是一个可能有用的游戏改变者许多;

我有一个200k行的表， with sequential id's ，我需要选择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 毫秒
- 最大： 0.241041183472 毫秒
- 订单： 0.216960906982 ms
根据此结果，订单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 ms （包括php端的所有操作）
回复于 2024-05-02T11:40:11+08:00

我用 slow cpu 获得了 fast queries （大约0.5秒），在400K寄存器MySQL数据库非缓存2Gb大小中选择了10个随机行 . 看到我的代码：Fast selection of random rows in MySQL

<?php
$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);
?>

回复于 2024-05-02T11:40:11+08:00

3
来自书：

Choose a Random Row Using an Offset

避免前面替代方案中发现的问题的另一种技术是计算数据集中的行并返回0和计数之间的随机数 . 然后在查询数据集时使用此数字作为偏移量
```
<?php
$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();
```
Use this solution when you can’t assume contiguous key values and you need to make sure each row has an even chance of being selected.
回复于 2024-05-02T11:40:11+08:00
-1
如果有自动生成的id，我觉得很好的一种方法是使用模运算符'％' . 例如，如果您需要10,000个随机记录70,000，您可以通过说您需要每7行中有1个来简化此操作 . 这可以在此查询中简化：
```
SELECT * FROM 
    table 
WHERE 
    id % 
    FLOOR(
        (SELECT count(1) FROM table) 
        / 10000
    ) = 0;
```
如果将目标行除以可用总数的结果不是整数，那么您将有一些额外的行，因此您应该添加一个LIMIT子句来帮助您修剪结果集，如下所示：
```
SELECT * FROM 
    table 
WHERE 
    id % 
    FLOOR(
        (SELECT count(1) FROM table) 
        / 10000
    ) = 0
LIMIT 10000;
```
这确实需要完整扫描，但它比ORDER BY RAND更快，在我看来比这个线程中提到的其他选项更容易理解 . 此外，如果写入数据库的系统批量创建行集，则可能无法获得与预期相同的随机结果 .
回复于 2024-05-02T11:40:11+08:00
1
我使用此查询：
```
select floor(RAND() * (SELECT MAX(key) FROM table)) from table limit 10
```
查询时间：0.016秒
回复于 2024-05-02T11:40:11+08:00
297
一个伟大的岗位处理几个案例，从简单到间隙，到不均匀的差距 .

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列表中可能存在间隙 . 有关更多高级示例，请参阅文章
回复于 2024-05-02T11:40:11+08:00

我使用了由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;

在文章中，他通过维护一个表（使用触发器等等）来解决导致 not so random results 的id中的 problem of gaps ;参见文章）;我正在通过向表中添加另一列来填充问题，从1开始填充连续的数字（ edit: 此列被添加到子查询在运行时创建的临时表中，不会影响您的永久表）：

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;

在文章中，我可以看到他竭尽全力优化代码;我不知道我的变化会影响性能，但对我来说效果很好 .

回复于 2024-05-02T11:40:11+08:00

-2
如果你想要一个随机记录（无论ids之间是否有噱头）：
```
PREPARE stmt FROM 'SELECT * FROM `table_name` LIMIT 1 OFFSET ?';
SET @count = (SELECT
        FLOOR(RAND() * COUNT(*))
    FROM `table_name`);

EXECUTE stmt USING @count;
```
资料来源：https://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/#comment-1266
回复于 2024-05-02T11:40:11+08:00

我改进了@Riedsio的答案 . 这是我可以在大型统一分布的表上找到的最有效的查询 with gaps （在从具有> 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 . 假设间隙均匀分布，这应该不是问题 .

执行联合可帮助您将所有内容都放入1个查询中，以避免执行多个查询 . 它还可以节省计算 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

回复于 2024-05-02T11:40:11+08:00

另一个简单的解决方案是对行进行排名并随机获取其中一个，使用此解决方案，您不需要在表中包含任何“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;

回复于 2024-05-02T11:40:11+08:00

使用以下简单查询从表中获取随机数据 .

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

回复于 2024-05-02T11:40:11+08:00

我猜这是最好的方式..

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

回复于 2024-05-02T11:40:11+08:00

-3
好吧，如果您的密钥没有间隙并且它们都是数字，您可以计算随机数并选择这些线 . 但情况可能并非如此 .

所以一个解决方案如下：
```
SELECT * FROM table WHERE key >= FLOOR(RAND()*MAX(id)) LIMIT 1
```
这基本上可以确保您获得钥匙范围内的随机数，然后选择下一个最好的更好 . 你必须这样做10次 .

但这并不是随机的，因为你的密钥很可能不会均匀分布 .

这真的是一个很大的问题，并不容易解决满足所有要求，如果你真的想要10个随机行，MySQL的rand（）是你能得到的最好的 .

然而，有另一个解决方案是快速的，但在随机性方面也有一个折衷，但可能更适合你 . 在这里阅读：How can i optimize MySQL's ORDER BY RAND() function?

问题是你需要它是多么随机 .

你能解释一下吗，我可以给你一个很好的解决方案 .

例如，我合作过的公司有一个解决方案，他们需要极快的绝对随机性 . 他们最终使用随机值预先填充数据库，然后再次选择降序并设置为不同的随机值 .

如果你几乎没有更新，你也可以填充递增的id，这样你就没有间隙，只能在选择之前计算随机密钥......这取决于用例！
回复于 2024-05-02T11:40:11+08:00

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

23 回答

如果您只有一个读取请求

如果您有许多读取请求

相关问题