Performance API : index, pagination et pool de connexions

Table des matières :

1. Ce qui fait chuter la performance d’une API (et comment le prouver)
2. Index SQL : concevoir les bons index pour filtres, tri, multi‑boutique
3. Pagination API : OFFSET/LIMIT, keyset (cursor) et coût caché des COUNT(*)
4. Pool de connexions : la réalité en PHP‑FPM (MySQL), Redis, et HTTP sortant
5. Implémenter proprement dans PrestaShop 9 : API Platform, CQRS, et SQL assumé
6. Valider en production : limites, tests de charge, et garde‑fous anti‑régression

Ce qui fait chuter la performance d’une API (et comment le prouver)

Sur une API e‑commerce, les lenteurs ne viennent presque jamais du « framework », mais d’un trio très banal : (1) requêtes SQL non indexées ou mal composées, (2) pagination naïve (OFFSET/LIMIT + COUNT(*)), (3) saturation de connexions (MySQL, Redis, HTTP sortant). Dans un contexte PrestaShop, ça se voit vite sur des endpoints “listing” (produits, commandes, clients) qui font du tri + filtres + jointures multi‑boutique/multi‑langue.

La première étape n’est pas “optimiser”, c’est mesurer. Sur PrestaShop 8/9 (PHP 8.1+ selon votre build), vous avez déjà assez d’outillage : logs SQL, slow query log MySQL/MariaDB, et profiling applicatif. Côté boutique, le combo le plus rentable reste : activer un profilage SQL ciblé (temps + nombre de requêtes) et corréler avec la latence p95/p99 de l’endpoint. Pour la partie PrestaShop, vous pouvez vous appuyer sur une méthodologie similaire à celle détaillée dans Optimisation de code PrestaShop : diagnostiquer lenteurs et requêtes SQL et PrestaShop debug profiling : activer et analyser performances SQL.

Un point que le cœur de PrestaShop ne résout pas “magiquement” : la visibilité bout‑en‑bout. Si vous exposez une API (Webservice legacy ou API d’administration PrestaShop 9), vous devez instrumenter les temps DB, la sérialisation (JSON/XML), et les appels externes (ERP, PSP, WMS). Sans ça, vous allez “optimiser” un contrôleur alors que 85% du temps est en IO DB ou en handshake TLS. Si vous êtes côté Symfony/PrestaShop 9, la démarche de profiling et refactoring mesurable se transpose très bien (cf. Dette technique Symfony : profiling Blackfire et refactoring mesurable).

Pour “le prouver” proprement en prod, vous voulez idéalement 3 marqueurs simples et corrélables par requête :

• un request id (header X-Request-Id, ou traceparent) recopié dans les logs applicatifs et proxy (Nginx/Ingress),
• un timing DB cumulé (ex. “dbtimems” + “dbqueriescount” par requête),
• un budget de latence découpé (DB vs sérialisation vs appels sortants).

Un diagnostic rapide ressemble souvent à ça : une latence p95 qui explose uniquement sur certains filtres, et une “signature” DB (1 requête à 1200 ms au lieu de 30 requêtes à 20 ms). À l’inverse, si le temps DB est faible mais que le temps total est élevé, votre goulot est plutôt dans la sérialisation, le réseau, la compression, ou un service tiers.

Tableau “symptôme → preuve → cause probable” (utile pour cadrer une investigation sans partir au hasard) :

Symptôme observé	Preuve à collecter	Cause probable
p95 augmente avec page=1000+	EXPLAIN + temps de la requête en fonction de l’OFFSET	Pagination OFFSET/LIMIT + tri non optimisé
p95 augmente uniquement avec certains filtres	EXPLAIN, index utilisés, “rows examined”	Index absent/mal ordonné, faible sélectivité
Pic de latence aléatoire + erreurs “too many connections”	métriques MySQL/Redis + logs PHP‑FPM	Saturation connexions, pm.max_children trop haut
Temps DB faible mais latence totale haute	APM + timing appels HTTP	Appels sortants (ERP/PSP), handshakes répétés, timeouts
CPU DB élevé, beaucoup de “filesort”	slow log + EXPLAIN (Extra: Using filesort)	ORDER BY non indexé / tri sur colonne calculée

