بهینه سازی پرس و جوهای تحلیلی

1) چرا بهینه سازی (زمینه iGaming)

سرعت کسب و کار: گزارش GGR/NET، ارائه دهندگان/بازی ها، RG/AML و بازاریابی در P95 SLA.
هزینه: کمتر بایت اسکن شده و shafl → زیر $/درخواست.
قابلیت اطمینان: ساعت پیک پایدار، بدون BI یخ می زند.
مقیاس: ده ها مارک/بازار، میلیاردها خط، دقیقه طراوت.

2) مشخصات بار و SLO

«90٪ اول» درخواست ها را شرح دهید: ویندوز (7/28/90d)، فیلترها («نام تجاری، کشور، ارائه دهنده، PSP، وضعیت»)، ویژگی های JSON، K بالا و صدک.
نمونه های SLO: p95 ≤ 1. 2 ثانیه برای داشبورد، بایت اسکن شده ≤ 256 مگابایت/درخواست، طراوت ≤ 5 دقیقه.

3) آناتومی برنامه ها: آنچه باید جستجو کنید

Predict/Projection pushdown - فیلترها و لیست ستون ها به منبع حذف می شوند.
هرس پارتیشن و پرش داده (min-max/bloom/manifest).
اسکن Vectorized/Materialization اواخر: ستون خوانده شده توسط JOIN/PROJECT به تعویق افتاد.
پیوستن به استراتژی: پخش هش (BHJ)، مرتب سازی ادغام (SMJ)، حلقه تو در تو (NLJ - избегать).
ریختن و زدن: حجم زدن و ریختن بر روی دیسک دشمن اصلی SLA است.
اجرای تطبیقی پرس و جو: تغییر استراتژی در زمان اجرا (سوئیچینگ BHJ↔SMJ، کویل پویا).

این طرح باید نشان دهد: چند بایت می خوانیم، shaflim کجاست، چه چیزی را ذخیره می کنیم.

4) احزاب، مرتب سازی، موارد خوشه ای

احزاب: با «تاریخ» + 1-2 ابعاد دسترسی (به عنوان مثال، «نام تجاری، کشور»).
مرتب سازی/خوشه بندی: 'ORDER BY/CLUSTER BY/Z-order' با فیلترهای مکرر/پیوست ('ارائه دهنده، game_id، occurred_at').
طبقه بندی مجدد و فشرده سازی: انتقال منظم برای پرش داده ها ؛ حجم فایل هدف 128-1024 مگابایت است.

5) پیوستن به الگوهای

Broadcast Hash Join (BHJ): ابعاد کوچک (≤ صدها مگابایت) → پخش به واقعیت.

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

Sort-Merge Join (SMJ): مجموعه های بزرگ، مرتب سازی کلید/موارد خوشه سازگار → شافت حداقل.
قبل از پیوستن/عدم انطباق: حرکت ویژگی های پایدار از "کم نور _' به عکس فوری واقعی (طرح ریزی/نمایش مادی) - منهای JOIN در مسیر بحرانی.
Anti/semijoins: بازنویسی «NOT IN/EXISTS» به برنامه های صریح نیمه/ضد پیوستن.
حذف انفجار کاردینال: کلیدهای تکراری را در ابعاد بررسی کنید، از کلیدهای جایگزین استفاده کنید.

6) GROUP BY، AGGREGATES و Preaggregations

مجموعه های Rollup/Cube/Grouping: یک فاز به جای چندین تجمع.

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

نمایش Materialized (MV )/پیش بینی: 'payments _ 7d _ by _ brand _ psp'، 'rounds _ 1d _ by _ provider _ game'.
تجمع جزئی → نهایی: اجازه دهید موتور تا حدی روی کارگران (محلی) و در نهایت روی هماهنگ کننده جمع شود.
تقریبی: HLL برای 'COUNT (کاربر متمایز)'، TDigest برای صدک - چند ارزان تر و به اندازه کافی برای BI.

7) توابع پنجره (شسته و رفته)

پارتیشن دقیقا بر روی کلید با انتخاب بالا ؛ سفارش توسط - مرتب سازی ستون.
پنجره های سنگین را با preaggregates و semi-joins جایگزین کنید.

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) فیلترها، صفحه بندی و TOP-K

