Sorğuların indeksləşdirilməsi və optimallaşdırılması

1) Indeksləşdirmə və optimallaşdırma məqsədləri

Gecikmə: P50/P95/P99 azaldılması.
Bant genişliyi: üfüqi miqyaslı olmadan QPS artımı.
Proqnozlaşdırıla bilər: sabit planlar və cavab vaxtında «sıçrayışların» olmaması.
Qənaət: daha az IO/CPU, bulud üçün daha az hesab.
Etibarlılıq: Düzgün giriş yolu ilə kilidlərin və deflokların azaldılması.

• Hər hansı bir optimallaşdırma düzgün və uyğun olmalıdır.
• Metrik və plan loqlarında təsiri izləmək.

2) Indekslərin əsas strukturları və onları nə zaman tətbiq etmək

2. 1 B-Tree (defolt)

Bərabər/diapazonlar, çeşidləmə, 'ORDER BY'.
Ən vaxt/ID/status filtrləri üçün yaxşıdır.

2. 2 Hash

Təmiz bərabərliklər ('='), yaddaşda daha ucuz, lakin sifarişsiz (PG: məhdudiyyətlər aradan qaldırılır, lakin hələ də niş seçimdir).

2. 3 GIN / GiST (PostgreSQL)

GIN: massivlər/JSONB açarları, tam mətn (tsvector), containment ('@>').
GiST: geo, diapazonlar, kNN.

2. 4 BRIN (PostgreSQL)

«Təbii sıralanmış» masalar üzrə super ucuz indeks (append-only). Böyük cədvəllər ilə time-series üçün yaxşıdır.

2. 5 Bitmap (MySQL/InnoDB: yerli deyil; DW-SUBD/OLAP)

Aşağı kardinallıq və fasetlər üçün effektiv, daha çox sütun anbarlarında.

2. 6 Sütun indeksləri (ClickHouse)

Primary key + data skipping (minmax), secondary через `skip indexes` (bloom, set).
aqreqasiya və diapazonlar ilə OLAP sorğular.

2. 7 Çevirilmiş indekslər (Elasticsearch/OpenSearch)

Tam mətn, fasetlər, hibrid axtarış. Dəqiq filtrələr üçün keyword sahələri və doc values istifadə edin.

2. 8 MongoDB

Single, compound, multikey (massivlər), partial, TTL, text, hashed (vahid açar üçün).

3) Açarların və kompozit indekslərin layihələndirilməsi

3. 1 «Sol prefiks» qaydası

İndeks sahələrinin sırası istifadəni təyin edir.
'WHERE tenant_id =? AND created_at >=? ORDER BY created_at DESC` → индекс `(tenant_id, created_at DESC, id DESC)`.

3. 2 Tie-breaker

Sabit çeşidləmə və seek-paginasiya üçün unikal quyruq (adətən 'id') əlavə edin.

3. 3 Qismən/süzülmüş indekslər

sql
CREATE INDEX idx_orders_paid_recent
ON orders (created_at DESC, id DESC)
WHERE status = 'paid' AND created_at > now() - interval '90 days';

3. 4 Örtük indeksləri

sql
CREATE INDEX idx_user_lastseen_inc ON users (tenant_id, last_seen DESC) INCLUDE (email, plan);

3. 5 Funksional/Hesablanabilir

sql
CREATE INDEX idx_norm_email ON users (lower(email));

4) Partiyalaşdırma və şardlaşdırma

4. 1 Partiyalaşdırma (PG native/cədvəl irsi; MySQL RANGE/LIST)

Vaxtına görə partiyaların rotasiyası ('daily/weekly') 'VACUUM/DELETE' -ni asanlaşdırır.
Indekslər yerli partiya → daha az B-Tree, daha sürətli plan.

sql
CREATE TABLE events (
tenant_id bigint,
ts timestamptz,
...
) PARTITION BY RANGE (ts);

4. 2 Partiyalaşdırma açarı

OLTP-də - 'tenant _ id' (yükün lokallaşdırılması).
time-series/OLAP-da - po 'ts' (diapazonlu sorğular).
Hibrid: '(tenant_id, ts)' + alt partiyalar.

4. 3 Şardlaşdırma

Consistent hashing/range-shard ilə 'tenant _ id' və ya vaxt.
Cross-shard sorğu → scatter-gather və k-way merge; cursor per-shard saxlamaq.

5) Statistika, kardinallıq və planlar

5. 1 Aktual statistika

Avtomatik analizi daxil edin ('autovacuum/autoanalyze'), «çirkli» paylamalar üçün 'default _ statistics _ target' artırın.

5. 2 Qabaqcıl Statistika (PG)

sql
CREATE STATISTICS stat_user_country_city (dependencies) ON country, city FROM users;
ANALYZE users;

5. 3 icra planı

