Quelles peuvent être les causes des lenteurs d’une base de données ?
De nombreux facteurs peuvent entrer en compte pour causer des lenteurs Mysql. Voici les principaux :
- Mauvaise conception de la base de donnée : choix inapproprié des types de champs, jointures mal faites, type de table non optimisé...
- Index inexistant, insuffisant, ou mal choisis :
- Trop d’index : et oui, ça vient en contrepartie du point précédent mais c’est bien réel.
- Utilisation des privilèges au niveau des tables et des colonnes des tables : lors de chaque accès MySQL doit vérifier les droits sur les tables et les colonnes des tables, ce qui ralenti fortement les performances.
Utiliser correctement les index
Un index est une table de recherche qui permet au moteur de base de données de retrouver rapidement des lignes dans une table.
Lorsqu’une table n’a pas d’index,ou si il est mal défini, lors de la recherche d’un enregistrement toutes les lignes devront être parcourues.
Au niveau de MySQL les index sont stockés sous forme d’arbres binaires (b-trees), qui sont des structures de données faciles et rapides à parcourir.
Un index peut inclure une ou plusieurs colonnes.
Lors d’une recherche portant sur la ou les colonnes indexées, l’index sera appelé et donc parcouru rapidement afin de retrouver l’enregistrement correspondant dans la table d’origine.
Avec MySQL l’indexation des tables est automatique en se basant sur les champs ayant les contraintes PRIMARY, KEY, UNIQUE.
Le principe à retenir est que si une ou plusieurs colonnes (champ) est/sont fréquemment utilisé pour exécuter une recherche, il est nécessaire de créer l’index correspondant afin d’optimiser le temps d’exécution de la requête.
Un index se crée grâce à la commande CREATE INDEX.
Mais attention à ne pas tomber dans l’excès inverse !
En effet, autant les index sont la solution pour améliorer un temps d’exécution d’une requête en lecture (un SELECT par exemple), autant la présence d’index va ralentir toutes les opérations d’écriture (INSERT, UPGRADE...). Une mise à jour va nécessiter, en plus de la mise à jour de la table, la mise à jour du ou des index.
Il faut donc bien choisir les index en fonction de l’utilisation de la table (d’où l’intérêt de l’étape de modélisation).
Modélisation optimale de la base de données
C’est là aussi un problème couramment rencontré, surtout avec les débutants en base de données.
Afin de mettre en place des bases de données performantes, il est indispensable d’utiliser les bonnes méthodologies de conception et de modélisation de base de données.
De nombreux facteurs sont à prendre en compte, tels que (liste non exhaustives, loin de là) :
- Choisir un type de variable adapté au besoin : inutile de choisir un Bigint pour stocker un nombre ne dépassant pas 5 chiffres, ne pas utiliser le type Text pour une chaine de 50 caractères maxi... Bref, vous devez utiliser les champs les plus petits possible (mais bien penser à tous les cas d’utilisation possible).
- Augmenter l’utilisation des contraintes NOT NULL quand cela est possible pour optimiser l’espace de stockage.
- Choisir le bon type pour les tables : MySQL permet d’avoir au niveau d’un même schéma des tables de types différents.
- Bien choisir ses index de table
- Utiliser des champs à longueur fixe plutôt qu’à longueur variable
- ...
Nous vous conseillons fortement la découverte de la méthode MERISE
Optimiser les tables
Équivalente à la défragmentation de disque que tout le monde connait, la commande OPTIMIZE TABLE permet de défragmenter les tables d’une base de données.
A effectuer régulièrement sur des tables fréquemment mise à jour.
Optimiser les requêtes
MySQL nous permet d’analyser les requêtes et de connaître leur temps et plan d’exécution. Ces informations vont permettre de comprendre ce qui rend ces requêtes lentes et d’en optimiser l’exécution.
En premier lieu, il est nécessaire d’identifier les requêtes lentes MySQL.
Une fois ces requêtes identifiées, utiliser la commande EXPLAIN (exemple : EXPLAIN SELECT * FROM ma_table ) afin de comprendre leur exécution, les points de ralentissements, et de pouvoir l’optimiser.