ترتیب فیلتر برای CBO مهم نیست، اما انتخابی و شاخص/مرتب سازی هستند.
محدود کردن... با روابط/تقریبا TOP-K - اسکن را کوتاه کنید.
صفحه بندی: «صفحه بندی keyset» به جای «OFFSET/LIMIT» برای جداول بزرگ.

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

9) JSON/نیمه ساختار یافته

مسیرهای داغ را به ستون ها تبدیل کنید ("دستگاه. اس پی اس. روش ').
استفاده از شاخص های معکوس/GIN در مسیرهای JSON اگر موتور پشتیبانی می کند.
اجتناب از UDF توسط خط: طرح ریزی بهتر با ویژگی های برجسته.

10) تقریبا و نمونه برداری

طرح HLL/تتا: ارزان 'شمارش متمایز'.
TDigest/KLL: صدک p95/p99 بدون مرتب سازی کامل.
نمونه برداری مخزن/طبقه بندی شده: تحقیقات تعاملی و پیش نمایش.

11) حافظه، تنگه و اختصار

ریختن گارد: محدودیت های حافظه در پیوستن/agg ؛ هنگام ریختن - کاهش دسته/موازی، مرتب سازی بر اساس کلید را افزایش دهید.
همزمانی & QoS: استخر برای «داغ» داشبورد و سنگین جهنم تک کاره; اسکن/محدودیت زمانی ؛ kill-switch را به درخواستهای «فراموش شده» تغییر دهید.
کش نتیجه/حافظه پنهان پرس و جو: برای قالب های تکرار BI فعال, غیر فعال کردن توسط نشانه طراوت.

12) آزمون رگرسیون و «دو اجرا»

ذخیره پروفایل های مرجع (طرح/اسکن بایت/زمان) برای نمایش داده های بالا N.
قبل از انتشار شاخص/خوشه - A/B اجرا: مقایسه p95، بایت اسکن شده، پرش به اشتراک گذاری، زدن.
آستانه های «fail-fast» را ایجاد کنید: اگر p95 افزایش یابد> X٪ - برگشت.

13) قابلیت مشاهده و SLO

• p50/p95/p99 تاخیر، بایت اسکن شده/پرس و جو، پرش بایت٪، فایل های لمس شده ؛
• زدن بایت ها، بایت های ریخته شده، حافظه پیک ؛
• نرخ ضربه کش ؛ دقت رویکرد aggregates.

هشدارها: افزایش بایت های اسکن شده، سقوط در سهم نادیده گرفته شده، NLJ های مکرر، سرریز> آستانه ها.

14) موارد iGaming (دستور العمل)

14. 1 پرداخت/PSPs: «قله چشم پوشی»

WHERE: 'ts BETWEEN NOW () -7D AND NOW ()'، 'نام تجاری، کشور، psp، وضعیت'.
حزب: روز ؛ ORDER/Z-order: '(نام تجاری، کشور، ts) ؛ bitmap: 'psp, وضعیت'; بلوم: 'transaction _ id'.
MV: 'payments _ 7d _ by _ brand _ psp (status)'.
نتیجه: p95 → ~ 1s، بایت اسکن شده ↓ 5-10 ×، تنگه صفر.

14. 2 دور بازی: بالا K بازی/ساعت

سفارش توسط/ по خوشه (ارائه دهنده، game_id، occurred_at) ؛ پیش بینی برای preaggregates.
تقریبا Top-K + TDigest برای مدت زمان دور p95.
خط پایین: نمودار زیر دوم در کش داغ.

14. 3 RG/AML محدودیت های فعال

JSON 'reason → ستون ؛ bitmap 'rg _ state', 'kyc _ level'; نیمه پیوستن به دولت گذشته.
نتیجه: گزارش «برای 30 روز» - ثانیه، بدون اسکن کامل.

15) چک لیست بهینه سازی (روزانه)

