Si vous avez lu le manuel MySQL, vous avez peut-être vu le ORDER BY RAND()pour randomiser les lignes et utiliser le LIMIT 1 pour juste prendre une des lignes.
SELECT name FROM random ORDER BY RAND() LIMIT 1;
Cet exemple fonctionne bien et est rapide si vous n’avez que disons 1000 lignes.Dès que vous avez 10000 lignes, le surcoût pour trier les lignes devientimportant. N’oubliez pas : nous ne trions que pour jeter presque toutes les lignes.
Je n’ai jamais aimé ça. Et il y a de meilleures façons de le faire. Sans un tri.Tant que nous avons une clé primaire numérique.
Pour les premiers exemples, nous supposons que l’ID be commence à 1 et que nous n’avons pas de trous entre 1 et la valeur maximale de l’ID.
Première idée : Nous pouvons simplifier tout le travail si nous calculons l’ID au préalable dans l’application.
SELECT MAX(id) FROM random;## generate random id in applicationSELECT name FROM random WHERE id = <random-id>
Comme MAX(id) == COUNT(id) nous générons juste un nombre aléatoire entre1 et MAX(id) et le passons dans la base de données pour récupérer la ligne aléatoire.
Le premier SELECT est un NO-OP et est optimisé loin. Le second est un eq_refcontre une valeur constante et aussi très rapide.
déplacer le travail dans la base de données
Mais est-il vraiment nécessaire de le faire dans l’application ? Ne pouvons-nous pas le faire dans la base de données ?
Ok, maintenant nous savons comment générer l’ID aléatoire, mais comment obtenir la ligne ?
NO, NO, NO. N’allez pas par là. C’est la façon la plus évidente, mais aussi la plus mauvaise de le faire.La raison : le SELECT dans la clause WHERE est exécuté pour chaque ligne que le SELECT externe va chercher.Cela conduit à 0 à 4091 lignes, selon votre chance.
Nous avons besoin d’un moyen de s’assurer que l’ID aléatoire n’est généré qu’une seule fois:
Le SELECT interne génère une table TEMPORAIRE constante et le JOIN sélectionne juste sur singlerow. Parfait.
Pas de tri, pas d’application, la plupart des parties de la requête sont optimisées.
Ajouter des trous aux nombres
Pour généraliser la dernière solution, nous ajoutons la possibilité de trous, comme lorsque vous DELETElignes.
Le JOIN ajoute maintenant tous les ID qui sont supérieurs ou égaux à notre valeur aléatoire et ne sélectionne que le voisin direct si une correspondance directe n’est pas possible. MAIS dès qu’une ligne est trouvée, on s’arrête (le LIMIT 1). Et nous lisons les lignes en fonction de l’index (ORDER BY id ASC). Comme nous utilisons >= au lieu d’un =, nous pouvons nous débarrasser d’un CEIL et obtenir le même résultat avec un peu moins de travail.
Distribution égale
Dès que la distribution des ID n’est plus égale, notre sélection de lignes n’est plus vraiment aléatoire non plus.
La fonction RAND génère des ID comme 9 à 15 qui mènent tous à l’id 16 à sélectionner comme numéro supérieur.
Il n’y a pas de réelle solution pour ce problème, mais vos données sont pour la plupart constantes, vous pouvez ajouter une table de correspondance qui fait correspondre le numéro de ligne à l’id:
Le row_id est maintenant à nouveau libre de trous et nous pouvons à nouveau exécuter notre requête aléatoire :
Après 1000 recherches, nous voyons à nouveau une distribution égale:
Maintien des trous
Prenons les tables comme avant:
Quand nous changeons quelque chose dans r2, nous voulons que r2_equi_dist soit aussi mis à jour.
INSERT est assez simple, sur DELETE nous devons mettre à jour l’equi-dist-id pour maintenir la configuration sans trou:
UPDATE est simple à nouveau. Nous devons seulement maintenir la contrainte de la clé étrangère:
Multiples rangs à la fois
Si vous voulez obtenir plus d’un rang retourné, vous pouvez :
- exécuter la requête plusieurs fois
- écrire une procédure stockée qui exécute la requête et stocke le résultat dans une temp-table
- faire une UNION
une procédure stockée
Les procédures stockées vous fournissent les structures que vous connaissez dans votre langage de programmation préféré :
- boucles
- structures de contrôle
- procédures
- …
Pour cette tâche, nous n’avons besoin que d’une LOOP:
Je laisse au lecteur le soin de régler les problèmes :
- utiliser le SQL dynamique et passer dans le nom de la table temporaire
- utiliser un index UNIQUE sur la table et attraper la violation de la clé UNIQUE pour supprimer les éventuels doublons dans le jeu de résultats
Performance
Maintenant, voyons ce qui arrive à nos performances. Nous avons 3 requêtes différentes pour résoudre nos problèmes.
- Q1. ORDER BY RAND()
- Q2. RAND() * MAX(ID)
- Q3. RAND() * MAX(ID) + ORDER BY ID
Q1 devrait coûter N * log2(N), Q2 et Q3 sont presque constants.
Pour obtenir des valeurs réelles, nous avons rempli la table avec N lignes ( de mille à un million)et exécuté chaque requête 1000 fois.