C’est aussi là qu’un “détail géographique” devient très concret : si votre API tourne en France/UE et qu’un service tiers (ERP, paiement, WMS) est hébergé dans une autre région, la latence réseau (aller‑retour) devient un plancher incompressible. Dans ce cas, l’optimisation n’est pas “SQL”, mais plutôt : réduction du nombre d’appels, batch, cache, et timeouts stricts.

Index SQL : concevoir les bons index pour filtres, tri, multi‑boutique

Derrière “index”, on parle ici d’index B‑Tree (MySQL/MariaDB) utilisés par l’optimiseur pour éviter les full scans et réduire les lectures aléatoires. Le rappel important, souvent oublié quand on tweaker un endpoint API : un index n’est utile que s’il colle à vos prédicats (WHERE) et votre tri (ORDER BY), dans le bon ordre. Le manuel MySQL résume l’objectif sans détour : “Indexes are used to find rows with specific column values quickly.” (MySQL 8.0 Reference Manual, section Indexes).

Cas concret classique en PrestaShop : lister des produits actifs pour une boutique, avec langue, tri par date de mise à jour, et pagination.

SELECT p.id_product, p.date_upd, pl.name
FROM ps_product p
JOIN ps_product_shop ps
  ON ps.id_product = p.id_product AND ps.id_shop = :shop
JOIN ps_product_lang pl
  ON pl.id_product = p.id_product AND pl.id_shop = :shop AND pl.id_lang = :lang
WHERE ps.active = 1
ORDER BY p.date_upd DESC, p.id_product DESC
LIMIT :limit OFFSET :offset;

Sans index adaptés, vous forcez l’optimiseur à balayer des volumes délirants dès que le catalogue dépasse quelques centaines de milliers de lignes. Le pattern d’indexation efficace, ici, est généralement :

• Sur ps_product_shop : index composite (id_shop, active, id_product) pour filtrer vite par boutique + actif et rejoindre.
• Sur ps_product_lang : index composite (id_shop, id_lang, id_product).
• Sur ps_product : index (date_upd, id_product) si vous triez réellement par date_upd de façon stable.

Le détail qui fait la différence : l’ordre des colonnes. Avec des index composites, MySQL exploite le leftmost prefix. Si vous mettez (active, id_shop, id_product) alors que toutes vos requêtes commencent par id_shop, vous perdez une partie de l’intérêt. Même logique côté tri : si vous faites ORDER BY date_upd DESC, id_product DESC, un index (date_upd, id_product) permet souvent d’éviter un filesort.

Deux points avancés (mais très fréquents en e‑commerce PrestaShop) méritent d’être explicités :

• Sélectivité / cardinalité : indexer une colonne “peu discriminante” (ex. active seul, ou un champ booléen) ne sert à presque rien. En revanche, combinée à id_shop, elle peut devenir utile si la boutique segmente réellement vos lignes.
• Index “couvrants” (covering) : si votre index contient toutes les colonnes nécessaires (filtre + tri + colonnes sélectionnées), le moteur peut parfois éviter de retourner à la table (moins d’IO). On n’en abuse pas, mais sur des endpoints “listing” très appelés, ça peut faire gagner beaucoup.

Mini‑scénario réaliste : une boutique multi‑langue FR/EN/DE, multi‑boutique B2C/B2B, avec un connecteur ERP qui synchronise les produits “modifiés depuis X”. Si la requête “produits modifiés” trie sur date_upd et filtre sur id_shop + active, l’index (date_upd, id_product) sur ps_product et (id_shop, active, id_product) sur ps_product_shop devient un levier direct pour tenir un débit stable pendant l’import, sans dégrader le front.

Enfin, n’empilez pas les index “au feeling”. Chaque index a un coût : plus de stockage, et surtout des écritures plus lourdes (INSERT/UPDATE/DELETE). Sur une boutique avec imports catalogue fréquents, un excès d’index peut dégrader l’API… en la rendant plus lente sur les écritures. Avant de toucher au schéma, reprenez la base : EXPLAIN/EXPLAIN ANALYZE, cardinalités, et identification des tables qui explosent. Concrètement, vous cherchez des signaux comme :

• type=ALL (scan complet) sur une table volumineuse,
• Extra: Using filesort ou Using temporary sur des listes,
• un volume de lignes examinées disproportionné vs le LIMIT.

Pour approfondir côté MySQL/MariaDB (InnoDB, schémas, perf), la ressource la plus alignée avec ce sujet est Développeur MySQL : optimiser requêtes, schémas et performances en production.

