Optimiser une base MySQL/MariaDB qui rame

Une page qui met 8 secondes à charger, un back-office WooCommerce qui mouline sur la liste des commandes, un export CSV PrestaShop qui timeout : dans 90 % des cas, le coupable est une requête SQL mal servie par la base. Avant de demander plus de RAM ou un serveur dédié, il faut diagnostiquer. Cette doc est la suite logique de mysql-cli : on part du principe que vous savez vous connecter et lire une table.

Profiler les requêtes lentes

Impossible d'optimiser ce qu'on n'a pas mesuré. Le slow query log enregistre toutes les requêtes qui dépassent un seuil de temps — c'est le point de départ.

Activer le slow query log

Sur un serveur Plesk, deux options :

• Si vous avez les droits admin : Tools & Settings > Database Servers > éditer les paramètres MySQL/MariaDB et activer slow_query_log.
• Sinon, ouvrez un ticket chez nous — on active le log pour votre instance, temporairement le temps du diagnostic.

Depuis une session MySQL, si votre utilisateur a les privilèges SUPER (rare en mutualisé, courant sur une base dédiée) :

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;        -- logue tout ce qui dépasse 1 seconde
SET GLOBAL log_queries_not_using_indexes = 1;  -- bonus : les requêtes sans index

Laissez tourner le temps de capturer un cycle d'usage réel (quelques heures, idéalement pendant une période de charge).

Analyser le log

mysqldumpslow agrège et classe le log. Le top 20 par temps total cumulé est généralement l'info la plus utile :

mysqldumpslow -s t /var/log/mysql/slow.log | head -20