• `Rows`, `Loops`, `Actual time`, `Shared Read/Hit`, `Recheck Cond`.
• Типы join: Nested Loop, Hash Join, Merge Join.
• Seq Scan vs Index Scan/Only Scan/Bitmap Heap Scan.

5. 4 Planların sabitliyi

Parametrləşdirmə (prepared statements) pis planda «yapışa» bilər. Plan cache guardrails istifadə edin (PG: 'plan _ cache _ mode = force_custom_plan' problemli sorğular üçün) və ya sabitin «sürüşməsi».

6) Join və çeşidləmə optimallaşdırılması

6. 1 Strategiyalar

Nested Loop: kiçik xarici, daxili sürətli indeks.
Hash Join: böyük dəstlər, hash masa altında kifayət qədər yaddaş.
Merge Join: artıq mövcud qaydada sərfəli olan sıralanmış girişlər.

6. 2 join altında indekslər

Üçün 'A JOIN B ON B.a_id = A.id' → indeks 'B (a_id)'.
join sonra filter üçün - daxili masa filter sütunlarında indeks.

6. 3 çeşidləmə

Müvafiq indeks olmadan 'ORDER BY' -dən çəkinin; böyük dəstlərində çeşidləmə yol yaddaş/disk.

7) Sorğuların yenidən yazılması (query rewrite)

«Qar dənələri» alt sorğulardan qurtulun; JOIN-də açın.
CTE-inline (PG ≥ 12 inlines CTE default istifadə edin, lakin 'MATERIALIZED' lazım olduqda ara nəticəni düzəldə bilər).
'SELECT' → sahələri sıralayın (IO/şəbəkəyə qənaət).
Hesablamaları 'WHERE' -dən indeksləşdirilən formaya (əvvəlcədən təyin olunan sütunlar) köçürün.
Aqreqasiyalar: əvvəlcədən məcmu cədvəllər/incremental yeniləmə ilə materiallaşdırılmış performans.

8) Batching, limit və paginasiya

Batch-insert/update: paket 500-5000 əvəzinə vahid.
Seek-pagination '(sort_key, id)' əvəzinə dərin 'OFFSET'.
Çeşidlənmədən/joydan əvvəl dəstin limitlənməsi (push-down 'LIMIT').

9) Caching və Denormalization

Query-cache proqram səviyyəsi (açar = SQL + bind-vars + versiyası hüquqları).
Ağır aqreqatlar üçün Materialized views; rotasiya/refres planı.
Denormalizasiya: tez-tez oxunan hesablanan sahələri (endirim ilə qiymət) saxlayın, lakin sabitlik üçün trigger/fon vəzifəsi ilə.
Redis L2 kimi «isti» açarlar üçün (TTL və hadisə əlilliyi ilə).

10) Məşhur mühərriklərin xüsusiyyətləri

10. 1 PostgreSQL

Индексы: B-Tree, Hash, GIN/GiST, BRIN, partial, functional, INCLUDE.

sql
CREATE INDEX idx_orders_tenant_created_desc
ON orders (tenant_id, created_at DESC, id DESC)
INCLUDE (amount, status);

sql
CREATE INDEX idx_docs_fts ON docs USING GIN (to_tsvector('russian', title          ' '          body));

10. 2 MySQL/InnoDB

sql
ALTER TABLE orders ALTER INDEX idx_old INVISIBLE; -- check risk-free plans

Histoqramlar üzrə statistika ('ANALYZE TABLE... UPDATE HISTOGRAM` в 8. 0).

10. 3 ClickHouse

Əsas açar = sıralama; 'ORDER BY (tenant_id, ts, id)'.

sql
CREATE TABLE events (
tenant_id UInt64,
ts DateTime64,
id UInt64,
payload String,
INDEX idx_bloom_payload payload TYPE bloom_filter GRANULARITY 4
) ENGINE = MergeTree()
ORDER BY (tenant_id, ts, id);

10. 4 MongoDB

js db. orders. createIndex({ tenant_id: 1, created_at: -1, _id: -1 });
db. orders. createIndex({ status: 1 }, { partialFilterExpression: { archived: { $ne: true } } });

Diaqnoz üçün 'hint ()' istifadə edin, 'covered query'.

10. 5 Elasticsearch/OpenSearch

Keyword vs text sahələri; doc_values/aqreqatların çeşidlənməsi.
Heap seqmentasiyası: aqreqasiya - ağır; 'size' ilə məhdudlaşın və 'composite' aqreqasiyasını (səhifə seçimi) istifadə edin.
Dəqiq müqayisə tələb olunan yerlərdə analizatorları işə salmayın.

11) Rəqabət, kilid və MVCC

Qısa əməliyyatlar; ehtiyac olmadan 'REPEATABLE READ' altında «uzun» oxumaqdan çəkinin.
İndeks əməliyyatları da kilidləri alır (write throughput azaldılması).
Onlayn indeksləşdirməni planlaşdırın: 'CREATE INDEX CONCURRENTLY' (PG), 'ALGORITHM = INPLACE '/' ONLINE' (MySQL).
Saat/ID → indeksin «qaynar səhifələri» quyruğuna əlavə edin; açarı paylayın (UUIDv7/duz).

