Comment puis-je optimiser la fonction ORDER BY RAND () de MySQL?

Je voudrais optimiser mes requêtes, alors je me mysql-slow.log vers mysql-slow.log .

La plupart de mes requêtes lentes contiennent ORDER BY RAND() . Je ne peux pas trouver une solution réelle pour résoudre ce problème. Theres est une solution possible à MySQLPerformanceBlog mais je ne pense pas que cela soit suffisant. Sur les tables mal optimisées (ou fréquemment mises à jour, gérées par l’utilisateur), cela ne fonctionne pas ou je dois exécuter deux requêtes ou plus avant de pouvoir sélectionner ma ligne aléatoire générée par PHP .

Y a-t-il une solution à ce problème?

Un exemple fictif:

 SELECT accomodation.ac_id, accomodation.ac_status, accomodation.ac_name, accomodation.ac_status, accomodation.ac_images FROM accomodation, accomodation_category WHERE accomodation.ac_status != 'draft' AND accomodation.ac_category = accomodation_category.acat_id AND accomodation_category.acat_slug != 'vendeglatohely' AND ac_images != 'b:0;' ORDER BY RAND() LIMIT 1 

Essaye ça:

 SELECT * FROM ( SELECT @cnt := COUNT(*) + 1, @lim := 10 FROM t_random ) vars STRAIGHT_JOIN ( SELECT r.*, @lim := @lim - 1 FROM t_random r WHERE (@cnt := @cnt - 1) AND RAND(20090301) < @lim / @cnt ) i 

Ceci est particulièrement efficace sur MyISAM (puisque COUNT(*) est instantané), mais même dans InnoDB il est 10 fois plus efficace que ORDER BY RAND() .

L'idée principale ici est de ne pas sortinger, mais de conserver deux variables et de calculer la running probability de passage d'une ligne à sélectionner dans l'étape en cours.

Voir cet article sur mon blog pour plus de détails:

  • Sélection de lignes aléatoires

Mettre à jour:

Si vous devez sélectionner un seul enregistrement aléatoire, essayez ceci:

 SELECT aco.* FROM ( SELECT minid + FLOOR((maxid - minid) * RAND()) AS randid FROM ( SELECT MAX(ac_id) AS maxid, MIN(ac_id) AS minid FROM accomodation ) q ) q2 JOIN accomodation aco ON aco.ac_id = COALESCE ( ( SELECT accomodation.ac_id FROM accomodation WHERE ac_id > randid AND ac_status != 'draft' AND ac_images != 'b:0;' AND NOT EXISTS ( SELECT NULL FROM accomodation_category WHERE acat_id = ac_category AND acat_slug = 'vendeglatohely' ) ORDER BY ac_id LIMIT 1 ), ( SELECT accomodation.ac_id FROM accomodation WHERE ac_status != 'draft' AND ac_images != 'b:0;' AND NOT EXISTS ( SELECT NULL FROM accomodation_category WHERE acat_id = ac_category AND acat_slug = 'vendeglatohely' ) ORDER BY ac_id LIMIT 1 ) ) 

Cela suppose que votre ac_id est dissortingbué plus ou moins uniformément.

Cela dépend du hasard dont vous avez besoin. La solution que vous avez liée fonctionne plutôt bien avec l’OMI. À moins que vous ayez de grandes lacunes dans le champ ID, cela rest assez aléatoire.

Cependant, vous devriez pouvoir le faire en une seule requête en utilisant ceci (pour sélectionner une seule valeur):

 SELECT [fields] FROM [table] WHERE id >= FLOOR(RAND()*MAX(id)) LIMIT 1 

Autres solutions:

  • Ajoutez un champ flottant permanent appelé random à la table et remplissez-le avec des nombres aléatoires. Vous pouvez alors générer un nombre aléatoire en PHP et faire "SELECT ... WHERE rnd > $random"
  • Saisissez la liste complète des ID et mettez-les en cache dans un fichier texte. Lisez le fichier et choisissez un identifiant aléatoire.
  • Cachez les résultats de la requête au format HTML et conservez-la pendant quelques heures.

