Les bases de données relationnelles MySQL et MariaDB sont largement utilisées en production.
Cependant, sans configuration adaptée, elles peuvent rapidement devenir un goulot d’étranglement pour les applications.
Dans cet article, nous allons explorer les leviers majeurs pour améliorer les performances : indexation, tuning serveur et mécanismes de cache.
Plan de l’article
- Analyse des performances et outils de diagnostic
- Bonnes pratiques d’indexation
- Paramètres critiques du serveur à ajuster
- Mécanismes de cache et optimisation applicative
- Réplication et partitionnement pour la scalabilité
- Outils de monitoring et tuning
- Bonnes pratiques générales
Analyse des performances
Avant toute optimisation, il faut identifier les requêtes lentes :
- Activer le slow query log :
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
- Utiliser
EXPLAINpour analyser les plans d’exécution :
EXPLAIN SELECT * FROM clients WHERE email = 'test@exemple.com';
- Outils :
pt-query-digest(Percona Toolkit), Performance Schema, MySQLTuner.
Indexation
Types d’index
- B-Tree : par défaut, efficace pour les recherches exactes ou plages.
- Hash : optimisé pour les recherches exactes (utilisé dans MEMORY).
- Fulltext : pour la recherche textuelle avancée.
Bonnes pratiques
- Indexer les colonnes utilisées dans les clauses
WHERE,JOINetORDER BY. - Préférer les index composites plutôt que plusieurs simples.
- Utiliser les covering indexes pour éviter d’accéder aux données.
- Surveiller la taille des index (coût en RAM et disque).
Exemple :
CREATE INDEX idx_client_email ON clients(email);
CREATE INDEX idx_commande_date_client ON commandes(client_id, date_commande);
Paramètres critiques du serveur
Dans my.cnf :
[mysqld]
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
tmp_table_size = 256M
max_connections = 300
query_cache_type = 0
query_cache_size = 0
Explications
- innodb_buffer_pool_size : doit représenter ~70% de la RAM (InnoDB).
- innodb_log_file_size : impacte les performances des transactions.
- tmp_table_size : taille max des tables temporaires en mémoire.
- max_connections : ajuster selon la charge réelle.
- query_cache : obsolète, à désactiver (sauf très vieux MySQL).
Mécanismes de cache
- Caching applicatif : Redis, Memcached pour stocker sessions, résultats de requêtes, objets.
- ProxySQL : proxy intelligent pour MySQL avec gestion du cache et routage.
- Prepared statements : évitent la recompilation des requêtes.
- Persistent connections (via pool de connexions).
Réplication et partitionnement
- Réplication Master-Slave : déléguer les lectures aux replicas.
- Réplication Master-Master : haute disponibilité mais plus complexe.
- Partitionnement : distribuer les données par plages (
RANGE) ou listes (LIST). - Sharding : au niveau applicatif pour les très grands volumes.
Outils de monitoring et tuning
- MySQLTuner : script d’analyse et recommandations.
- Percona Monitoring and Management (PMM) : dashboards avancés.
- Grafana + Prometheus : exporter metrics avec mysqld_exporter.
- pt-query-digest : analyse détaillée des requêtes lentes.
Bonnes pratiques générales
- Toujours tester en préproduction avant d’appliquer en production.
- Surveiller régulièrement la croissance des tables et index.
- Nettoyer les données inutiles et archiver.
- Mettre à jour MySQL/MariaDB pour bénéficier des optimisations récentes.
- Documenter toutes les optimisations effectuées.
Conclusion
Optimiser MySQL/MariaDB n’est pas seulement une question de configuration :
c’est un travail continu qui combine analyse des requêtes, indexation réfléchie, tuning des paramètres serveur et mise en place de caches efficaces.
En appliquant ces bonnes pratiques, vous garantirez des performances stables et une meilleure scalabilité de vos bases de données.
