MySQL vs Elasticsearch pour la recherche e‑commerce : pourquoi SQL s’effondre sous les filtres

Pourquoi la recherche SQL s’effondre sous les filtres

La « recherche » e‑commerce n’est pas une seule requête. C’est une conversation entre un humain et votre catalogue :
taper quelque chose, restreindre, restreindre encore, trier, paginer et exiger que l’interface réponde
en moins de quelques centaines de millisecondes tout en affichant les comptes de facettes. Chaque clic ajoute des contraintes,
et chaque contrainte multiplie les façons dont votre base de données peut se tromper.

Le mode d’échec : douleur combinatoire, pas une seule requête lente

Avec MySQL, le schéma habituel est :

• Une requête pour récupérer les résultats (souvent avec des jointures pour prix, inventaire, catégorie, attributs).
• Plusieurs requêtes pour calculer les comptes de facettes (comptes par marque, taille, couleur, etc.).
• Une autre requête pour « vouliez‑vous dire » ou synonymes ou bidouilles de scoring.

Si vous faites tout cela en direct sur des tables normalisées, vous demandez à une base transactionnelle
de se comporter comme un moteur de recherche. Elle va essayer. Elle va aussi mettre vos CPUs en feu.

Ce que font les « filtres » au SQL : ils détruisent les hypothèses de sélectivité

Les index B‑tree (défaut d’InnoDB dans MySQL) sont excellents pour réduire un ensemble de données quand
les prédicats sont sélectifs et alignés avec le préfixe d’un index. Les filtres dans la recherche à facettes ne s’alignent pas poliment.
Ils ressemblent à :

• WHERE category IN (...) (valeurs multiples, souvent peu sélectives en tête)
• AND brand_id IN (...) (sélection multiple contrôlée par l’utilisateur)
• AND price BETWEEN ... (les prédicats de plage sabotent le reste des index composites)
• AND (title LIKE '%term%' OR description LIKE '%term%') (wildcard en tête = l’index est décoratif)
• AND attr_id=... AND attr_value IN (...) répété N fois (explosion de jointures EAV)
• ORDER BY popularity DESC avec pagination profonde (filesort + scan)

L’optimiseur de MySQL doit choisir un plan. Pour des prédicats complexes, il devine, en se basant sur des statistiques
qui peuvent être obsolètes, grossières ou fausses pour des données biaisées (ce que sont absolument les catalogues produits).
Quand il se trompe, il ne devient pas « 10 % plus lent ». Il donne des « timeouts et une réunion ».

Le piège des « comptes de facettes » : vous faites de l’analyse sur de l’OLTP

Les comptes de facettes sont essentiellement des agrégations GROUP BY sur un ensemble filtré. C’est du travail analytique.
En SQL, vous l’implémentez avec GROUP BY sur des tables jointes, et vous avez soit :

• Exécuter une grosse agrégation par facette (N requêtes par requête), ou
• Tenter une méga‑requête avec agrégats conditionnels, puis pleurer quand elle devient ingérable.

La tragédie est que chaque requête de facette répète la plupart du travail : appliquer les mêmes filtres, scanner des lignes similaires,
joindre les mêmes tables, puis compter. Vous pouvez mettre en cache, mais les filtres sont définis par l’utilisateur, et la combinatoire
du caching explose sauf si votre trafic est minuscule ou votre catalogue est minuscule.

Une vérité opérationnelle sèche : MySQL est prévisible ; les filtres utilisateurs ne le sont pas

MySQL brille quand vous pouvez prédire les schémas d’accès et indexer pour eux. La recherche à facettes est adversariale :
les utilisateurs génèrent en pratique des requêtes ad‑hoc avec des combinaisons étranges. MySQL peut gérer une partie,
mais l’enveloppe de performance s’effondre rapidement dès que vous ajoutez :

• plusieurs jointures pour les attributs
• filtres de plage + tri
• pagination profonde
• agrégations de facettes
• stemming multilingue/synonymes/ajustement de pertinence

Blague #1 : Faire une recherche à facettes sur un schéma entièrement normalisé, c’est comme remorquer un bateau avec un vélo — possible,
mais tout le monde vous regarde nerveusement.

Comment MySQL exécute réellement votre recherche « simple »

Si vous voulez comprendre pourquoi la recherche SQL s’effondre, vous devez intégrer ce pour quoi MySQL est optimisé :
des recherches pointées et des scans d’index de plage étroits avec cohérence transactionnelle, sur des stockages en lignes, avec des index B‑tree.
Les moteurs de recherche sont construits autour d’index inversés, de valeurs de document de style colonnaire, de bitsets de filtres en cache et d’un modèle de scoring.
Physiques différents.

Index B‑tree : excellent pour « trouver des lignes comme ça », médiocre pour « calculer la pertinence »

Un index B‑tree excelle quand le prédicat réduit rapidement l’ensemble de candidats. Il lutte quand :

• Vous devez matcher des tokens à l’intérieur d’un texte sans pré‑tokenisation (LIKE '%term%').
• Vous avez besoin d’un classement par pertinence (nécessite scoring, fréquences de termes, boosts par champ).
• Vous avez une logique riche en OR qui élargit le scan (brand IN (...) OR category IN (...)).
• Vous avez des comptes d’agrégation sur de nombreuses dimensions (facettes).

FULLTEXT de MySQL aide, mais ne résout pas le faceting

Les index FULLTEXT de MySQL peuvent fournir un matching basé sur des tokens pour certains workloads,
et dans InnoDB ils sont réels et utiles. Mais la recherche e‑commerce veut plus :

