Tirer des lignes au hasard dans MySQL sans trier toute la table

Optimiser ORDER BY RAND() revient à supprimer le tri global : MySQL calcule une valeur random pour chaque row, trie tout le result set, puis ne garde qu'un résultat. Les réponses anglophones sur mysql order by rand nomment ces performance issues « order by rand slow » et « full table scan », et listent des efficient techniques pour randomizing your query results. La parade : un seul tirage, dans PHP ou une sous-requête, puis une lecture par l'index, sans loop de tri.

Pourquoi ORDER BY RAND() ralentit quand la base grossit

ORDER BY RAND() ralentit parce que la fonction RAND() n'est pas une constante pour l'optimiseur de MySQL : le manuel de référence précise qu'elle est réévaluée pour chaque row, et qu'aucun index ne peut donc servir à ordonner le résultat. La requête la plus courante semble pourtant anodine :

SELECT id, titre FROM articles ORDER BY RAND() LIMIT 1;

Le serveur lit toutes les lignes, attribue à chacune une valeur random, trie l'ensemble, puis jette presque tout. Depuis MySQL 5.6, un ORDER BY suivi d'un petit LIMIT peut passer par une file de priorité en mémoire au lieu d'un tri complet ; le parcours intégral, lui, demeure. Sur quelques centaines de lignes, personne ne remarque la différence. Sur plusieurs centaines de milliers, chaque appel coûte un full table scan, et le tri déborde souvent sur disque quand les colonnes sélectionnées contiennent du texte long.

Le coût croît donc avec le volume, pas avec le nombre de lignes demandées : un tirage d'une ligne coûte presque autant qu'un tirage de cent. C'est ce qui rend le problème sournois, car la page « article au hasard » tourne vite en développement et s'effondre en production, le jour où la base atteint sa taille réelle.

Lire le coût réel avec EXPLAIN et EXPLAIN ANALYZE

Le diagnostic d'un ORDER BY RAND() lent se fait en deux commandes. EXPLAIN, placé devant la requête, affiche le plan : les mentions Using temporary et Using filesort dans la colonne Extra, avec un type ALL, confirment le parcours complet suivi d'un tri.

EXPLAIN ANALYZE, disponible depuis MySQL 8.0.18, exécute vraiment la requête et donne pour chaque étape le temps mesuré (actual time), le nombre de lignes produites et le nombre de passages (loops). Sur la requête naïve, la sortie ressemble à ceci :

-> Limit: 1 row(s)  (cost=48210 rows=1) (actual time=412..412 rows=1 loops=1)
    -> Sort: rand(), limit input to 1 row(s) per chunk  (cost=48210 rows=480000) (actual time=412..412 rows=1 loops=1)
        -> Table scan on articles  (cost=48210 rows=480000) (actual time=0.08..298 rows=480000 loops=1)

Les valeurs ci-dessus illustrent la forme de la sortie, pas une mesure publiée : elles varient selon la machine. La ligne utile est la dernière, où le nombre de rows lues égale la taille de la base. Après optimisation, la même lecture doit montrer une recherche par index (Index lookup ou Index range scan) et un rows proche de 1. Le journal des requêtes lentes donne le même indice en production : sa valeur Rows_examined rapportée au Rows_sent trahit tout tirage aléatoire mal écrit.

Quatre requêtes plus rapides pour un tirage aléatoire

Tirer un identifiant, puis lire par l'index

Tirer un identifiant au hasard est la méthode la plus rapide quand la clé primaire est un entier auto-incrémenté : on calcule un nombre entre le plus petit et le plus grand id, puis on prend la première ligne qui suit.

SELECT a.id, a.titre
FROM articles a
JOIN (SELECT FLOOR(MIN(id) + RAND() * (MAX(id) - MIN(id) + 1)) AS tirage
      FROM articles) t ON a.id >= t.tirage
ORDER BY a.id
LIMIT 1;

La table dérivée t n'est calculée qu'une fois, et MIN et MAX se lisent directement aux deux extrémités de l'index. Seule réserve : si des lignes ont disparu, celle qui suit un trou sort plus souvent que les autres. Pour une rubrique de découverte, ce biais reste acceptable ; pour un tirage au sort avec enjeu, non.

Compter, puis décaler avec OFFSET

Compter les lignes puis décaler avec OFFSET donne un tirage parfaitement uniforme, même avec des trous et un filtre WHERE. Le script PHP tire le rang avec random_int(), générateur cryptographiquement sûr selon la documentation de php.net :

$total = (int) $pdo->query('SELECT COUNT(*) FROM articles WHERE publie = 1')->fetchColumn();
$rang  = random_int(0, max(0, $total - 1));
$req   = $pdo->prepare('SELECT id, titre FROM articles WHERE publie = 1 ORDER BY id LIMIT 1 OFFSET ?');
$req->bindValue(1, $rang, PDO::PARAM_INT);
$req->execute();

Le serveur parcourt tout de même l'index jusqu'au rang tiré : le coût moyen vaut la moitié du volume filtré, ce qui reste raisonnable jusqu'à quelques centaines de milliers de lignes, mais pas au-delà. Le COUNT peut se mettre en cache quelques minutes pour économiser une requête sur deux.

Trier une colonne étroite, puis charger les lignes

Trier seulement les identifiants convient quand il faut plusieurs lignes distinctes : RAND() reste en place, mais le tri porte sur une colonne étroite, lue dans un index couvrant, avant la jointure qui ramène le reste.

SELECT a.*
FROM articles a
JOIN (SELECT id FROM articles WHERE publie = 1 ORDER BY RAND() LIMIT 10) h USING (id);

Le tri manipule des entiers au lieu de lignes entières avec leur texte : il tient bien plus souvent en mémoire, dans la limite du sort_buffer_size. Le parcours complet subsiste, ce qui réserve cette écriture aux volumes moyens.

Indexer une colonne aléatoire

Une colonne alea remplie par RAND() et indexée permet un tirage par plage, le plus économe de tous à la lecture. Une entrée de crontab la recalcule chaque nuit pour renouveler l'ordre, et les nouvelles lignes reçoivent leur valeur à l'insertion.

ALTER TABLE articles ADD COLUMN alea DOUBLE NOT NULL DEFAULT 0, ADD INDEX (alea);
UPDATE articles SET alea = RAND();
SELECT id, titre FROM articles WHERE alea >= ? ORDER BY alea LIMIT 1;

Le paramètre du dernier SELECT vient de PHP (un flottant tiré avec mt_rand() / mt_getrandmax()) : écrit alea >= RAND(), il serait réévalué ligne par ligne et l'index ne servirait plus. Si le tirage ne trouve rien au-dessus de la valeur, le résultat est vide : relancer avec alea >= 0 pour revenir au début, ou sélectionner la plus petite valeur, ce qui garantit toujours un résultat.

Plusieurs lignes distinctes sans ORDER BY RAND()

Tirer plusieurs lignes distinctes sans ORDER BY RAND() demande d'éviter les doublons que produiraient plusieurs tirages d'identifiants. Deux écritures tiennent la route sur de gros volumes.

La première tire un échantillon d'identifiants côté PHP, dans une loop qui s'arrête dès que le nombre voulu est atteint ; un tableau associatif écarte les doublons, puis une seule requête WHERE id IN (...) ramène les lignes. Les identifiants absents (lignes supprimées) se compensent en tirant un peu plus que nécessaire.

La seconde reste côté serveur, dans une procédure stockée qui répète le tirage par index :

CREATE PROCEDURE tirer_articles(IN n INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  CREATE TEMPORARY TABLE IF NOT EXISTS tirage (id INT PRIMARY KEY);
  boucle: LOOP
    INSERT IGNORE INTO tirage
      SELECT a.id FROM articles a
      WHERE a.id >= (SELECT FLOOR(MIN(id) + RAND() * (MAX(id) - MIN(id) + 1)) FROM articles)
      ORDER BY a.id LIMIT 1;
    SET i = i + 1;
    IF (SELECT COUNT(*) FROM tirage) >= n OR i >= n * 3 THEN LEAVE boucle; END IF;
  END LOOP boucle;
  SELECT a.* FROM articles a JOIN tirage USING (id);
END

La clé primaire de la table temporaire rejette les doublons, et le compteur i borne la loop si la base contient moins de lignes que demandé. Chaque passage coûte une lecture d'index : dix tirages restent bien plus rapides qu'un seul tri complet.

Quelle méthode retenir selon le volume et le besoin ?

Le choix d'une alternative à ORDER BY RAND() se fait sur trois critères : l'uniformité du tirage, le coût par appel et le nombre de lignes voulues.

Dernier levier, souvent le plus efficace : mettre le résultat en cache. Une sélection aléatoire affichée en page d'accueil n'a pas besoin d'être recalculée à chaque visite ; la renouveler toutes les cinq minutes divise la charge par le nombre de visites de l'intervalle. Les autres réglages de performance se trouvent avec la jointure LEFT OUTER JOIN optimisée, autre cas où le plan d'exécution dit tout.

ORDER BY RANDOM() dans PostgreSQL et SQLite

ORDER BY RANDOM() est l'équivalent de la requête MySQL dans PostgreSQL et SQLite, et il souffre du même défaut : une valeur random par ligne, puis un tri. PostgreSQL offre depuis sa version 9.5 une sortie que MySQL n'a pas, la clause TABLESAMPLE. TABLESAMPLE SYSTEM (1) lit environ 1 % des blocs, très vite mais par paquets de lignes voisines ; TABLESAMPLE BERNOULLI (1) retient chaque row avec une probabilité de 1 %, plus uniforme mais au prix d'un parcours complet. Aucune des deux ne garantit un nombre exact de lignes : on ajoute un LIMIT, et l'on tire un peu plus large.

SQLite, souvent utilisé en développement, masque le problème : sur une base de quelques milliers d'enregistrements, ORDER BY RANDOM() répond en un temps imperceptible. C'est l'une des raisons pour lesquelles une requête aléatoire passe les tests puis ralentit une fois portée sur le serveur de production. Tester le tirage sur un jeu de données de taille réelle évite la surprise.

Oracle Database suit la même logique : ORDER BY DBMS_RANDOM.VALUE attribue une value à chaque ligne et trie tout, tandis que la clause SAMPLE (1) d'Oracle joue le rôle de TABLESAMPLE.

Chercher la solution en anglais : les formulations qui trouvent

Les formulations anglaises comptent, car la plupart des réponses détaillées sur le tirage aléatoire en SQL sont publiées en anglais, sur Stack Overflow, dev.to ou les blogs web de développeurs spécialisés en database. Une recherche « how to select a random row in MySQL » ou « how to get random rows from a large table » ramène les fils les plus votés ; « ORDER BY RAND() alternative » et « how to optimize ORDER BY RAND » ramènent les comparatifs de performance. Ajouter le tag mysql à la question filtre les réponses écrites pour un autre moteur.

• « holes in the id » désigne le biais de la méthode par identifiant tiré : plus le trou est grand, plus la ligne qui le suit sort souvent. La réponse acceptée (accepted answer) propose en général de sélectionner un peu plus de lignes, puis d'en garder une au hasard.
• « result set » désigne le jeu de résultats ; « return a random result » veut dire renvoyer une ligne au hasard, but the most cited answers also warn that the way you pick the number matters more than the function you use.
• « randomness » vise la qualité du hasard, pas la vitesse : une méthode rapide peut avoir un résultat biaisé, et l'inverse.
• « which query will work with more data » est la vraie question : un benchmark posted sur une base de 10 000 records ne dit rien d'une application web qui en compte des millions.

Avant de copier une réponse, vérifier sa date et la version visée : un post écrit avant MySQL 8 ne connaît ni les CTE ni EXPLAIN ANALYZE, et les blogs qui comparent le cost de chaque méthode mesurent sur des données différentes des vôtres. Une key map ou un index sur la colonne triée change aussi tout le point de comparaison. La documentation officielle reste l'aide la plus sûre, l'aide d'un forum sert à sélectionner les pistes, et l'aide d'EXPLAIN tranche.

Garder la colonne aléatoire à jour sans tâche planifiée

La colonne alea peut aussi se remplir à l'insertion, sans attendre la tâche nocturne, grâce à un trigger BEFORE INSERT sur MySQL qui lui affecte RAND() :

CREATE TRIGGER articles_alea BEFORE INSERT ON articles
FOR EACH ROW SET NEW.alea = RAND();

La clause FOR EACH ROW s'exécute une fois par ligne insérée, ce qui ne coûte presque rien. Le recalcul nocturne reste utile pour brasser l'ordre : sans lui, les mêmes lignes resteraient voisines dans l'index et les tirages successifs se ressembleraient. Sur une base très active, l'UPDATE global se découpe en lots de quelques milliers de lignes pour ne pas verrouiller longtemps.

Tirage aléatoire en SQL, les cas limites

ORDER BY RAND(42) donne-t-il toujours le même ordre ?

ORDER BY RAND(42) donne le même ordre à chaque exécution tant que les données ne changent pas : la graine fixe rend la suite de nombres reproductible, ce qui sert pour une pagination stable d'une liste mélangée. La requête garde le même coût, parcours et tri compris.

RAND() convient-il pour un tirage au sort avec enjeu ?

RAND() ne convient pas pour un tirage avec enjeu : le manuel de MySQL indique que la fonction n'est pas conçue comme générateur aléatoire parfait. Pour un concours, mieux vaut tirer le rang avec random_int() en PHP, puis lire la ligne par COUNT et OFFSET, méthode uniforme.

À partir de combien de lignes ORDER BY RAND() pose-t-il problème ?

ORDER BY RAND() pose problème dès que son temps de réponse se voit dans la page, ce qui dépend du serveur et de la largeur des lignes plus que d'un seuil fixe. EXPLAIN ANALYZE répond pour votre base : au-delà de quelques dizaines de millisecondes par appel, passer à une alternative.

Sources : manuel de référence de MySQL 8.4 (fonctions mathématiques, RAND ; EXPLAIN et EXPLAIN ANALYZE ; optimisation d'ORDER BY ; triggers), documentation de PostgreSQL (clause TABLESAMPLE), manuel de php.net (random_int(), PDO).