12) Müşahidə və SLO

• 'db _ query _ latency _ ms' (P50/P95/P99) sorğunun adı ilə.
• `rows_examined`, `rows_returned`, `buffer_hit_ratio`.
• `deadlocks`, `lock_wait_ms`, `temp_sort_disk_usage`.
• 'Index Scan' gözlənilən yerdə 'Seq Scan' ilə planların payı.
• DBB versiyasını/parametrlərini dəyişdirərkən reqress alertləri.

• Eşik ilə slow query log daxil edin (məsələn, 200 ms).
• Span sorğularının korrelyasiyası (trace_id).
• Problemli sorğu planlarını çıxarın və retrospektiv üçün obyektin anbarında saxlayın.

• Oxunma P95 '<= 150 ms' zaman 'LIMIT <= 50' və «isti» tenant.
• Qeydlərin P95 '<= 200 ms' 1000 sətrə qədərdir.

13) Təhlükəsizlik və çox tenant

Giriş nəzarəti sahələri üzrə indekslər ('tenant _ id', 'owner _ id') məcburidir.
Siyasətçilər (RLS/ABAC) ön filtr olmalıdır; əks halda optimallaşdırıcı səhv planlaşdırır.
Həssas sahələri açıq formada indeksləşdirməyin; hash/tokenlər istifadə edin.

14) Anti-nümunələr

Seek-kursor alternativi olmadan dərin 'OFFSET'.
«Hamısı üçün bir indeks» - yaddaş həddindən artıq yüklənməsi və write-path.
Kritik yollarda 'SELECT'.
Funksional indeks olmadan 'WHERE' sütununun üstündəki funksiyalar.
Köhnə statistikaya görə qeyri-sabit planlar.
Sabit sifariş gözləyərkən 'ORDER BY' olmaması.
Indekslər üçün indekslər: ROI <0, çünki bahalı qeyd/dəstək.

15) Giriş çek siyahısı

1. QPS və vaxt Top-N sorğular → 3-5 namizəd seçin.
2. «EXPLAIN ANALYZE» planlarını çıxarın, kardinallığı yoxlayın vs faktiki.
3. Indeksləri dizayn edin: sahə sırası, INCLUDE/partial/functional.
4. Böyük cədvəllər üçün partiyalaşdırma tətbiq edin (müvəqqəti/tenant açarları).
5. Sorğuları yenidən yazın: 'SELECT' çıxarın, sadə CTE-ləri birləşdirin, yığımı məhdudlaşdırın.
6. batching və seek-pagination daxil edin.
7. Cache konfiqurasiya: L1/L2, hadisələr üçün əlillik.
8. Planların monitorinqini və slow-log, reqresslər üçün alertlər daxil edin.
9. Real məlumat paylanması ilə yükləmə testləri aparın.
10. İnkişaf üçün bələdçiləri yeniləyin (ORM-xintlər, indeksləşdirmə, limitlər).

16) Nümunələr «əvvəl/sonra»

sql
SELECT FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

sql
-- Индекс: (status, created_at DESC, id DESC) INCLUDE (amount, currency)
SELECT id, amount, currency, created_at
FROM orders
WHERE status = 'paid'
AND (created_at, id) < (:last_ts,:last_id)   -- seek
ORDER BY created_at DESC, id DESC
LIMIT 50;

17) ORM və API protokolları

N + 1-dən çəkinin: acgöz nümunələr ('includes', 'JOIN FETCH', 'preload').
Sahələrin aydın proyeksiyaları, paginate kursor.
gRPC/REST: 'page _ size' limitini, 'sort' yazın, qeyri-şəffaf tokenlərdən istifadə edin.
Cache planı: parametrləşdirmə istifadə edin; Hər çağırışda «unikal» SQL yaratmayın.

18) Miqrasiya və istismar

Online indeksləri əlavə edin və INVISIBLE/CONCURRENTLY kimi qeyd edin, planları test edin, sonra dəyişdirin.
Indekslərin təftişləri - müntəzəm sanitar təmizləmə: köhnə sahələr üçün dublikatlar, istifadə olunmayan, «ölü».
Partiyanın rotasiya planı (köhnə) və 'VACUUM/OPTIMIZE' cədvəli.

19) Xülasə

Sorğuların optimallaşdırılması sistem mühəndisliyidir: düzgün açarlar və indekslər, dəqiq planlar, düşünülmüş partizanlaşdırma və şardlaşdırma, sorğularda nizam-intizam və ORM, keşləmə və müşahidə. Təsvir edilmiş nümunələrə riayət edərək, sürətli, proqnozlaşdırıla bilən və səmərəli, data artımına və yükə davamlı bir sistem əldə edəcəksiniz.