• préfixe, fuzziness, tolérance aux fautes de frappe
• synonymes et « vouliez‑vous dire »
• ajustement de pertinence par champ (titre vs description)
• filtres structurés qui doivent être rapides
• comptes de facettes et « montrez‑moi combien d’éléments correspondent si je clique ceci »

Même si FULLTEXT obtient correctement la correspondance textuelle, le travail de jointure et de filtrage qui suit peut rester votre goulot d’étranglement.
Vous vous retrouvez avec une requête en deux étapes : le matching de texte renvoie des IDs, puis vous joignez les filtres, puis vous triez. Chaque étape a ses propres falaises de performance.

Schémas EAV : le tueur silencieux

Les systèmes d’attributs sont souvent implémentés en EAV (entity‑attribute‑value) : un produit a de nombreux attributs,
chacun stocké en tant que lignes. C’est flexible et terrible pour les filtres de recherche. Une requête comme « taille=10 et couleur=noir »
devient plusieurs auto‑jointures ou sous‑requêtes contre la table d’attributs. Sur un grand catalogue, c’est une explosion de jointures
avec des prédicats de faible sélectivité. L’optimiseur ne peut pas toujours réordonner les jointures de manière profitable, et même quand il peut,
les jeux de résultats intermédiaires deviennent rapidement volumineux.

Tri et pagination : « ORDER BY popularity » n’est pas gratuit

Le tri est un second goulot qui apparaît après que vous avez « corrigé » le filtrage. Si votre requête sélectionne beaucoup de candidats
puis trie par quelque chose non couvert par un index, MySQL peut effectuer un filesort. Ce n’est pas littéralement « trier sur disque »
à chaque fois, mais cela peut dégénérer et consommer CPU et mémoire.

La pagination profonde (LIMIT 20 OFFSET 2000) est particulièrement vilaine : MySQL doit encore trouver et éliminer les 2000 premières lignes
après avoir appliqué les filtres et l’ordre. C’est du travail gaspillé par requête, et cela croît linéairement avec l’offset.

Stabilité du plan : en production, le « parfois » devient « toujours »

Le principal casse‑tête opérationnel n’est pas qu’une requête soit lente. C’est que la même forme de requête devient lente selon :

• quelle catégorie est sélectionnée
• quelles marques sont choisies
• heure de la journée (buffer pool chaud vs froid)
• données biaisées (une marque possède 40 % des produits)
• fraîcheur (nouveaux produits non reflétés dans les stats)

Vous voyez la latence p95 bondir, puis vous accusez le réseau, puis l’application, puis vous découvrez qu’un utilisateur
a cliqué un filtre « Toutes les couleurs » implémenté comme IN (...centaines d'ids...). C’est toujours la fonctionnalité UX anodine.

Une citation qui a été douloureusement fiable en exploitation : « Hope is not a strategy. » — Gene Kranz.

Comment Elasticsearch rend les filtres bon marché (et pourquoi ce n’est pas magique)

Elasticsearch repose sur Lucene, qui est conçu pour la recherche. Cela semble évident et tautologique,
mais cela a des conséquences pratiques : la structure d’index est un index inversé pour le texte plus des doc values de type colonnaire
pour les agrégations et le tri. Les filtres deviennent des opérations sur des bitsets, pas des puzzles de jointures.

Index inversé : l’avantage central

Dans un index inversé, chaque terme pointe vers une liste de postings d’identifiants de documents qui le contiennent. Intersectez les listes de postings,
appliquez les filtres comme ensembles pré‑calculés, scorez, et vous avez terminé. L’important est que le travail se mesure au nombre de correspondances,
pas au nombre de lignes scannées à travers des tables jointes.

Les filtres sont cacheables et composables

Dans Elasticsearch, un filtre (par exemple brand:Acme) devient souvent un bitset : un bitmap de documents correspondants.
Combinez les filtres via AND/OR rapidement. De nombreux filtres peuvent être efficacement mis en cache car ils sont réutilisés entre les requêtes,
en particulier les facettes courantes comme « en stock » ou « livraison gratuite ».

Les comptes de facettes (agrégations) sont des citoyens de première classe. Elasticsearch peut calculer les comptes par marque, taille, couleur, etc.,
en utilisant les doc values et des optimisations au niveau des segments. Vous n’écrivez pas N GROUP BY différents en priant
que l’optimiseur ne voie pas votre schéma comme un défi personnel.

La dénormalisation est le prix de la performance

Elasticsearch fonctionne mieux quand chaque produit est un document unique contenant les champs sur lesquels vous filtrez et triez.
Cela signifie dénormaliser : inclure le nom de la marque, le chemin de catégorie, les attributs, l’état de disponibilité, peut‑être même le rang calculé.
Les ingénieurs OLTP résistent souvent à la dénormalisation par principe. La recherche vous y oblige de toute façon, mais dans un système séparé.

Vérification de réalité opérationnelle : Elasticsearch vous achète la performance, pas la simplicité

Elasticsearch n’est pas gratuit :

• Les pipelines d’indexation peuvent échouer. Les backfills peuvent prendre des heures.
• Les erreurs de mapping sont pour toujours (ou du moins jusqu’à un reindex).
• La taille du cluster est un vrai travail d’ingénierie, pas de la bonne volonté.
• La cohérence est par défaut éventuelle ; vous devez concevoir autour.
• Vous exécutez maintenant deux systèmes : MySQL pour la vérité, Elasticsearch pour la recherche.

Mais si votre produit a besoin de facettes rapides et de pertinence, Elasticsearch est le bon type de complexité.
Il correspond à la forme du problème.

Blague #2 : Elasticsearch est l’ami qui vous aide à déménager vite — puis vous demande de l’aider à déménager un cluster le week‑end suivant.

Faits intéressants et contexte historique (ce qui explique les compromis d’aujourd’hui)

1. Lucene est antérieur à Elasticsearch d’environ une décennie. Lucene a démarré à la fin des années 1990 ; Elasticsearch est arrivé plus tard comme une surcouche distribuée avec une API.
2. InnoDB est devenu le moteur par défaut de MySQL dans MySQL 5.5. Ce changement a compté : transactions, récupération après crash et comportement du buffer pool façonnent ce que « rapide » signifie.
3. FULLTEXT dans InnoDB a été ajouté après que MyISAM l’avait depuis des années. La fonctionnalité existe, mais l’écosystème d’analyseurs et de scoring dans Lucene est plus riche.
4. La « recherche » e‑commerce précoce était souvent navigation par catégorie plus LIKE. Cet héritage hante encore les schémas : tables EAV, tables de mots‑clés et colonnes de pertinence bricolées.
5. La navigation à facettes est devenue courante avec les grands catalogues dans les années 2000. Une fois « filtrer par taille/couleur/marque » devenu essentiel, les bases OLTP ont commencé à être abusées.
6. Elasticsearch a popularisé le « schema on write » pour la recherche. Vos décisions de mapping au moment de l’indexation affectent fortement les performances et la correction au moment de la requête.
7. Les doc values colonnaires ont été un tournant pour les agrégations. Sans doc values, « comptes par facette » à grande échelle seraient bien plus coûteux.
8. Les clusters de recherche distribués ont rendu la pertinence une préoccupation de production. Vous ne faites pas qu’interroger ; vous affinez les analyseurs, synonymes, stemming et scoring à travers les shards.

Trois mini‑histoires d’entreprise issues du terrain

1) Incident causé par une mauvaise hypothèse : « C’est indexé, donc c’est rapide »

