Optymalizacja zapytań analitycznych
1) Dlaczego zoptymalizować (kontekst iGaming)
Szybkość prowadzenia działalności: raporty GGR/NET, dostawcy/gry, RG/AML i marketing w p95 SLA.
Koszt: mniej skanowane bajty i wał → poniżej $/żądanie.
Niezawodność: stabilne godziny szczytu, bez zamrażania BI.
Skala: Dziesiątki marek/rynków, miliardy linii, minuty świeżości.
2) Profil obciążenia i SLO
Opisz „pierwsze 90%” żądań: okna (7/28/90d), filtry ('marka, kraj, dostawca, psp, status'), atrybuty join, JSON, top K i percentyle.
Przykłady SLO: p95 ≤ 1. 2 s dla deski rozdzielczej, skanowane bajty ≤ 256 MB/żądanie, świeżość ≤ 5 min.
3) Anatomia planów: czego szukać
Pushdown Predict/Projection - Filtry i lista kolumn są pomijane do źródła.
Przycinanie partycji i pomijanie danych (min-max/bloom/manifest).
Skanowanie wektorowe/późna materializacja: kolumna jest odroczona przez JOIN/PROJECT.
Dołącz do strategii: Transmisja Hash (BHJ), Sort-Merge (SMJ), Nested Loop (NLJ - ибебата).
Spill & shuffle: Objętość shuffling i rozlania na dysku jest głównym wrogiem SLA.
Adaptacyjne wykonanie zapytań: zmiana strategii w czasie trwania (przełączanie BHJ, koale dynamiczne).
Plan powinien pokazać: ile bajtów czytamy, gdzie jest shaflim, co buforujemy.
4) Strony, sortowanie, sprawy klastrowe
Strony: przez „data” + 1-2 wymiary dostępu (na przykład „marka, kraj”).
Sortowanie/klastrowanie: 'ORDER BY/CLUSTER BY/Z-order' przez częste filtry/przyłącza ('provider, game_id, occurred_at').
Przeklasyfikowanie i zagęszczenie: regularne przekazywanie w celu pominięcia danych; Docelowy rozmiar pliku to 128-1024 MB.
5) PRZYŁĄCZ wzory
Transmisja Hash Join (BHJ): mały wymiar (≤ setki MB) → nadawane do faktu.
sql
/ hint if engine supports/
SELECT /+ BROADCAST(dim_provider) /...Sort-Merge Join (SMJ): duże zestawy, kompatybilne przypadki sortowania kluczy/klastra → minimalny wał.
Wstępne połączenie/denormalizacja: przesuń stabilne atrybuty z 'dim _' do rzeczywistego migawki (rzut/zmaterializowany widok) - minus JOIN na ścieżce krytycznej.
Anty/semijoiny: przepisać „NIE MA/ISTNIEJE” w wyraźne plany pół-/anty-join.
Eliminacja eksplozji kardynalnej: sprawdź duplikaty kluczy w wymiarach, użyj kluczy zastępczych.
6) GRUPA WEDŁUG, kruszywa i preagregacje
Zestawy Rollup/Cube/Grouping: jedna faza zamiast wielu agregacji.
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));Zmaterializowane widoki (MV )/projekcje: 'payments _ 7d _ by _ brand _ psp', 'rounds _ 1d _ by _ provider _ game'.
Częściowa → Końcowa agregacja: umożliwić silnikowi częściowe agregowanie na pracownikach (lokalnych), a wreszcie na koordynatorze.
Przybliżony: HLL dla 'COUNT (DISTINCT user)', TDigest dla percentyli - wiele tańszych i wystarczająco dla BI.
7) Funkcje okien (schludne)
PARTYCJA dokładnie na klawiszy o wysokiej selektywności; ZAMÓW - według sortowania kolumn.
W miarę możliwości wymienić ciężkie okna na preagregaty i półłączniki.
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) Filtry, paginacja i TOP-K
Kolejność filtrów nie jest ważna dla CBO, ale selektywność i indeksy/sortowanie są.
LIMIT... Z KRAWATAMI/OK TOP-K - skróć skan.
Paginacja: 'pagination keyset' zamiast 'OFFSET/LIMIT' dla dużych tabel.
sql
-- keyset
SELECT FROM t WHERE (date, id) > (:last_date,:last_id) ORDER BY date, id LIMIT 1000;9) JSON/półstrukturalne
Urzeczywistnić gorące ścieżki w kolumnach ('device. os ',' psp. metoda ").
Użyj odwróconych/GIN indeksów na ścieżkach JSON, jeśli silnik obsługuje.
Unikaj UDF według linii: lepsza projekcja z zaznaczonymi atrybutami.
10) Ok i pobieranie próbek
HLL/Theta Sketch: Tanie 'COUNT DISTINCT'.
TDigest/KLL: percentyle p95/p99 bez pełnego sortowania.
Zbiornik/próbki stratyfikowane: interaktywne badania i podglądy.
11) Pamięć, cieśnina i konkarrencja
Spill-guard: limity pamięci w połączeniu/agg; przy rozlewaniu - zmniejszyć partię/paralelizm, zwiększyć sortowanie przez klucz.
Concurrency & QoS: baseny do „gorących” desek rozdzielczych i ciężkich hell-hoc; skanowanie/terminy; kill-switch na „zapomniane” żądania.
Cache result/query cache: enable for repeatable BI templates, disable by freshness token.
12) Badania regresyjne i „double run”
Przechowuj profile referencyjne (plan/skanowanie bajtów/czas) dla najlepszych zapytań N.
Przed wydaniem indeksów/klastrów - A/B run: porównaj p95, skanowane bajty, pominięty udział, shuffle.
Tworzenie progów „fail-fast”: jeśli p95 wzrosła> X% - rollback.
13) Obserwowalność i SLO
SLI:- p50/p95/p99 opóźnienia, skanowane bajty/zapytanie, pominięte bajty%, dotknięte pliki;
- shuffle bajty, rozlane bajty, pamięć szczytowa;
- szybkość trafienia w pamięci podręcznej; agregaty podejścia dokładności.
Alerty: wzrost skanowanych bajtów, spadek pominiętego udziału, częste NLJ, rozlanie> progi.
14) iGaming cases (przepisy)
14. 1 Płatności/dostawcy usług płatniczych: „szczyty zwolnień”
GDZIE: 'ts BETWEEN NOW () -7d AND NOW ()', 'marka, kraj, psp, status'.
Strona: dzień; ORDER/Z-order: „(marka, kraj, ts)”; mapa bitowa: „psp, status”; bloom: 'transaction _ id'.
MV: „płatności _ 7d _ by _ brand _ psp (status)”.
Wynik: p95 → ~ 1s, zeskanowane bajty, 5-10 ×, zero cieśniny.
14. 2 rundy gry: Top K gry/godzina
ORDER BY/cluster „(dostawca, game_id, occurred_at)”; projekcja dla preagratów.
Ok Top-K + TDigest na czas trwania p95.
Linia dolna: wykresy podsektorowe na podgrzewaczu.
14. 3 aktywne limity RG/AML
JSON 'reason' → kolumna; mapa bitowa 'rg _ state', 'kyc _ level'; połączyć się z ostatnim stanem.
Wynik: raport „przez 30 dni” - sekundy, bez pełnego skanowania.
15) Lista kontrolna optymalizacji (dziennie)
1. Zbiór żądań top N i ich profili (plan/bajty/wał).
2. Partie według daty + uzgodnione przypadki sortowania/klastra.
3. Sprawdzanie przycinania i przycinania projekcji (tylko wymagane kolumny).
4. JOIN strategy: broadcast small, kind for SMJ, no NLJ.
5. Wstępna agregacja/SN do desek rozdzielczych na gorąco.
6. Ok, gdzie ważne (odrębne/percentyle/top-k).
7. JSON → kolumny i/lub indeksy odwrócone.
8. zagęszczenie/przeklasyfikowanie; pominięty cel bajtów ≥ 70%.
9. Pamięć podręczna wyników i oddzielne baseny konkarrencji.
10. Monitoring: p95, skanowane bajty, shuffle, wyciek, hit-rate.
16) Szablony (gotowe do użycia)
16. 1 Polityka optymalizacji (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: 12016. 2 Test regresji zapytania (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 ODRĘBNE PISMO ZWROTNE
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 Paginacja klucza
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) Anty-wzory
"SELECT 'in prod; brak przycinania projekcji.
OFFSET paginacja na milionach linii.
LICZYĆ ODRĘBNE bez szkiców; percentyle przez pełny rodzaj.
NLJ na dużych zestawach; Dołącz do wyrażeń JSON.
Małe partie i rozproszone pliki (burza metadanych).
UDF struny w GDZIE zamiast materializować kolumny.
Ignoruj statystyki/ANALYZE - ślepy optymalizator i pełny skan.
Brak testów regresyjnych i progów wstecznych.
18) Plan działania na rzecz realizacji
0-30 dni (MVP)
1. Pomiar żądań top N i montaż SLO/SLI.
2. Partie według daty + przypadki sortowania/klastra; włącz pomijanie/kwitnienie danych.
3. Jeden MV na gorącą płatność; HLL/TDigest БI.
4. Podziel puli zapytań, włącz pamięć podręczną wyników.
30-90 dni
1. Ciężki spis okien/JSON → preagregacja/kolumny.
2. Broadcast-join małe wymiary; SMJ dla dużych; eliminacja NLJ.
3. Harmonogram zagęszczania i przeklasyfikowania; Doradca klucza.
4. Obserwowalność i ostrzeżenia o degradacji, plany A/B, auto-rollback.
3-6 miesięcy
1. Katalog projekcji/MV z wersją i SLA.
2. Ok jądro dla różnych/percentile/top-k na wszystkich deskach rozdzielczych.
3. Jednolite szablony testów regresji i budżetów $/request.
4. Higiena stała JSON i UDF: materializacja i wskaźniki.
19) RACI
Platforma danych (R): partycje/klastrowanie/kompresja, MV/projekcje, bufory, monitorowanie.
Analityka/BI (R): pisanie zwrotne SQL, ok. kruszywa, testy regresyjne.
Właściciele domeny (C): wymagania dotyczące sekcji i dokładności.
Bezpieczeństwo/DPO (A/R): prywatność/PII, k-anonimowość agregatów.
SRE/Observability (C): SLO/alertowanie, konkarrencja i zdolność.
Finanse (C): budżety na $/wniosek i efekt gospodarczy.
20) Sekcje powiązane
Analytical Storage Indexing, Data Schemas and Evolution, Data Validation, KeyOps Practices, Data Clustering, Dimension Reduction, Analytics and Metrics API, MLOps: Model Exploitation.
Razem
Optymalizacja zapytań nie jest „magiczną wskazówką”, ale systemem: kompetentny znacznik danych (partycje/klastry), preagregacja i przybliżone algorytmy, poprawne strategie JOIN, pamięć podręczna/konkarrencja i stałe monitorowanie p95 i skanowanych bajtów. Dla iGaming oznacza to szybkie i stabilne wskaźniki płatności, gier i zgodności - w ramach SLA i budżetu.