Pagination API : OFFSET/LIMIT, keyset (cursor) et coût caché des COUNT(*)

Sur des listes, la pagination est l’endroit où vous brûlez le plus de CPU/IO pour… une fonctionnalité UI. Le problème d’OFFSET/LIMIT est mécanique : plus l’offset augmente, plus le moteur doit parcourir/ignorer de lignes avant de retourner la page. Sur un endpoint “commandes” en back‑office ou “produits” côté headless, une page 5000 avec OFFSET 100000 est souvent un anti‑pattern (latence qui grimpe, buffer pool qui souffre, contention).

Le second piège est le COUNT(*) systématique pour afficher “X résultats”. Sur gros catalogues, compter précisément peut coûter plus cher que de renvoyer la page elle‑même, surtout si des filtres/jointures entrent en jeu. Dans une API à vocation intégration (ERP/WMS), le client n’a souvent pas besoin d’un total exact : il lui faut un “has_more” ou un curseur “next”. En pratique, beaucoup de systèmes modernes préfèrent des tokens de continuation opaques (cursor‑based pagination) plutôt qu’un total.

Astuce pragmatique (et souvent suffisante) pour remplacer un total : demander LIMIT (limit + 1), puis renvoyer has_more=true si vous avez reçu une ligne supplémentaire. Vous payez un coût marginal (1 ligne), au lieu d’un COUNT(*) potentiellement très cher.

La solution robuste côté SQL est la keyset pagination (aussi appelée cursor pagination) : on pagine sur une clé de tri stable et indexée (souvent (date_upd, id_product) ou simplement id_order). Exemple sur le tri ci‑dessus :

-- page suivante : on repart du dernier couple (date_upd, id_product)
SELECT p.id_product, p.date_upd, pl.name
FROM ...
WHERE ps.active = 1
  AND (p.date_upd, p.id_product) < (:last_date_upd, :last_id)
ORDER BY p.date_upd DESC, p.id_product DESC
LIMIT :limit;

Trois effets immédiats : (1) temps de réponse quasi constant même à des pages “profondes”, (2) cohérence meilleure en présence d’écritures concurrentes (moins de doublons/sauts), (3) indexabilité bien meilleure si votre index suit exactement l’ordre du tri. Côté API, vous exposez un cursor (encodé en base64 ou token signé) et vous renvoyez le lien vers la page suivante via un header Link ou un champ JSON.

Comparatif rapide (utile pour expliquer à un PO/PM pourquoi vous changez l’API) :

Méthode	Coût quand on va “loin”	Stabilité si données changent	Facile à consommer
OFFSET/LIMIT	élevé (augmente avec l’offset)	moyenne (doublons/sauts possibles)	oui
OFFSET/LIMIT + COUNT(*)	très élevé	moyenne	oui
Cursor/keyset	quasi constant	bonne si tri stable	oui (si cursor documenté)

Point important côté keyset : imposez un ordre total (une colonne de tri + un tie‑breaker unique). Le couple (date_upd, id_product) est une bonne pratique, parce qu’il évite les ambiguïtés quand plusieurs lignes ont la même date_upd.

Si vous travaillez avec les APIs PrestaShop, gardez une contrainte en tête : le Webservice legacy est fonctionnel mais pas conçu pour de la pagination intelligente à très grande échelle (et l’XML n’aide pas côté payload). Si votre besoin dépasse ce que propose nativement l’existant, partez sur une API dédiée (PrestaShop 9 / API Platform ou middleware) et gardez le Webservice pour les usages CRUD simples (voir API Webservice PrestaShop : accès CRUD, authentification et bonnes pratiques).

Pool de connexions : la réalité en PHP‑FPM (MySQL), Redis, et HTTP sortant

“Pool de connexions” est un terme piégeux en environnement PHP classique. Avec PHP‑FPM, vous n’avez pas un pool partagé type Java/HikariCP : vous avez N workers, et chacun peut ouvrir ses connexions. Si votre API monte à 50 workers et que chaque requête ouvre 2 connexions MySQL + 1 Redis + 1 HTTP sortant, vous créez une tempête de connexions et vous transférez le problème sur le serveur DB/Redis ou sur le service tiers.

Un calcul simple (à faire avant de “scaler”) :