Un détaillant de taille moyenne avait un endpoint de recherche basé sur MySQL qui « fonctionnait bien » en staging. La production était une autre histoire.
L’endpoint joignait produits, inventaire, prix, catégories et attributs, puis tri par popularité. Ils avaient
ajouté des index partout. Quelqu’un avait même un tableur.

Le trafic a grimpé pendant une campagne saisonnière, et la latence p95 des recherches est passée d’« acceptable » à « vous pouvez rafraîchir la page
et attendre encore ». Le CPU de la base était élevé mais pas saturé. La vraie douleur venait des E/S et du churn du buffer pool :
la requête touchait trop de pages, de manière trop imprévisible.

L’hypothèse erronée était subtile : ils croyaient que parce que chaque table avait un index sur les clés de jointure, la jointure
resterait bon marché sous les filtres. Mais les filtres portaient sur des attributs stockés en lignes EAV, donc l’ordre des jointures
changeait selon les filtres. L’optimiseur a choisi un plan qui semblait bon pour des filtres courants et catastrophique pour une catégorie spécifique avec une grande diversité d’attributs.

La réponse à l’incident n’était pas glamour. Ils ont temporairement désactivé les facettes multi‑sélection pour cette catégorie et forcé
un sous‑ensemble de filtres plus étroit. La latence est revenue immédiatement. La correction racine a été de déplacer le filtrage et le classement
des facettes dans Elasticsearch, MySQL servant uniquement comme catalogue de référence.

La note postmortem importante : « La présence d’un index n’est pas l’utilité de l’index. » Si un prédicat n’est pas sélectif, votre
index est une suggestion, pas une solution.

2) Optimisation qui s’est retournée contre eux : mettre en cache les résultats SQL

Une autre entreprise a tenté le classique : « Mettons en cache les réponses de recherche dans Redis. » Ils hashaient les paramètres de requête
et mettaient en cache la réponse JSON complète pendant 10 minutes. Sur le papier, cela réduisait la charge. En pratique, cela a créé de nouveaux modes de panne.

Le catalogue changeait fréquemment : prix mis à jour, inventaire fluctuant, promotions qui commencent et s’arrêtent. Le cache servait
des résultats périmés qui violaient des règles métier (articles en rupture affichés comme disponibles, prix promotionnels absents). Le support client
l’a remarqué. Le marketing aussi. Le CFO aussi.

Ils ont resserré le TTL. La charge est revenue. Ils ont ajouté des hooks d’invalidation depuis le pipeline du catalogue. C’est devenu un système
événementiel complexe et fragile, et ils n’ont toujours pas réussi à invalider toutes les combinaisons de filtres. La combinatoire les a battus :
vous ne pouvez pas invalider efficacement « toutes les requêtes qui incluent la marque X et n’importe quelle couleur et prix entre A et B ».

La correction finale était ennuyeuse : arrêter de mettre en cache les réponses complètes et mettre plutôt en cache des sous‑résultats stables (comme des bitsets de filtres)
dans le moteur de recherche, où les primitives sont conçues pour cela. Ils ont introduit Elasticsearch pour le temps de requête et utilisé MySQL pour
les écritures et la vérité. Le caching de réponses est resté, mais uniquement pour les pages de catégorie de haut niveau avec paramètres prévisibles.

3) Pratique ennuyeuse mais correcte qui a sauvé la mise : double écriture + indexation rejouable

Une organisation plus grande a fait quelque chose d’impopulaire : elle a traité l’indexation de recherche comme un pipeline de production de première classe.
Les mises à jour produit passaient dans une queue durable. Un consommateur mettait à jour Elasticsearch. Un autre consommateur mettait à jour MySQL.
Et chaque événement était rejouable depuis le stockage.

Un après‑midi, un bug de mapping s’est glissé : un champ numérique a été indexé comme texte. Les comptes de facettes sont devenus bizarres, le tri a cassé,
et quelqu’un a tenté de corriger à chaud en changeant le mapping. Elasticsearch a poliment refusé parce que les mappings ne sont pas si flexibles.
L’équipe n’a pas paniqué. C’est tout l’intérêt d’un process ennuyeux.

