Optimizarea interogărilor analitice

1) De ce optimiza (context iGaming)

Viteza de afaceri: rapoarte GGR/NET, furnizori/jocuri, RG/AML și marketing în p95 SLA.
Cost: mai puțin bytes scanate și shafl → sub $/cerere.
Fiabilitate: ore de vârf stabile, fără înghețuri BI.
Scară: Zeci de mărci/piețe, miliarde de linii, minute de prospețime.

2) Profilul de încărcare și SLO

Descrieți „primele 90%” de cereri: ferestre (7/28/90d), filtre ('brand, country, provider, psp, status'), join's, atribute JSON, top K și percentile.
Exemple SLO: p95 ≤ 1. 2 s pentru tabloul de bord, octeți scanați ≤ 256 MB/cerere, prospețime ≤ 5 min.

3) Anatomia planurilor: ce să caute

Predict/Projection pushdown - Filtrele și lista de coloane sunt omise la sursă.
Tăierea partițiilor și sărirea datelor (min-max/bloom/manifest).
Scanare vectorizată/materializare târzie: coloana citește amânată de JOIN/PROJECT.
Alăturați-vă strategiei: Broadcast Hash (BHJ), Sort-Merge (SMJ), Imbricate Loop (NLJ - избегать).
Vărsare și amestecare: Volumul de amestecare și vărsare pe disc este principalul inamic al SLA.
Execuție interogare adaptivă: schimbarea strategiei în timpul rulării (comutare BHJ↔SMJ, coale dinamice).

Planul ar trebui să arate: câți octeți citim, unde este shaflim, ce cache.

4) Părți, sortare, cazuri de cluster

Părți: prin „data” + 1-2 dimensiuni de acces (de exemplu, „marcă, țară”).
Sortare/grupare: „ORDINE BY/CLUSTER BY/Z-order” prin filtre/îmbinări frecvente ('furnizor, game_id, occurred_at').
Reclasificare și compactare: transfer regulat pentru sărirea datelor; Dimensiunea fișierului țintă este 128-1024 MB.

5) ALĂTURAȚI-VĂ modelelor

Broadcast Hash Join (BHJ): dimensiune mică (≤ sute de MB) → difuzat la fapt.

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

Sort-Merge Join (SMJ): seturi mari, chei compatibile de sortare/cluster cazuri → arbore minim.
Pre-alăturare/denormalizare: mutați atributele stabile de la 'dim _' la instantaneul real (proiecție/vizualizare materializată) - minus JOIN pe calea critică.
Anti/semijoins: rescrieți 'NOT IN/EXISTS' în planuri explicite semi/anti-unire.
Eliminarea unei explozii cardinale: verificați cheile duplicate în dimensiuni, utilizați cheile surogat.

6) GRUP BY, agregate și preagregări

Seturi de rollup/cub/grupare: o singură fază în loc de mai multe agregări.

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

Vizualizări materializate (MV )/proiecții: 'payments _ 7d _ by _ brand _ psp', 'rounds _ 1d _ by _ provider _ game'.
Agregarea parțială → finală: permiteți motorului să se agregeze parțial pe lucrători (locali) și, în cele din urmă, pe coordonator.
Aproximativă: HLL pentru „COUNT (utilizator distinct)”, TDiest pentru percentile - multiple mai ieftine și suficiente pentru BI.

7) Funcțiile ferestrei (îngrijite)

PARTITION BY exact pe chei cu selectivitate ridicată; COMANDA BY - după sortarea coloanelor.
Înlocuiți ferestrele grele cu preagregate și semi-îmbinări acolo unde este posibil.

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) Filtre, paginare și TOP-K

Ordinea filtrului nu este importantă pentru CBO, dar selectivitatea și indicii/sortarea sunt.
LIMITĂ... CU LEGĂTURI/APROX TOP-K - scurtați scanarea.
Pagination: 'keyset pagination' în loc de 'OFFSET/LIMIT' pentru tabele mari.

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

9) JSON/Semi-structurat

Materializarea traseelor fierbinți în coloane ("dispozitiv. os ',' psp. metodă ").
Utilizați indici inversați/GIN pe căile JSON dacă motorul acceptă.
Evitați UDF pe linie: proiecție mai bună cu atribute evidențiate.

10) Aprox și eșantionare

HLL/Theta Sketch: Ieftin "COUNT DISTINCT'.
TDistest/KLL: percentile p95/p99 fără sortare completă.
Rezervor/prelevare de probe stratificate: cercetare interactivă și previzualizări.

11) Memorie, strâmtoare și concarrență

Garda de scurgere: limite de memorie la îmbinare/agg; când vărsați - reduceți lotul/paralelismul, creșteți sortarea după cheie.
Concurență și QoS: piscine pentru tablouri de bord „fierbinți” și hell-hoc grele; scanare/termene; kill-switch la cereri „uitate”.
Rezultat cache/interogare cache: activați pentru șabloane BI repetabile, dezactivați prin token prospețime.

12) Încercări de regresie și „dublă rulare”

Stocați profiluri de referință (plan/scanare octeți/timp) pentru interogări de top N.
Înainte de a elibera indici/clustere - A/B rula: compara p95, bytes scanate, sărit cota, shuffle.
Creați praguri „fail-fast”: dacă p95 a crescut> X% - rollback.

