MySQL est le système de gestion de base de données relationnelle le plus utilisé au monde pour les applications web. Mais une base mysql mal configurée ou des requêtes non optimisées peuvent rapidement devenir un goulot d'étranglement critique. Voici comment l'optimiser efficacement.
Les index : premier levier d'optimisation
Les index accélèrent drastiquement les requêtes SELECT en évitant les full table scans. Sans index, MySQL parcourt chaque ligne de la table. Avec un index, il localise directement les lignes concernées.
-- Vérifier les index existants
SHOW INDEX FROM users;
-- Créer un index simple
CREATE INDEX idx_email ON users(email);
-- Index composé pour les requêtes multi-colonnes
CREATE INDEX idx_status_created ON orders(status, created_at);
-- Index unique pour les contraintes d'unicité
CREATE UNIQUE INDEX idx_unique_email ON users(email);
Règle pratique : indexez les colonnes utilisées dans WHERE, JOIN ON, ORDER BY et GROUP BY. Mais n'abusez pas : chaque index ralentit les INSERT, UPDATE et DELETE.
EXPLAIN : comprendre l'exécution des requêtes
EXPLAIN révèle le plan d'exécution choisi par mysql pour une requête. C'est l'outil diagnostique essentiel :
EXPLAIN SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC;
Regardez les colonnes type (ALL = mauvais, ref/range = bien, const = parfait), rows (nombre de lignes examinées) et Extra (Using index = excellent, Using filesort = à optimiser).
Requêtes lentes : identification et correction
Activez le slow query log pour identifier les requêtes problématiques :
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- Requêtes > 1 seconde
Les causes fréquentes de lenteur : absence d'index, SELECT * (récupérer plus de colonnes que nécessaire), sous-requêtes non optimisées, et jointures sur des colonnes non indexées.
Réécrire une sous-requête corrélée en JOIN peut réduire le temps d'exécution de 10x à 100x.
Optimisation des requêtes JOIN
Les jointures sont efficaces quand les colonnes de jointure sont indexées des deux côtés. mysql utilise l'index de la table « drive » pour chercher dans la table « driven ». Assurez-vous que les types de colonnes correspondent exactement : une jointure entre un INT et un VARCHAR force une conversion implicite et invalide l'index.
-- Bien : types correspondants, colonnes indexées
SELECT * FROM orders o
JOIN users u ON u.id = o.user_id -- u.id: INT PK, o.user_id: INT indexed
WHERE o.created_at > '2025-01-01';
Configuration serveur MySQL
Quelques paramètres clés à ajuster dans my.cnf selon votre RAM disponible :
innodb_buffer_pool_size = 1G # 70-80% de la RAM pour un serveur dédié
query_cache_size = 256M # Cache des résultats de requêtes (MySQL 5.x)
max_connections = 200
innodb_log_file_size = 256M
Pour MySQL 8.0, le query cache a été supprimé au profit du query rewrite plugin et du caching applicatif (Redis, Memcached).
Partitionnement et archivage
Pour les très grandes tables (millions de lignes), le partitionnement par RANGE sur une date permet d'isoler les données anciennes et d'accélérer les requêtes sur des périodes récentes. L'archivage régulier des données inactives dans des tables séparées maintient les performances sur le long terme.
La documentation officielle MySQL est une référence complète sur toutes les techniques d'optimisation.
Conclusion
L'optimisation mysql est un travail continu. Commencez par identifier les requêtes lentes avec le slow query log, analysez-les avec EXPLAIN, ajoutez les index manquants et réécriveez les requêtes problématiques. Un mysql bien optimisé peut gérer des millions de requêtes par jour sur du matériel modeste.
L'optimisation MySQL est un processus continu. Activez le slow query log (longquerytime = 1) pour identifier les requêtes lentes en production. Désactivez le Query Cache MySQL 5.7 souvent contre-productif et utilisez Redis à la place. Pour les grandes tables, la partitioning par date ou ID réduit drastiquement les temps de scan complet. Surveillez les métriques clés : Threadsconnected, Innodbbufferpoolhit_rate (visez > 99%), et le nombre de Questions par seconde. Percona Monitoring and Management est l'outil de référence pour le monitoring MySQL avancé et la détection proactive des problèmes de performances en production.