Ils ont créé un nouvel index avec le bon mapping, rejoué les événements depuis le log durable, validé les comptes par rapport à MySQL,
et basculé un alias. Aucune interruption ; le « rayon d’explosion » s’est limité à des anomalies temporaires de pertinence pendant le reindex.

Rien de tout cela n’était astucieux. C’était juste discipliné : log d’événements immuable, indexation idempotente, basculements par alias,
et tableaux de bord comparant les documents de l’index de recherche aux comptes source‑de‑vérité. Quand votre recherche est une fonctionnalité produit,
c’est ce à quoi ressemble l’âge adulte.

Playbook de diagnostic rapide

Quand la recherche ralentit, votre travail n’est pas de débattre quel système est « meilleur ». Votre travail est de trouver le goulot en minutes,
pas en jours, et de prendre une décision sûre sous pression.

Première étape : classer la lenteur (appli vs base de données vs cluster de recherche)

• Vérifiez p95/p99 à la périphérie : tout l’endpoint est‑il lent ou seulement certaines formes de requêtes ?
• Comparez « sans filtres » vs « beaucoup de filtres » : si les filtres provoquent un saut superlinéaire, c’est du travail de plan/agrégation.
• Vérifiez les budgets d’erreur : timeouts, 5xx, déclenchements de circuit breaker.

Deuxième étape : isoler le travail au temps de requête du travail de fraîcheur des données

• MySQL : est‑ce lent à cause de scans/sorts, ou parce que vous êtes bloqué (verrous) ?
• Elasticsearch : est‑ce lent à cause d’agrégations lourdes, ou parce que le cluster est occupé à merger des segments / GC / disque saturé ?

Troisième étape : cherchez les falaises classiques

• Falaises MySQL : filesort, tables temporaires, explosion de « rows examined », jointures EAV, pagination OFFSET profonde, stats obsolètes.
• Falaises Elasticsearch : agrégations haute cardinalité, trop de shards, explosion du fielddata, disques lents, pression sur le heap, contention refresh/merge.

Quatrième étape : prenez l’atténuation à moindre risque

• Désactivez temporairement la pire facette ou option de tri.
• Limitez OFFSET (ou imposez « search after »).
• Réduisez la taille des agrégations.
• Revenez à un mode mots‑clés sans comptes.

Cinquième étape : décidez architecture, pas exploits héroïques

Si votre produit exige filtrage riche + comptes de facettes + pertinence, déplacez la charge de requête vers un moteur de recherche.
Si votre produit est une « recherche admin » avec quelques filtres, MySQL suffit — faites‑le simplement et intentionnellement.

Tâches pratiques avec commandes, sorties et décisions

Voici les tâches que j’exécute en production quand quelqu’un dit « la recherche est lente ». Chaque tâche inclut :
une commande exécutable, une sortie d’exemple réaliste, ce que cela signifie, et la décision à prendre.

Tâche 1 : Identifier les statements MySQL lents dominants (par temps total)

cr0x@server:~$ sudo pt-query-digest /var/log/mysql/mysql-slow.log | head -n 25
# 120s user time, 2s system time, 33.12M rss, 101.02M vsz
# Current date: Mon Dec 30 12:10:07 2025
# Hostname: db1
# Files: /var/log/mysql/mysql-slow.log
# Overall: 1.2k total, 37 unique, 0 QPS, 0x concurrency ____________
# Time range: 2025-12-30T10:00:00 to 2025-12-30T12:00:00
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          3500s   150ms     12s      3s      9s     2s   1.5s
# Rows examine        3.1G       0    6.0M   2.6M   5.9M   1.1M   2.1M
# Query 1: 45% 0.12 QPS, 0.40x concurrency, ID 0xA1B2C3D4 at byte 12345
# Scores: V/M = 0.98
# Time range: 2025-12-30T10:10:00 to 2025-12-30T11:55:00
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         22     270
# Exec time     45   1580s   800ms     12s      6s     10s     2s      5s
# Rows examine  62   1.9G   400k    6.0M   7.1M   5.9M   1.1M   6.8M

Ce que cela signifie : Votre « requête de recherche » n’est pas lente ; elle scanne des millions de lignes par requête et domine le temps.

Décision : Extraire cette forme de requête, exécuter EXPLAIN ANALYZE, et soit redesigner index/schéma soit déplacer la charge hors de MySQL.

Tâche 2 : Trouver les requêtes MySQL en cours et leur durée

cr0x@server:~$ mysql -e "SHOW FULL PROCESSLIST\G" | sed -n '1,35p'
*************************** 1. row ***************************
     Id: 91234
   User: app
   Host: 10.0.2.15:53912
     db: catalog
Command: Query
   Time: 18
  State: Sending data
   Info: SELECT p.id, p.title FROM products p
         JOIN product_attr pa ON pa.product_id=p.id
         WHERE p.category_id IN (12,13) AND pa.attr_id=7 AND pa.value IN ('black','navy')
         ORDER BY p.popularity DESC LIMIT 20 OFFSET 2000

Ce que cela signifie : « Sending data » depuis 18 secondes signifie souvent scan + jointure + tri, pas I/O réseau.

Décision : Atténuer la pagination profonde et examiner si le prédicat de jointure est suffisamment sélectif pour utiliser des index.

Tâche 3 : Inspecter le plan de requête avec métriques runtime (MySQL 8)

