Comment optimiser les performances de MySQL

L'optimisation des performances MySQL est cruciale pour les applications gérant de grandes quantités de données ou un trafic élevé. Ce guide couvre les techniques essentielles pour améliorer les performances MySQL, du réglage de la configuration à l'optimisation des requêtes.

Optimisation de la configuration MySQL

Modifiez le fichier de configuration MySQL pour optimiser les performances en fonction des ressources de votre serveur :

# Modifier la configuration MySQL (Ubuntu/Debian)
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Modifier la configuration MySQL (CentOS/RHEL)
sudo nano /etc/my.cnf

# Ajouter ou modifier ces paramètres :
[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
max_connections = 200
query_cache_size = 64M
query_cache_type = 1
tmp_table_size = 64M
max_heap_table_size = 64M

# Redémarrer MySQL
sudo systemctl restart mysql  # Ubuntu/Debian
sudo systemctl restart mysqld   # CentOS/RHEL

Optimisation InnoDB

InnoDB est le moteur de stockage par défaut de MySQL. Optimisez-le pour de meilleures performances :

# Dans /etc/mysql/mysql.conf.d/mysqld.cnf ou /etc/my.cnf
[mysqld]
# Pool de tampons InnoDB (utilisez 70-80% de la RAM disponible)
innodb_buffer_pool_size = 2G

# Taille du fichier journal InnoDB
innodb_log_file_size = 512M

# Méthode de vidage InnoDB (pour SSD)
innodb_flush_method = O_DIRECT

# Threads d'E/S InnoDB
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# Vidage du journal InnoDB à la validation de la transaction
innodb_flush_log_at_trx_commit = 2

Configuration du cache de requêtes

Le cache de requêtes peut améliorer les performances pour les charges de travail intensives en lecture (Note : Le cache de requêtes est déprécié dans MySQL 8.0) :

# Pour MySQL 5.7 et antérieur
[mysqld]
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M

# Vérifier le statut du cache de requêtes
mysql> SHOW VARIABLES LIKE 'query_cache%';

# Vérifier les statistiques du cache de requêtes
mysql> SHOW STATUS LIKE 'Qcache%';

Optimisation des index

Une indexation appropriée est cruciale pour les performances des requêtes :

# Analyser la table pour mettre à jour les statistiques d'index
ANALYZE TABLE table_name;

# Vérifier les index non utilisés
SELECT * FROM sys.schema_unused_indexes;

# Créer un index
CREATE INDEX idx_column ON table_name(column_name);

# Créer un index composite
CREATE INDEX idx_multi ON table_name(col1, col2, col3);

# Afficher les index
SHOW INDEXES FROM table_name;

# Supprimer un index non utilisé
DROP INDEX idx_name ON table_name;

Optimisation des requêtes

Optimisez vos requêtes SQL pour de meilleures performances :

• Utilisez EXPLAIN pour analyser les plans d'exécution des requêtes : EXPLAIN SELECT * FROM table WHERE column = 'value'
• Évitez SELECT * - sélectionnez uniquement les colonnes nécessaires
• Utilisez LIMIT pour restreindre les ensembles de résultats
• Utilisez des clauses WHERE appropriées avec des colonnes indexées
• Évitez les fonctions dans les clauses WHERE (par exemple, WHERE YEAR(date) = 2026 → WHERE date >= '2026-01-01')
• Utilisez JOIN au lieu de sous-requêtes lorsque c'est possible
• Utilisez UNION au lieu de OR pour plusieurs conditions lorsque c'est approprié

Surveillance des performances MySQL

# Vérifier le statut MySQL
mysql> SHOW STATUS;

# Vérifier les requêtes lentes
mysql> SHOW VARIABLES LIKE 'slow_query%';

# Activer le journal des requêtes lentes
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

# Vérifier la liste des processus
mysql> SHOW PROCESSLIST;

# Vérifier le statut de la table
mysql> SHOW TABLE STATUS LIKE 'table_name';

# Vérifier le statut InnoDB
mysql> SHOW ENGINE INNODB STATUS;

Conseils d'optimisation des performances

• Exécutez régulièrement OPTIMIZE TABLE pour défragmenter les tables
• Surveillez le journal des requêtes lentes et optimisez les requêtes lentes
• Utilisez le regroupement de connexions pour réduire la surcharge des connexions
• Partitionnez les grandes tables par date ou plage pour de meilleures performances
• Utilisez des répliques en lecture pour les charges de travail intensives en lecture
• Maintenez MySQL à jour avec la dernière version stable
• Surveillez les E/S disque et envisagez d'utiliser un stockage SSD/NVMe
• Utilisez EXPLAIN ANALYZE (MySQL 8.0+) pour une analyse détaillée des requêtes