MySQL sélectionner 10 lignes aléatoires de 600K lignes rapides

SELECT column FROM table
ORDER BY RAND()
LIMIT 10

pas la solution efficace mais les travaux

sa requête très simple et simple.

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

je suis requêtes rapides (autour de 0,5 secondes) avec un cpu lent , la sélection de 10 lignes aléatoires dans un 400K de registres de base de données MySQL non mis en cache de taille de 2 go. Voir ici mon code: sélection rapide de lignes aléatoires dans 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);
?>

du livre:

choisir une ligne au hasard en utilisant un Offset

encore une autre technique qui évite les problèmes trouvés dans le précédent alternative est de compter les lignes dans l'ensemble de données et de retourner un aléatoire nombre entre 0 et le nombre. Puis utilisez ce nombre comme offset en interrogeant l'ensemble de données

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

utilisez cette solution lorsque vous ne pouvez pas supposer des valeurs clés contiguës et vous devez vous assurer que chaque ligne a une chance égale d'être sélectionné.

Simple requête qui a excellente performance (fonctionne avec des lacunes):

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

deux sous-séries imbriquées sont utilisées parce que MySQL ne supporte pas encore LIMIT dans la première.

c'est rapide car la phase de tri n'utilise que la colonne indexée ID.

pour la version pondérée: https://stackoverflow.com/a/41577458/893432

Comment sélectionner des lignes aléatoires à partir d'une table:

D'ici: sélectionner des lignes au hasard dans MySQL

une amélioration rapide par rapport à" table scan " est d'utiliser l'index pour récupérer des identificateurs aléatoires.

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

Eh bien, si vous n'avez pas de trous dans vos clés et qu'elles sont toutes numériques, vous pouvez calculer des nombres aléatoires et sélectionner ces lignes. mais ce ne sera probablement pas le cas.

ainsi, une solution serait la suivante:

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

qui garantira essentiellement que vous obtenez un nombre aléatoire dans la gamme de vos clés et ensuite vous sélectionnez le meilleur suivant qui est plus grand. tu dois le faire 10 fois.

cependant ce n'est pas vraiment aléatoire parce que vos clés seront très probablement pas distribués uniformément.

c'est vraiment un gros problème et pas facile à résoudre satisfaire toutes les exigences, Rand() De MySQL est le meilleur que vous pouvez obtenir si vous voulez vraiment 10 lignes aléatoires.

il y a cependant une autre solution qui est rapide mais a aussi un compromis quand il s'agit de l'aléatoire, mais peut vous convenir mieux. comment optimiser L'ordre de MySQL par la fonction RAND ()?

la Question Est de savoir si vous en avez besoin au hasard.

Pouvez-vous expliquer un peu plus pour que je puisse vous donner une bonne solution.

par exemple, une entreprise avec laquelle j'ai travaillé avait une solution où ils avaient besoin de l'aléatoire absolu extrêmement rapide. Ils ont fini par pré-peupler la base de données avec des valeurs aléatoires qui ont été sélectionnées en décroissant et réglées à des valeurs aléatoires différentes par la suite.

si vous avez à peine Toujours mettre à jour vous pourriez également remplir un incrementing id de sorte que vous n'avez pas de lacunes et pouvez simplement calculer des clés aléatoires avant de sélectionner... Cela dépend du cas d'utilisation!

j'ai utilisé ce http://jan.kneschke.de/projects/mysql/order-by-rand/ posté par Riedsio (j'ai utilisé le cas d'une procédure stockée qui renvoie une ou plusieurs valeurs aléatoires):

   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;