cr0x@server:~$ mysql -e "EXPLAIN ANALYZE SELECT p.id FROM products p JOIN product_attr pa ON pa.product_id=p.id WHERE p.category_id IN (12,13) AND pa.attr_id=7 AND pa.value IN ('black','navy') ORDER BY p.popularity DESC LIMIT 20 OFFSET 2000;"
+----------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------+
| -> Limit/Offset: 20/2000  (cost=84512.23 rows=2020) (actual time=15123.412..15123.900 rows=20 loops=1)
    -> Sort: p.popularity DESC  (cost=84512.23 rows=40123) (actual time=15123.410..15123.650 rows=2020 loops=1)
        -> Nested loop inner join  (cost=40211.11 rows=40123) (actual time=112.310..15012.000 rows=420000 loops=1)
            -> Index range scan on p using idx_category (category_id in (12,13))  (cost=1123.00 rows=70000) (actual time=2.100..50.000 rows=68000 loops=1)
            -> Index lookup on pa using idx_attr (attr_id=7, value in ('black','navy')) (cost=0.50 rows=6) (actual time=0.200..0.210 rows=6 loops=68000) |
+----------------------------------------------------------------------------------------------------------------------------------+

Ce que cela signifie : 68k lignes externes provoquent 68k lookups répétés ; les résultats intermédiaires explosent ; trier 420k candidats pour en garder 20 est du travail gaspillé.

Décision : Éviter le paging OFFSET, précomputez/dénormalisez les attributs pour les filtres courants, ou déplacez filtrage/tri vers Elasticsearch.

Tâche 4 : Vérifier les définitions d’index et l’utilité des préfixes

cr0x@server:~$ mysql -e "SHOW INDEX FROM product_attr\G" | sed -n '1,35p'
*************************** 1. row ***************************
        Table: product_attr
   Non_unique: 1
     Key_name: idx_attr
 Seq_in_index: 1
  Column_name: attr_id
    Collation: A
  Cardinality: 32
*************************** 2. row ***************************
        Table: product_attr
   Non_unique: 1
     Key_name: idx_attr
 Seq_in_index: 2
  Column_name: value
    Collation: A
  Cardinality: 2100
*************************** 3. row ***************************
        Table: product_attr
   Non_unique: 1
     Key_name: idx_attr
 Seq_in_index: 3
  Column_name: product_id
    Collation: A
  Cardinality: 800000

Ce que cela signifie : La faible cardinalité pour attr_id suggère beaucoup de lignes par attribut ; l’index aide moins que vous ne le pensez sous des catégories larges.

Décision : Envisagez des index composites correspondant aux motifs de requête les plus fréquents, ou remodelez les attributs (aplatir les attributs chauds en colonnes ou une table dénormalisée).

Tâche 5 : Vérifier si MySQL déverse sur disque à cause de tables temporaires

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%';"
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 184203   |
| Created_tmp_files       | 9132     |
| Created_tmp_tables      | 512004   |
+-------------------------+----------+

Ce que cela signifie : Un fort ratio de tables temporaires sur disque indique des sorts/group‑by qui ne tiennent pas en mémoire ou ne peuvent pas utiliser d’index.

Décision : Réduire la complexité des GROUP BY, s’assurer que les index soutiennent le groupement, ou arrêter de faire des comptes de facettes dans MySQL.

Tâche 6 : Confirmer la pression sur le buffer pool (InnoDB)

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"
+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| Innodb_buffer_pool_read_requests      | 982341223 |
| Innodb_buffer_pool_reads              | 18233411  |
+---------------------------------------+-----------+

Ce que cela signifie : Les lectures du buffer pool (physiques) sont significatives. Les requêtes de recherche brassent les pages et manquent le cache.

Décision : Soit provisionner plus de mémoire (soulagement temporaire), soit réduire l’ensemble de travail en déplaçant la recherche hors de MySQL / dénormaliser / cacher différemment.

Tâche 7 : Vérifier les attentes de verrous dans MySQL impactant la recherche

cr0x@server:~$ mysql -e "SELECT * FROM performance_schema.data_lock_waits\G" | sed -n '1,40p'
*************************** 1. row ***************************
REQUESTING_ENGINE_LOCK_ID: 139941888:106:4:5:12345
REQUESTING_ENGINE_TRANSACTION_ID: 812345
BLOCKING_ENGINE_LOCK_ID: 139941888:106:4:5:12345
BLOCKING_ENGINE_TRANSACTION_ID: 812100
REQUESTING_THREAD_ID: 2221
BLOCKING_THREAD_ID: 2199

Ce que cela signifie : La latence de recherche peut provenir de la contention de verrous, souvent due à des écritures ou longues transactions qui bloquent les lectures (ou verrous de métadonnées).

Décision : Corriger le chemin d’écriture, raccourcir les transactions, et séparer des replicas en lecture pour la recherche si vous devez conserver la recherche en SQL.

Tâche 8 : Mesurer la latence de recherche Elasticsearch et l’état des shards

cr0x@server:~$ curl -s http://localhost:9200/_cat/health?v
epoch      timestamp cluster status node.total node.data shards pri relo init unassign pending_tasks max_task_wait_time active_shards_percent
1735560300 12:05:00  es-prod yellow          6         6   420 210    0    0       12             0                  -                 97.1%

Ce que cela signifie : Un état yellow avec shards non assignés peut ajouter de la latence et du risque, surtout si les réplicas manquent et que les nœuds sont sous pression.

Décision : Restaurer la capacité d’allocation de réplicas, corriger les watermarks disque, ou réduire le nombre de shards avant d’affiner les requêtes.

Tâche 9 : Identifier les requêtes Elasticsearch lentes via le profilage de recherche

