Optimisation des requêtes analytiques

1) Pourquoi optimiser (contexte iGaming)

Vitesse d'affaires : rapports GGR/NET, fournisseurs/jeux, RG/AML et marketing en p95 SLA.
Coût : Moins d'octets et de saffles numérisés → moins de $/demande.
Fiabilité : heures de pointe stables, pas de « gel » BI.
Échelle : des dizaines de marques/marchés, des milliards de lignes, des minutes de fraîcheur.

2) Profil de charge et SLO

Décrire les « 90 % premiers » des requêtes : fenêtres (7/28/90d), filtres ('marque, pays, fournisseur, psp, status'), join's, attributs JSON, top K et percentiles.
Exemples de SLO : p95 ≤ 1. 2 s pour dashboard, bytes scannés ≤ 256 MW/requête, freshness ≤ 5 min.

3) Anatomie des plans : que chercher

Predicate/Projection pushdown : les filtres et la liste des colonnes sont abaissés à la source.
Partition pruning & data skipping : coupure des lots/fichiers superflus (min-max/bloom/manifeste).
Scan vectoriel/late materialization : lecture par colonne retardée par JOIN/PROJECT.
Join strategy: Broadcast Hash (BHJ), Sort-Merge (SMJ), Nested Loop (NLJ — избегать).
Spill & shuffle : le volume et le détroit sur disque sont le principal ennemi de la SLA.
Mise en œuvre rapide adaptative : changement de stratégie dans le rentim (commutation de BHJ↔SMJ, coalès dynamique).

Le plan doit montrer : combien d'octets nous lisons, où est le shaflim, ce que nous mettons en cache.

4) Lots, tri, cluster-case

Lots : par 'date' + 1-2 mesures d'accès (par exemple 'marque, pays').
Tri/clustering : 'ORDER BY/CLUSTER BY/Z-order' par filtres/joines fréquents ('provider, game_id, occurred_at').
Reclassement et compaction : transfert régulier pour le data skipping ; taille de fichier cible 128-1024 Mo.

5) Modèles JOIN

Broadcast Hash Join (BHJ) : petite dimension (≤ centaines de Mo) → broadcast au fait.

sql
/ hint if engine supports/
SELECT /+ BROADCAST(dim_provider) /...

Sort-Merge Join (SMJ) : de grands ensembles, les tris/clusters-attachés-cases compatibles clés → minimal шафл.
Pré-join/dénormalisation : faites passer les attributs stables de « bou _ » à un instantané réel (projection/materialized view) - moins JOIN sur le chemin critique.
Anti/semijoins : réécrire 'NOT IN/EXISTS' en plans semi-/anti-join explicites.
Élimination de l'explosion radicale : vérifiez les clés dupliquées dans les mesures, utilisez les clés surrogate.

6) GROUP BY, agrégats et préagrégations

Rollup/Cube/Grouping Sets : une phase au lieu de plusieurs agrégations.

sql
SELECT brand, country, DATE(ts) d, SUM(amount)
FROM gold. payments
WHERE ts >= NOW() - INTERVAL '7 days'
GROUP BY GROUPING SETS ((brand,country,d),(brand,d),(d));

Représentations matérialisées (MV )/projections : 'payments _ 7d _ by _ brand _ psp', 'rounds _ 1d _ by _ provider _ game'.
Partial → Final aggregation : laissez le moteur s'agréger en partie sur les workers (local) et en finale sur le coordinateur.
Approximate : HLL pour 'COUNT (DISTINCT user)', TDigest pour percentiles - multiple moins cher et suffisant pour BI.

7) Fonctions de fenêtre (soigneusement)

PARTITION BY exactement par clés à haute sélectivité ; ORDER BY - par tri par colonne.
Remplacez les fenêtres lourdes par des pré-unités et semi-joins si possible.

sql
-- Instead of window distinct
SELECT brand, COUNT() users
FROM (SELECT DISTINCT brand, user_id FROM gold. sessions WHERE d>=CURRENT_DATE-7) t
GROUP BY brand;

8) Filtres, pagination et TOP-K

