Développeur MySQL : optimiser requêtes, schémas et performances en production

Table des matières :

1. Ce que “performance MySQL” veut dire en production (et ce qui ne sert à rien)
2. Diagnostiquer : slow query log, plans d’exécution, et observabilité SQL exploitable
3. Optimiser les requêtes : index, cardinalité, pagination, et anti‑patterns typiques PrestaShop
4. Optimiser le schéma : types, collation, contraintes, et migrations sans casser la prod
5. Paramétrage InnoDB et architecture : buffer pool, IO, réplication et caches
6. Checklist de déploiement : éviter les régressions SQL quand le trafic monte

Un développeur MySQL en environnement e‑commerce ne se juge pas à sa capacité à écrire des SELECT lisibles, mais à sa capacité à réduire la latence P95, à stabiliser les verrous, à prévenir les régressions et à faire évoluer le schéma sans downtime. Sur un stack PrestaShop (8.1/8.2 ou 9.x) en PHP 8.1–8.3, la base est souvent le premier goulot quand le cache applicatif est “déjà correct” et que le trafic augmente.

Pour cadrer : en boutique en ligne, MySQL n’est pas seulement “la base”. C’est aussi le système transactionnel qui garantit que le panier, le stock et la commande restent cohérents quand les pics (soldes, Black Friday, campagnes TV/SEA) amènent de la concurrence sur les mêmes lignes. Votre objectif n’est donc pas “une requête rapide”, mais un comportement stable sous charge.

Ce que “performance MySQL” veut dire en production (et ce qui ne sert à rien)

La performance MySQL en prod, c’est d’abord des SLO mesurables : temps de réponse P95/P99 côté API/checkout, temps de génération des pages, et surtout latence des requêtes critiques (panier, stock, prix, recherche, commandes). Pour un site PrestaShop, les requêtes “business” qui font mal sont rarement des requêtes isolées : ce sont des patterns (N+1, jointures sur tables *_lang, agrégations sur orders, lecture/écriture concurrente sur stock_available). Sans ces métriques, “optimiser” revient à déplacer le problème.

Un cadrage simple (à adapter à votre contexte) est de lister les parcours où l’utilisateur abandonne si ça rame, puis de mapper les requêtes correspondantes :

Parcours	Symptôme côté utilisateur	Ce que vous mesurez côté MySQL
Recherche / navigation catégories	pages longues, filtres qui “chargent”	latence + rows examined, tmp tables, tri filesort
Ajout au panier / mise à jour quantités	blocages intermittents	verrous (data_locks), deadlocks, durée transaction
Checkout / paiement	timeouts, erreurs 500/503	saturation threads, pics de latence P99, contention InnoDB
Back‑office commandes	BO lent, export qui fige	requêtes d’agrégation, index manquants, pagination offset

Deuxième point : MySQL (ou MariaDB) est rarement seul. Le CPU peut être bloqué par le PHP, la queue du serveur web, ou le réseau stockage. D’où l’intérêt de corréler : temps MySQL (slow log / Performance Schema), temps PHP (profiling), et symptômes infra (IO wait, saturation NVMe, throttling). Si vous avez déjà une méthode d’audit de bout en bout, capitalisez dessus plutôt que de “tuner au hasard” (voir la démarche reproductible : Audit performance PrestaShop : méthode en 6 étapes).

Enfin, attention aux optimisations inutiles voire contre‑productives. Exemple classique : ajouter 15 index “au cas où” sur des tables volumineuses. Ça peut accélérer une lecture mais ralentir toutes les écritures (INSERT/UPDATE) et augmenter l’empreinte disque + la pression sur le buffer pool. Autre piège : se concentrer sur les requêtes rapides en volume alors que votre P99 est dominé par 3 requêtes lentes. La phrase attribuée à W. Edwards Deming résume bien l’approche : “In God we trust; all others must bring data.” (mesurez avant de toucher).

Deux autres “fausses bonnes idées” fréquentes en e‑commerce :

• Augmenter max_connections pour “absorber le trafic” : ça peut empirer la contention (verrous, CPU, mémoire) et transformer une lenteur en effondrement (effet “thundering herd”). Il vaut mieux maîtriser la concurrence côté app (pooling, timeouts, files d’attente) et garder MySQL dans une zone stable.
• Optimiser uniquement le temps moyen : la prod se joue sur les queues de distribution (P95/P99). Une requête qui passe de 40 ms à 25 ms ne sert à rien si une autre passe de 300 ms à 8 s pendant les pics.

Diagnostiquer : slow query log, plans d’exécution, et observabilité SQL exploitable

La base du job d’un développeur MySQL est de produire un diagnostic reproductible. Sur PrestaShop, vous avez deux entrées complémentaires : le slow query log au niveau serveur, et le debug profiling côté application. Le slow log donne la réalité en prod (avec paramètres, durée, rows examined), et le profiling aide à relier une requête au code/hook/module. Pour le slow log : Requêtes MySQL lentes PrestaShop : activer slow query log. Pour le profiling côté CMS : PrestaShop debug profiling : activer et analyser performances SQL.

Pour rendre le slow log vraiment exploitable, la logique “prod” est :

• Seuil bas au début, puis ajustement : mieux vaut capturer trop pendant 30 minutes sur une plage représentative, puis filtrer, que rater les requêtes critiques.
• Regrouper par empreinte (même requête, paramètres différents) : c’est souvent un module ou un endpoint unique qui génère une famille de requêtes.
• Regarder Rows_examined vs Rows_sent : un ratio énorme signale un index absent, un mauvais ordre de jointure, ou un tri coûteux.
• Repérer les requêtes qui “semblent” courtes mais créent des verrous longs (ça arrive quand une transaction attend un lock, puis exécute vite une fois le lock acquis).

Côté MySQL 8.0, la lecture d’un EXPLAIN “classique” ne suffit plus quand il faut justifier une régression. Utilisez EXPLAIN ANALYZE pour obtenir des timings par itérateur et vérifier l’écart entre estimations et réel. Exemple minimal :

EXPLAIN ANALYZE
SELECT o.id_order, o.date_add, c.firstname, c.lastname
FROM ps_orders o
JOIN ps_customer c ON c.id_customer = o.id_customer
WHERE o.current_state IN (2,3,4)
ORDER BY o.date_add DESC
LIMIT 50;

Astuce pratique : quand EXPLAIN ANALYZE montre une grosse divergence entre les estimations et la réalité, le bon réflexe n’est pas toujours “ajouter un index”, mais de vérifier :

• statistiques et distributions (ex. états très déséquilibrés),
• colonnes filtrées via fonctions (ex. DATE(o.date_add)),
• collations/charsets qui forcent des conversions,
• requêtes préparées avec paramètres atypiques (un cas “rare” peut ruiner le plan).

Si vos estimations sont catastrophiques (cardinalités sous‑estimées), vous perdez du temps à “ajouter un index” alors que le problème est statistique, de distribution, ou de schéma. Activez aussi Performance Schema (avec parcimonie) et exploitez le schéma sys pour remonter les top statements, top waits, et mutex/locks dominants. En prod, documentez l’impact : Performance Schema n’est pas gratuit, et certains hébergeurs mutualisés le brident.

Dernier bloc : la corrélation incident/SQL. Une montée en 503 n’est pas “un problème PHP” ou “un problème MySQL” : c’est souvent une cascade (verrouillage → threads saturés → latence → timeouts). Gardez un runbook clair : logs MySQL, logs PHP, métriques système. Vous pouvez vous appuyer sur PrestaShop monitoring d’erreurs : logs PHP, MySQL, JavaScript et alertes e-mail et, côté symptômes visibles, Erreur HTTP 503 : diagnostic serveur, logs et ressources.

Pour les incidents liés aux verrous, gardez aussi dans votre trousse à outils :

-- Aperçu des transactions InnoDB en cours et des blocages
SHOW ENGINE INNODB STATUS\G;

-- Si Performance Schema est activé (MySQL 8), utile pour voir qui bloque qui
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

L’objectif n’est pas d’avoir “des commandes”, mais un chemin de diagnostic : qui attend quoi, sur quelle table, depuis combien de temps.

Optimiser les requêtes : index, cardinalité, pagination, et anti‑patterns typiques PrestaShop

En MySQL/InnoDB, l’optimisation passe presque toujours par réduire rows examined et rendre les accès indexés. Un index n’est utile que s’il correspond à vos prédicats (WHERE), vos jointures (JOIN ON), et parfois votre tri (ORDER BY). Sur PrestaShop, les colonnes candidates reviennent souvent : id_shop, id_lang, active, date_add, id_product, id_category, id_customer, current_state. Les tables *_lang peuvent créer des plans qui explosent si id_lang ou id_shop est absent d’un index composite.

Prenez un exemple concret : listing BO/FO trié par date, filtré par état. Un index composite (current_state, date_add) peut sembler bon, mais si vous joignez ensuite customer et filtrez aussi par shop, vous aurez besoin d’un index qui reflète réellement le pattern : (id_shop, current_state, date_add) sur ps_orders si id_shop est sélectif et présent dans vos requêtes. Le bon réflexe est : exécuter la requête réelle, mesurer, puis itérer. Gardez en tête que “SELECT *” empêche souvent les covering indexes (index qui couvre toutes les colonnes nécessaires, évitant des lectures de page supplémentaires).

Sur PrestaShop, trois anti‑patterns SQL reviennent souvent (core + modules) :

• Fonctions sur colonnes filtrées : WHERE DATE(date_add) = ... ou WHERE LOWER(email) = ... → l’index devient difficilement exploitable. Préférez filtrer sur des bornes (date_add >= ... AND date_add < ...) ou normaliser en amont.
• LIKE '%terme%' sur de gros volumes → nécessite souvent un scan. Pour de la recherche “contient”, il faut envisager un moteur dédié ou des stratégies de pré-indexation.
• Jointures inutiles (ou trop tôt) : joindre une table *_lang avant d’avoir réduit le set de lignes côté table principale peut exploser les lignes intermédiaires.

Sur les gros catalogues, la pagination LIMIT 100000, 50 est un tueur silencieux : MySQL doit parcourir (et souvent trier) un offset massif avant de retourner 50 lignes. La solution côté dev MySQL est la keyset pagination (aussi appelée “seek method”) : on pagine sur une clé stabile (date_add, id_product) et non un offset. Exemple :

-- Page suivante à partir du dernier id_product vu
SELECT p.id_product, p.date_add
FROM ps_product p
WHERE p.id_product < :last_id
ORDER BY p.id_product DESC
LIMIT 50;

Mini‑scénario e‑commerce (typique en période de soldes) : une page catégorie avec filtres charge 2 secondes en moyenne, puis 8–10 secondes lors des pics. En analysant, vous trouvez une requête de listing avec LIMIT 80000, 40. Le passage à une pagination par curseur (avec un tri stable) fait baisser la latence en pic, car vous éliminez la partie “parcours + tri” sur un offset énorme — et vous réduisez mécaniquement la pression sur le buffer pool.

Enfin, n’ignorez pas les requêtes “annexes” qui explosent en volume : recherche interne, facettes, agrégations sur commandes. Sur PrestaShop, le moteur de recherche natif s’appuie sur des tables d’index dédiées ; comprendre ces tables et leurs jointures change totalement l’optimisation (voir : Index de recherche PrestaShop : fonctionnement, tables SQL et pondérations). Si vous externalisez vers Meilisearch/ES, l’objectif MySQL devient : sortir MySQL du chemin critique et limiter les recalculs (voir : Recherche interne PrestaShop : intégrer Meilisearch pour booster la conversion).

Optimiser le schéma : types, collation, contraintes, et migrations sans casser la prod

Le schéma, c’est la performance “structurelle”. Un développeur MySQL qui sait optimiser en prod sait aussi dire “non” à certains designs : VARCHAR(255) partout, collation incohérente, colonnes non nullables mal gérées, champs TEXT utilisés comme pseudo‑JSON sans stratégie d’indexation. En InnoDB, le choix des types a un impact direct sur la taille des index et la densité des pages : BIGINT par défaut sur des identifiants qui ne dépasseront jamais 10 millions est rarement justifié ; idem pour des chaînes surdimensionnées.

Un point sous-estimé en e‑commerce multi‑langue : la collation et la cohérence charset/collation entre tables. Sur MySQL 8, beaucoup d’instances passent à utf8mb4 avec des collations plus récentes ; si une table module reste en ancienne collation et que vous joignez/comparez, MySQL peut faire des conversions implicites et casser l’usage d’index dans certains cas. La bonne pratique est simple : décider d’un standard (utf8mb4 + collation unique) et s’y tenir sur toutes les tables module.

PrestaShop impose son schéma core, et beaucoup de modules ajoutent des tables. Le piège récurrent côté modules : créer des tables sans index sur les FK “logiques” (id_product, id_order, id_customer) puis requêter en prod avec des WHERE sur ces colonnes. Résultat : table scans, IO inutiles, contention. Bonne pratique : dès la conception du module, écrivez les 5 requêtes principales, puis construisez l’indexation pour ces requêtes. Exemple (module qui stocke des métadonnées par produit et shop) :

CREATE TABLE ps_mod_meta (
  id_product INT UNSIGNED NOT NULL,
  id_shop INT UNSIGNED NOT NULL,
  meta_key VARCHAR(64) NOT NULL,
  meta_value VARCHAR(255) NOT NULL,
  PRIMARY KEY (id_product, id_shop, meta_key),
  KEY idx_shop_key (id_shop, meta_key)
) ENGINE=InnoDB;

Pour éviter les index “au petit bonheur”, vous pouvez utiliser une checklist de conception (rapide mais efficace) :

• Quelle est la clé d’accès la plus fréquente (produit, commande, client) ?
• La table est-elle multi-shop / multi-lang ? Si oui, l’index doit souvent inclure id_shop / id_lang.
• Y aura-t-il un tri systématique (ex. date_add DESC) ? Si oui, l’index peut l’aider si l’ordre des colonnes est compatible.
• Quel est le ratio lecture/écriture attendu ? Un index en plus, c’est un coût sur chaque écriture.
• Est-ce que l’on peut limiter les colonnes sélectionnées (meilleure chance de covering index) ?

La partie vraiment “production” est l’évolution du schéma. Ajouter un index sur une table de plusieurs dizaines de millions de lignes peut bloquer (selon version/engine/DDL algorithm) et faire tomber le site si vous le faites à la main en heure ouvrée. Utilisez des approches d’online schema change (pt-online-schema-change : https://www.percona.com/doc/percona-toolkit/pt-online-schema-change.html, ou gh-ost : https://github.com/github/gh-ost), planifiez une fenêtre, testez sur un clone, et gardez un rollback. La règle : si vous ne pouvez pas expliquer l’impact DDL sur les locks, vous ne le lancez pas en prod.

Dernier point “terrain” : si votre base est hébergée dans une région et votre app dans une autre (ou si vous avez un stockage réseau plus lent), la latence s’ajoute à chaque aller‑retour. Pour des stacks en Europe (ex. app en région Paris et base en région Francfort, ou l’inverse), la différence peut se ressentir sur les pages qui font beaucoup de petites requêtes. L’optimisation MySQL (requêtes + réduction N+1) devient alors aussi une réduction du nombre de round‑trips réseau, pas seulement des millisecondes côté CPU.

Paramétrage InnoDB et architecture : buffer pool, IO, réplication et caches

L’optimisation MySQL n’est pas qu’une affaire de SQL. Sur InnoDB, le buffer pool est le cœur : si votre working set tient en RAM, vous réduisez drastiquement les lectures disque. En pratique : surveillez le buffer pool hit ratio, les lectures aléatoires, et le temps moyen d’IO. Sur un serveur dédié NVMe, vous pouvez tolérer plus d’IO qu’en stockage réseau, mais ce n’est pas une excuse pour scanner des tables.

Heuristique pragmatique (pas une règle absolue) : sur un serveur dédié principalement orienté MySQL, on vise souvent un buffer pool “large” tout en laissant de la marge au système (cache OS, threads, connexions, backups). Sur un serveur qui héberge aussi PHP + Redis + moteur de cache, il faut au contraire arbitrer : un buffer pool trop grand peut déclencher du swap et dégrader tout le stack.

Côté configuration, évitez les “tuning guides” génériques. L’objectif est d’aligner les paramètres sur votre workload (lecture vs écriture, pics vs stable, taille des tmp tables, fréquence de purge). Exemples de paramètres qui ont un impact réel : innodb_buffer_pool_size, innodb_log_file_size / capacité redo, tmp_table_size/max_heap_table_size, innodb_flush_log_at_trx_commit (à discuter selon exigences de durabilité), et le dimensionnement des connexions (trop de connexions = contention et mémoire). Si vous êtes sur LiteSpeed/LSAPI, intégrez aussi la vision “app server” car une file d’attente côté PHP peut masquer un MySQL “qui attend” (voir : Performances PHP LiteSpeed : cache opcode, LSAPI et suivi WaitQ).

Un angle souvent négligé : les tables temporaires et les tris. Quand vous voyez des requêtes qui créent des tmp tables (ou qui basculent sur disque), la résolution passe autant par l’indexation et la réécriture SQL que par la config. Avant d’augmenter tmp_table_size, vérifiez si vous pouvez :

• réduire les colonnes sélectionnées,
• éviter des GROUP BY/ORDER BY non indexables,
• pré-filtrer plus tôt (sous‑requête ou table dérivée bien indexée),
• mettre en cache applicatif les résultats “semi‑stables” (ex. agrégats BO).

Enfin, l’architecture : read replicas, séparation lecture/écriture, ProxySQL, ou au minimum une stratégie de sauvegardes + tests de restauration. Pour un e‑commerce, la réplication est aussi un outil de réduction de risque (PRA), pas seulement de performance. Et si vous misez sur des caches (Redis/Varnish), clarifiez le rôle de MySQL : source de vérité transactionnelle, pas cache de page. Les ressources utiles côté stack PrestaShop : Cache PrestaShop : Varnish, Redis, Memcached et OPcache côté serveur et, pour le dimensionnement infra, Serveurs dédiés infogérés e-commerce : NVMe, Redis, Varnish et haute disponibilité.

Checklist de déploiement : éviter les régressions SQL quand le trafic monte

Un développeur MySQL “prod” livre une checklist et des garde-fous, pas juste un patch. Première étape : figer les versions et compatibilités (MySQL vs MariaDB, SQL_MODE, charset/collation). Sur PrestaShop, partez de la matrice supportée et évitez les surprises liées à une version de base “trop récente” ou à un SQL mode plus strict : Exigences système : compatibilités PHP, MariaDB et Elasticsearch minimales. Côté CI, l’idéal est d’exécuter un jeu de requêtes représentatives et de détecter des plans qui changent.

Deuxième étape : tester comme en vrai. Ça veut dire un dataset réaliste (volumétrie commandes, produits, clients), et des tests de charge qui reproduisent le ratio lecture/écriture (listing + panier + checkout + BO). Sans cela, vous validez des optimisations en environnement vide. Sur PrestaShop, anticipez les événements “soldes” : la charge augmente, mais surtout la concurrence sur stock et panier se tend, et certains verrous se révèlent (approche monitoring + runbooks : PrestaShop performance : monitoring, tests de charge et runbooks soldes et optimisation en période de pic : PrestaShop soldes : optimiser caches, CDN et Varnish pour trafic élevé).

Troisième étape : sécuriser l’atterrissage en prod. Toute modification SQL (index, requête, schéma) doit venir avec : (1) la requête avant/après, (2) le plan d’exécution, (3) la mesure (P95, rows examined, CPU/IO), (4) la stratégie de rollback. Et si vous touchez au schéma, traitez ça comme une opération à risque : sauvegarde vérifiée, fenêtre, et outil d’OSC si nécessaire.

Checklist opérationnelle (compacte) à coller dans vos PR / tickets :

• [ ] Avant : capture slow log + top requêtes (empreintes) sur une période représentative
• [ ] Avant : EXPLAIN + si possible EXPLAIN ANALYZE sur dataset réaliste
• [ ] Changement : index/réécriture limitée au besoin (pas “un index par colonne”)
• [ ] Après : comparaison Rows_examined, temps, et fréquence (la fréquence compte autant que la durée)
• [ ] Verrous : vérifier deadlocks/lock waits (notamment sur stock_available et écritures panier)
• [ ] Déploiement : plan de rollback explicite (requête inverse, drop index, revert code, feature flag)
• [ ] Surveillance : alerte sur latence P95/P99 + saturation threads + erreurs applicatives
• [ ] PRA : restauration testée (au moins périodiquement) et RPO/RTO clarifiés

C’est rarement “sexy”, mais c’est exactement ce qui distingue un développeur MySQL orienté production d’un simple exécutant SQL.