cr0x@server:~$ curl -s -H 'Content-Type: application/json' http://localhost:9200/products/_search -d '{
  "profile": true,
  "size": 20,
  "query": {
    "bool": {
      "must": [{"match": {"title": "shoes"}}],
      "filter": [
        {"terms": {"brand_id": [12, 15, 18]}},
        {"term": {"in_stock": true}},
        {"range": {"price": {"gte": 50, "lte": 120}}}
      ]
    }
  },
  "aggs": {"brands": {"terms": {"field": "brand_id"}}}
}' | sed -n '1,35p'
{
  "took" : 38,
  "timed_out" : false,
  "_shards" : { "total" : 12, "successful" : 12, "skipped" : 0, "failed" : 0 },
  "hits" : { "total" : { "value" : 18432, "relation" : "eq" }, "hits" : [ ... ] },
  "profile" : { "shards" : [ { "searches" : [ { "query" : [ { "type" : "BooleanQuery", "time_in_nanos" : 8200000 } ] } ] } ] }
}

Ce que cela signifie : took: 38ms est correct ; le profilage montre où le temps est passé (requête vs agrégation vs fetch).

Décision : Si les agrégations dominent, réduire la cardinalité, utiliser des agrégations de type filter, ou précalculer certaines facettes.

Tâche 10 : Détecter trop de shards (un auto‑sabotage Elasticsearch facile)

cr0x@server:~$ curl -s http://localhost:9200/_cat/indices/products?v
health status index    uuid                   pri rep docs.count docs.deleted store.size pri.store.size
green  open   products Zk8p3bYfQkOaK9mD1n2x3Q  24   1   820000        12000     96gb        48gb

Ce que cela signifie : 24 shards primaires pour <1M docs est souvent excessif. Le sur‑sharding augmente le coût et la latence.

Décision : Reindexer dans moins de shards, ou utiliser des patterns rollover/ILM si vous avez vraiment besoin d’indices temporels (la plupart des catalogues produits n’en ont pas besoin).

Tâche 11 : Vérifier les indicateurs de pression du heap JVM d’Elasticsearch

cr0x@server:~$ curl -s http://localhost:9200/_nodes/stats/jvm?filter_path=nodes.*.jvm.mem.heap_used_percent,nodes.*.name | sed -n '1,25p'
{
  "nodes" : {
    "9a1b" : { "name" : "es-data-1", "jvm" : { "mem" : { "heap_used_percent" : 78 } } },
    "9a1c" : { "name" : "es-data-2", "jvm" : { "mem" : { "heap_used_percent" : 83 } } }
  }
}

Ce que cela signifie : Une utilisation élevée et soutenue du heap corrèle avec des pauses GC et des pics de latence, surtout avec des agrégations lourdes.

Décision : Réduire la charge d’agrégation, corriger les mappings pour éviter le fielddata, scaler les nœuds de données, ou ajuster le sizing du heap prudemment.

Tâche 12 : Valider les mappings pour les champs de filtre (keyword vs text)

cr0x@server:~$ curl -s http://localhost:9200/products/_mapping | sed -n '1,45p'
{
  "products" : {
    "mappings" : {
      "properties" : {
        "brand" : { "type" : "text" },
        "brand_id" : { "type" : "integer" },
        "color" : { "type" : "keyword" },
        "title" : { "type" : "text" },
        "in_stock" : { "type" : "boolean" },
        "price" : { "type" : "scaled_float", "scaling_factor" : 100 }
      }
    }
  }
}

Ce que cela signifie : Filtrer/agréger sur brand en tant que text est un piège ; vous voulez keyword (ou utiliser le sous‑champ brand.keyword).

Décision : Corriger le mapping dans un nouvel index et reindexer. Ne « contournez » pas cela avec des scripts runtime sauf si vous aimez la latence.

Tâche 13 : Vérifier la fraîcheur des statistiques MySQL (sanité de l’optimiseur)

cr0x@server:~$ mysql -e "ANALYZE TABLE products, product_attr;"
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| catalog.products  | analyze | status   | OK       |
| catalog.product_attr | analyze | status | OK       |
+-------------------+---------+----------+----------+

Ce que cela signifie : Un rafraîchissement des stats peut changer drastiquement les plans si elles étaient obsolètes.

Décision : Si cela « répare » la recherche, vous avez toujours besoin d’un plan durable : planifier des ANALYZE, ou redesigner les requêtes pour qu’elles soient robustes.

Tâche 14 : Détecter l’abus de pagination profonde au niveau applicatif

cr0x@server:~$ sudo awk '{print $7}' /var/log/nginx/access.log | grep -E 'search\?' | sed 's/.*offset=\([0-9]\+\).*/\1/' | sort -n | tail -n 10
2000
2400
3000
3600
4200
5000
6200
8000
12000
20000

Ce que cela signifie : Des utilisateurs (ou bots) demandent des offsets qui provoquent un travail linéaire dans MySQL et un travail non trivial dans les moteurs de recherche.

Décision : Limiter l’offset, exiger la pagination par curseur, et appliquer des mesures anti‑bots. La pagination profonde est une décision produit déguisée en technique.

Tâche 15 : Vérifier la latence disque (la dépendance cachée pour les deux systèmes)

cr0x@server:~$ iostat -x 1 3
Linux 6.5.0 (db1)  12/30/2025  _x86_64_  (32 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           12.1    0.0     3.2    18.7    0.0    66.0

Device            r/s     w/s   rkB/s   wkB/s  await  svctm  %util
nvme0n1         120.0   340.0  9800.0 40200.0  18.4   1.2   92.0

Ce que cela signifie : Un await élevé et un %util proche de la saturation signifient que le stockage est le facteur limitant. Les charges de recherche amplifient les lectures aléatoires.

Décision : Si le disque est le goulot, aucun réglage de requête ne vous sauvera complètement. Réduisez les E/S (meilleurs index/dénormalisation) ou améliorez le stockage.

Erreurs courantes : symptômes → cause racine → correction

1) Symptom : « La recherche est rapide pour certaines catégories, terrible pour d’autres »

