Optimiser MySQL : sauvegarde, index et requêtes lentes

Pourquoi optimiser MySQL quand on dirige une PME

Derrière un site WordPress, une boutique en ligne ou un logiciel métier, il y a presque toujours une base de données. À chaque page affichée, le site envoie des requêtes à MySQL : lister des produits, vérifier un compte, enregistrer une commande. Tant que les tables sont petites, tout va vite. Ensuite les données grossissent, et des requêtes qui passaient sans souci se mettent à ramer.

Le visiteur, lui, ne voit pas la base. Il voit une page qui met du temps à charger, un panier qui hésite, un back-office qui gèle. Ces lenteurs côté serveur pèsent sur le temps de réponse, donc sur les Core Web Vitals et sur la conversion. Améliorer la mysql performance, c'est donc protéger le chiffre d'affaires du site, pas seulement faire plaisir aux développeurs.

Ce qui fait varier le temps et le coût du travail

Il n'y a pas de durée type. Tout dépend de quelques facteurs que l'on peut identifier à l'avance.

• Le volume de données et le nombre de tables concernées.
• L'accès dont vous disposez : hébergement mutualisé, serveur virtuel ou serveur dédié.
• La qualité du code qui génère les requêtes : extension WordPress, framework, code maison.
• L'existence ou non d'une sauvegarde récente et d'un environnement de test.
• Le besoin de continuité : peut-on couper le site quelques minutes, ou pas du tout ?

Étape 1 : sauvegarder the database avant tout

On ne touche jamais à une structure de table sans copie de secours. Ajouter un index, reconstruire une table ou changer un paramètre peut mal tourner, surtout sur une grosse base. La sauvegarde est votre filet. Elle s'inscrit aussi dans un vrai plan de reprise d'activité.

Les deux familles de sauvegarde

• La sauvegarde logique, avec mysqldump : elle produit un fichier SQL lisible. Sur des tables InnoDB, l'option --single-transaction donne une copie cohérente sans bloquer le site.
• La sauvegarde physique, avec des outils comme Percona XtraBackup ou MySQL Enterprise Backup : elle copie les fichiers de données, ce qui va plus vite sur les gros volumes.
• Le journal binaire (binary log) : activé sur le mysql server, il permet de rejouer les modifications jusqu'à un instant précis, entre deux sauvegardes complètes.

Le piège : la sauvegarde jamais testée

Une sauvegarde qui n'a jamais été restaurée ne vaut pas grand-chose. Restaurez-la de temps en temps sur une copie du site pour vérifier qu'elle est complète. Automatisez les exports avec une tâche cron et gardez au moins une copie hors du serveur. Sur WordPress, le guide pour sauvegarder un site WordPress détaille la marche à suivre.

Étape 2 : trouver les requêtes lentes

Optimiser sans mesurer, c'est deviner. MySQL sait pourtant vous dire quelles requêtes coûtent cher. Il suffit de le lui demander.

Activer le journal des requêtes lentes

Le slow query log enregistre chaque requête qui dépasse un seuil fixé par le paramètre long_query_time. On l'active avec slow_query_log = 1. L'option log_queries_not_using_indexes y ajoute les requêtes qui lisent une table sans index. Ces fichiers se lisent comme les autres logs serveur.

Faire le tri

Un journal brut devient vite illisible. L'outil mysqldumpslow, livré avec MySQL, regroupe les requêtes semblables. pt-query-digest, de la Percona Toolkit, les classe selon le temps total consommé. Sur MySQL 8, le schéma performance_schema et la vue sys.statement_analysis offrent la même vision sans fichier à analyser.

Classez vos requêtes par temps cumulé, pas par durée unitaire. Une requête de deux secondes lancée une fois par jour pèse moins qu'une requête rapide exécutée des milliers de fois par heure. C'est là que la query optimization rapporte le plus.

Étape 3 : lire le plan d'exécution avec EXPLAIN

Une fois la requête coupable trouvée, on demande à MySQL comment il compte l'exécuter. Placez EXPLAIN devant the query et lisez le résultat. Depuis MySQL 8.0.18, EXPLAIN ANALYZE va plus loin : il exécute vraiment la requête et affiche les temps réels de query execution.

