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

  1. Samir dit :

    Bonjour,

    J’ai aussi affaire à ce genre de problème. Et la solution été d’utiliser les tables temporaires.
    Au début Mes scripts fonctionnent à grand V. Mais au file du temps les données récupérés dans les tables temporaires deviennent assez importantes, et mes scripts rament énormément.
    J’ai fait un petit test de comparaison avec une table temporaire et une table simple => La table temporaire réagit mal face à un flux de données importants

    Me voila donc entrains de changer tout mes scripts :s

  2. acharrex dit :

    Salut,

    J’ai également eu ce genre de soucis sur mon site. En effet, je devais afficher une liste d’articles avec pour chacun d’eux les 3 derniers prix entrés (depuis une table dans laquelle je stockais tous les changements de prix).

    Ces prix étaient affichés dans 3 colonnes différentes et chacunes d’entre elles pouvaient être triées par ordre croissant ou décroissant. Une table temporaire s’imposait donc car elle me facilitait grandement la vie.

    Mais au final, je me suis aperçu qu’un script PHP sélectionnant tous les prix de chaque article et les triant dans un tableau, était 2 fois plus rapide que la création d’une table temporaire!

    Pour l’instant, le temps de chargement reste correcte car j’essaie de n’afficher qu’une dizaine d’enregistrements par page. Mais c’est vrai qu’avec un flux important, cette "fonctionnalité" reste très lente.

  3. Afaf dit :

    Merci pour le tuyau,
    en ce qui me concerne,j’ai utilisé un cache dans lequel je transfère tous le resultat html d’une requete, pris dans la base une seule bonne fois pour toute !
    ensuite pour la même requete(car les requêtes peuvent se répeter) c’est dans le cache que je vais chercher le résultat, avec quelques traitements sur ce cache comme les tris et pages suivantes!
    c’est super rapide 100 fois plus rapide que l’accès à la base qui n’est sollicitée qu’une seule fois pour une requete nouvellement formulée
    les caches sont des fichiers .php qui portent le nom cache suivi de l’identifiant de la requete et donc l’accès est direct!

  4. Boby Brown dit :

    Salut, je trouve ton article tres interessant, je suis aussi dans la meme situation avec des logs montant a plus de 350 000 entrees par jour, a savoir que l’on compte monter a plus a 1 000 000 entrees par jour. Aussi, le probleme que j’ai est lie au reporting qui du coup prend un temps absoluement phenomenal.

    La premiere solution que j’ai mis en place est un systeme de caching des donnees ‘pseudo statis’ (nb impressions jour precedent, semaine precedente, …) de sorte a ne calculer ces valeurs qu’une seule fois par jour. Du coup, je passe de 8 variables a 2 variables a mettre a jour pour le reporting en temps reel.

    Maintenant, je ne peux pas passer par des tables temporaires parce que j’ai beaucoup trop de donnees et les performances seront exactement les memes que celle que j’ai actuellement. Aussi, je pense mettre en place des tables de reporting intermediaire de sorte a calculer le total quotidien pour differents criteres via des CRONs et baser le reporting global sur l’utilisation de ces valeurs.

    Pour ce qui est du reporting avance, malheureusement je ne pense pas qu’il y de solution evidente pour optimiser les queries…

    Encore merci pour cet article !!!

    Boby Brown

    Ps: Kill the SPAM !!!

  5. voila... dit :

    Et les "datawarehouse" ?
    C’est exactement le principe expliqué plus haut mais normalisé…

    Création de plusieurs tables afin de créer une granularité définie et précalculée. (a l’heure, au jour, a la semaine, au mois et a l’annee… voir meme par type (interne,moteur recherche, externes…)).

    Pour un "historique" de requetes entrantes je vois pas l’interet de recalculer des stats à partir d’une table d’un million de lignes!

    Par contre ce problème ce pose dès que l’on entre dans un calcul de moyennes… :s

    Les reporting en temps réel ok, mais uniquement si ils se basent sur la granularité la plus petites… mais ce n’est plus du "VRAI" temps réel… :s

  6. Raton dit :

    Bonjour,

    L’enchainement des requêtes allonge énormément le temps d’exécution. Il vaudrait mieux faire une requête unique avec les différents critères, puis trier les réultats de l’array. C’est plus compliqué que de faire une requête par critère mais beaucoup plus rapide, et ça devrait être faisable vu que tous tes SELECT sont de même nature.

    Un autre facteur ralentissement est la quantité de code Html qu’on demande d’afficher. Par exemple si on affiche 100 entrées par page, en enlevant 10 caractères superflus par ligne on économise 1000 caractères.

    HS: il y a un Parse error en bas de cette page :
    Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING, expecting ‘)’ in /home.2/lepotlat/www/ecrire/tools/bbclone/var/access.php on line 861

    A +

  7. Michaël dit :

    ça peut être intéressant, mais je n’aurait pas raisonné comme ça moi.

    J’aurais plutôt fait une seule requête (au lieu des 6 successives), avec un « order by `date` », pour ensuite traiter les résultats dans le code php afin de séparer l’affichage des données bien comme il faut.

    D’une manière générale d’ailleurs, je limite au maximum le nombre de requete, car un traitement mysql est plus long la plupart du temps.

  8. Keturah dit :

    Merci pour l’article

Laisser un commentaire

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