L'ordre des filtres n'est pas important pour le CBO, mais la sélectivité et les indices/triages sont importants.
LIMIT … WITH TIES/APPROX TOP-K - raccourcir le scan.
Pagination : 'keyset pagination' au lieu de 'OFFSET/LIMIT' pour les grandes tables.

sql
-- keyset
SELECT FROM t WHERE (date, id) > (:last_date,:last_id) ORDER BY date, id LIMIT 1000;

9) JSON/semi-structuré

Matérialisez les chemins chauds dans les colonnes ('device. os`, `psp. method`).
Utilisez les index/GIN inversés sur les chemins JSON si le moteur prend en charge.
Évitez l'UDF par ligne : meilleure projection avec mise en évidence des attributs.

10) Approx et sample

HLL/Theta Sketch : pas cher 'COUNT DISTINCT'.
TDigest/KLL : percents p95/p99 sans sort complet.
Reservoir/sampling stratifié : recherche interactive et aperçu.

11) Mémoire, détroit et concarrensi

Spill-guard : limites de mémoire sur join/agg ; dans le détroit - réduire le batch/parallélisme, augmenter le tri par clé.
Concurrency & QoS : pools pour les dashboards « chauds » et les ad hoc lourds ; limites de balayage/temps ; kill-switch pour les demandes « oubliées ».
Result cache/query cache : incluez pour les modèles BI reproductibles, réduisez le jeton de fraîcheur.

12) Essais de régression et « double course »

Stockez les profils de référence (plan/scan-octets/temps) pour les demandes N principales.
Avant la sortie des index/clusters - A/B-run : comparer p95, scanned bytes, skipped share, shuffle.
Créez des seuils « fail-fast » : si p95 a augmenté> X % est un retour en arrière.

13) Observabilité et SLO

• p50/p95/p99 latency, scanned bytes/query, skipped bytes %, files touched;
• shuffle bytes, spilled bytes, peak memory;
• cache hit-rate; accuracy approx-agrégats.

Alert : croissance scannée bytes, chute skipped share, fréquents NLJ, détroit> seuil.

14) Case iGaming (recettes)

14. 1 Paiements/PSP : « pics de refus »

WHERE: `ts BETWEEN now()-7d AND now()`, `brand,country,psp,status`.
Lot : jour ; ORDER/Z-order: `(brand,country,ts)`; bitmap: `psp,status`; bloom: `transaction_id`.
MV: `payments_7d_by_brand_psp(status)`.
Résultat : p95 → ~ 1s, bytes scannés ↓ 5-10 ×, détroit zéro.

14. 2 tours de jeu : top K jeux/heure

ORDER BY / cluster по `(provider, game_id, occurred_at)`; projection pour les préagrégats.
Approx Top-K + TDigest pour la durée du tour p95.
Résultat : Sous-secondes graphiques sur le cache chaud.

14. 3 RG/AML : restrictions actives

JSON 'reason' → colonne ; bitmap `rg_state`, `kyc_level`; semi-join avec le dernier état.
Résultat : le rapport « 30 jours à l'avance » est en secondes, sans scan complet.

15) Chèque d'optimisation (quotidien)

1. Collecte du top N des requêtes et de leurs profils (plan/octets/saffle).
2. Lots par date + tri convenu/cluster-case.
3. Contrôle pushdown et projection pruning (seulement les colonnes désirées).
4. Stratégie JOIN : petit broadcast, tri pour SMJ, pas de NLJ.
5. Préagrégation/MV pour les dashboards chauds.
6. Approx là où c'est permis (distinct/percentiles/top-k).
7. JSON → colonnes et/ou index inversés.
8. Compaction/reclassement ; l'objectif de skipped bytes est ≥ 70 %.
9. Cache des résultats et pools concarrensi séparés.
10. Surveillance : p95, scanned bytes, shuffle, spill, hit-rate.

