Optimiser MySQL (2)

La nécessité

L’optimisation Mysql peut devenir une réelle nécessité que vous soyez en hébergement mutualisé ou en dédié. Votre site répondra beaucoup plus rapidement et donc attirera plus de visiteurs avec des requêtes optimisés. Sans compter l’économie d’un serveur dédié moins puissant !

Nous allons voir donc quelques principes de bases sans entrer dans le cœur de Mysql.

Detecter les requêtes non optimisés

Le premier but va être de détecter vos requêtes non optimisées. Pour cela vous avez déjà un moyen visuel trés simple. Vous cliquez sur une rubrique de votre site et le chargement est extrêmement long ? La page d’entrée du site se charge par étape ? Demandez-vous ce qui peut causer ces soucis, cela peut être une page trop lourde à charger, des images trop volumineuses ou des requêtes SQL non optimisés !

Un autre moyen consiste à utiliser aussi le calcul du temps pris par les requêtes Mysql avec PHP, voir par exemple ce tutoriel. Cela consiste à utiliser une fonction temps de PHP qui donnera des indications trés précises sur le temps utilisé. Vous mettez cette fonction avant et après la requête et vous affichez le résultat !

Pour ceux qui sont en dédiés ou qui ont accès au fichier my.cnf dans /etc/mysql, vous pouvez rajouter ces lignes

dans le fichier my.cnf (et faire un restart de Mysql par exemple avec /etc/init.d/mysql restart). Avec ces paramètres les requêtes Mysql trop longue d’une seconde (paramètre long_query_time) seront indiquées dans le fichier mysql-slow.log. Ainsi la consultation de ce fichier vous permet simplement de cibler précisément vos optimisations.

Une autre fonction Mysql est aussi pratique : la fonction EXPLAIN. Il suffit par exemple dans PhpMyAdmin de positionner EXPLAIN devant la requête SQL que vous voulez analyser. Celle-ci retourne un résultat expliquant exactement ce que fait Mysql concernant cette requête. Et notamment la colonne rows indique le nombre de ligne parcouru (celui-ci doit être le plus petit possible) et la colonne key indique les clefs utilisées (ci c’est NULL alors aucune clef est utilisée pour cette requête)

Clef et index !

Une erreur classique consiste à négliger les clefs et les index. Dans une requête Mysql, lorsque vous utilisez la condition WHERE, celle-ci pourra être extrêmement améliorée par l’utilisation d’un index dans la condition WHERE. Par exemple :

Dans cette requête ci-dessous, si la table tutoriaux contient 100 000 enregistrements, Mysql va passer les 100 000 enregistrements pour chercher les tutoriaux de catégorie 1 !! C’est du temps perdu. Il suffit de mettre catégorie en index pour que de 100 000 enregistrements examinés on passe à …1 !

Vous allez me dire, dans ce cas là, pourquoi ne pas mettre des index partout ? Il y a plusieurs raisons à cela : d’abord un index prend de la place mémoire dans la table Mysql et si vous avez trop d’index, cette place mémoire peut être plus importante que la place que prend les données. Ensuite lorsque vous faites une insertion dans la table, Mysql est obligé de mettre à jour tous les index, ce qui prend du temps aussi !

Vous devez donc calculer ce qui est le plus intéressant pour vous. Si la requête est exécutée très souvent, il sera intéressant de mettre des index sur les conditions WHERE MAIS si vous faites beaucoup d’insertions dans la table, il faudra limiter les index au strict necessaire et calculer au plus juste avec EXPLAIN le gain réalisé en mettant un index.

Jointures et inclusions

Il faut faire attention à ne pas utiliser des requêtes trop complexe. Et surtout faire attention aux jointures et aux inclusions. Voici un exemple de requête :

Dans cette requête, nous recherchons des tutoriaux dont la catégorie appartient à la catégorie “mère” 2. Cette requête est très belle mais peut rapidement poser d’énormes problèmes de temps si elle n’est pas optimisée. Déjà, oublier un index sur id_cat par exemple ou encore oublier un index sur id_cat_mere surtout que cette dernière table sera plutôt fixe (nous ne rajoutons pas des catégories tous les jours) avec une table assez conséquente de plusieurs milliers d’enregistrements conduira à une requête s’exécutant très lentement…pouvant prendre plusieurs secondes !

Toutefois on peut faire une chose pour optimiser tout cela. Pourquoi ne pas mettre l’information id_cat_mere dans la table tutoriaux ? D’accord, c’est moins joli, c’est moins conceptuel et on devra faire plus de mise à jour si on change la table tutoriaux_categorie MAIS ALORS que de temps de gagner ! Puisqu’au final on arrive à ça

Avec un index sur id_cat_mere, la requête est exécutée immédiatement !

Concernant les jointures, c’est la même chose…Par exemple :

Ici cette requête recherche les tutoriaux avec une information supplémentaire : le nombre de commentaire sur chaque tutoriel. Comme les commentaires sont dans une autre table Mysql, une jointure est effectué permettant de récupérer le nombre d’enregistrements (donc de commentaires) concernant le tutorial. Si vous avez 10 000 tutoriaux et des centaines de milliers de commentaires, cette requête est totalement explosive (pour le serveur !)

Hors nous pouvons faire une chose très simple. Inclure dans la table tutoriaux un champ nb_commentaire. Alors bien sur chaque fois qu’un commentaire est ajouté, il faudra incrémenter le champ nb_commentaire mais au final que de temps gagné car la requête devient :

Lire l'article original : http://blog2.webdesign-tutoriaux.com/tutoriaux/optimiser-mysql/