Mysql tunning

Voici un rapport concernant l'optimisation de mysql suite à des lenteurs (Doc fournie à Agarik par le prestataire d'un client)

On peut aussi utiliser un script pour tunner mysql : http://mysqltuner.pl/mysqltuner.pl

Piste pour l'optimisation

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 !


function utime_temps()
{
$t=microtime();
$t=((double)strstr($t, ' ')+(double)substr($t,0,strpos($t,' ')));
return $t;
}

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

log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 1

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 :
SELECT * FROM tutoriaux WHERE categorie=1

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 :

SELECT * FROM tutoriaux WHERE id_cat IN
(SELECT id_cat FROM tutoriaux_categorie WHERE id_cat_mere=2)

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

SELECT * FROM tutoriaux WHERE id_cat_mere=2

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 :

SELECT *,count(c.nb_com) as nb FROM tutoriaux t LEFT JOIN tutoriaux_commentaire c WHERE t.id=c.id_tutoriaux GROUP BY t.id

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 :

SELECT * FROM tutoriaux

Ce n’est pas plus simple ?