1. مجموعه ای از درخواست های N بالا و پروفایل های آنها (plan/bytes/shafl).
2. دسته های تاریخ + موارد مرتب سازی/خوشه توافق شده.
3. چک کردن pushdown و طرح ریزی هرس (فقط ستون های مورد نیاز).
4. استراتژی JOIN: پخش کوچک، مرتب سازی برای SMJ، بدون NLJ.
5. قبل از تجمع/MV برای داشبورد داغ.
6. تقریبا در کجا معتبر (متمایز/صدک/بالا-K).
7. JSON → ستون ها و/یا شاخص های معکوس.
8. فشرده سازی/طبقه بندی مجدد ؛ بایت های رد شده ≥ 70٪ هدف قرار می گیرند.
9. نتایج کش و استخر های جداگانه.
10. نظارت: p95، بایت اسکن شده، زدن، ریختن، نرخ ضربه.

16) قالب (آماده برای استفاده)

16. 1 سیاست بهینه سازی (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 آزمون رگرسیون پرس و جو (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 بازنویسی متمایز

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 صفحه بندی صفحه کلید

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) ضد الگوهای

'SELECT' IN PROD ؛ عدم هرس طرح ریزی.
صفحه بندی افست در میلیون ها خط.
شمارش متمایز بدون طرح ؛ درصد از طریق مرتب سازی کامل.
NLJ در مجموعه های بزرگ ؛ پیوستن به عبارات JSON.
دسته های کوچک و فایل های پراکنده (طوفان ابرداده).
رشته های UDF در WHERE به جای تحقق ستون ها.
چشم پوشی از آمار/تجزیه و تحلیل - بهینه ساز کور و اسکن کامل.
بدون تست رگرسیون و بدون آستانه برگشت.

18) پیاده سازی نقشه راه

0-30 روز (MVP)

1. اندازه گیری N درخواست بالا و نصب SLO/SLI.
2. دسته های تاریخ + مرتب سازی/موارد خوشه ؛ فعال کردن داده ها پرش/شکوفه.
3. یک MV در هر گزارش پرداخت داغ ؛ HLL/TDiest в BI.
4. تقسیم استخر پرس و جو، فعال کردن کش نتیجه.

30-90 روز

1. Heavy windows census/JSON → پیش تجمیع/ستونها.

2. ابعاد کوچک را پخش کنید SMJ برای بزرگ ؛ حذف NLJ

3. فشرده سازی و طبقه بندی مجدد برنامه ؛ مشاور کلیدی

4. قابلیت مشاهده و هشدارهای تخریب، برنامه های A/B، بازگشت خودکار.

3-6 ماه

1. کاتالوگ پروجکشن/MV با نسخه و SLA.
2. تقریبا هسته برای متمایز/صدک/بالا-K در تمام داشبورد.
3. قالب یکنواخت برای آزمون رگرسیون و بودجه $/درخواست.
4. JSON و UDF بهداشت دائمی: تحقق و شاخص.

19) RACI

پلت فرم داده (R): پارتیشن/خوشه بندی/فشرده سازی، MV/پیش بینی ها، کش ها، نظارت.
تجزیه و تحلیل/BI (R): بازنویسی SQL، تقریبا aggregates، آزمون رگرسیون.
صاحبان دامنه (C): الزامات مربوط به بخش ها و دقت.
امنیت/DPO (A/R): حریم خصوصی/PII، k-ناشناس بودن aggregates.
SRE/Observability (C): SLO/هشدار، سازگاری و ظرفیت.
امور مالی (C): بودجه برای $/درخواست و اثر اقتصادی است.

20) بخش های مرتبط

نمایه سازی ذخیره سازی تحلیلی، طرح داده ها و تکامل، اعتبار سنجی داده ها، شیوه های DataOps، خوشه بندی داده ها، کاهش ابعاد، تجزیه و تحلیل و معیارهای API، MLOps: بهره برداری از مدل.

مجموع

بهینه سازی پرس و جو یک «نکته جادویی» نیست، بلکه یک سیستم است: نشانه گذاری داده های صالح (پارتیشن/خوشه)، الگوریتم های پیش جمع آوری و تقریبی، استراتژی های صحیح JOIN، حافظه پنهان/مخلوط و نظارت مداوم بر p95 و بایت های اسکن شده. برای iGaming، این به معنای معیارهای سریع و پایدار برای پرداخت، بازی و انطباق - در SLA و بودجه است.