Il existe une multitude d’astuces pour optimiser les performances d’une base de données MySQL. Cet article se concentre sur une série de recommandation concernant la structure d’une base de données MySQL.
Logo MySQL
Structure des tables
La structure des tables est un premier point à travailler, dès le début de la conception de la base. Voici une petite liste de recommandations:
- Ne pas utiliser un BIGINT si un TINYINT suffit amplement. Idem pour les autres types de données.
- Pour en savoir plus : valeurs max des données numériques en SQL
- Privilégier VARCHAR plutôt que CHAR pour stocker des données alphanumériques de longueurs variables. Ca permet d’économiser de l’espace mémoire car CHAR a une taille fixe, tandis que VARCHAR a une taille variable (ne fonctionne pas avec UTF8)
- Latin1 est plus rapide que UTF8 et UTF16
- Choisir avec parcimonie les moteurs de stockage (MyIsam, InnoDB …). Si c’est nécessaire il est possible d’utiliser différents moteurs de stockage selon les besoins.
Index
L’utilisation d’index est indispensable pour conserver de bonnes performances lors de la lecture des données.
- Utiliser des indexes sur les colonnes qui en ont besoin
- Ne pas utiliser des indexes là où ce n’est pas nécessaires. Il faut vérifier une à une toutes les requêtes SQL
- Lors d’un gros import de données, il est parfois plus rapide d’enlever un index puis de le remettre une fois l’import terminé
Données
Les données sont bien entendues importantes. Il convient tout de même de les maîtriser car le poids d’une base de données influe sur les performances.
- S’assurer d’utiliser TRIM() sur les données alphanumériques
- Eviter les données redondantes
- Pour les colonnes contenants des valeurs textuelles prédéfinies, il est préférable d’utiliser une colonne numérique faisant référence au texte. La correspondance peut se faire dans l’application utilisant la base de données ou via une table de correspondance.
- Compresser les données TEXT et BLOB
- Sur une table qui possède une colonne TEXT ou BLOB avec d’autres champs, il est parfois préférable de séparer la table en 2 pour avoir d’un côté le BLOB/TEXT et de l’autre les autres colonnes. Cela est valable spécifiquement si les autres données sont souvent lues alors que les données dans le BLOB/TEXT ne le sont pas.
Petite base au début, puis …
Lors de la conception d’une petite base de données les petites optimisations ne semble pas toujours indispensable. Toutefois, au fur et à mesure qu’une table devient de plus en plus imposante il faut regarder de plus en plus près les petits détails. Une surveillance accrue est nécessaire pour éviter des pertes de vitesse.
- Purger les données qui ne seront plus jamais utilisées
- Archiver les vieilles données qui ne sont plus utilisées mais qui pourront l’être ultérieurement
- Effectuer des tests régulièrement, au fur et à mesure que la table grossis
- Tester chaque changement sur un environnement de test
- A chaque gros changements, il faut plutôt y aller étape par étape. Un petit changement sur une table énorme peut avoir de graves conséquences
Quoi d’autre ?
Si vous avez quelques astuces supplémentaires qui n’ont pas été listées ci-dessus, n’hésitez pas à les partager dans les commentaires.
