two - php y mysql pdf

MySQL selecciona 10 filas aleatorias de 600K filas rápido

Question

¿Cómo puedo escribir mejor una consulta que seleccione 10 filas al azar de un total de 600k?

Answer 1

Si solo tienes una solicitud de lectura

Combina 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 tome 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, puede tamizar en la primera parte:

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

Si tienes muchas peticiones de lectura

1. Versión: Podría mantener la tabla tmp_randorder persistente, llamarlo datatable_idlist. Recree esa tabla en ciertos intervalos (día, hora), ya que también tendrá agujeros. Si tu mesa se vuelve muy grande, también puedes rellenar agujeros.

   seleccione l.data_id como un conjunto en datatable_idlist l unir a la izquierda en dat.id = l.data_id donde dt.id es nulo;

2. Versión: asigne a su conjunto de datos una columna random_sortorder directamente en datatable o en una tabla extra persistente datatable_sortorder . Indexar esa columna. Genere un valor aleatorio en su 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 borde" con el mayor y el menor random_sortorder, así que reordénalas en intervalos (una vez al día).

Answer 2

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

Tengo una tabla con 200k filas, con identificaciones secuenciales , necesitaba elegir N filas aleatorias, así que opto por generar valores aleatorios basados ​​en la ID más grande de la tabla, creé esta secuencia de comandos para descubrir 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

En base a estos resultados, el orden desc es la operación más rápida para obtener la ID máxima,
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);

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

Answer 3

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 fácil de escribir y es muy rápido de ejecutar.

Answer 4

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

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

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

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

Sin embargo, esto NO es realmente aleatorio porque lo más probable es que sus claves no se distribuyan uniformemente.

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

Sin embargo, existe otra solución que es rápida, pero también tiene un compromiso 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 qué tan aleatorio necesitas que sea.

¿Puedes explicar un poco más para que pueda darte una buena solución?

Por ejemplo, una empresa con la que trabajé tenía una solución en la que necesitaban una aleatoriedad absoluta extremadamente rápida. Terminaron rellenando previamente la base de datos con valores aleatorios que se seleccionaron de forma descendente y luego se configuraron diferentes valores aleatorios.

Si casi nunca actualiza, también puede llenar una identificación incremental para que no tenga espacios vacíos y solo pueda calcular claves aleatorias antes de seleccionar ... ¡Depende del caso de uso!

Answer 5

Cómo seleccionar filas aleatorias de una tabla:

Desde aquí: Seleccionar filas aleatorias en MySQL

Una mejora rápida sobre la "exploración de tabla" es utilizar el índice para recoger identificadores aleatorios.

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

Answer 6

Consulta simple que tiene un rendimiento excelente (trabaja 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 LIMIT en la primera.

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

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

Answer 7

Del libro:

Elija una fila aleatoria usando un desplazamiento

Otra técnica que evita los problemas encontrados en las alternativas anteriores es contar las filas en el conjunto de datos y devolver un número aleatorio entre 0 y el recuento. Luego use este número como un desplazamiento cuando consulte 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();

Use esta solución cuando no pueda asumir valores clave contiguos y necesita asegurarse de que cada fila tenga una posibilidad uniforme de ser seleccionada.

Answer 8

Estoy obteniendo consultas rápidas (alrededor de 0,5 segundos) con una CPU lenta , seleccionando 10 filas aleatorias en una base de datos MySQL de 400K que no almacenan en caché el tamaño de 2Gb. Vea 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);
?>

Answer 9

He revisado todas las respuestas, y creo que nadie menciona esta posibilidad en absoluto, y no estoy seguro de por qué.

Si desea la mayor simplicidad y velocidad, a un costo menor, entonces para mí tiene sentido almacenar un número aleatorio contra cada fila en el DB. Simplemente cree una columna adicional, random_number , y establezca su valor predeterminado en RAND() . Crea 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 compárelo con la columna.

SELECT FROM tbl WHERE random_number >= :random LIMIT 1

Supongo que, aunque es muy bueno para una sola fila, para diez filas como la OP pidió que tuvieras que llamar diez veces por separado (o crear un pellizco inteligente que se me escape de inmediato)

Answer 10

