Tutorial MySQL : alléger des requêtes successives avec CREATE TEMPORARY TABLE

Travailler avec des tables SQL de 30 Mo pour en triturer les données est une épreuve capable de mettre à genoux votre serveur dédié de compétition. La fonction CREATE TEMPORARY TABLE peut vous aider à alléger grandement les performances. Etude de cas sur une application de statistiques de visite et autres optimisations possibles.

Travailler avec des journaux (logs) de visites bruts de 30 Mo est une épreuve d’optimisation pour l’administrateur d’un site Web, même avec un serveur SQL rapide. Lorsque, de plus, votre application de statistiques nécessite de triturer les données selon différents critères, les requêtes s’enchaînent et menacent de mettre à genoux votre serveur dédié de compétition. A tout le moins, c’est la patience de l’utilisateur qui sera mise à rude épreuve lorsque les temps de calcul se comptent en dizaines de secondes.

Comment en est-on arrivé là ? Dans les premiers mois, la page des statistiques du mois s’affichait en quelques secondes. La situation n’a fait qu’empirer au fil du temps pour devenir intenable. La situation s’était déjà améliorée en indexant la table (clé d’indexation sur la date), le temps de calcul étant descendu de 40 à 20 secondes. Et puis le besoin d’affiner toujours plus les statistiques (notamment en filtrant les IP de notre boîte pour éliminer le trafic « interne », ou celles des robots de Google et ses copains) ont fini par faire exploser ce score. Ayant bien compris (en mesurant le temps consacré à chaque requête) que la taille de la base était en cause, j’ai résolu d’archiver les logs les plus anciens pour « figer » la page de statistiques résultantes. Avec un gain important de rapidité. Mais en agissant ainsi je me privais de la possibilité d’effectuer des comparaisons annuelles ou des nouveaux croisements de données sur les anciens logs. Mais que faire alors ?

Créer un paquet de données sur mesure

Le problème, c’était la masse de données accumulée au fil des mois. Mon script, qui tournait à vitesse grand V sur une base de petite taille lors des premiers mois d’exploitation, finissait par s’embourber de plus en plus dans les requêtes successives. Pourquoi ? Parce que chacune de ces requêtes portait sur l’intégralité des enregistrements de la table. En substance, mon code ressemblait à ça :

 $periode = "MONTH(date) = " . $_GET['mois'] . " AND YEAR(date) = " . $_GET['annee']; mysql_query("SELECT val1 FROM $nom_de_la_table_log WHERE $periode AND $critere1"); mysql_query("SELECT val2 FROM $nom_de_la_table_log WHERE $periode AND $critere2"); mysql_query("SELECT val3 FROM $nom_de_la_table_log WHERE $periode AND $critere3"); mysql_query("SELECT val4 FROM $nom_de_la_table_log WHERE $periode AND $critere4"); mysql_query("SELECT val5 FROM $nom_de_la_table_log WHERE $periode AND $critere5"); mysql_query("SELECT val6 FROM $nom_de_la_table_log WHERE $periode AND $critere6"); 

 

On le voit, à chaque requête le serveur MySQL va consulter la même table qui comporte, allez, un an d’historique, et va en ramener seulement un douzième sur lequel il va effectuer le filtrage nécessaire.

La solution idéale pour éviter ce gâchis d’énergie, ce serait que le serveur constitue initialement un « paquet » avec les enregistrements qui nous intéressent (uniquement le mois de mai 2007 par ex.), qu’il mettrait ensuite à disposition des requêtes successives pour filtrer par IP, par page vue, par jour, etc.

Créer une table temporaire

C’est par hasard que je suis tombé sur la fonction CREATE TEMPORARY TABLE, qui convient pile poil à ce type de besoin. A condition d’avoir les privilèges nécessaires, on peut créer sur la base SQL une ou plusieurs tables temporaires qui seront automatiquement détruites à la fin de l’exécution du script.

La syntaxe pour créer la table est très simple, et cela requiert très peu de ressources. Il faut que les champs de la table correspondent à ceux de la table de log, puisque l’étape suivante consiste à remplir la table temporaire avec les enregistrements voulus de la table principale :

 $table_temp = $nom_de_la_table_log . '_temp'; 