• pm.max_children = 80
• chaque worker ouvre 1 connexion MySQL (non persistante) par requête, mais reste connecté pendant le traitement
• vous avez aussi des cron/CLI (imports, indexation, exports) qui ouvrent 10–30 connexions

Si MySQL est configuré avec max_connections=150, vous êtes mécaniquement en zone rouge : il suffit d’un pic de trafic + un cron pour atteindre la limite, et vos endpoints deviennent instables (timeouts, latence qui grimpe, erreurs 500).

Côté MySQL/MariaDB, deux stratégies pragmatiques existent :

1) Connexions persistantes (PDO persistent) pour réutiliser la connexion au sein du même worker. Ça réduit la latence (moins de handshakes) mais augmente le risque de “connexions fantômes” et d’épuiser max_connections si vous dimensionnez mal PHP‑FPM.
2) Proxy de connexion (ProxySQL / MaxScale / service managé équivalent) pour lisser les pics et réutiliser des connexions côté proxy. C’est souvent la meilleure réponse quand vous avez plusieurs apps (PrestaShop + workers + backoffice + cron) qui frappent la même DB.

Deux optimisations souvent sous‑estimées en complément :

• Privilégier le socket Unix si PHP et MySQL sont sur le même hôte (latence moindre, moins de surcoût TCP), quand c’est possible dans votre architecture.
• Réduire le “temps de rétention” des connexions inutiles (timeouts côté application et DB) afin de ne pas bloquer des slots alors que le worker est déjà sorti d’un chemin d’exécution (exception, client parti, etc.).

Le dimensionnement doit être fait à l’envers : vous partez de max_connections acceptable côté DB, puis vous calibrez pm.max_children (PHP‑FPM) et vos clients. Exemple : si MySQL tient 300 connexions et que vous avez 2 apps + 1 pool de workers, vous ne pouvez pas laisser chaque composant “ouvrir au maximum”. Ça paraît évident, mais c’est exactement ce qui arrive quand on scale horizontalement sans gouvernance.

Redis : même logique. Une API qui fait du cache/lock/session sur Redis peut se retrouver à saturer Redis en connexions si chaque worker crée/ferme sans réutilisation (ou si les timeouts sont mal réglés). L’objectif est de minimiser la churn (open/close) et de fixer des timeouts (connexion/lecture) adaptés à votre SLA.