Cause racine : Biais des données + statistiques obsolètes/inexactes entraînent des plans MySQL instables ; certaines catégories font exploser la cardinalité des jointures.

Correction : Exécuter EXPLAIN ANALYZE sur des catégories représentatives ; rafraîchir les stats ; ajouter des index composites ciblés ; envisager de déplacer le faceting vers Elasticsearch.

2) Symptom : « Ajouter un filtre fait passer la latence de 200ms à 8s »

Cause racine : Le prédicat change l’ordre des jointures ou désactive l’usage d’un index ; un prédicat de plage ou une condition OR casse le préfixe d’un index composite ; des tables temporaires/filesort apparaissent.

Correction : Réécrire la requête pour réduire les OR ; éviter le LIKE avec wildcard en tête ; aplatir les attributs chauds ; arrêter OFFSET ; ou décharger vers le contexte filter d’Elasticsearch.

3) Symptom : « Les comptes de facettes sont faux ou manquants sous charge »

Cause racine : Mise en cache de réponses complètes obsolètes ; ou exécution des comptes sur des replicas avec retard ; ou mélange d’un index de recherche éventuel avec un stock temps réel.

Correction : Décidez ce qui doit être fortement cohérent (généralement le checkout, pas la recherche). Utilisez une indexation quasi temps réel et séparez la logique « disponibilité » avec des SLA clairs.

4) Symptom : « Le CPU MySQL est bon mais le temps de réponse est horrible »

Cause racine : Attente d’E/S disque due à de grands scans, mauvaise localité, ou misses du buffer pool ; les tris déversent sur disque.

Correction : Mesurez Innodb_buffer_pool_reads et la latence disque ; réduisez les rows examined ; provisionnez mémoire/stockage ; arrêtez de faire du faceting analytique dans MySQL.

5) Symptom : « Elasticsearch est lent après avoir ajouté plus de champs à l’index »

Cause racine : Explosion de mappings ; agrégations haute cardinalité ; trop de champs keyword ; augmentation des doc values et de l’empreinte disque.

Correction : Auditer les mappings ; indexer uniquement ce que vous interrogez ; désactiver l’indexation pour les champs non recherchés ; préférer des IDs numériques pour les facettes ; réduire la taille des agrégations.

6) Symptom : « Le cluster Elasticsearch a l’air sain mais les requêtes expirent »

Cause racine : Sur‑sharding, merges lents, pression sur le heap, ou disques proches de la saturation ; le fan‑out des requêtes multiplie le coût par shard.

Correction : Réduire le nombre de shards via reindex ; améliorer le stockage ; surveiller le heap et le GC ; ajuster refresh/merge ; scaler horizontalement les nœuds de données.

7) Symptom : « On a utilisé MySQL FULLTEXT et ça ne suffit toujours pas »

Cause racine : FULLTEXT gère le matching de tokens, mais le filtrage structuré + comptes de facettes + tuning de pertinence restent la vraie charge.

Correction : Traitez FULLTEXT comme un outil partiel pour des cas limités. Si vous avez besoin de faceting et de pertinence, utilisez Elasticsearch (ou un autre moteur dédié).

8) Symptom : « L’ordre des résultats change entre les requêtes »

Cause racine : Tie‑breaking non déterministe dans les tris SQL ; mises à jour concurrentes ; dans Elasticsearch, les égalités de score et les merges de segments peuvent réordonner des scores égaux.

Correction : Ajouter des tris secondaires stables (par ex. product_id), utiliser des clés de tri déterministes, et être explicite sur le comportement de tri.

Listes de contrôle / plan étape par étape

Quand MySQL est acceptable pour la « recherche » (faites ceci, pas cela)

• Utilisez MySQL pour la recherche admin, les outils du service client, et les petits catalogues avec filtres simples.
• Évitez les jointures EAV sur les chemins critiques. Si vous devez filtrer sur des attributs, précomputez une table dénormalisée clé‑par‑product_id.
• Gardez les requêtes compatibles avec les index : pas de LIKE avec wildcard en tête ; limiter la logique OR ; éviter OFFSET profond.
• Mesurez les rows examined et les tables temporaires ; ne comptez pas sur le seul « il y a un index ».

Quand Elasticsearch est le bon choix (la plupart des boutiques e‑commerce)

• Vous avez besoin de navigation facettée avec comptes.
• Vous avez besoin de classement par pertinence et d’ajustements par champ.
• Vous avez besoin de tolérance aux fautes de frappe, synonymes, stemming, analyseurs multilingues.
• Vous avez besoin de latence basse et constante sous des combinaisons de filtres imprévisibles.

Plan de migration étape par étape : MySQL vérité, Elasticsearch recherche

1. Définir le contrat : quels champs sont recherchables, filtrables, triables ; quelle fraîcheur vous garantissez.
2. Concevoir le modèle de document : un document produit avec champs dénormalisés, plus des attributs imbriqués si nécessaire.
3. Créer les mappings intentionnellement : keyword pour correspondances exactes/facettes, text pour recherche analysée, numérique pour plages, scaled_float pour prix.
4. Construire un pipeline d’indexation : événements en queue, mises à jour idempotentes, logique de retry, gestion des dead‑letters.
5. Backfill en sécurité : construire un nouvel index, bulk load, valider comptes et requêtes échantillons, puis basculer avec des alias.
6. Implémenter des lectures duales pendant le déploiement : comparer MySQL et ES pour un sous‑ensemble de trafic pour détecter la dérive logique/mapping.
7. Garde‑fous : limiter les agrégations, limiter la profondeur de page, rate‑limiter les bots, et tracer les formes de requêtes.
8. Exploitez‑le comme en production : tableaux de bord pour heap/disque/latence, alertes sur le retard d’indexation, runbooks pour reindex.

