Optimiser les performances de vos bases de données MySQL/MariaDB : indexation, cache et tuning serveur

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.
Trois leviers font la différence : indexation, tuning serveur et mécanismes de cache.

• 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

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 EXPLAIN pour analyser les plans d'exécution :

EXPLAIN SELECT * FROM clients WHERE email = 'test@exemple.com';

• Outils : pt-query-digest (Percona Toolkit), Performance Schema, MySQLTuner.

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, JOIN et ORDER 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);

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).

• Caching applicatif : Redis, Memcached pour stocker sessions, résultats de requêtes, objets. Découvrez caching Redis pour optimiser vos performances applicatives.
• 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 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.

Pour une comparaison avec une autre base relationnelle, consultez notre guide sur l'optimisation PostgreSQL qui aborde des stratégies similaires.

• 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.

• 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.
• Déployer une stratégie de sauvegarde système pour protéger vos données.

Optimiser MySQL/MariaDB ne se résume pas à la configuration. C'est un travail continu : analyse des requêtes, indexation réfléchie, tuning des paramètres serveur et caches.
En appliquant ces pratiques, vous tenez des performances stables et un scaling propre.