Optimierung analytischer Abfragen

1) Warum optimieren (iGaming Kontext)

Geschäftsgeschwindigkeit: GGR/NET-Berichte, Anbieter/Spiele, RG/AML und Marketing in p95 SLA.
Kosten: Weniger gescannte Bytes und Shafle → unter $/Anfrage.
Zuverlässigkeit: stabile Spitzenstunden, kein „Einfrieren“ von BI.
Maßstab: Dutzende Marken/Märkte, Milliarden von Zeilen, Minuten der Frische.

2) Lastprofil und SLO

Beschreiben Sie die „ersten 90%“ der Abfragen: Fenster (7/28/90d), Filter ('brand, country, provider, psp, status'), join's, JSON-Attribute, Top K und Perzentile.
SLO-Beispiele: p95 ≤ 1. 2 s für Dashboard, gescannte Bytes ≤ 256 MB/Anfrage, Frische ≤ 5 min.

3) Die Anatomie der Pläne: was zu suchen

Predicate/Projection pushdown: Filter und Spaltenliste werden zur Quelle abgesenkt.
Partition pruning & data skipping: Schneiden Sie überflüssige Partitionen/Dateien (min-max/bloom/manifest).
Vectorized scan/late materialization: Lesen Sie durch Spalten, die von JOIN/PROJECT verzögert wurden.
Join strategy: Broadcast Hash (BHJ), Sort-Merge (SMJ), Nested Loop (NLJ — избегать).
Spill & Shuffle: Das Volumen des Shufflings und das Streuen auf die Scheibe ist der Hauptfeind des SLA.
Adaptive Query Execution: Strategiewechsel im Rentime (BHJ↔SMJ, dynamische Koales).

Der Plan sollte zeigen: wie viele Bytes wir lesen, wo Shaflim ist, was wir zwischenspeichern.

4) Parteien, Sortierung, Cluster-Fälle

Parties: durch 'date' + 1-2 Zugriffsmessungen (z.B. 'brand, country').
Sortierung/Clustering: 'ORDER BY/CLUSTER BY/Z-order' nach häufigen Filtern/Joins ('provider, game_id, occurred_at').
Umklassifizierung und Compaction: regelmäßige Verschiebung für das Datenspringen; Zieldateigröße 128-1024 MB.

5) JOIN-Muster

Broadcast Hash Join (BHJ): Eine kleine Dimension (≤ Hunderte von MB) → Broadcast zu einer Tatsache.

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

Sort-Merge Join (SMJ): große Sets, kompatible Key-Sortier-/Cluster-Cases → minimaler Shaff.
Pre-join/denormalization: Übergeben Sie stabile Attribute aus' dim _ 'in den Ist-Schnappschuss (Projektion/materialisierte Ansicht) - minus JOIN auf dem kritischen Pfad.
Anti/Semijoins: Umschreiben von 'NOT IN/EXISTS' in explizite Semi-/Anti-Join-Pläne.
Beseitigung der Kardinalexplosion: Überprüfen Sie doppelte Schlüssel in den Messungen, verwenden Sie Surrogate-Keys.

6) GROUP BY, Aggregate und Voraggregate

Rollup/Cube/Grouping Sets: eine Phase statt mehrerer Aggregationen.

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));

Materialisierte Ansichten (MV )/Projektionen: 'payments _ 7d _ by _ brand _ psp', 'rounds _ 1d _ by _ provider _ game'.
Partial → Final aggregation: Lassen Sie die Engine teilweise auf Worker (lokal) und schließlich auf den Koordinator aggregieren.
Approximate: HLL für 'COUNT (DISTINCT user)', TDigest für Perzentile - ein Vielfaches günstiger und ausreichend für BI.

7) Fensterfunktionen (ordentlich)

PARTITION BY genau nach Schlüsseln mit hoher Selektivität; ORDER BY - nach Säulensortierung.
Ersetzen Sie schwere Fenster durch Pre-Units und Semi-Joins, wo immer möglich.

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) Filter, Pagination und TOP-K

Die Reihenfolge der Filter ist für CBO nicht wichtig, aber Selektivität und Indizes/Sortierung sind wichtig.
LIMIT … MIT TIES/APPROX TOP-K - Reduzieren Sie den Scan.
Pagination: „keyset pagination“ statt „OFFSET/LIMIT“ für große Tische.

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

9) JSON/halbstrukturiert

Materialisieren Sie heiße Pfade in Spalten ('Gerät. os`, `psp. method`).
Verwenden Sie invertierte Indizes/GIN auf JSON-Pfaden, wenn die Engine unterstützt.
Vermeiden Sie zeilenweise UDF: bessere Projektion mit Attributauswahl.

10) Approx und Sampling

HLL/Theta Sketch: billig „COUNT DISTINCT“.
TDigest/KLL: Perzentile p95/p99 ohne Vollsortierung.
Reservoir/stratified sampling: Interaktive Recherche und Vorschau.

11) Erinnerung, Meerenge und Konkarrensi

Spill-guard: Speicherlimits pro Join/Agg; bei Meerenge - Batch/Parallelismus reduzieren, Sortierung nach Schlüssel erhöhen.
Concurrency & QoS: Pools für „heiße“ Dashboards und schwere Ad-hoc; Scan/Zeitlimits; kill-switch für „vergessene“ Anfragen.
Ergebnis Cache/Query Cache: Aktivieren Sie für wiederholbare BI-Muster, deaktivieren Sie das Frische-Token.

12) Regressionstests und „Doppeldurchlauf“

