Quelle que soit la technologie que vous utilisez pour votre site web ou intranet, s’il s’agit d’un site dynamique, il y a fort à parier qu’il fonctionne avec un système de gestion de base de données relationnelle (SGBDR). Dans ce domaine MySql est le leader incontestable depuis plusieurs années. Voici quelques astuces pour profiter au mieux de ses performances.
1. Optimiser la structure
- Lors de la conception, utiliser la forme normale permet de réduire la redondance des données et donc la taille de la base de données. Concrètement, si une chaîne se répète plusieurs fois dans un champ, il est en général préférable de créer une table et de remplacer ce champ par une clé étrangère numérique.
- Utiliser de préférence des clés primaires numériques autoincrémentées simplifie l’indexation et améliore les performances par rapport à des clés de chaînes de caractères et/ou des clés composées.
- Créer des index pour chaque clé etrangère et/ou champ de recherche permet d’améliorer les performances lors des requêtes avec jointure ou des recherches.
- Utiliser le moteur de données MyISAM plutôt que InnoDB améliore les performances de vos requêtes SELECT.
- Utiliser l’encodage UTF-8 vous permettra de gérer tous les caractères internationaux sans problème d’encodage.
2. Optimiser la configuration
- Des outils comme MySqlTuner permettent d’analyser l’usage moyen de votre serveur MySql et d’obtenir des conseils pour optimiser la configuration.
- Le serveur MySql est souvent installé sur la même machine que le serveur web, dans ce cas on peut configurer MySql pour utiliser les sockets UNIX au lieu de sockets TCP, dans la plupart des cas cela permet une meilleure performance.
- L’ouverture et la fermeture de connexions au serveur de données est une opération coûteuse en temps. Utiliser des connexions persistentes économise ces 2 opérations sur chaque page du site. Attention cependant le partage de connexions ne fonctionne pas dans le cas de processus séparés (CGI).
3. Optimiser les requêtes SQL
- Evitez si possible les SELECT * et réduisez le nombre de champs, afin de réduire les données chargées en mémoire.
- Remplacez vos clauses WHERE … IN par WHERE EXISTS.
- Comptez les requêtes sur chaque page, un grand nombre de requêtes peut signifier un « problème N+1 », c’est à dire une requête SELECT placée dans une boucle
- Utilisez les requêtes préparées ou les procédures stockées facilite la mise en cache des requêtes en interne par MySql et vous assure un bon niveau de sécurité.
- Utilisez la clause EXPLAIN pour comprendre le fonctionnement d’une requête et quelles sont les clauses qui impactent ses performances.
4. Utiliser la mise en cache
- MySql dispose d’un mécanisme de mise en cache interne, réglez cette valeur au maximum suivant la RAM dont vous disposez.
- Sinon vous pouvez utiliser un système de cache externe comme Memcache. L’intérêt d’un cache externe est de décharger le serveur MySql tout en ayant l’efficacité du cache en RAM.
5. Maintenir la base de données
- Des tables volumineuses peuvent causer des erreurs, ralentir le site voire causer des erreurs (crash table). Si vous disposez de tables temporaires pensez à mettre en place un script de purge et/ou de réparation régulière.
- MySql dispose d’une fonction de réparation et d’une fonction d’optimisation de base de données, planifiez ces tâches pendant la nuit juste avant la tâche de sauvegarde quotidienne.
Pour conclure, le meilleur conseil que l’on puisse vous donner et d’avoir à votre disposition un bon administrateur de bases de données ou si vous êtes motivés, de le devenir vous-mêmes. Si vous avez des doutes sur la performance de votre base de données, n’hésitez pas à consulter nos experts.