Optimisation de la base de données d’un site autonome : requêtes lentes, index et structure des tables
1. Pourquoi la base de données WordPress ralentit
WordPress utilise MySQL comme base de données. La structure de tables par défaut tient la route pour un petit site, mais plus le contenu s’accumule, plus tout ralentit. La cause n’est pas la configuration du serveur, mais deux problèmes de conception : la table wp_postmeta stocke tous les champs personnalisés dans une seule table clé-valeur, ce qui oblige à faire de multiples JOIN pour lire les paramètres d’un produit ; la table wp_options mélange tous les réglages, et chaque chargement de l’administration balaie la table entière.
Une page WordPress exécute en moyenne 12 à 20 requêtes, dont la plupart interrogent wp_postmeta. Avec 10 extensions installées, chacune écrivant ses champs personnalisés dans wp_postmeta, cette table peut gonfler jusqu’à plusieurs centaines de milliers de lignes. La moindre requête de page contenant un meta_query doit alors balayer ces centaines de milliers de lignes, et les requêtes lentes apparaissent.
| Sources fréquentes de requêtes lentes | Table concernée | Symptôme typique | Part |
|---|---|---|---|
| Filtrer des articles par champ personnalisé | wp_postmeta | La page de filtrage produits met plus de 3 secondes à charger | environ 40 % |
| Charger la configuration d’une extension dans l’administration | wp_options | Écran blanc de quelques secondes après chaque enregistrement dans l’administration | Environ 20 % |
| Pages d’archive par étiquette/catégorie | wp_term_relationships | Les pages d’archive ralentissent quand les articles sont nombreux | Environ 15 % |
| Requêtes sur les tables de commentaires et d’utilisateurs | wp_comments/wp_users | Liste de commentaires ou gestion des utilisateurs lente | environ 10 % |
| Requêtes complexes propres à une extension | Tables créées par l’extension | Une page de fonctionnalité particulièrement lente | Environ 15 % |
Quand la base de données ralentit, le cache de page peut masquer une partie du problème : en cas de cache hit, aucune requête n’est exécutée. Mais au cache miss (juste après la publication d’un article, ou sur une page non mise en cache), les requêtes lentes se révèlent. L’optimisation de la base de données et la stratégie de cache doivent donc être pensées ensemble, voir Stratégie de cache à trois niveaux pour un site indépendant. Si la base de données est déjà si lente qu’elle pénalise les Core Web Vitals, voir les priorités d’optimisation dans Optimiser les Core Web Vitals.
2. Journal des requêtes lentes : comment trouver les requêtes SQL lentes
La première étape pour optimiser une base de données n’est pas d’ajouter des index, mais de trouver quelle requête SQL est lente. Impossible de le deviner à l’œil nu : vous pensez qu’une requête est lente, alors que c’en est peut-être une autre. MySQL intègre un journal des requêtes lentes : une fois activé, il enregistre toutes les requêtes dépassant un seuil.
Dans la configuration MySQL (my.cnf ou my.ini), ajoutez trois lignes : slow_query_log=1, long_query_time=1, slow_query_log_file=/var/log/mysql/slow.log. Autrement dit : activer le journal des requêtes lentes, enregistrer les requêtes de plus d’une seconde, écrire le journal dans le fichier indiqué. Après une journée d’exécution, utilisez mysqldumpslow ou pt-query-digest pour regrouper le journal par type de requête et identifier le TOP 10 des requêtes les plus lentes.
| Étape | Actions | Outil / commande | Objectif |
|---|---|---|---|
| 1. Activer le journal des requêtes lentes | Modifier my.cnf et ajouter trois lignes de configuration | Redémarrer MySQL pour appliquer | Enregistrer les requêtes lentes |
| 2. Laisser tourner une journée | Faire tourner le site normalement pendant une journée | — | Collecter les vraies requêtes lentes |
| 3. Agréger et analyser | Regrouper par empreinte SQL | mysqldumpslow -s t | Identifier le TOP 10 |
| 4. EXPLAIN une par une | Examiner le plan d’exécution | EXPLAIN la requête SQL | Vérifier si l’index est utilisé |
| 5. Retester après optimisation | Comparer les temps avant et après optimisation | SHOW PROFILE ou chronométrage | Confirmer le résultat |
Exemple concret : vous constatez que la page de filtrage des produits est particulièrement lente. En ouvrant le journal des requêtes lentes, vous voyez que la requête la plus lente (TOP 1) est une requête SQL avec meta_query qui interroge un champ personnalisé de la table wp_postmeta. En l’analysant avec EXPLAIN, la colonne type affiche ALL (balayage complet de la table) et la colonne rows indique 120 000 lignes scannées : cette requête parcourt la table wp_postmeta à chaque exécution. Vous savez alors qu’il faut ajouter un index sur le champ meta_key, et non changer de serveur.
Ne laissez pas le journal des requêtes lentes activé en permanence : il génère lui-même des E/S. Désactivez-le après une journée de collecte, ou réglez le seuil à 2 secondes pour ne consigner que les plus lentes. Pour la surveillance quotidienne, utilisez un outil de monitoring de performance de base de données (comme Prometheus + Grafana), voir Checklist de surveillance et d’alerte du site.
III. Optimisation des index : quels champs indexer
Dans le résultat d’EXPLAIN, les deux colonnes les plus importantes sont type et rows. Si la colonne type affiche ALL, la requête fait un balayage complet de la table et doit être indexée ; si elle affiche ref ou eq_ref, l’index est déjà utilisé, inutile d’y toucher. La colonne rows indique le nombre de lignes que MySQL estime devoir parcourir : plus le chiffre est élevé, plus c’est risqué. Jusqu’à 10 000 lignes, ça va ; au-delà de 100 000, il faut optimiser.
Dans une base de données WordPress, la table qui mérite le plus d’être indexée est wp_postmeta. Par défaut, elle n’a qu’un index sur post_id, alors que les requêtes filtrent souvent par meta_key et meta_value. Ajoutez un index composite (meta_key, meta_value) : les requêtes filtrant par champ personnalisé deviennent plus de 10 fois plus rapides. Attention, il s’agit d’un index composite, pas d’un index sur meta_key seul : avec un index sur meta_key seul, un filtrage sur meta_value oblige toujours à revenir à la table.
| Table | Cas de requête | Index recommandé | Effet |
|---|---|---|---|
| wp_postmeta | Filtrer par meta_key + meta_value | (meta_key, meta_value(191)) | Plus de 10 fois plus rapide |
| wp_postmeta | Consulter les meta par post_id | Index post_id déjà présent | Inutile d’y toucher |
| wp_options | Consulter la configuration par option_name | Index option_name déjà présent | Inutile d’y toucher |
| wp_comments | Requêtes de commentaires par post_id | Index comment_post_ID déjà présent | Inutile d’y toucher |
| wp_posts | Requêtes par post_type + post_status | Index composite déjà présent | Inutile d’y toucher |
| Tables créées par l’extension | Filtrage par date / statut | À voir avec EXPLAIN | À ajouter au besoin |
Contre-intuitif : beaucoup de gens ajoutent un index dès qu’une requête est lente, mais un index n’est pas gratuit. Chaque index occupe de l’espace disque, et à chaque INSERT ou UPDATE, MySQL doit mettre à jour tous les index. Sur une table comme wp_options, qui s’écrit beaucoup et se lit peu (chaque enregistrement de configuration depuis l’admin écrit dedans), ajouter des index au hasard ralentit les écritures. La bonne approche : sur les tables lues souvent et écrites rarement (wp_postmeta, wp_posts), on peut ajouter des index sans hésiter ; sur celles écrites souvent et lues rarement (wp_options, wp_comments), on y va prudemment, et si possible pas du tout.
Avant d’ajouter un index, testez toujours en staging. Un ALTER TABLE directement sur la base de production peut verrouiller une grosse table plusieurs minutes et faire tomber le site en 502. En production, utilisez un outil de DDL en ligne comme pt-online-schema-change ou gh-ost, qui ne verrouille pas la table. Retrouvez l’expérience de maintenance de crater sur les bases de sites à l’international sur La stack technique de crater et ses quatre extensions maison.
4. Optimisation des tables : les pièges de wp_postmeta et wp_options
Certains problèmes ne se règlent pas avec un index : il faut modifier la structure de la table. La conception clé-valeur de wp_postmeta en fait partie. Elle stocke tous les champs personnalisés dans une seule table, et pour récupérer les caractéristiques d’un produit, il faut plusieurs JOIN. Quand on atteint quelques milliers de produits et plusieurs dizaines de champs personnalisés, cette table gonfle jusqu’à plusieurs centaines de milliers de lignes et le gain de l’optimisation par index plafonne.
Deux pistes s’offrent alors : remplacer postmeta par une table personnalisée, en créant une table dédiée pour les champs personnalisés les plus souvent interrogés, stockés par type de champ, et en interrogeant directement la table concernée ; ou utiliser le mécanisme autoload de wp_options de WordPress, en activant le chargement automatique des options lues souvent et écrites rarement, pour éviter d’interroger la table options à chaque fois. Pour un site B2B autonome, la première piste convient aux sites aux paramètres produits complexes, la seconde aux sites qui accumulent les extensions.
| Question | Symptôme | Axe d’optimisation | Risque |
|---|---|---|---|
| wp_postmeta gonfle | Pages de filtrage lentes, table de plus de 100 000 lignes | Créer une table dédiée pour les champs fréquents | Modification du code, tests nécessaires |
| Trop d’options en autoload dans wp_options | Admin lent, autoload supérieur à 1 Mo | Nettoyer les configurations des extensions inutilisées | Sauvegarder avant de supprimer |
| Trop de brouillons / révisions dans wp_posts | Liste des articles lente, révisions inutiles accumulées | Nettoyer les révisions et brouillons automatiques | Vérifier avant de supprimer |
| wp_commentmeta gonfle | Liste des commentaires lente | Désactiver l’enregistrement automatique des spams | Impact limité |
| Table wp_users volumineuse | Gestion des utilisateurs lente | Nettoyer les utilisateurs inactifs | Attention aux suppressions par erreur |
Exemple concret : le site d’un fabricant d’équipements industriels compte 50 produits, chacun avec 20 paramètres personnalisés (matériau, puissance, dimensions, précision, etc.). Ces paramètres sont tous stockés dans wp_postmeta, et afficher la fiche d’un produit oblige à joindre wp_postmeta 20 fois. La bonne approche consiste à créer une table personnalisée wp_product_specs et à stocker les 20 paramètres en colonnes sur une seule ligne : la lecture des caractéristiques du produit se fait alors en un seul SELECT, sans JOIN. Le travail de développement reste limité, mais le gain de performance est d’un tout autre ordre de grandeur.
Sauvegardez la base de données avant toute modification de structure. Pour la sauvegarde et les tests de restauration, voir Sauvegarde et restauration : s’entraîner à réagir. Si la base a déjà été piratée, la procédure de nettoyage et de durcissement est décrite dans Que faire après un piratage de son site.
V. Vérifier et maintenir après optimisation
Après avoir ajouté un index ou modifié la structure d’une table, le travail n’est pas terminé. Trois vérifications s’imposent. D’abord, relancez avec EXPLAIN la requête lente d’avant l’optimisation et vérifiez que la colonne type passe de ALL à ref ou eq_ref. Ensuite, comparez les temps d’exécution réels avant et après pour confirmer que la requête s’est vraiment accélérée. Enfin, surveillez pendant une semaine le journal des requêtes lentes pour repérer d’éventuelles nouvelles requêtes lentes : une optimisation déplace souvent le goulot d’étranglement d’un endroit à un autre.
| Élément à vérifier | Méthode de vérification | Critère de conformité | En cas d’échec |
|---|---|---|---|
| Plan d’exécution EXPLAIN | Relancer EXPLAIN sur la requête lente | type n’est plus ALL, rows divisé par dix | L’index ne s’applique pas, vérifiez le type des champs |
| Temps d’exécution réel | Chronométrer directement le SQL | Passer de plus d’une seconde à moins de 100 millisecondes | Index inadapté, refaire l’analyse |
| Journal des requêtes lentes | Consulter le journal après 3 jours | Les requêtes lentes ont disparu ou fortement diminué | De nouvelles requêtes lentes apparaissent, poursuivre l’optimisation |
| Performance en écriture | Mesurer le temps des INSERT/UPDATE | L’écriture n’a pas ralenti de façon notable | Trop d’index, supprimez les index redondants |
| Temps de chargement des pages | Score Lighthouse | LCP en cas de cache miss<1.5秒 | Il reste d’autres goulots d’étranglement |
L’optimisation de la base de données n’est jamais terminée. La croissance du contenu, les mises à jour d’extensions et les refontes de thème introduisent de nouvelles requêtes lentes. Analysez le journal des requêtes lentes une fois par mois pour voir si le TOP 10 a changé. L’optimisation de la base fait partie de l’entretien courant, voir Les six tâches mensuelles de la maintenance d’un site. Pour le cadre général de choix techniques d’un site indépendant, voir Stack technique crater, et pour l’articulation entre mise à niveau de PHP et configuration de la base, voir Guide de maintenance du site.
FAQ — Questions fréquentes
Comment activer le journal des requêtes lentes MySQL de WordPress ?
Pourquoi la table wp_postmeta est-elle si lente ?
Ajouter un index ralentit-il le site ?
Comment lire un résultat EXPLAIN ?
Ajouter un index sur une base de production verrouille-t-il la table ?
À quelle fréquence optimiser la base de données ?
Pour conclure
En matière d’optimisation de base de données, le piège le plus courant est de changer de serveur dès que ça ralentit. Passer de 1 cœur et 2 Go à 4 cœurs et 8 Go coûte le double, et les requêtes lentes restent lentes : le goulot d’étranglement n’est pas le CPU, mais un balayage complet de table faute d’index. Le bon ordre est toujours le même : activez d’abord le journal des requêtes lentes pour identifier la requête SQL exacte qui pose problème, examinez ensuite son plan d’exécution avec EXPLAIN, et décidez seulement à ce moment-là d’ajouter un index ou de modifier la structure de la table.
Trois étapes concrètes : premièrement, activez le slow query log pendant une journée et utilisez mysqldumpslow pour identifier le TOP 10 ; deuxièmement, analysez chaque requête avec EXPLAIN, ajoutez un index là où type=ALL, en priorité un index composite (meta_key, meta_value) sur wp_postmeta ; troisièmement, retestez après optimisation pour confirmer que les requêtes lentes passent de 1 seconde à moins de 100 millisecondes. Une fois ces trois étapes terminées, vous constaterez que la configuration du serveur n’a pas changé d’un iota, mais que les pages sont plusieurs fois plus rapides.
Une phrase à retenir : quand la base de données ralentit, cherchez d’abord le slow log, n’augmentez pas la puissance du serveur — 80 % des requêtes lentes sont des full table scans sans index, et ajouter le bon index est plus efficace que de monter d’un cran la configuration.