Options de tri : -s t (temps total), -s at (temps moyen par exécution), -s c (nombre d'appels). Une requête qui prend 200 ms mais qui tourne 500 fois par page fait plus de dégâts qu'une requête de 3 s appelée une fois par jour.

EXPLAIN, la base du diagnostic

Vous avez votre requête suspecte. On regarde comment le moteur compte l'exécuter :

EXPLAIN SELECT * FROM commandes
WHERE client_id = 42 AND statut = 'payé';

Les colonnes à surveiller en priorité :

Variante plus verbeuse : EXPLAIN FORMAT=JSON, ou EXPLAIN ANALYZE (MySQL 8.0.18+ et MariaDB 10.1+) qui exécute la requête et donne les temps réels par étape.

Ajouter les bons index

Un index, c'est une table de lookup triée qui évite à MySQL de lire toutes les lignes. On en ajoute sur les colonnes qui apparaissent dans :

• Les clauses WHERE
• Les conditions de JOIN
• Les ORDER BY et GROUP BY

Si plusieurs colonnes sont filtrées ensemble, un index composite est plus efficace que plusieurs index séparés :

-- Pour WHERE client_id = X AND statut = Y
CREATE INDEX idx_client_statut ON commandes(client_id, statut);

-- L'ordre compte : mettre d'abord la colonne la plus sélective
-- (celle qui divise le plus le résultat)

Trop d'index, c'est aussi un problème

Chaque index est maintenu à chaque INSERT, UPDATE, DELETE. Sur une table qui écrit beaucoup, multiplier les index ralentit les écritures et gonfle le disque. Repérez les index jamais utilisés :

-- Lister les index d'une table
SHOW INDEX FROM wp_posts;

-- Stats d'usage via performance_schema (si activé)
SELECT object_schema, object_name, index_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'monclient_prod'
  AND count_star = 0
  AND index_name IS NOT NULL;

Les index à count_star = 0 n'ont jamais servi depuis le dernier redémarrage. Candidats au DROP INDEX.

Les cinq optimisations rapides à connaître

1. Passer les tables MyISAM restantes en InnoDB. MyISAM lock toute la table à chaque écriture et n'est pas crash-safe. InnoDB fait du row-level locking et survit aux coupures.

   ALTER TABLE ma_table ENGINE=InnoDB;

2. OPTIMIZE TABLE après un gros DELETE. InnoDB ne rend pas automatiquement l'espace disque libéré — cette commande défragmente et reconstruit les index.

   OPTIMIZE TABLE commandes;

3. innodb_buffer_pool_size. C'est la RAM allouée au cache InnoDB, paramètre le plus impactant côté serveur. Si votre volume grandit vite, ouvrez-nous un ticket, on vérifie que le tuning est aligné avec votre base.
4. Analyser les JOIN coûteux. Parfois la bonne réponse n'est pas d'optimiser mais de dénormaliser : ajouter une colonne redondante pour éviter un JOIN sur une table de 10 millions de lignes à chaque affichage.
5. LIMIT et SELECT ciblé. SELECT * tire toutes les colonnes y compris les gros TEXT. Sur une requête exploratoire ou une liste paginée, nommez les colonnes nécessaires et limitez à ce que vous affichez.

Spécifique WordPress

wp_options gonflée d'autoload

Toutes les options avec autoload='yes' sont chargées à chaque page. Des plugins abandonnés peuvent y laisser des blobs de plusieurs Mo.

SELECT option_name, LENGTH(option_value) AS taille
FROM wp_options
WHERE autoload = 'yes'
ORDER BY taille DESC
LIMIT 20;

Pour les options volumineuses d'un plugin dont vous n'avez plus besoin, passez-les à autoload='no' ou supprimez-les.

Transients expirés

WordPress ne purge pas toujours les transients expirés. Sur un site ancien, wp_options peut contenir des dizaines de milliers d'entrées obsolètes.

DELETE FROM wp_options
WHERE option_name LIKE '_transient_timeout_%'
  AND option_value < UNIX_TIMESTAMP();

Plus propre : le plugin gratuit Transients Manager fait le ménage avec une interface.

wp_postmeta, la jungle des sites anciens

Sur un site vieux de cinq ans, wp_postmeta peut peser plusieurs Go avec des meta orphelines (posts supprimés, plugins désinstallés). Un nettoyage périodique avec WP-CLI (wp db optimize) ou le plugin WP-Optimize fait beaucoup de bien.

Query Monitor

Plugin gratuit indispensable pour débugger : il affiche en bas de l'écran, en contexte réel de navigation, toutes les requêtes SQL jouées, avec leur temps, la fonction PHP appelante et le plugin responsable. Bien plus parlant qu'un slow query log quand on cherche le coupable d'une page précise.

WooCommerce et PrestaShop

Ces deux plateformes mettent énormément de pression sur MySQL. Avant même d'attaquer les index, activez un cache objet Redis (voir redis-cache) : une bonne partie des lectures répétitives (panier, sessions, métadonnées produit) sort alors du circuit SQL. C'est souvent le gain le plus rapide et le plus visible.

Côté requêtes coûteuses : la liste des commandes en back-office WooCommerce et l'export produit PrestaShop sont des classiques du slow query log. Sur des catalogues gros, regardez si vous pouvez indexer les champs de tri/filtre les plus utilisés (_order_total, _order_status, date_add).

MariaDB vs MySQL

Chez Datacampus, la plupart des serveurs tournent sur MariaDB récent (compatible drop-in avec MySQL dans 99 % des cas). MariaDB a souvent l'avantage sur certains patterns de requêtes, un optimiseur un peu plus malin et des améliorations spécifiques (threadpool, storage engines additionnels). Pour la très grande majorité des applications PHP, migrer entre les deux ne demande aucun changement de code.

Quand ouvrir un ticket chez Datacampus

Vous avez fait le tour : les index sont en place, EXPLAIN montre un plan propre, les transients sont purgés, Redis tourne. Et la même requête reste lente. Ouvrez-nous un ticket. À ce stade, le problème est probablement côté serveur :

• I/O saturées (disque occupé par un voisin bruyant sur un mutualisé)
• innodb_buffer_pool_size sous-dimensionné par rapport à la taille active de votre base
• Un paramètre MySQL/MariaDB qui mérite d'être ajusté pour votre profil de charge
• Un besoin de passer sur une base dédiée ou un VPS avec plus de RAM

On regarde les métriques côté infrastructure (utilisation disque, cache hit ratio InnoDB, threads actifs) et on vous dit si c'est un problème serveur ou si la piste est ailleurs. Chaque site chez nous tourne avec son utilisateur Linux dédié, ce qui nous permet d'isoler finement qui consomme quoi.