Speichern Sie Referenzprofile (Plan/Scan-Bytes/Zeit) für Top-N-Abfragen.
Vor der Veröffentlichung von Indizes/Clustern - A/B-Lauf: Vergleichen Sie p95, gescannte Bytes, skipped share, shuffle.
Erstellen Sie „fail-fast“ -Schwellen: Wenn p95 gestiegen ist> X% - Rollback.

13) Beobachtbarkeit und SLO

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

Alertas: Wachstum von gescannten Bytes, fallender Skipped-Share, häufige NLJs, Strait> Schwelle.

14) iGaming Fälle (Rezepte)

14. 1 Zahlungen/PSP: „Absprungspitzen“

WHERE: `ts BETWEEN now()-7d AND now()`, `brand,country,psp,status`.
Partei: Tag; ORDER/Z-order: `(brand,country,ts)`; bitmap: `psp,status`; bloom: `transaction_id`.
MV: `payments_7d_by_brand_psp(status)`.
Das Ergebnis: p95 → ~ 1s, gescannte Bytes ↓ 5-10 ×, Null Meerenge.

14. 2 Spielrunden: Top K Spiele/Stunde

ORDER BY / cluster по `(provider, game_id, occurred_at)`; Projektion für Voraggregate.
Approx Top-K + TDigest für p95 Rundendauer.
Fazit: Sub-Sekunden-Charts auf dem Hot Cache.

14. 3 RG/AML: aktive Einschränkungen

JSON 'reason' → Spalte; bitmap `rg_state`, `kyc_level`; Semi-Join mit dem letzten Status.
Das Ergebnis: ein Bericht „in 30 Tagen“ - in Sekunden, ohne Vollscan.

15) Optimierungs-Checkliste (täglich)

1. Sammlung von Top-N-Anfragen und deren Profilen (Plan/Bytes/Schäfle).
2. Parteien nach Datum + vereinbarte Sortierung/Cluster-Fälle.
3. Überprüfen Sie Pushdown und Projection Pruning (nur die gewünschten Spalten).
4. JOIN-Strategie: Broadcast klein, Sortierung für SMJ, kein NLJ.
5. Voraggregationen/MV für heiße Dashboards.
6. Approx wo zulässig (distinct/percentiles/top-k).
7. JSON → Spalten und/oder invertierte Indizes.
8. Compaction/Requalifizierung; Ziel für Skipped Bytes ≥ 70%.
9. Ergebniscache und getrennte Concarrency-Pools.
10. Überwachung: p95, gescannte Bytes, Shuffle, Spill, Hit-Rate.

16) Vorlagen (gebrauchsfertig)

16. 1 Optimierungsrichtlinie (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 Regressionstest der Abfrage (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 DISTINCT umschreiben

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-Muster

„SELECT“ in der Produktion; keine Projektion pruning.
OFFSET-Paginierung auf Millionen von Zeilen.
COUNT DISTINCT ohne Sketche; Perzentil durch ein vollständiges Sort.
NLJ auf großen Sets; join durch JSON-Ausdrücke.
Kleine Parteien und verstreute Dateien (Metadatensturm).
UDF-Strings in WHERE anstatt Spalten zu materialisieren.
Ignorieren Statistiker/ANALYZE - Blind Optimizer und Full Scan.
Keine Regressionstests und Rollback-Schwellenwerte.

18) Fahrplan für die Umsetzung

0-30 Tage (MVP)

1. Messung der Top N Anfragen und SLO/SLI Installation.
2. Parties nach Datum + Sortierung/Cluster-Fälle; data skipping/bloom aktivieren.
3. Ein MV pro „heißer“ Zahlungsbericht; HLL/TDigest в BI.
4. Trennung von Anforderungspools, Einbeziehung des Ergebniscache.

30-90 Tage

1. Zählung schwerer Fenster/JSON → Voraggregationen/Spalten.
2. Broadcast-Join in kleinen Dimensionen; SMJ für große; Beseitigung von NLJ.
3. Kompression und Umklassifizierung nach Zeitplan; Autoschreiber der Schlüssel.
4. Beobachtbarkeit und Alert Degradationen, A/B-Pläne, Auto-Rollback.

3-6 Monate

1. Projektionsverzeichnis/MV mit Versionierung und SLA.
2. Approx-Core für distinct/percentile/top-k auf allen Dashboards.
3. Einheitliche Muster von Regress-Tests und Budgets $/Anfrage.
4. Ständige Hygiene JSON und UDF: Materialisierung und Indizes.

19) RACI

Data Platform (R): Parties/Clustering/Compaction, MV/Projektionen, Caches, Monitoring.
Analytics/BI (R): SQL-Neuschreibung, Approx-Aggregate, Regressionstests.
Domain Owners (C): Anforderungen an Schnitte und Genauigkeit.
Sicherheit/DPO (A/R): Datenschutz/PII, k-Anonymität der Aggregate.
SRE/Observability (C): SLO/Alerting, Concarrensi und Capacity.
Finanzen (C): Budgets für $/Anfrage und wirtschaftliche Auswirkungen.

20) Verwandte Abschnitte

Indizierung analytischer Speicher, Datenschemata und deren Entwicklung, Datenvalidierung, DataOps-Praktiken, Datenclustering, Dimensionsreduktion, Analytik und Metrik APIs, MLOps: Ausnutzung von Modellen.

Summe

Die Abfrageoptimierung ist kein „magisches Hint“, sondern ein System: kompetente Datenmarkierung (Partition/Cluster), Präaggregationen und approximatische Algorithmen, korrekte JOIN-Strategien, Cache/Concarrency und permanente Überwachung von p95 und gescannten Bytes. Für iGaming bedeutet dies schnelle und stabile Zahlungs-, Spiel- und Compliance-Metriken - innerhalb von SLA und Budget.