Sur un de mes projets utilisant une base de données MySQL avec plusieurs millions d'enregistrements, j'ai constaté avec stupeur d'énormes lenteurs sur mes requêtes UPDATE.
En effet, mes requêtes s'effectuaient en boucle et le serveur MySQL explosait en consommation CPU.
Ma requête était aussi simple comme ci-dessous :
UPDATE matable set cible='nouvelle_valeur';
Mais l'exécution de cette requête pouvait dépasser les 60 secondes sur ma table de plusieurs millions de lignes.
J'avais un serveur suffisamment dimensionné pour le projet avec 16Go de RAM sur un VPS.
Après quelques analyses, il s'est avéré que le problème provenait de l'absence d'indexation du champs cible.
Phase 1 : diagnostique
Il est important de comprendre ce qui tourne ou qui consomment de la ressource pour comprendre pourquoi votre base de données devient lente.
Pour cela utiliser au passage une simple commande top, si vous êtes sur Debian, permettra de comprendre qui vous consomme le plus de la ressource.
Sur MySQL vous pouvez vérifier les requêtes qui tournent en prenant beaucoup de temps.
SHOW FULL PROCESSLIST
Vous verrez dans les résultats les requêtes, ainsi que le temps mis par chacune d'elle.
Phase 2 : correction
Dans mon cas, c'étaient plusieurs centaines de requêtes updates qui tournaient et prenaient énormément de temps.
Il a fallut donc lancer une requête EXPLAIN pour voir comment ma requêtes se comporte réellement.
EXPLAIN select * from matable where cible='nouvelle_valeur';
Le résultat de cette requête m'a montré que le système parcourait les millions de lignes de ma table pour chaque exécution. J'avais donc l'explication sur les lenteurs de mes UPDATE. Zut !!!
Eh bien, la solution à ce problème est simplement de créer un index pour mon champs cible. Ainsi, le système n'aura plus à parcourir toutes les lignes de ma table, mais seulement une seule. Ceci rend l'exécution extrêmement plus rapide pour les centaines de requêtes en boucle.
ALTER TABLE matable ADD INDEX (cible);
Phase 3 : vérification
Une fois l'index est crée sur le champs cible, vous pouvez immédiatement voir une parfaite amélioration des vos requêtes UPDATE. refaite un EXPLAIN comme indiqué précédemment et le résultat montrera que le système ne parcours d'une seule ligne de votre table.
Enseignement :
Pour une table volumineuse, si vous devez mettre à jour des données à une grande fréquence, il est important de créer des index sur les champs de filtre. Il s'agit notamment des champs de la clause where. Ceci permettra de gagner en performance. Bien entendu, il existe bien d'autres aspects d'optimisations de la bases de données, prenez toujours le temps de poser un bon diagnostique afin de trouver la bonne correction à appliquer.
.
Commentaires
Enregistrer un commentaire