dans l'article il résout le problème de lacunes dans l'ids causant pas si résultats aléatoires en maintenant une table (en utilisant des déclencheurs, etc...voir l'article); Je résous le problème en ajoutant une autre colonne à la table, peuplée de nombres contigus, à partir de 1 ( "edit: cette colonne est ajoutée à la table temporaire créée par la sous-requête à l'exécution, n'affecte pas votre table 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;

dans l'article je peux voir qu'il est allé très loin pour optimiser le code; je n'ai aucune idée si/combien mes changements ont un impact sur la performance mais fonctionne très bien pour moi.

j'ai eu besoin d'une requête pour retourner un grand nombre de lignes aléatoires d'une table assez grande. C'est ce que je suis venu avec. Tout d'abord, obtenir le maximum record id:

SELECT MAX(id) FROM table_name;

remplacer cette valeur par:

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

où max est le maximum record id dans le tableau et n est le nombre de lignes que vous voulez dans votre ensemble de résultats. L'hypothèse est qu'il n'y a pas de lacunes dans les identifications des enregistrements, bien que je doute que cela affecte le résultat s'il y a étaient (n'ont pas essayé). J'ai également créé cette procédure stockée pour être plus générique; passer dans le nom de la table et le nombre de lignes à retourner. J'exécute MySQL 5.5.38 sur Windows 2008, 32 Go, dual 3GHz E5450, et sur une table avec 17.361.264 lignes il est assez cohérent à ~.03 sec / ~11 sec pour retourner 1 000 000 de lignes. (les heures sont de MySQL Workbench 6.1; vous pouvez également utiliser CEIL au lieu de plancher dans la 2ème déclaration select en fonction de votre préférence)

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

puis

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

Ici est un changeur de jeu qui peut être utile pour beaucoup de monde;

j'ai une table avec des lignes de 200k, avec des id séquentiels , j'avais besoin de choisir N lignes aléatoires, donc j'ai choisi de générer des valeurs aléatoires basées sur le plus grand ID de la table, j'ai créé ce script pour trouver qui est l'opération la plus rapide:

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

les résultats sont:

• Comte: 36.8418693542479 ms
• Max: 0.241041183472 ms
• : 0.216960906982 ms

basé sur ces résultats, l'ordre desc est l'opération la plus rapide pour obtenir le max id,

Voici ma réponse à la question:

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: pour obtenir 10 lignes aléatoires d'une table de 200k, il m'a fallu 1,78 ms (y compris toutes les opérations dans le côté php)

toutes les meilleures réponses ont déjà été postées (principalement celles faisant référence au lien http://jan.kneschke.de/projects/mysql/order-by-rand / ).

je veux mettre en évidence une autre possibilité d'accélération- caching . Pensez à pourquoi vous avez besoin pour obtenir des lignes aléatoires. Probablement vous voulez afficher un certain post aléatoire ou publicité aléatoire sur un site web. Si vous obtenez 100 req/s, est-il vraiment nécessaire que chaque visiteur reçoit lignes aléatoires? Habituellement, il il est tout à fait correct de mettre en cache ces lignes x aléatoires pendant 1 seconde (ou même 10 Secondes). Il n'a pas d'importance si 100 visiteurs uniques dans le même 1 seconde obtiennent les mêmes postes aléatoires, parce que la seconde suivante un autre 100 visiteurs obtiendront un ensemble différent de postes.

lors de l'utilisation de cette mise en cache, vous pouvez également utiliser une partie de la solution plus lente pour obtenir les données aléatoires car il sera récupéré à partir de MySQL une seule fois par seconde indépendamment de votre req/s.

si vous N'avez Qu'une demande de lecture

combiner la réponse de @redsio avec une table-temp (600K n'est pas tant que ça):

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;

et puis prendre une version de @redsios réponse:

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 table est grande, vous pouvez tamiser sur la première partie:

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

Si vous avez beaucoup de lecture-les demandes

1. Version: vous pouvez garder la table tmp_randorder persistante, appelez-la datatable_idlist. Recréer cette table à certains intervalles (jour, heure), car elle aura aussi des trous. Si votre table devient vraiment grande, vous pouvez également remplir des trous

   sélectionnez L. data_id dans son ensemble de datatable_idlist l sur la gauche, rejoindre dt.id = L. data_id où dt.id est null;

2. Version: donnez à votre ensemble de données une colonne random_sortorder soit directement dans datatable soit dans une colonne persistante tableau supplémentaire datatable_sortorder . Indexez cette colonne. Générez une valeur aléatoire dans votre Application (je l'appellerai $rand ).

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

Cette solution distingue les "rangées de bordures" avec l'ordre aléatoire le plus élevé et le plus bas, et les réarrange par intervalles (une fois par jour).

une autre solution simple serait de classer les lignes et d'en récupérer une au hasard et avec cette solution vous n'aurez pas besoin d'avoir une colonne basée sur 'Id' dans le tableau.

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;

Vous pouvez changer la valeur limite selon votre besoin d'accéder à autant de lignes que vous le souhaitez mais ce serait principalement des valeurs consécutives.

cependant, si vous ne voulez pas de valeurs aléatoires consécutives, vous pouvez obtenir un plus grand échantillon et choisir au hasard à partir de celui-ci. quelque chose comme ...

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;

une façon que je trouve assez bonne s'il y a un id autogénéré est d'utiliser l'opérateur modulo '%'. Par exemple, si vous avez besoin de 10 000 enregistrements aléatoires sur 70 000, vous pouvez simplifier cela en disant que vous avez besoin d'un enregistrement sur 7. Ceci peut être simplifié dans cette requête:

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

si le résultat de la division des lignes de la cible par le total disponible n'est pas un entier, vous aurez quelques lignes supplémentaires que ce que vous avez demandé, donc vous devriez ajouter une clause limite pour vous aider à couper le jeu de résultats comme ceci:

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

cela nécessite un scan complet, mais il est plus rapide que L'ordre Par RAND, et à mon avis plus simple à comprendre que d'autres options mentionnées dans ce fil. En outre, si le système qui écrit à la base de données crée des ensembles de lignes dans les lots, vous pourriez ne pas obtenir un tel résultat aléatoire que vous où attend.

j'ai amélioré la réponse de @Riedsio. C'est la requête la plus efficace que je peux trouver sur un grand, uniformément distribué tableau avec des lacunes (testé sur l'obtention de 1000 lignes aléatoires à partir d'une table qui a > 2,6 lignes B).

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

laissez-moi déballer ce qui se passe.

1. @max := (SELECT MAX(id) FROM table)
   • Je calcule et sauve le max. Pour les très grandes tables, il y a un léger surplomb pour calcul de MAX(id) chaque fois que vous avez besoin d'une rangée
2. SELECT FLOOR(rand() * @max) + 1 as rand)
   • obtient une identification aléatoire
3. SELECT id FROM table INNER JOIN (...) on id > rand LIMIT 1
   • cela comble les lacunes. Fondamentalement, si vous sélectionnez au hasard un nombre dans les écarts, il va juste choisir la prochaine id. En supposant que les écarts soient uniformément répartis, cela ne devrait pas poser de problème.

faire le syndicat vous aide à tout ajuster dans 1 requête de sorte que vous pouvez éviter de faire des requêtes multiples. Il vous permet également de sauver la tête de calcul de MAX(id) . Selon votre application, cela peut avoir son importance ou très peu.

notez que ceci obtient seulement les ids et les obtient dans l'ordre aléatoire. Si vous voulez faire quelque chose de plus avancé je vous recommande de faire ceci:

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 vous voulez un enregistrement aléatoire (peu importe s'il y a des écarts entre les ids):

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

EXECUTE stmt USING @count;

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

vieille question, mais c'est quelque chose que j'ai rencontré aujourd'hui, voulant sélectionner une page au hasard. J'ai choisi de ne pas utiliser les réponses ici en raison de préoccupations au sujet de la performance et du fait que beaucoup d'entre elles sont fortement biaisées dans le "hasard". Voici ma solution (en utilisant 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);
}

en gros, tout ce qu'il fait est d'obtenir un tableau de page limaces à partir de la DB, et utiliser PHP pour en choisir un au hasard dans le tableau retourné.

Si vous voulez 10 enregistrements, juste parcourir le tableau et supprimer ceux choisis pour éviter les doublons, puis les ajouter à un tableau de résultats. Quelque chose comme ceci:

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

j'ai regardé toutes les réponses, et je ne pense pas que quiconque mentionne cette possibilité du tout, et je ne sais pas pourquoi.

si vous voulez la plus grande simplicité et la vitesse, à un coût mineur, alors à moi, il semble logique de stocker un nombre aléatoire contre chaque ligne dans le DB. Il suffit de créer une colonne supplémentaire, random_number , et de définir sa valeur par défaut à RAND() . Créer un index sur cette colonne.

puis quand vous voulez récupérer une rangée nombre aléatoire dans votre code (PHP, Perl, peu importe) et comparez cela à la colonne.

SELECT FROM tbl WHERE random_number >= :random LIMIT 1

je suppose que même si c'est très soigné pour une seule rangée, pour dix rangées comme L'OP a demandé que vous avez à l'appeler dix fois distinctes (ou à venir avec un tweak intelligent qui m'échappe immédiatement)

J'utilise cette requête:

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

moment de la requête:0.016 s

utilisez la requête simple ci-dessous pour obtenir des données aléatoires à partir d'une table.

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

C'est comment je le fais:

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

je l'aime parce que ne nécessite pas d'autres tables, il est simple à écrire, et il est très rapide à exécuter.

je suppose que c'est la meilleure manière possible..

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