Mejoré la respuesta que tenía @Riedsio. Esta es la consulta más eficiente que puedo encontrar en una tabla grande, distribuida uniformemente con huecos (probada al obtener 1000 filas aleatorias de una tabla que tiene> 2.6B 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 pequeña sobrecarga para calcular MAX(id) cada vez que necesita una fila
2. SELECT FLOOR(rand() * @max) + 1 as rand)
   • Obtiene una identificación aleatoria
3. SELECT id FROM table INNER JOIN (...) on id > rand LIMIT 1
   • Esto llena los huecos. Básicamente, si selecciona aleatoriamente un número en los huecos, solo elegirá la siguiente identificación. Suponiendo que las brechas se distribuyen uniformemente, esto no debería ser un problema.

Hacer la unión le ayuda a encajar todo en una consulta para que pueda evitar hacer múltiples consultas. También te 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 identificadores 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

Answer 11

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. El supuesto es que no hay lagunas en los ID de registro, aunque dudo que afecte el resultado si hubiera (no lo he intentado). También creé este procedimiento almacenado 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 de filas. (los tiempos son de MySQL Workbench 6.1; también puede usar CEIL en lugar de FLOOR en la segunda declaración de selección según sus preferencias)

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

entonces

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

Answer 12

Otra solución simple sería clasificar las filas y obtener una de ellas al azar 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 seleccionar de forma aleatoria. algo como ...

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;

Answer 13

Pregunta antigua, pero esto es algo que encontré hoy, queriendo seleccionar una página aleatoria. Opté por no usar ninguna de las respuestas aquí debido a las preocupaciones con el rendimiento y al hecho de que muchas de ellas 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 serie de barras de página de la base de datos y usar PHP para elegir una aleatoria de la matriz devuelta.

Si desea 10 registros, simplemente itere a través de la matriz y elimine los elegidos para evitar duplicados, y luego agréguelos 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;
}

Answer 14

Si desea un registro aleatorio (no importa si hay espacios entre los identificadores):

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

Answer 15

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

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

Answer 16

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

Answer 17

Un gran puesto que maneja varios casos, desde simples, hasta huecos, hasta no uniformes con huecos.

http://jan.kneschke.de/projects/mysql/order-by-rand/

Para el caso más general, aquí es cómo lo haces:

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

Esto supone que la distribución de ids es igual y que puede haber espacios en la lista de id. Vea el artículo para ejemplos más avanzados

Answer 18

Una forma que encuentro bastante buena si hay una ID generada automáticamente es usar el operador de módulo ''%''. Por ejemplo, si necesita 10,000 registros aleatorios de 70,000, puede 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 número entero, tendrá algunas filas adicionales de lo que solicitó, por lo que debe agregar una cláusula LIMIT para ayudarlo a recortar el conjunto de resultados de esta manera:

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

Esto requiere un escaneo completo, pero es más rápido que ORDER BY RAND, y en mi opinión, es más fácil 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 tan aleatorio como esperaba.

Answer 19

Utilicé este 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 las brechas en los identificadores que causan resultados no tan aleatorios al mantener una tabla (usando desencadenantes, etc ... vea el artículo); Estoy resolviendo el problema agregando otra columna a la tabla, rellenada con números contiguos, comenzando desde 1 ( edición: esta columna se agrega a la tabla temporal creada por la subconsulta en tiempo de ejecución, no afecta 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 que puedo ver, hizo todo lo posible para optimizar el código; No tengo ninguna idea de si mis cambios afectan el rendimiento, pero me funciona muy bien.

Answer 20

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

Answer 21

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: el almacenamiento en caché . Piensa por qué necesitas filas aleatorias. Probablemente desee mostrar alguna publicación aleatoria o anuncio aleatorio en un sitio web. Si está obteniendo 100 req / s, ¿es realmente necesario que cada visitante obtenga filas aleatorias? Por lo general, es completamente correcto almacenar estas X filas aleatorias durante 1 segundo (o incluso 10 segundos). No importa si 100 visitantes únicos en el mismo 1 segundo obtienen las mismas publicaciones aleatorias, ya que en el siguiente segundo, otros 100 visitantes obtendrán un conjunto diferente de publicaciones.

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

Answer 22

Yo uso esta consulta:

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

tiempo de consulta: 0.016s

Answer 23

SELECT column FROM table
ORDER BY RAND()
LIMIT 10

No es la solución eficiente pero funciona.