Les colonnes à regarder en premier

• type : la valeur ALL signale une lecture complète de la table, ligne par ligne. C'est souvent le coupable.
• key : l'index choisi par the optimizer. Une valeur NULL veut dire qu'aucun index n'est utilisé.
• rows : une estimation du nombre de lignes lues. Plus elle est élevée face au résultat attendu, plus il y a de gâchis.
• Extra : Using filesort ou Using temporary indiquent un tri ou une table temporaire. Using index est bon signe : MySQL répond depuis l'index sans lire la table.

Réécrire avant d'indexer

Certaines lenteurs viennent de l'écriture de la requête elle-même. SELECT * ramène des columns inutiles : listez seulement celles dont vous avez besoin. Une fonction appliquée à une colonne indexée dans la where clause, comme YEAR(date_commande) = 2025, empêche l'usage de l'index. Écrivez plutôt une plage de dates. De même, un LIKE qui commence par % ne profite d'aucun index classique.

Étape 4 : poser les bons index

Un index fonctionne comme celui d'un livre. Au lieu de feuilleter toutes les pages, MySQL va droit aux lignes utiles. Avec InnoDB, la primary key sert en plus à ranger physiquement les données : elle doit rester courte et stable, un entier auto-incrémenté le plus souvent.

Index simple ou composite

Un index composite couvre plusieurs colonnes dans un ordre précis. MySQL ne peut s'en servir qu'en partant de la gauche : un index sur (client_id, date_commande) aide une recherche par client, mais pas une recherche par date seule. Placez d'abord les colonnes filtrées par égalité, puis celles utilisées pour une plage ou un ORDER BY.

Le revers des index

Chaque index a un coût. Il occupe de la place sur le disque et en mémoire, et il ralentit les INSERT et les UPDATE, puisque MySQL doit le tenir à jour. Ne créez pas un index par colonne « au cas où ». Supprimez les doublons et ceux que personne n'utilise : la vue sys.schema_unused_indexes aide à les repérer.

Étape 5 : régler le serveur et entretenir les tables

Le réglage du serveur vient en dernier, une fois les requêtes assainies. Sinon, on met plus de mémoire au service de requêtes mal écrites.

La mémoire d'abord

Le paramètre clé d'InnoDB est innodb_buffer_pool_size : c'est la mémoire où MySQL garde les données et les index les plus lus. Sur un serveur dédié à la base, la documentation conseille d'y consacrer une large part de la RAM. Sur un serveur partagé avec PHP et le serveur web, il faut trouver un équilibre. Le vieux cache de requêtes, lui, a été supprimé dans MySQL 8 : inutile de le chercher.

Entretenir les tables

Après de grosses suppressions, une table garde de l'espace vide. OPTIMIZE TABLE la reconstruit et rend cet espace. ANALYZE TABLE met à jour les statistiques qui guident l'optimiseur dans le choix des index. Sur WordPress, révisions et transients s'accumulent : le guide pour nettoyer WordPress explique comment alléger la base sans risque.

Ressources pour explorer plus loin

Le manuel officiel de MySQL consacre un chapitre entier à l'optimisation. Il est en anglais, mais bien structuré. Voici les sections à lire en priorité, avec leur titre d'origine pour les retrouver facilement.

• « Optimizing SELECT Statements » : réécrire les requêtes de lecture.
• « Optimization and Indexes » : comment MySQL utilise les index et la clé primaire.
• « Understanding the Query Execution Plan » : tout sur EXPLAIN et EXPLAIN ANALYZE.
• « Optimizing Database Structure » : types de colonnes, taille des tables, choix du moteur.
• « Optimizing the MySQL Server » : mémoire, disque et paramètres.
• « Measuring Performance (Benchmarking) » : mesurer avant et après chaque changement.

Si votre site repose sur un logiciel conçu pour vous, l'optimisation se pense dès la conception du schéma : c'est ce que nous faisons en création de logiciel, sur devis. Pour un site vitrine sur-mesure, RankerSquare propose une formule dès 19,90 € par mois en abonnement.

Questions fréquentes