Pour l’HTTP sortant (API ERP/PSP), la mutualisation passe par keep‑alive et la réutilisation des connexions TCP/TLS par le client (cURL/Guzzle). La RFC HTTP/1.1 le rappelle explicitement : “HTTP/1.1 defaults to the use of persistent connections.” (RFC 7230, section 6.3 : https://www.rfc-editor.org/rfc/rfc7230#section-6.3). En clair : si votre module PrestaShop fait 20 appels HTTP séquentiels sans réutilisation propre (ou en recréant le client à chaque appel), vous payez des handshakes en boucle. Créez un client HTTP réutilisable (singleton par request) et fixez des timeouts stricts (connect, read) + une politique de retry bornée (uniquement sur erreurs transitoires).

Pour une mise en perspective “pool” hors PHP, l’article Worker Threads Node.js : concevoir un pool performant en production est intéressant : il montre ce qu’est un vrai pool applicatif… et pourquoi PHP‑FPM ne joue pas dans la même catégorie.

Implémenter proprement dans PrestaShop 9 : API Platform, CQRS, et SQL assumé

Depuis PrestaShop 9, l’API d’administration s’appuie sur API Platform v3 et une logique CQRS sur certaines ressources. Sur le papier, c’est un gros pas en avant : standardisation, sérialisation, filtres, sécurité. Dans les faits, dès que vous ciblez la performance, vous retombez sur le même débat : Doctrine/ORM vs SQL. L’ORM accélère le delivery, mais il est très facile de retomber dans du N+1, des jointures inutiles, et des paginations coûteuses. Le cadrage est bien posé dans ORM : limites, requêtes N+1 et quand préférer le SQL brut.

Une approche qui marche en production :

• garder API Platform pour la couche HTTP (auth, serialization, validation),
• implémenter un DataProvider / Repository spécifique pour les endpoints “listing lourds”,
• pousser la pagination cursor côté SQL (keyset),
• retourner un DTO “plat” (éviter l’hydratation d’arbres d’entités).

Concrètement, “DTO plat” veut dire : vous choisissez les champs strictement nécessaires au cas d’usage (ex. id, date_upd, name, active, price_tax_incl) et vous évitez les relations auto‑sérialisées qui déclenchent des cascades de requêtes. En API e‑commerce, c’est une source de gains très fréquente : moins de SQL, moins de CPU PHP, moins de payload, moins de temps de sérialisation.

C’est exactement le type d’architecture détaillée dans API d’administration PrestaShop 9 : OAuth, API Platform v3, endpoints CQRS : vous gagnez en contrôle, et vous évitez de transformer l’API en miroir involontaire du modèle de données.

Autre point concret : le cache. Beaucoup d’APIs PrestaShop servent des données quasi statiques (catalogue public, tables de référence, config). Un cache Redis bien posé (clé + TTL + invalidation sur événements d’update) peut réduire drastiquement la pression DB, surtout si vous avez déjà du trafic frontend. Pour la mise en place système, voir Redis sur Linux : installation, configuration et sécurisation production. Ne cachez pas “tout” : cachez ce qui est cher à recalculer, stable, et invalide facilement.

Un repère utile : si votre endpoint “listing produits” est appelé très souvent et que le catalogue ne change pas en continu, un cache court (ex. 30–120 s) + invalidation à l’update peut suffire à lisser des pointes (campagnes, marketplaces, flux). À l’inverse, sur des endpoints “commandes” ou “stock”, le cache doit être soit très court, soit événementiel, soit absent : l’objectif n’est pas de “mettre du cache partout”, mais de réduire la charge là où le coût est structurel.

Valider en production : limites, tests de charge, et garde‑fous anti‑régression

Une API performante est une API bornée. Concrètement : limitez itemsPerPage (ex. 50/100 max), refusez les tris non indexés, et imposez un ordre stable (sinon votre cursor pagination devient incohérente). Dans un contexte d’intégration, documentez vos contraintes côté client : “pas plus de X req/s”, “pas de page=99999”, “champs sélectionnables”. Si vous ne le faites pas, vous allez compenser avec du hardware et vous perdrez.

Ajoutez aussi des garde‑fous “techniques” côté API, qui évitent les requêtes toxiques :

• whitelister les champs de tri (sort) réellement indexés,
• borner le nombre de filtres combinables (ou leur complexité),
• imposer un timeout DB raisonnable (pour éviter qu’une requête bloque tout),
• retourner des erreurs explicites (400) quand un client demande une pagination ou un tri non supporté.

Côté sécurité/perf, le rate limiting et la gouvernance des clés API ne sont pas optionnels : c’est aussi une mesure de protection de la base (et donc de la latence). L’article API : sécuriser apikey, limiter le débit et renforcer la conformité couvre la partie “garde‑fou” (quotas, rotation, scopes). Appliquez‑la même logique à vos endpoints internes : une API interne non limitée peut crasher la prod aussi sûrement qu’un bot.

Enfin, testez sous charge avant d’indexer “au hasard”. Un protocole simple : (1) capture d’un jeu de requêtes représentatif, (2) test k6/JMeter avec ramp‑up et paliers, (3) lecture corrélée des métriques DB (temps de requêtes, buffer pool hit ratio, threads running, connexions) et PHP‑FPM (busy/idle, queue, temps d’exécution). Toute modification d’index ou de pagination doit être validée sur un environnement proche prod et déployée avec précautions (sauvegardes, rollback, fenêtre). Pour ce cadre opérationnel, la checklist de Mise à jour PrestaShop : checklist sauvegarde, pré-production et tests est transposable : on parle des mêmes risques (DDL, caches, régressions).

Un bon “test anti‑régression” spécifique à la pagination et aux index : rejouer le même endpoint avec (a) filtres courants, (b) filtres “pire cas” (faible sélectivité), (c) pagination profonde. Si le p95 est stable en (a) mais explose en (c), votre priorité est la pagination. Si le p95 explose en (b), votre priorité est l’indexation/le plan d’exécution.

Si vous ne deviez faire qu’une seule chose : choisissez un endpoint lent, supprimez COUNT(*), remplacez OFFSET par un cursor, et ajoutez l’index composite qui colle exactement à (WHERE + ORDER BY). Sur des catalogues volumineux, c’est typiquement le genre de changement qui fait passer un p95 de plusieurs secondes à quelques centaines de millisecondes, sans toucher au “code métier”.