16) Modèles (prêt à l'emploi)

16. 1 Politique d'optimisation (YAML)

yaml workload: bi_hot slo:
p95_latency_ms: 1200 scanned_bytes_max_mb: 256 skipped_bytes_share_min: 0. 70 storage:
partition_by: ["date"]
cluster_by: ["brand","country","occurred_at"]
indexes:
bloom: ["transaction_id","user_surrogate_id"]
bitmap: ["psp","status","rg_state"]
aggregation:
mv:
- name: mv_payments_7d_brand_psp window: "7d"
group_by: ["brand","psp","status"]
approx:
count_distinct: "hll"
percentile: "tdigest"
concurrency:
pools: {bi_hot: 50, adhoc: 10}
timeout_s: 120

16. 2 Test de régression de requête (pseudo-SQL)

sql
-- baseline: p95<=1200ms, scanned_bytes<=256MB
EXPLAIN ANALYZE
SELECT brand, psp, status, COUNT() cnt, SUM(amount) amt
FROM gold. payments
WHERE ts >= NOW() - INTERVAL '7 days'
AND brand =:brand AND country =:country
GROUP BY brand, psp, status;

16. 3 Réécriture DISTINCT

sql
-- Bad: Heavy COUNT (DISTINCT user_id)
SELECT COUNT(DISTINCT user_id) FROM gold. sessions WHERE d>=CURRENT_DATE-7;

-- Better: HLL sketch/preaggregate
SELECT hll_union(user_hll) FROM agg. sessions_7d_user_hll WHERE d>=CURRENT_DATE-7;

16. 4 Keyset-pagination

sql
SELECT
FROM gold. game_rounds
WHERE (occurred_at, round_id) > (:ts,:rid)
AND brand=:brand AND country=:country
ORDER BY occurred_at, round_id
LIMIT 1000;

17) Anti-modèles

« SELECT » en vente ; absence de projection pruning.
Pagination OFFSET sur des millions de lignes.
COUNT DISTINCT sans sketch ; ils ont été percés à travers le sort complet.
NLJ sur de grands ensembles ; join par les expressions JSON.
Petits lots et fichiers dispersés (tempête de métadonnées).
Chaîne UDF dans WHERE au lieu de matérialiser les colonnes.
Ignorer Statistics/ANALYZE est un optimiseur aveugle et un scan complet.
Absence de tests de régression et de seuils de recul.

18) Feuille de route pour la mise en œuvre

0-30 jours (MVP)

1. Mesure du top N des requêtes et installation de SLO/SLI.
2. Lots par date + tri/cluster-case ; activer data skipping/bloom.
3. Un MV par rapport de paiement « à chaud » ; HLL/TDigest в BI.
4. Fractionner les pools de requêtes, activer result cache.

30-90 jours

1. Recensement des fenêtres lourdes/JSON → préagrégation/colonne.
2. Broadcast-join de petites dimensions ; SMJ pour les grands ; élimination de la NLJ.
3. Compaction et reclassement programmés ; Conseiller automatique des clés.
4. Observation et alertes de dégradation, plans A/B, auto-retour.

3-6 mois

1. Catalogue de projections/MV avec versioning et SLA.
2. Approx-noyau pour distinct/percentile/top-k sur tous les dashboards.
3. Modèles uniques de tests de régression et de budgets $/demande.
4. Hygiène constante JSON et UDF : matérialisation et indices.

19) RACI

Data Platform (R) : lots/clustering/compaction, MV/projections, caches, surveillance.
Analytics/BI (R) : réécriture SQL, agrégats approx, tests de régression.
Domain Owners (C) : exigences de coupe et de précision.
Sécurité/DPO (A/R) : vie privée/PII, anonymat k des agrégats.
SRE/Observability (C) : SLO/alerting, concarrensi et capaciti.
Finances (C) : budgets de $/demande et effets économiques.

20) Sections connexes

Indexation des référentiels analytiques, Schémas de données et leur évolution, Validation des données, Pratiques DataOps, Clustering de données, Réduction de la dimension, API analytiques et métriques, MLOps : exploitation des modèles.

Résultat

L'optimisation des requêtes n'est pas un « hint magique », mais un système : balisage des données (lots/clusters), pré-agrégation et algorithmes approximate, stratégies JOIN correctes, cache/concarrensy et surveillance continue p95 et bytes scannés. Pour iGaming, cela signifie des mesures rapides et stables des paiements, des jeux et de la conformité - dans le cadre de la SLA et du budget.