Règles strictes qui évitent les incidents récurrents

• Ne jamais déployer une nouvelle facette sans tests de charge sur des combinaisons réalistes.
• Ne jamais laisser l’UI générer des listes IN (...) non bornées sans un plafond.
• Toujours traiter la pagination profonde comme une décision produit avec une limite explicite.
• Toujours garder MySQL comme source de vérité ; l’index de recherche est une projection.
• Toujours rendre le reindex répétable (alias + source rejouable).

FAQ

1) Puis‑je tout garder dans MySQL si j’ajoute plus d’index ?

Pour une recherche simple, oui. Pour une recherche e‑commerce à facettes avec de nombreux filtres et comptes, non. Les index peuvent atténuer la douleur,
mais ils ne changent pas le désaccord fondamental : juntions + group‑bys + scoring ne sont pas le point fort de MySQL à grande échelle.

2) Et si j’utilise MySQL FULLTEXT ?

FULLTEXT peut aider pour le matching de tokens, mais le faceting et le tuning de pertinence vous poussent encore vers un moteur de recherche.
FULLTEXT ne vous sauvera pas non plus des jointures EAV, de la pagination profonde et des comptes de facettes coûteux.

3) Pourquoi les filtres rendent‑ils SQL plus lent que la recherche textuelle seule ?

Parce que les filtres forcent souvent des jointures (attributs, inventaire, tables de prix) et tuent la sélectivité des index. L’ensemble de candidats reste large,
puis le tri et la pagination multiplient le travail. Dans Elasticsearch, beaucoup de filtres deviennent des intersections d’ensembles mises en cache.

4) Elasticsearch est‑il toujours plus rapide ?

Pour les workloads de type recherche (texte + filtres + agrégations), généralement oui. Pour des recherches transactionnelles et une stricte cohérence, non.
Elasticsearch peut aussi être plus lent si vous sur‑shardez, mappez mal des champs, ou exécutez des agrégations lourdes sur du matériel faible.

5) Comment garder l’inventaire « temps réel » si Elasticsearch est finalement consistant ?

Définissez ce que « temps réel » signifie pour l’UX. Patterns courants : indexer l’inventaire avec des mises à jour fréquentes ; traiter « en stock » comme quasi temps réel ;
ou valider la disponibilité au moment de l’ajout au panier/checkout. Ne prétendez pas que les résultats de recherche sont votre vérité finale.

6) Quelle est la plus grande erreur de schéma en Elasticsearch pour l’e‑commerce ?

Mapper les champs de facette comme text au lieu de keyword, et indexer tout « au cas où ». Cela gonfle les doc values, ralentit les agrégations,
et rend l’utilisation du heap très variable.

7) Comment éviter les problèmes de pagination profonde ?

Préférez la pagination par curseur. Dans MySQL, utilisez la « méthode seek » (retenez la dernière clé de tri vue + id). Dans Elasticsearch, utilisez
search_after pour des tris stables. Aussi : plafonnez la profondeur maximale des pages. Personne n’achète la page 500.

8) Puis‑je faire un hybride : filtrer d’abord en MySQL, puis Elasticsearch ?

C’est possible, mais souvent c’est une taxe de complexité avec des bénéfices faibles. Si MySQL fournit un petit ensemble de candidats (très sélectif),
alors oui. Mais la plupart des filtres de vitrine ne sont pas assez sélectifs, et vous vous retrouvez à faire deux requêtes coûteuses au lieu d’une.

9) Quel est le minimum opérationnel pour faire tourner Elasticsearch correctement ?

Disques solides, assez de heap, nombres de shards sensés, monitoring du heap/disque/latence, et une stratégie de reindex via alias.
Aussi : un pipeline d’indexation avec retries et un moyen de détecter le retard.

10) Quand ne pas utiliser Elasticsearch ?

Si votre « recherche » est vraiment une petite interface d’administration, ou si votre catalogue est minuscule et stable, MySQL est plus simple.
Si votre équipe ne peut pas exploiter un système distribué, investissez dans cette compétence ou choisissez une solution managée.

Conclusion : étapes pratiques suivantes

Si votre recherche e‑commerce inclut plusieurs filtres, comptes de facettes, classement par pertinence, et changements fréquents de tri,
arrêtez d’essayer de sur‑indexer le problème dans MySQL. Vous passerez des semaines à tailler des millisecondes jusqu’à ce qu’une nouvelle facette réintroduise des secondes.
Ce n’est pas de l’ingénierie ; c’est du déni avec des métriques.

Faites plutôt ceci :

1. Mesurez : capturez les formes de requêtes lentes, les rows examined, les tables temporaires et la latence disque.
2. Mettez des garde‑fous sur l’UX : limiter la pagination profonde, limiter l’explosion multi‑sélection, rate‑limiter les bots.
3. Décidez le contrat : ce qui doit être frais, ce qui peut être quasi temps réel, et ce que signifie la correction.
4. Déplacez la recherche vers Elasticsearch quand l’ensemble de fonctionnalités l’exige : faceting, pertinence, tolérance aux fautes.
5. Exploitez le pipeline : indexation rejouable, basculements par alias, tableaux de bord sur le retard d’indexation et la santé du cluster.

MySQL reste l’endroit approprié pour les commandes, la vérité d’inventaire, les règles de tarification, et tout ce que vous expliquerez aux auditeurs.
Elasticsearch est l’endroit où vous laissez les clients explorer votre catalogue à vitesse sans transformer votre base de données en radiateur.