PHP - Optimiser MySQL

Une règle d'or lorsque l'on conçoit une requête MySQL : ne ramener que ce dont on a besoin. Plus le nombre de données à ramener sera faible, plus ce sera rapide (à requête équivalente bien entendu). Prenons l'exemple d'une structure de table destinée à stocker des informations sur les membres d'un site, cette table s'appellera "membres" :

Les champs :

La fermeture de la connexion :

La clause LIMIT :

Imaginez un livre. Plus le livre contient de pages, plus vous allez mettre du temps à rechercher l'information désirée. C'est pour cela qu'il existe en général une table des matières au début ou à la fin du livre. Cette table vous permet d'accéder très rapidement sans feuilleter le livre à l'information que vous recherchez. Un index en MySQL fonctionne sur ce principe, à savoir qu'il va vous permettre d'accélérer nettement (une requête 10 fois plus rapide n'est pas un cas exceptionnel dans le cas d'un index bien utilisé) vos requêtes de sélection. Il faut cependant être conscient qu'un index ralentit les requêtes d'insertion ou de mise à jour (dans une moindre mesure) de donnés, il faut donc les utiliser avec parcimonie. Indexer tous les champs d'une table MySQL est stupide. D'une manière générale, il faut indexer les champs apparaissant dans une clause WHERE de la requête. Nous allons rajouter un champ date_inscription à notre table de membres, puis nous utiliserons une requête type pour récupérer les membres étant inscrits depuis moins d'une semaine. Voici la nouvelle structure de la table :

Une requête type consistant à récupérer les membres inscrits depuis moins de 7 jours consiste à faire ceci :

<?php
 mysql_connect('hote','user','passe') OR die('Erreur de connexion à la base');
 mysql_select_db('base') OR die('Selection de la base impossible');

 $requete=mysql_query('SELECT pseudo FROM membres WHERE date_inscription >'.time()-7*3600*24);

 mysql_close();

 while($r=mysql_fetch_array($requete))
 {
echo $r['pseudo'],'<br />';
 }
?>

Nous voyons donc que le champ "date_inscription" sera utilisé par MySQL pour savoir si le pseudo du membre doit être retourné ou non. On constate donc qu'il serait judicieux de placer un index sur le champ "date_inscription" pour pouvoir accélérer la recherche des pseudos concernés. Pour ajouter un index dans PHPMyAdmin, on clique sur la petite icône représentant un éclair, en face du champ "date_inscription". L'index est maintenant ajouté et la requête est optimisée.

Pensez également aux index multi colonnes : Si vous avez une requête de la forme 

SELECT pseudo FROM membres WHERE date_inscription > X AND id_membre BETWEEN  40 AND 60

Il est judicieux de créer dans ce cas un index sur deux colonnes (date_inscription et id_membre). Pour ce faire, dans PHPMyadmin, regardez la zone "créer une clé sur ... colonnes". Mettez "2" dans le nombre de colonnes et cliquez sur Exécuter. Vous avez accès à ceci :

Dans la zone "Type de clé" choisissez "Index". Choisissez ensuite comme premier champ "id_membre" et ensuite "date_inscription", puis cliquez sur "sauvegarder". Ne vous occupez pas de la colonne "taille" de de PHPMyadmin, cette colonne ne sert (en gros) que pour les index sur les champs de caractères (VARCHAR entre autres). L'avantage est double pour les champs de ce type : Si vous mettez "2" dans la zone "taille", l'index indexera les deux premiers caractères de chaque mot. Il serait peu recommandé de mettre la longueur maximale du champ dans cette zone car vous perdrez en performances et en espace disque.

Il est important de souligner que les index sur les champs de type numériques sont plus rapides à traiter par MySQL. Ainsi, il est peu recommandé d'utiliser dans les clauses WHERE les champs de type chaînes de caractères. Pour sélectionner des informations sur un membre, on n'utilisera donc pas son pseudo. La requête suivante n'est pas optimisée (même si un index est placé sur le champ pseudo) :

SELECT nom,prenom FROM membres WHERE pseudo = 'webmaster'

On préfèrera la requête suivante :

SELECT nom,prenom FROM membres WHERE id_membre = X

D'une part, vous gagnerez en performances, mais d'autre part vous gagnerez en espace disque car on a été obligé d'indexer le champ id_membre (vu que c'est un champ AUTOINCREMENTE, il doit être obligatoirement accompagné d'un index). Donc au lieu d'avoir deux index (l'un sur le champ id_membre et l'autre sur le champ pseudo) nous n'en avons qu'un, ce qui cumule les avantages que ce soit en terme de performances (en écriture ou en lecture) ainsi qu'en espace disque (indexer un champ de type "chaîne" prend plus de place).

Pour des données peu sensibles (compteur de connectés, système personnel de sessions utilisant MySQL, etc...) vous pouvez utiliser les tables de type HEAP (ou MEMORY selon les versions de MySQL). Ce type de table est très particulier puisque les données sont stockées dans la mémoire vive. Il n'y a ainsi plus d'accès au disque dur pour récupérer les données ce qui augmente nettement les performances.

Attention cependant, ce type de tables n'est pas à utiliser pour stocker des données sensibles, car en cas de redémarrage de MySQL ou/et de plantage du serveur, les données seront perdues.

Cette clause rajoutée aux requêtes de type SELECT va vous permettre d'auditer les performances de vos requêtes. Lorsque vous rajoutez le mot EXPLAIN devant votre requête de type SELECT (dans PHPMyadmin) vous allez pouvoir avoir quelque chose qui ressemble à ceci (avec des valeurs pouvant varier bien entendu) :

On constate qu'il y a plusieurs colonnes résultant de cette action. Voici une explication des colonnes retournées :

• Id : le numéro de la requête SELECT dans la requête globale.
• Select_type (quelques valeurs que peut prendre cette colonne) :
  • SIMPLE : SELECT simple (sans UNION ni sous- requêtes).
  • SUBQUERY : Premier SELECT d'une sous- requête.
• Table : Table utilisée.
• Type : type de jointure utilisée. Voici quelques types de jointures, du plus rapide au plus lent :
  • system : Cas rare, lorsque la table comporte une seule ligne (jointure de type CONST).
  • const : la table possède au plus une seule ligne répondant à la valeur demandée qui sera lue dès le début de la requête. Ce type de jointure est très rapide.
  • eq_ref : C'est le meilleur type de jointure possible. Ce type est utilisé lorsque toutes les parties d'un index sont utilisées par la jointure. L'index doit être de type UNIQUE ou PRIMARY KEY.
  • ref : Si MySQL ne peut pas retourner une seule ligne en fonction de la jointure, toutes les lignes ayant des valeurs correspondantes seront lues par MySQL.
  • ref_or_null : Comme le type ref, mais un coût supplémentaire pour les recherches ayant des valeurs NULL comme cas possible. Il est d'ailleurs recommandé de mettre vos champs en NOT NULL pour accélérer les traitements.
  • range : seules les lignes dans un intervalle donné seront lues dans l'index.
  • index : même chose que ALL, seul l'index est utilisé ce qui peut accélérer légèrement les performances étant donné que la taille du fichier d'index est généralement plus faible que celle des données.
  • ALL : à bannir, une analyse complète de la table est faite.
• Possible_keys : Cette colonne indique quels index MySQL va pouvoir choisir pour trouver les lignes correspondant à la requête.
• Key : Cette colonne indique l'index que MySQL aura finalement utilisé pour trouver les lignes correspondant à la requête.
• Key_len : Cette colonne indique la taille de la clé utilisée par MySQL.
• Ref : Cette colonne indique quelle colonne ou quelles constantes sont utilisées pour sélectionner les lignes de la table
• Rows : Estimation du nombre de lignes à parcourir avant d'obtenir le résultat. Plus cette valeur est faible, meilleures seront les performances.
• Extra : Cette colonne contient quelques informations supplémentaires très utiles sur la procédure de résolution de la requête. Voici quelques informations utiles :
  • Using filesort : MySQL va avoir besoin d'effectuer un second passage pour trier les lignes. Sur un petit nombre de lignes retournées ça peut convenir, mais essayez d'éviter d'obtenir des Usign filesort avec 1000 enregistrements retournés par exemple...
  • Using Index : MySQL n'ouvre pas la table et lit directement les informations dans l'index. C'est très rapide et devrait être présent le plus possible dans vos requêtes.
  • Using Temporary : MySQL va créer une table temporaire pour contenir le résultat. Cela se passe généralement en cas d'utilisation d'une clause ORDER BY sur une colonne différente de celle utilisée pour la clause GROUP BY. Cela nuit aux performances.
  • Using Where : Une clause Where sera utilisée pour limiter le nombre de lignes retournées.

De nombreux forums (payants ou gratuits) tel que PHPBB ou encore IPB, Simple Machines Forum, etc... sont très peu optimisés ce qui fait que dès que les forums en question deviennent fréquentés (avec un nombre de messages conséquent) le temps de génération de chaque page augmente très rapidement en parallèle. Ces forums ne supportent donc pas une forte charge qui pourrait leur être imposée. Face à ce problème, il n'y a généralement (après avoir désactivé toutes les fonctionnalités inutiles gourmandes proposées) pas d'autre solution que de changer le serveur, car reprogrammer entièrement de tels forums est une pure perte de temps (par où commencer ? il y a tellement de travail...). Le problème de ces forums est qu'ils utilisent pour sélectionner une tranche de topics (ou de messages) la clause LIMIT dans leurs requêtes MySQL (mal conçues). La solution ultime consiste à concevoir soi-même son propre forum. Bien sûr cela demande du temps et de bonnes connaissances en optimisation PHP et MySQL (sous peine de refaire les mêmes erreurs). Il existe cependant de bons forums gratuits open source. Je peux vous en citer un : Punbb. Ce forum est léger, personnalisable et disponible en français.

Une bonne solution pour réaliser son forum personnel est d'utiliser les requêtes de type BETWEEN. Il faudra généralement pour cela rajouter un ou deux champs supplémentaires (deux dans la table des topics et un dans la table des messages) si vous avez des sous catégories. Sinon un seul champ par table suffira. Ce champ servira de "marqueur de position" et c'est sur ce champ que sera fait le BETWEEN, ce qui fait que seuls les topics de la page désirée seront scannés, le forum tiendra ainsi la charge même avec plusieurs milliers de topics/messages.