Voici comment je le ferais:

 SET @r := (SELECT ROUND(RAND() * (SELECT COUNT(*) FROM accomodation a JOIN accomodation_category c ON (a.ac_category = c.acat_id) WHERE a.ac_status != 'draft' AND c.acat_slug != 'vendeglatohely' AND a.ac_images != 'b:0;'; SET @sql := CONCAT(' SELECT a.ac_id, a.ac_status, a.ac_name, a.ac_status, a.ac_images FROM accomodation a JOIN accomodation_category c ON (a.ac_category = c.acat_id) WHERE a.ac_status != ''draft'' AND c.acat_slug != ''vendeglatohely'' AND a.ac_images != ''b:0;'' LIMIT ', @r, ', 1'); PREPARE stmt1 FROM @sql; EXECUTE stmt1; 

Cela vous donnera une sous-requête unique qui utilisera l’index pour obtenir un identifiant aléatoire, alors que l’autre requête lancera l’obtention de votre table jointe.

 SELECT accomodation.ac_id, accomodation.ac_status, accomodation.ac_name, accomodation.ac_status, accomodation.ac_images FROM accomodation, accomodation_category WHERE accomodation.ac_status != 'draft' AND accomodation.ac_category = accomodation_category.acat_id AND accomodation_category.acat_slug != 'vendeglatohely' AND ac_images != 'b:0;' AND accomodation.ac_id IS IN ( SELECT accomodation.ac_id FROM accomodation ORDER BY RAND() LIMIT 1 ) 

La solution pour votre exemple factice serait:

 SELECT accomodation.ac_id, accomodation.ac_status, accomodation.ac_name, accomodation.ac_status, accomodation.ac_images FROM accomodation, JOIN accomodation_category ON accomodation.ac_category = accomodation_category.acat_id JOIN ( SELECT CEIL(RAND()*(SELECT MAX(ac_id) FROM accomodation)) AS ac_id ) AS Choices USING (ac_id) WHERE accomodation.ac_id >= Choices.ac_id AND accomodation.ac_status != 'draft' AND accomodation_category.acat_slug != 'vendeglatohely' AND ac_images != 'b:0;' LIMIT 1 

Pour en savoir plus sur les alternatives à ORDER BY RAND() , vous devriez lire cet article .

J’optimise beaucoup de requêtes existantes dans mon projet. La solution de Quassnoi m’a aidé à accélérer les requêtes! Cependant, je trouve difficile d’intégrer ladite solution dans toutes les requêtes, en particulier pour les requêtes complexes impliquant de nombreuses sous-requêtes sur plusieurs grandes tables.

J’utilise donc une solution moins optimisée. Fondamentalement, cela fonctionne de la même manière que la solution de Quassnoi.

 SELECT accomodation.ac_id, accomodation.ac_status, accomodation.ac_name, accomodation.ac_status, accomodation.ac_images FROM accomodation, accomodation_category WHERE accomodation.ac_status != 'draft' AND accomodation.ac_category = accomodation_category.acat_id AND accomodation_category.acat_slug != 'vendeglatohely' AND ac_images != 'b:0;' AND rand() < = $size * $factor / [accomodation_table_row_count] LIMIT $size 

$size * $factor / [accomodation_table_row_count] détermine la probabilité de choisir une ligne aléatoire. Le rand () générera un nombre aléatoire. La ligne sera sélectionnée si rand () est plus petit ou égal à la probabilité. Cela permet d'effectuer une sélection aléatoire pour limiter la taille de la table. Comme il y a une chance que le résultat soit inférieur au nombre de limites défini, nous devons augmenter la probabilité de nous assurer que nous sélectionnons suffisamment de lignes. Par conséquent, nous multiplions $ size par un facteur $ (je règle habituellement $ factor = 2, fonctionne dans la plupart des cas). Enfin, nous faisons la limit $size

Le problème est en train de régler l' accountation_table_row_count . Si nous connaissons la taille de la table, nous pourrions coder la taille de la table. Ce serait le plus rapide, mais ce n’est évidemment pas idéal. Si vous utilisez Myisam, obtenir le nombre de tables est très efficace. Depuis que j'utilise innodb, je ne fais qu'un simple compte + sélection. Dans votre cas, cela ressemblerait à ceci:

 SELECT accomodation.ac_id, accomodation.ac_status, accomodation.ac_name, accomodation.ac_status, accomodation.ac_images FROM accomodation, accomodation_category WHERE accomodation.ac_status != 'draft' AND accomodation.ac_category = accomodation_category.acat_id AND accomodation_category.acat_slug != 'vendeglatohely' AND ac_images != 'b:0;' AND rand() < = $size * $factor / (select (SELECT count(*) FROM `accomodation`) * (SELECT count(*) FROM `accomodation_category`)) LIMIT $size 

La partie délicate consiste à trouver la bonne probabilité. Comme vous pouvez le voir, le code suivant ne calcule que la taille approximative de la table temporaire (en fait, trop grossière!): (select (SELECT count(*) FROM accomodation) * (SELECT count(*) FROM accomodation_category)) cette logique pour donner une approximation de la taille de la table plus proche. Notez qu'il est préférable de sur-sélectionner que de sous-sélectionner les lignes. Par exemple, si la probabilité est trop faible, vous risquez de ne pas sélectionner suffisamment de lignes.

Cette solution est plus lente que la solution de Quassnoi, car nous devons recalculer la taille de la table. Cependant, je trouve ce codage beaucoup plus facile à gérer. C'est un compromis entre précision + performance et complexité de codage . Cela dit, sur les grandes tables, ceci est encore beaucoup plus rapide que Order by Rand ().

Remarque: Si la logique de requête le permet, effectuez la sélection aléatoire le plus tôt possible avant toute opération de jointure.

(Ouais, je vais me faire douter de ne pas avoir assez de viande ici, mais ne peux-tu pas être végétalienne pour un jour?)

Cas: AUTO_INCREMENT consécutif sans lacunes, 1 ligne renvoyée
Cas: AUTO_INCREMENT consécutif sans espaces, 10 lignes
Cas: AUTO_INCREMENT avec des lacunes, 1 ligne renvoyée
Cas: colonne FLOAT supplémentaire pour la randomisation
Cas: colonne UUID ou MD5

Ces 5 cas peuvent être rendus très efficaces pour les grandes tables. Voir mon blog pour les détails.

 function getRandomRow(){ $id = rand(0,NUM_OF_ROWS_OR_CLOSE_TO_IT); $res = getRowById($id); if(!empty($res)) return $res; return getRandomRow(); } //rowid is a key on table function getRowById($rowid=false){ return db select from table where rowid = $rowid; }