13) Observabilitate și SLO

• p50/p95/p99 latență, bytes scanate/interogare, sărit octeți%, fișiere atinse;
• amestecați octeți, biți vărsați, memorie de vârf;
• rata de lovire a memoriei cache; agregate de abordare a preciziei.

Alerte: creșterea octeților scanați, scăderea cotei sărite, NLJ-uri frecvente, scurgeri> praguri.

14) iGaming cazuri (rețete)

14. 1 Plăți/PSP: „vârfuri de renunțare”

UNDE: 'TS ÎNTRE ACUM () -7d ȘI ACUM ()', 'brand, country, psp, status'.
Petrecere: zi; COMANDĂ/comandă Z: „(marcă, ţară, ts)”; bitmap: 'psp, status'; bloom: 'tranzaction _ id'.
MV: 'payments _ 7d _ by _ brand _ psp (status)'.
Rezultat: p95 → ~ 1s, octeți scanați ↓ 5-10 ×, strâmtoare zero.

14. 2 Runde de joc: Top K Jocuri/Oră

ORDINE PRIN/cluster по „(furnizor, game_id, occurred_at)”; proiecție pentru preagregate.
Aproximativ Top-K + TDiest pentru durata rotundă p95.
Linia de fund: grafice sub-secundă pe cache-ul fierbinte.

14. Limite active 3 RG/AML

JSON 'reason' → coloană; bitmap 'rg _ state', 'kyc _ level'; semi-se alătură cu ultimul stat.
Rezultat: raport „timp de 30 de zile” - secunde, fără scanare completă.

15) Lista de verificare de optimizare (zilnic)

1. Colectarea de solicitări de top N și profilurile lor (plan/octeți/shafl).
2. Loturi după data + cazuri convenite de sortare/cluster.
3. Verificarea împingerii și a tăierii proiecției (numai coloanele necesare).
4. ALĂTURAȚI-VĂ strategiei: difuzare mică, sortare pentru SMJ, fără NLJ.
5. Pre-agregare/MV pentru tablouri de bord fierbinți.
6. Aprox în cazul în care este valabil (distinct/percentile/top-k).
7. JSON → coloane și/sau indici inversați.
8. Compactare/reclasificare; sărit bytes țintă ≥ 70%.
9. Rezultate cache și piscine de concarrency separate.
10. Monitorizare: p95, bytes scanate, shuffle, deversare, hit-rate.

16) Șabloane (gata de utilizare)

16. 1 Politica de optimizare (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 Testul de regresie a interogării (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 Rescriere 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 Paginare Keyset

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

"SELECT 'in prod; lipsa de tăiere a proiecției.
OFFSET paginare pe milioane de linii.
COUNT DISTINCT fără schițe; percentile prin sortare completă.
NLJ pe seturi mari; alăturați-vă prin expresii JSON.
Loturi mici și fișiere împrăștiate (furtună de metadate).
Șiruri UDF în cazul în care în loc de materializarea coloane.
Ignorați statisticile/ANALYZE - optimizator orb și scanare completă.
Nu există teste de regresie și nici praguri de întoarcere.

18) Foaia de parcurs privind implementarea

0-30 zile (MVP)

1. Măsurarea cererilor de top N și instalarea SLO/SLI.
2. Loturi dupa data + cazuri sortare/cluster; activați sărirea/înflorirea datelor.
3. Un MV per raport de plată la cald; HLL/TDiest в BI.
4. Împărțiți piscinele de interogare, activați cache-ul rezultat.

30-90 zile

1. Recensământ ferestre grele/JSON → preagregare/coloane.
2. Broadcast-se alăture dimensiuni mici; SMJ pentru mari; eliminarea NLJ.
3. Planificarea compactării și reclasificării; Consilier cheie.
4. Observabilitate și alerte de degradare, planuri A/B, auto-rollback.

3-6 luni

1. Catalog proiectie/MV cu versioning si SLA.
2. Aproximativ kernel pentru distincte/percentile/top-k pe toate tablourile de bord.
3. Șabloane uniforme pentru teste de regresie și bugete $/cerere.
4. JSON și UDF igienă permanentă: materializare și indici.

19) RACI

Platforma de date (R): partiții/clustering/compresie, MV/proiecții, cache-uri, monitorizare.
Analytics/BI (R): rescriere SQL, agregate aprox, teste de regresie.
Proprietarii de domenii (C): cerințe pentru secțiuni și precizie.
Securitate/DPO (A/R): confidențialitate/PII, k-anonimatul agregatelor.
SRE/Observabilitate (C): SLO/alertare, concarrabilitate și capacitate.
Finanțe (C): bugete pentru $/cerere și efect economic.

20) Secțiuni conexe

Indexarea stocării analitice, schemele de date și evoluție, validarea datelor, practicile DataOps, clustering de date, reducerea dimensiunilor, API de analiză și metrică, MLOps: model de exploatare.

Total

Optimizarea interogării nu este un „indiciu magic”, ci un sistem: marcaj de date competent (partiții/clustere), preagregare și algoritmi aproximativi, strategii JOIN corecte, cache/concarrency și monitorizarea constantă a p95 și octeți scanați. Pentru iGaming, aceasta înseamnă valori rapide și stabile pentru plăți, jocuri și conformitate - în cadrul SLA și buget.