MySQL seleccione 10 filas aleatorias de 600K filas rápidamente

SELECT column FROM table
ORDER BY RAND()
LIMIT 10

No es la solución eficiente, pero funciona

Es una consulta muy simple y de una sola línea.

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

Estoy recibiendo consultas rápidas (alrededor de 0.5 segundos) con una cpu lenta, seleccionando 10 filas aleatorias en una base de datos MySQL de 400K de tamaño no almacenado en caché de 2 Gb. Ver aquí mi código: Selección rápida de filas aleatorias en 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);
?>

Del libro:

Elija una Fila Aleatoria Usando un Desplazamiento

Otra técnica que evita problemas encontrados en el anterior alternativas es contar las filas en el conjunto de datos y devolver un aleatorio número entre 0 y la cuenta. A continuación, utilice este número como un desplazamiento al consultar el conjunto de datos

<?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();

Utilice esta solución cuando no pueda asumir valores de clave contiguos y es necesario asegurarse de que cada fila tiene una oportunidad uniforme de ser seleccionado.

Consulta simple que tiene excelente rendimiento (funciona con huecos):

SELECT * FROM tbl WHERE id IN 
    (SELECT id FROM (SELECT id FROM tbl ORDER BY RAND() LIMIT 10) t)

Se utilizan dos subconsultas anidadas porque MySQL aún no admite LÍMITE en la primera.

Esto es rápido porque la fase de ordenación solo utiliza la columna ID indexada.

Para la versión ponderada: https://stackoverflow.com/a/41577458/893432

Cómo seleccionar filas aleatorias de una tabla:

Desde aquí: Seleccionar filas aleatorias en MySQL

Una mejora rápida sobre "table scan" es usar el índice para recoger ID aleatorios.

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

Bueno, si no tiene espacios en sus claves y todas son numéricas, puede calcular números aleatorios y seleccionar esas líneas. pero probablemente no sea así.

Así que una solución sería la siguiente:

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

Lo que básicamente asegurará que obtenga un número aleatorio en el rango de sus claves y luego seleccione el siguiente mejor que es mayor. tienes que hacer esto 10 veces.

Sin embargo, esto no es realmente aleatorio porque lo más probable es que sus claves no sean distribuido uniformemente.

Es realmente un gran problema y no es fácil de resolver cumpliendo con todos los requisitos, rand() de MySQL es lo mejor que puede obtener si realmente desea 10 filas aleatorias.

Sin embargo, hay otra solución que es rápida, pero también tiene una compensación cuando se trata de aleatoriedad, pero puede adaptarse mejor. Lea sobre esto aquí: ¿Cómo puedo optimizar la función ORDER BY RAND() de MySQL?

La pregunta es cuán aleatoria necesitas que sea.

¿Puedes explicar un poco más para poder darte una buena solución.

Por ejemplo, una compañía con la que trabajé tenía una solución donde necesitaban aleatoriedad absoluta extremadamente rápida. Terminaron rellenando previamente la base de datos con valores aleatorios que se seleccionaron descendentemente y se establecieron en diferentes valores aleatorios después de nuevo.

Si casi nunca actualiza, también podría llenar un id incremental para que no tenga huecos y solo pueda calcular claves aleatorias antes de seleccionar... Depende del caso de uso!

Usé esto http://jan.kneschke.de/projects/mysql/order-by-rand / publicado por Riedsio (usé el caso de un procedimiento almacenado que devuelve uno o más valores aleatorios):

   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;

En el artículo resuelve el problema de huecos en ids causando resultados no tan aleatorios manteniendo una tabla (usando disparadores, etc...ver el artículo); Estoy resolviendo el problema agregando otra columna a la tabla, poblada con números contiguos, a partir de 1 (editar: esta columna se agrega a la tabla temporal creada por la subconsulta en tiempo de ejecución, no afecta a su tabla permanente):

   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;

En el artículo puedo ver que hizo todo lo posible para optimizar el código; no tengo idea de si/cuánto afectan mis cambios al rendimiento, pero funciona muy bien para mí.

Necesitaba una consulta para devolver un gran número de filas aleatorias de una tabla bastante grande. Esto es lo que se me ocurrió. Primero obtenga el id de registro máximo:

SELECT MAX(id) FROM table_name;

Luego sustituye ese valor en:

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

Donde max es el id de registro máximo en la tabla y n es el número de filas que desea en su conjunto de resultados. La suposición es que no hay lagunas en el registro de identificación aunque dudo que afectaría el resultado si lo hubiera (no lo he intentado aunque). También creé esto almacenado procedimiento para ser más genérico; pase el nombre de la tabla y el número de filas a devolver. Estoy ejecutando MySQL 5.5.38 en Windows 2008, 32GB, dual 3GHz E5450, y en una tabla con 17,361,264 filas es bastante consistente en ~.03 seg / ~11 seg para devolver 1,000,000 filas. (los tiempos son de MySQL Workbench 6.1; también puede usar CEIL en lugar de FLOOR en la instrucción 2nd select dependiendo de su preferencia)

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
$$

Luego

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

Aquí hay un cambio de juego que puede ser útil para muchos;

Tengo una tabla con 200k filas, con id secuenciales , necesitaba elegir N filas aleatorias, así que opto por generar valores aleatorios basados en el ID más grande de la tabla, creé este script para averiguar cuál es la operación más rápida:

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();

Los resultados son:

• Cuenta: 36.8418693542479 ms
• Max: 0.241041183472 ms
• Orden: 0.216960906982 ms

Basado en estos resultados, el orden desc es la operación más rápida para obtener el id máximo,
Aquí está mi respuesta a la pregunta:

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);

Para tu información: Para obtener 10 filas aleatorias de una tabla de 200k, me tomó 1.78 ms (incluyendo todas las operaciones en el lado de php)

Ya se han publicado todas las mejores respuestas (principalmente las que hacen referencia al enlace http://jan.kneschke.de/projects/mysql/order-by-rand/).

Quiero señalar otra posibilidad de aceleración- caching. Piense en por qué necesita obtener filas aleatorias. Probablemente quieras mostrar algún post al azar o anuncio al azar en un sitio web. Si está obteniendo 100 req / s, ¿es realmente necesario que cada visitante obtenga filas aleatorias? Por lo general, está completamente bien almacenar en caché estas X filas aleatorias para 1 segundo (o incluso 10 segundos). No importa si 100 visitantes únicos en el mismo 1 segundo obtienen las mismas publicaciones aleatorias, porque al segundo siguiente otros 100 visitantes obtendrán un conjunto diferente de publicaciones.

Al usar este almacenamiento en caché, puede usar también algunas de las soluciones más lentas para obtener los datos aleatorios, ya que se obtendrán de MySQL solo una vez por segundo, independientemente de su req/s.

Si solo tiene una solicitud de lectura

Combine la respuesta de @redsio con una tabla temporal (600K no es mucho):

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;

Y luego tomar una versión de @redsios Respuesta:

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;

Si la mesa es grande, se puede tamizar en la primera parte:

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

Si tiene muchas solicitudes de lectura

1. Versión: Puede mantener la tabla tmp_randorder persistente, llámela datatable_idlist. Vuelva a crear esa tabla en ciertos intervalos (día, hora), ya que también obtendrá agujeros. Si su mesa se hace muy grande, también podría rellenar agujeros

   Seleccione l. data_id como un todo desde datatable_idlist l izquierda join datatable dt on dt.id = l. data_id donde dt.id es nulo;

2. Versión: Proporcione a su conjunto de datos una columna random_sortorder directamente en datatable o en una tabla adicional persistente datatable_sortorder. Indexa esa columna. Genera un valor aleatorio en tu Aplicación (lo llamaré $rand).

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

Esta solución discrimina las 'filas de bordes' con el más alto y el más bajo random_sortorder, así que reorganizarlos en intervalos (una vez al día).

Otra solución simple sería clasificar las filas y obtener una de ellas aleatoriamente y con esta solución no necesitará tener ninguna columna basada en 'Id' en la tabla.

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;

Puede cambiar el valor límite según su necesidad de acceder a tantas filas como desee, pero eso sería principalmente valores consecutivos.

Sin embargo, si no desea valores aleatorios consecutivos, puede obtener una muestra más grande y seleccionarla aleatoriamente. algo así ...

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;

Una forma que me parece bastante buena si hay un id autogenerado es usar el operador de módulo '%'. Por ejemplo, si necesita 10,000 registros aleatorios 70,000, podría simplificar esto diciendo que necesita 1 de cada 7 filas. Esto se puede simplificar en esta consulta:

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

Si el resultado de dividir las filas de destino por el total disponible no es un entero, tendrá algunas filas adicionales de las que pidió, por lo que debe agregar una cláusula de LÍMITE para ayudarlo a recortar el conjunto de resultados como esto:

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

Esto requiere un análisis completo, pero es más rápido que ORDER BY RAND, y en mi opinión más simple de entender que otras opciones mencionadas en este hilo. Además, si el sistema que escribe en la base de datos crea conjuntos de filas en lotes, es posible que no obtenga un resultado aleatorio como esperaba.

Mejoré la respuesta que tenía @Riedsio. Esta es la consulta más eficiente que puedo encontrar en una tabla grande y uniformemente distribuida con huecos (probada al obtener 1000 filas aleatorias de una tabla que tiene > 2.6 B filas).

(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)

Déjame desempacar lo que está pasando.

1. @max := (SELECT MAX(id) FROM table)
   • Estoy calculando y guardando el máximo. Para tablas muy grandes, hay una ligera sobrecarga para calcular MAX(id) cada vez que necesita una fila
2. SELECT FLOOR(rand() * @max) + 1 as rand)
   • Obtiene una random id