// Je crée une table avec les mêmes colonnes que ma table d'origine 
mysql_query("   
  CREATE TEMPORARY TABLE $table_temp   (   
    `nompage` varchar(127) NOT NULL default '',   
    `ip` varchar(15) NOT NULL default '',   
    `date` datetime NOT NULL default '0000-00-00 00:00:00',   
    `referrer` varchar(127) NOT NULL default ''   
  )   TYPE=MyISAM"); 

$periode = "MONTH(date) = " . $_GET['mois'] . " AND YEAR(date) = " . $_GET['annee']; 

// J'insère les enregistrements de la période $periode dans la table temporaire 
mysql_query ("     
  INSERT INTO $table_temp         
  (SELECT nompage,ip,date,referrer         
  FROM $nom_de_la_table_log         
  WHERE $periode) ") 
OR DIE ("Erreur insertion temp table"); 

Il ne reste plus qu’à effectuer les requêtes suivantes sur cette table temporaire :

mysql_query("SELECT val1 FROM $table_temp WHERE $critere1"); 
mysql_query("SELECT val2 FROM $table_temp WHERE $critere2"); 
mysql_query("SELECT val3 FROM $table_temp WHERE $critere3"); 
mysql_query("SELECT val4 FROM $table_temp WHERE $critere4"); 
mysql_query("SELECT val5 FROM $table_temp WHERE $critere5"); 
mysql_query("SELECT val6 FROM $table_temp WHERE $critere6"); 

 

Chaque requête s’effectuera sur un paquet de données réduit au strict nécessaire. Au bout d’un an, on aura grosso modo divisé par 12 le temps de calcul de chaque requête, passée la première. J’ai pu constater un temps de calcul passant de 25 secondes à 10 secondes par ce biais, ce qui reste important mais beaucoup plus raisonnable pour deux ou trois consultations quotidiennes.

Aller plus loin dans l’optimisation

Je suis bien conscient qu’on est loin d’un résultat complètement optimisé, mais je voulais surtout démontrer le gain réalisé via l’utilisation de CREATE TEMPORARY TABLE. Le choix d’une table unique pour l’enregistrement des visites est la cause de cette surcharge. Sur de tels volumes, on gagnerait par exemple à créer des tables multiples qui collectent des données déjà agrégées (visites uniques par jour, par mois, referrers, moteurs de recherche, etc.) lors de la visite : le supplément de temps de calcul pour insérer trois ou quatre valeurs serait infime pour chaque visiteur. Mais outre la multiplication des tables qui oblige à une gestion plus complexe, cette méthode a le défaut de « figer » les valeurs et impose de savoir à l’avance les variables que l’on souhaite mesurer.

Avec des logs bruts, on peut croiser les critères : par exemple filtrer toutes les visites en provenance de www.google.fr sur la journée du 7 mai 2007 et voir quelles pages ont été vues en priorité. L’analyse est plus fine qu’avec des tables qui donnent une valeur unique pour la journée. A moins de créer une table pour chaque referrer et chaque IP de visiteur, mais là on risque de tourner fou. Et si l’on conserve les logs des mois précédents, on peut évaluer certains phénomènes passés inaperçus, comme un accroissement soudain du nombre de visiteurs, en réalité causé par un moteur de recherche qui utilise différentes IP pour parcourir vos pages. Ou comment découvrir que vos stats étaient surestimées de 20%…

Une solution alternative consisterait en un compromis entre l’option « log brut » et l’option « tables agrégées ». Elle consisterait à placer en cache les données compilées à partir du log brut. Si les critères changent (lors de l’ajout d’un nouveau moteur de recherche dans la liste des IP filtrées), une simple fonction régénèrera le cache à partir du log brut. Cette opération sera très longue, mais elle ne sera pas exécutée plus d’une fois par mois. Lors de la consultation quotidienne, seule la partie non mise en cache depuis la dernière visite sera recalculée, d’où un gain de performances potentiellement énorme.

La principale difficulté ici sera d’effectuer les calculs de moyenne à partir de données disparates, issues soit du cache, soit du log brut. Si j’arrive à trouver le temps de m’y coller, je ferai part de mes conclusions sur ce blog.

Et vous, avez-vous eu à traiter des données « lourdes » ? Quelles optimisations avez-vous mises en place ?

Outil de référencement professionnel - essai gratuit Ce contenu a été publié dans Développement PHP, avec comme mot(s)-clé(s) , , , , , . Vous pouvez le mettre en favoris avec ce permalien.

8 réponses à Tutorial MySQL : alléger des requêtes successives avec CREATE TEMPORARY TABLE

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *