Audit MySQL/MariaDB : optimiser les requêtes lentes e‑commerce

Table des matières :

1. Ce qu’on appelle “requête lente” en e‑commerce (et pourquoi c’est rarement juste un long_query_time)
2. Instrumentation fiable : slow query log, Performance Schema et métriques exploitables
3. Trier et comprendre : digest, pt-query-digest, et plans d’exécution avec runtime
4. Optimisations SQL qui paient : index composés, “covering”, pagination, et anti‑patterns classiques
5. Tuning InnoDB/MariaDB : mémoire, redo, I/O… sans tomber dans le réglage folklorique
6. Spécificités PrestaShop : modules, facettes, caches… et pourquoi la DB prend tout dans la tête
7. Contention et verrous : la vraie cause des “lenteurs aléatoires” en production
8. Industrialiser l’audit : staging réaliste, IaC, alerting, et critères de non‑régression
9. Check‑list d’intervention en production (avec risques et rollback)

Les requêtes lentes ne sont pas un concept abstrait en e‑commerce : c’est du TTFB en plus, des locks qui s’empilent, des threads PHP-FPM occupés, et au final du throughput en moins au pire moment (pics SEO, soldes, campagnes). Sur PrestaShop (8.1/9.x, PHP 8.2/8.3), le problème est souvent amplifié par un mix classique : schéma relationnel très jointé, modules qui ajoutent leurs propres tables sans vraie stratégie d’index, et pattern de requêtes “catalogue” (tri + pagination + filtres) particulièrement coûteux.

Dans les faits, quelques dizaines de millisecondes côté DB se répercutent vite sur l’expérience : si votre page fait 80 requêtes SQL et que 10 d’entre elles prennent 200 ms au lieu de 40 ms, votre budget “backend” explose. Et le TTFB est un signal suivi (notamment pour l’UX et, indirectement, la perception de vitesse) : voir la synthèse Google sur le sujet pour cadrer l’impact d’une latence serveur.

Ce qu’on appelle “requête lente” en e‑commerce (et pourquoi c’est rarement juste un long_query_time)

Dans un audit MySQL/MariaDB, la première erreur est de définir “lent” uniquement comme “> 1 s”. En e‑commerce, une requête à 200–300 ms répétée 200 fois par minute sur une route critique (liste produits, panier, checkout) vaut souvent plus cher qu’un export back-office à 2 s lancé 2 fois par jour. L’objectif n’est pas de “chasser toutes les requêtes lentes”, mais d’optimiser celles qui dégradent vos SLO applicatifs : p95/p99 de la latence HTTP, saturation CPU, contention InnoDB et backlog des workers.

Une règle simple (utile pour prioriser) : coût = temps moyen × fréquence. Exemple réaliste :

• Requête A : 250 ms × 120/min = 30 s de temps DB par minute
• Requête B : 2,5 s × 2/jour ≈ négligeable en production

Le contexte e‑commerce ajoute deux contraintes techniques. D’abord, la distribution est à longue traîne : beaucoup de petites requêtes, quelques “monstres” déclenchées par des filtres spécifiques (facettes, prix, stock multi-entrepôts). Ensuite, l’impact est souvent non linéaire : une requête qui passe de 80 ms à 350 ms peut faire basculer un pool PHP-FPM de “stable” à “en file d’attente” si vous êtes déjà proches de la limite de concurrence (voir aussi le diagnostic serveur/TTFB côté PrestaShop).

Enfin, il faut distinguer latence “DB pure” (temps d’exécution) et latence “DB observée” (queueing). Une requête peut être “rapide” isolément mais lente en production car elle attend :

• un verrou de ligne (row lock) sur une table fortement écrite (stock_available, cart, orders selon flux),
• un verrou metadata (DDL / ALTER TABLE qui bloque des requêtes),
• ou un goulot d’I/O (flush, temp tables sur disque, saturation du stockage).

C’est pour ça qu’un audit sérieux combine logs de requêtes + métriques InnoDB + analyse de contention, au lieu d’optimiser à l’aveugle des EXPLAIN pris hors contexte.

Instrumentation fiable : slow query log, Performance Schema et métriques exploitables

La base, c’est le slow query log, mais configuré pour capter ce qui compte. Sur une boutique, un long_query_time à 1 s rate souvent une grande partie des requêtes “toxiques” (celles entre ~150 ms et 800 ms, très fréquentes sur le catalogue).

En pratique, sur MySQL 8.0+ ou MariaDB 10.6/11.x, démarrez plutôt avec 0.2–0.5 s, en gardant un œil sur le volume généré. Exemple (à adapter, production = changement à valider + reload maîtrisé) :

[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=0.2
min_examined_row_limit=1000
log_slow_admin_statements=1
# À activer avec prudence : bruit important
# log_queries_not_using_indexes=1

Quelques précisions “terrain” qui évitent les pièges :

• min_examined_row_limit : utile pour filtrer le bruit (petites requêtes lentes à cause d’un lock, par exemple), mais attention : vous risquez de rater des requêtes peu gourmandes en lignes mais très fréquentes. En e‑commerce, il vaut souvent mieux laisser ce seuil modéré (1000 est un départ) puis ajuster avec l’analyse par digest.
• Rotation des logs : si le slow log écrit beaucoup, prévoyez une rotation via logrotate et un disque suffisamment rapide (sinon, vous “créez” de la latence). En cas de charge, évitez de stocker le slow log sur un FS déjà saturé.
• Échantillonnage : si votre trafic est massif (grosses campagnes, périodes de soldes), vous pouvez temporairement relever long_query_time (ex. 0.5 s) pour éviter une avalanche, puis revenir à 0.2 s pour une fenêtre d’analyse plus fine.

Le slow log seul n’est pas suffisant, parce qu’il ne donne pas une vision globale (top digests, variance, temps cumulé). Activez performance_schema (désormais standard) et exploitez les vues sys quand disponibles. Les tables performance_schema.events_statements_summary_by_digest permettent de classer par temps total, moyenne, et nombre d’exécutions — exactement ce qu’il faut pour décider “quoi traiter en premier”.

En parallèle, mettez des métriques “DB” dans votre monitoring (Netdata/Prometheus) : buffer pool hit ratio, Threads_running, Innodb_row_lock_time, temp tables on-disk, Handler_read_rnd_next. Un bon point d’appui pour câbler des collecteurs sans bricolage : article sur l’intégration Netdata/Prometheus.

Mini-checklist de métriques utiles (et ce qu’elles racontent) :

• Threads_running qui monte + latence HTTP qui grimpe : souvent queueing (verrous, I/O, CPU saturée).
• Created_tmp_disk_tables qui augmente : GROUP BY/ORDER BY qui débordent en temp tables disque (index manquant, tri coûteux, tmp_table_size trop bas).
• Handler_read_rnd_next élevé : symptômes de full scan (ou d’accès séquentiel via mauvais plan).
• Innodb_row_lock_time / Innodb_row_lock_waits : contention write (transactions longues, batchs, index absents sur UPDATE/DELETE).

Trier et comprendre : digest, pt-query-digest, et plans d’exécution avec runtime

Le bon niveau d’abstraction, c’est le digest (normalisation des requêtes). Sur PrestaShop, vous aurez des requêtes qui varient par id_lang, id_shop, id_customer, et qui deviennent illisibles si vous analysez “au cas par cas”. Le digest regroupe ces variantes et révèle la réalité : 3 digests font souvent 80 % du temps DB.

Pour exploiter le slow log sérieusement, Percona Toolkit reste une valeur sûre. pt-query-digest fournit un top par temps cumulé, des percentiles, et des exemples de requêtes (voir la documentation Percona Toolkit). Typiquement :

pt-query-digest /var/log/mysql/slow.log --limit=20 > /tmp/report.txt

Si vous voulez une vue rapide sans parser le slow log (utile en audit “à chaud”), vous pouvez aussi interroger performance_schema pour sortir un top digests par temps cumulé. Exemple (MySQL 8+ ; l’unité est en picosecondes dans performance_schema) :

SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_ms,
  ROUND(AVG_TIMER_WAIT/1e9, 2) AS avg_us
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

L’étape suivante est le plan d’exécution, mais pas le plan “théorique” uniquement. Sur MySQL 8.0.18+, EXPLAIN ANALYZE exécute la requête et produit un plan annoté avec des timings (référence : documentation MySQL EXPLAIN ANALYZE). Sur MariaDB, utilisez EXPLAIN FORMAT=JSON et les outils disponibles selon version (certaines versions ont des capacités de profiling/trace différentes). Le point clé : validez vos hypothèses sur données réalistes (staging cloné) et pas sur une base “light”. Pour créer un staging fidèle, l’approche Docker est souvent la moins douloureuse : guide staging Docker.

Conseil pratique : quand vous comparez deux variantes (index A vs index B, ou requête réécrite), mesurez au minimum :

• rows_examined / rows_sent (dans le slow log ou via EXPLAIN ANALYZE),
• présence de Using temporary / Using filesort,
• temps total et variance (une requête “rapide en moyenne” mais instable fait mal en p95/p99).

Optimisations SQL qui paient : index composés, “covering”, pagination, et anti‑patterns classiques

Sur les boutiques PrestaShop, les requêtes lentes “catalogue” ont des patterns récurrents : jointures sur product + product_shop + stock_available + product_lang, filtres (id_category, active, visibility), tri (price, date_add, position), et pagination. Si vos index ne couvrent pas l’ordre des colonnes réellement filtrées/triées, l’optimiseur finit en scan + filesort + temp table.

L’optimisation la plus rentable est souvent un index composite aligné sur le WHERE puis le ORDER BY (règle du préfixe gauche). Exemple concret (à adapter à votre schéma exact) : si vous avez une requête du type :

SELECT p.id_product
FROM ps_product p
JOIN ps_product_shop ps ON ps.id_product=p.id_product AND ps.id_shop=1
WHERE ps.active=1 AND ps.visibility IN ('both','catalog')
ORDER BY ps.date_add DESC
LIMIT 20 OFFSET 200;

Un index (id_shop, active, visibility, date_add, id_product) sur ps_product_shop peut drastiquement réduire les rows examinées et supprimer un filesort.

Deux points qui font souvent la différence en audit :

• Égalités d’abord, plage ensuite : si vous avez WHERE id_shop = 1 AND active = 1 AND date_add >= ... ORDER BY date_add, l’ordre des colonnes dans l’index a un impact réel. Les colonnes à forte sélectivité (ou très structurantes pour le filtrage) doivent être placées intelligemment, mais sans casser le besoin du tri.
• Index “covering” (couvrant) : si la requête ne lit que des colonnes présentes dans l’index, InnoDB peut éviter de remonter à la table (moins d’I/O et de pages à charger). C’est particulièrement intéressant pour des listings qui ne remontent que des IDs avant de faire une seconde requête plus ciblée.

Attention : l’ajout d’index n’est pas “gratuit” (écriture plus chère, taille disque, invalidation de cache). En audit, mesurez : rows_examined, temp tables on disk, et le temps cumulé par digest. Un gain typique observé sur des catalogues 100k–300k produits : passer d’un rows_examined ~2–10 millions à <50k sur les pages liste, avec une latence qui tombe de 600–900 ms à 80–150 ms sur le digest dominant (mais ça dépend du ratio cache, du stockage et de la distribution des données).

Côté anti‑patterns, trois reviennent tout le temps en e‑commerce :

• Pagination en OFFSET élevé (coût croissant). Si vous avez la main côté code, préférez la pagination “seek” (keyset pagination) basée sur une colonne stable triée (date_add/id). Sinon, limitez la profondeur et offloadez les pages profondes (SEO inclus) via stratégie de navigation.
  Mini-scenario : une catégorie très SEO avec 20 000 produits. Les pages 1–10 sont vues, mais les pages 200+ sont surtout crawlées. Un OFFSET 4000 sur un tri non indexé devient un aspirateur à CPU/IO.
• LIKE '%term%' sur des champs non indexables : basculez vers un vrai moteur (Meilisearch/Elasticsearch) au lieu de forcer MySQL à faire du full scan (ex. : mise en production Meilisearch).
• SELECT * + jointures “large” : surcharge I/O + buffer pool. Réduisez les colonnes, visez des indexes couvrants quand c’est pertinent.

Dernier levier souvent oublié : mettre à jour les statistiques. Après de grosses opérations (imports massifs, purge de tables), exécuter ANALYZE TABLE sur les tables les plus sollicitées peut stabiliser les plans (à faire prudemment et idéalement en période creuse, selon volume).

Tuning InnoDB/MariaDB : mémoire, redo, I/O… sans tomber dans le réglage folklorique

Avant de toucher aux paramètres, fixez le cadre : version exacte (MariaDB 10.6 LTS vs 11.4, MySQL 8.0), type de stockage, RAM, pattern de charge (lecture vs écriture). MariaDB 11.4 apporte des changements côté optimiseur et InnoDB qui peuvent modifier les plans et la stabilité sous charge (référence interne utile : article sur MariaDB 11.4). Ne transposez pas des “tuning guides” de 2018 sur une stack 2026.

Le paramètre le plus structurant reste innodb_buffer_pool_size. Sur un serveur DB dédié, viser 60–75 % de la RAM pour le buffer pool est un ordre de grandeur réaliste, mais uniquement si vous contrôlez le reste (OS cache, autres services, pics). La métrique à suivre : Innodb_buffer_pool_reads (lectures disque) vs Innodb_buffer_pool_read_requests (lectures logiques), et la latence I/O.

Ensuite viennent les réglages “I/O et durabilité” : taille des redo logs, fréquence de flush, et temp tables. Les symptômes d’un mauvais tuning sont visibles : Created_tmp_disk_tables élevé, Innodb_log_waits, latence en dents de scie.

Quelques axes concrets (à manipuler avec méthode) :

• Temp tables : si beaucoup de temp tables passent sur disque, vérifiez d’abord les index et les ORDER BY/GROUP BY, puis ajustez tmp_table_size et max_heap_table_size (ils se combinent : la limite effective est le minimum des deux).
• Redo / flush : des redo logs trop petits peuvent provoquer des flush agressifs et de la variance en charge (notamment sur périodes d’écriture : imports, synchro ERP, mises à jour de stock). Mais augmenter la taille/ajuster la durabilité peut modifier le comportement en cas d’incident : documentez et validez avec votre politique de risque.
• I/O capacity : sur SSD/NVMe, des valeurs trop conservatrices peuvent sous-utiliser le stockage, trop agressives peuvent provoquer des à-coups. Là encore : un changement, une mesure, puis itération.

Sur MariaDB/MySQL, documentez les paramètres, changez un axe à la fois, et mesurez sur une fenêtre de charge comparable (même heure, même type de trafic, mêmes jobs cron).

Spécificités PrestaShop : modules, facettes, caches… et pourquoi la DB prend tout dans la tête

PrestaShop “core” fait le job, mais il ne vous protège pas de l’entropie module. Les modules de recherche/facettes, d’analytics, d’exports, de marketplaces, ajoutent des jointures et des tables avec des indexes parfois approximatifs. Résultat : une boutique qui “marche” à 20k produits devient imprévisible à 200k. C’est un angle mort classique : on optimise la DB, alors que la vraie source est une requête générée dans un module avec un GROUP BY surdimensionné.

Dans un audit PrestaShop, identifiez explicitement :

• les requêtes “catalogue” (catégories, recherche, layered navigation/facettes),
• les écritures fréquentes (paniers, sessions, stats, logs),
• et les “jobs” (cron, import/export, re-indexations).

Un exemple très courant : un module de facettes qui, pour calculer les compteurs, fait un GROUP BY sur des tables volumineuses en période de trafic. Le coût est parfois invisible en moyenne, mais explose quand la table grossit ou quand le cache est invalidé.

Le cache applicatif est souvent un multiplicateur d’effet. Ne confondez pas “mettre Redis/Varnish” et “résoudre les slow queries” : le cache masque les symptômes sur une partie du trafic, puis s’effondre à la moindre invalidation (flush, déploiement, variation de panier, client loggué). Pour une stratégie 2026 réaliste (OPcache + Redis + Varnish + CDN, segmentation logged-in vs anonymous), appuyez-vous sur : stratégie cache PrestaShop 2026.

Autre point concret : la taille et la qualité de la base. Les tables de logs, de paniers abandonnés, de connexions, d’indexation peuvent grossir et dégrader les stats de l’optimiseur (cardinalités, distribution). Nettoyer la base n’est pas du “ménage cosmétique”, c’est une action de performance quand les tables deviennent disproportionnées et que les indexes ne tiennent plus en cache. Sur ce volet, le plan est détaillé ici : optimisation et nettoyage de la base.

Point “GEO” pragmatique (France/UE) : beaucoup de boutiques françaises ont des pics très marqués lors d’événements commerciaux (soldes, Black Friday, campagnes TV/radio) et un trafic “métropole” concentré (latence réseau faible, donc la lenteur perçue vient rapidement du serveur). Cela rend les dérives DB particulièrement visibles : si votre infra est proche des utilisateurs (hébergement en France/UE), chaque milliseconde serveur devient proportionnellement plus importante que la latence réseau.

Contention et verrous : la vraie cause des “lenteurs aléatoires” en production

Une partie des lenteurs ne vient pas de l’optimiseur mais des verrous. Un pic de mises à jour de stock, une importation, une re-indexation, ou un module qui fait des UPDATE massifs dans une transaction trop longue, et vous obtenez des lock waits qui se propagent partout. Symptôme typique : Threads_running augmente, la CPU n’est pas forcément à 100 %, mais la latence explose.

Pour diagnostiquer, combinez :

• SHOW ENGINE INNODB STATUS (instantané, utile mais verbeux),
• performance_schema.data_locks / data_lock_waits (vision structurée),
• information_schema.innodb_trx (transactions longues),
• et une corrélation temporelle avec vos jobs (cron PrestaShop, imports ERP, queues).

Exemple de requête “utile en war room” (MySQL 8+) pour repérer des transactions longues :

SELECT
  trx_id,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_s,
  trx_mysql_thread_id,
  trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started ASC
LIMIT 10;

Côté remédiation, la règle est simple : réduire la durée des transactions, batcher, éviter les scans + updates non indexés, et planifier les jobs hors charge. Typiquement :

• au lieu d’un UPDATE ... sur 500 000 lignes en une transaction, découpez en lots (5 000–20 000) avec commits intermédiaires ;
• assurez-vous que les WHERE de UPDATE/DELETE utilisent un index (sinon vous lockez “large” et longtemps) ;
• évitez les ALTER TABLE en plein trafic (metadata locks).

Si vous implémentez des triggers ou automatismes DB, attention : c’est puissant mais ça peut ajouter des locks et de la latence cachée. Et si vous touchez à la sécurité/permissions pour faire de l’observabilité (comptes de monitoring, exports), gardez un modèle “least privilege” (guide sécurité MySQL).

Industrialiser l’audit : staging réaliste, IaC, alerting, et critères de non‑régression

Optimiser une requête une fois ne sert à rien si elle régresse au prochain module ou au prochain import. Le niveau pro, c’est d’industrialiser : 1) reproduire la charge en staging, 2) valider des budgets de performance, 3) surveiller en continu les digests dominants. Sur des stacks modernes, vous pouvez même versionner une partie du paramétrage DB et de l’observabilité (sans mettre les secrets en clair) via Infrastructure as Code : article IaC et conteneurs.

Un pipeline minimal utile en agence/CTO : dump prod anonymisé (ou prod clone isolé), import dans staging, exécution d’un replay de trafic (k6/JMeter) focalisé sur routes e‑commerce, collecte des métriques DB (Prometheus) et extraction top digests.

Pour éviter l’effet “ça allait hier”, formalisez des garde-fous simples :

• Budget DB par route (liste, fiche produit, panier, checkout) : p95 DB < X ms, et surtout pas de dérive après déploiement.
• Top digests : le top 10 ne doit pas changer brutalement (nouveau digest dominant = souvent un module/feature).
• Erreurs et timeouts : si vous avez des timeouts côté PHP, comparez les timestamps avec les pics Threads_running/locks.

Enfin, définissez des critères de “done” mesurables. Exemples réalistes :

• top 10 digests : temps cumulé réduit de 30 % sur 24 h,
• sur routes liste/panier : p95 DB < 50–80 ms (hors cache),
• Created_tmp_disk_tables réduit de X %,
• disparition des filesorts sur un digest critique.

Sans ça, vous optimisez “au ressenti”, et vous finissez par empiler des indexes sans stratégie.

Check‑list d’intervention en production (avec risques et rollback)

Un audit MySQL/MariaDB en e‑commerce implique des manipulations potentiellement risquées : activation de logs (I/O), ajout d’index (lock), changement de paramètres (redémarrage parfois requis), modifications de requêtes côté code (régression fonctionnelle). Le minimum : fenêtre de change, snapshot/backup validé, plan de rollback, et capacité à couper la collecte si elle surcharge.

Check-list courte (et réellement actionnable) :

• Avant : sauvegarde testée (restauration validée), espace disque suffisant (logs + index), fenêtre annoncée, point de retour (config versionnée).
• Pendant : monitoring en temps réel (latence HTTP, Threads_running, I/O, replication lag si réplica), communication (qui décide d’un rollback).
• Après : mesure “avant/après” sur les mêmes digests, purge/rotation des logs, documentation de ce qui a été changé.

Pour les indexes, privilégiez les opérations online quand possible, et testez la durée sur une copie de la table. Sur gros volumes, un ALTER TABLE ... ADD INDEX peut immobiliser ou dégrader fortement la prod si l’algorithme n’est pas online/lock-free sur votre version. Si vous ne pouvez pas absorber le risque, utilisez des outils de migration online (Percona) ou faites l’opération via réplication (ajout sur replica puis switchover) — c’est plus long, mais c’est contrôlable.

Pour les paramètres InnoDB, documentez exactement : version DB, configuration actuelle, métriques “avant”, hypothèse de gain, et “après”. Faites un changement à la fois, et gardez la capacité de revenir en arrière (fichier de conf versionné, redémarrage maîtrisé). Si vous êtes déjà en phase “boutique instable” (500, timeouts, queue), traitez d’abord les symptômes urgents (erreurs 500, saturation, limites PHP-FPM) avant de partir dans un tuning DB profond : guide erreur 500 PrestaShop.