3. SELECT id FROM table INNER JOIN (...) on id > rand LIMIT 1
   • Esto llena los vacíos. Básicamente, si selecciona aleatoriamente un número en los huecos, solo elegirá el siguiente id. Suponiendo que las brechas estén distribuidas uniformemente, esto no debería ser un problema.

Hacer la unión le ayuda a encajar todo en 1 consulta para que pueda evitar hacer múltiples consultas. También le permite ahorrar la sobrecarga de calcular MAX(id). Dependiendo de su aplicación, esto puede importar mucho o muy poco.

Tenga en cuenta que esto obtiene solo los id y los obtiene en orden aleatorio. Si quieres hacer algo más avanzado te recomiendo que hagas esto:

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

Si desea un registro aleatorio (sin importar si hay espacios entre id):

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

EXECUTE stmt USING @count;

Fuente: https://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/#comment-1266

Vieja pregunta, pero esto es algo que encontré hoy, querer seleccionar una página al azar. Opté por no utilizar ninguna de las respuestas aquí debido a las preocupaciones con el rendimiento y el hecho de que muchos de ellos tienen fuertes sesgos en el "azar". Aquí estaba mi solución (usando 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);
}

Básicamente, todo lo que está haciendo es obtener una matriz de slugs de página de la base de datos, y usar PHP para elegir una aleatoria de la matriz devuelta.

Si quieres 10 registra, simplemente itera a través de la matriz y elimina los elegidos para evitar duplicados, y luego añádelos a una matriz de resultados separada. Algo como esto:

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;
}

He mirado a través de todas las respuestas, y no creo que nadie mencione esta posibilidad en absoluto, y no estoy seguro de por qué.

Si desea la máxima simplicidad y velocidad, a un costo menor, entonces me parece que tiene sentido almacenar un número aleatorio contra cada fila en la base de datos. Simplemente cree una columna adicional, random_number, y establezca su valor predeterminado en RAND(). Cree un índice en esta columna.

Luego, cuando desee recuperar una fila, genere un número aleatorio en su código (PHP, Perl, lo que sea) y compare eso con la columna.

SELECT FROM tbl WHERE random_number >= :random LIMIT 1

Supongo que aunque es muy limpio para una sola fila, para diez filas como la OP pidió que tendría que llamarlo diez veces separadas (o llegar a un ajuste inteligente que se me escapa de inmediato)

Utilizo esta consulta:

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

Tiempo de consulta: 0.016 s

Utilice la siguiente consulta simple para obtener datos aleatorios de una tabla.

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

Así es como lo hago:

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

Me gusta porque no requiere otras tablas, es simple de escribir, y es muy rápido de ejecutar.

Supongo que esta es la mejor manera posible..

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