Dizinleme ve Sorgu Optimizasyonu

1) Endeksleme ve optimizasyon hedefleri

Gecikme: P50/P95/P99 azaltma.
Verim: Ölçeklendirme olmadan QPS büyümesi.
Öngörülebilirlik: istikrarlı planlar ve yanıt süresinde "sıçramalar" yok.
Tasarruf: Daha az IO/CPU, daha az bulut faturası.
Güvenilirlik: Doğru erişim nedeniyle kilitleri ve kilitlenmeleri azaltmak.

• Herhangi bir optimizasyon doğruluğu ve tutarlılığı korumalıdır.
• Metriklerdeki etkiyi izleyin ve günlükleri planlayın.

2) Temel indeks yapıları ve ne zaman uygulanacakları

2. 1 B Ağacı (varsayılan)

Eşittir/aralık, sıralayın, 'ORDER BY'.
Çoğu zaman/kimlik/durum filtreleri için iyidir.

2. 2 Karma

Saf eşitlikler ('='), bellekte daha ucuz ama sıra dışı (PG: kısıtlamalar kaldırıldı ama yine de niş seçim).

2. 3 CIN/GiST (PostgreSQL)

CIN: diziler/JSONB tuşları, tam metin (tsvector), containment ('@>').
GiST: geo, range, kNN.

2. 4 BRIN (PostgreSQL)

"Doğal olarak sıralanmış" tablolara göre süper ucuz dizin (yalnızca zamana göre ekleyin). Büyük tablolarla zaman serisi için iyidir.

2. 5 Bitmap (MySQL/InnoDB: hiçbiri yerel değil; DW-DBMS/OLAP)

Düşük kardinalite ve fasetler için etkilidir, daha çok sütun depolamada.

2. 6 Sütun indeksleri (ClickHouse)

Birincil anahtar + veri atlama (minmax), ikincil через 'dizinleri atla' (bloom, set).
Toplama ve aralıklarla OLAP sorguları.

2. 7 Ters dizinler (Elasticsearch/OpenSearch)

Tam metin, fasetler, hibrit arama. Hassas filtreler için anahtar kelime alanlarını ve doc değerlerini kullanın.

2. 8 MongoDB

Tek, bileşik, çoklu (diziler), kısmi, TTL, metin, karma (tek tip anahtar sharding için).

3) Anahtar ve kompozit indeks tasarımı

3. 1 Sol önek kuralı

Dizindeki alanların sırası kullanılabilirliği belirler.
Query 'WHERE tenant_id =? VE created_at> =? created_at DESC tarafından sipariş 'индекс' (tenant_id, created_at DESC, id DESC) '.

3. 2 tie-breaker

Kararlı sıralama ve sayfalama aramak için benzersiz bir kuyruk (genellikle'id ') ekleyin.

3. 3 Kısmi/filtrelenmiş endeksler

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 Kapsayan endeksler

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

3. 5 Fonksiyonel/hesaplanmış

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

4) Bölümleme ve parçalama

4. 1 Bölümleme (PG yerel/tablo mirası; MySQL RANGE/LIST)

Partilerin zamana göre döndürülmesi ('günlük/haftalık') 'VAKUM/DELETE' işlemini basitleştirir.
Dizinler yerel bölümlerdir - B-Tree'den daha küçük, daha hızlı plan.

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

4. 2 Bölümleme anahtarı

OLTP'de - 'tenant _ id' (load localization) ile.
Zaman serisinde/OLAP - 'ts' (aralık sorguları) ile.
Hibrit: '(tenant_id, ts)' + alt taraflar.

4. 3 Sharding

'Tenant _ id'ya da zamana göre tutarlı hashing/range-shard.
Çapraz parça sorgusu - scatter-gather ve k-way birleştirme; Parça başına imlecini tutun.

5) İstatistik, kardinalite ve planlar

5. 1 Güncel istatistikler

Otomatik analizi etkinleştirin ('autovacuum/autoanalyze'), kirli dağıtımlar için 'default _ statistics _ target' değerini artırın.

5. 2 Gelişmiş İstatistikler (PG)

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

5. 3 Uygulama planı

• 'Rows', 'Loops', 'Actual time', 'Shared Read/Hit', 'Receck Cond'.
• Типы join: İç içe Döngü, Hash Join, Birleştir Join.
• Seq Scan vs Index Scan/Only Scan/Bitmap Heap Scan.

5. 4 Planların istikrarı

Parametrelendirme (hazırlanmış ifadeler) kötü bir plana "yapışabilir". Plan önbellek korkuluklarını (PG: 'plan _ cache _ mode = problem sorguları için force_custom_plan') veya "yönlendirme" sabitlerini kullanın.

6) Birleştirme ve çeşitlerin optimizasyonu

6. 1 Stratejiler

İç İçe Döngü: küçük harici, dahili hızlı dizin.
Karma Birleştirme: büyük kümeler, karma tablo için yeterli bellek.
Birleştir Birleştir: sıralanmış girişler, mevcut sırayla avantajlı.

6. 2 Katılma altındaki dizinler

'A JOIN B ON B.a_id = A.id', için dizin 'B (a_id)'.
Katıldıktan sonra filtre için - iç tablonun filtresinin sütunlarındaki dizin.

6. 3 Triyaj

Karşılık gelen bir dizin olmadan 'ORDER BY'den kaçının; Büyük setlerde sıralama, bellek/disk tarafından pahalıdır.

7) Sorgu yeniden yazma

Alt depremlerin "kar taneleri'nden kurtulun; JOIN'de genişleyin.
CTE satır içi (PG ≥12 CTE varsayılan satır içi) kullanın, ancak 'MATERIALIZED' gerekirse bir ara sonuç verebilir).
'SELECT'i kaldırın - alanları listeleyin (IO/ağ tasarrufları).
Hesaplamaları 'WHERE'den dizinlenmiş forma (önceden hesaplanmış sütunlar) aktarın.
Toplamalar: Artımlı güncelleştirme ile ön özet tabloları/materyalize görünümler.

8) Kasaplık, sınırlama ve sayfalama

Batch-insert/update: Tek tek yerine 500-5000 parti.
Derin 'OFFSET' yerine '(sort_key, id)'tarafından sayfalama isteyin.
Sıralama/joyne ('LIMIT' aşağı itme) önce çevirme sınırlayın.

9) Önbellekleme ve denormalizasyon

Uygulama düzeyinde sorgu-önbellek (anahtar = SQL + bind-vars + rights sürümü).
Ağır agregalar için materyalize görünümler; rotasyon/referans planı.
Denormalizasyon - Sık sık hesaplanan alanları (indirim dahil fiyat) okuyun, ancak tutarlılık için tetikleyici/arka plan görevi ile saklayın.
Redis, kısayol tuşları için L2 olarak (TTL ve olay özürlü).

10) Popüler motorların özellikleri

10. 1 PostgreSQL

Индексы: B-Tree, Hash, GIN/GiST, BRIN, kısmi, fonksiyonel, 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

Histogram istatistikleri ('ANALYZE TABLE... HISTOGRAM 'I GÜNCELLE' в 8. 0).

10. 3 ClickHouse

Birincil anahtar = 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 } } });

Tanılama için 'ipucu ()' kullanın, 'kapalı sorgu' için izleyin.

10. 5 Elasticsearch/OpenSearch

Anahtar kelime vs metin alanları; Sıralama/toplama için doc_values.
Yığın segmentasyonu: toplamalar - ağır; 'Boyut'u kısıtlayın ve' bileşik 'toplamaları kullanın (sayfalama).
Doğru bir karşılaştırmanın gerekli olduğu analizörleri dahil etmeyin.

11) Rekabetçilik, kilitler ve MVCC

Kısa işlemler; Gereksiz yere 'TEKRARLANABILIR OKUMA' altındaki "uzun" okumalardan kaçının.
Dizin işlemleri ayrıca kilitleri de alır (yazma verimi azaltma).
Çevrimiçi indekslemeyi planlayın: 'CREATE INDEX CONCRETELY' (PG), 'ALGORITHM = INPLACE'/' ONLINE' (MySQL).
Bir saat/ID için kuyruğa ekler - dizinin "sıcak sayfaları"; Anahtarı dağıtın (UUIDv7/tuz).

12) Gözlemlenebilirlik ve SLO

• 'db _ query _ latency _ ms' (P50/P95/P99) sorgu adına göre.
• 'rows _ examined', 'rows _ returned', 'buffer _ hit _ ratio'.
• 'deadlocks', 'lock _ wait _ ms', 'temp _ sort _ disk _ usage'.
• Planların 'Seq Scan'ile paylaşılması' Index Scan 'bekleniyordu.
• DBMS'nin sürümünü/parametrelerini değiştirirken regresyon uyarıları.

• Yavaş sorgu günlüğünü bir eşikle etkinleştirin (örneğin, 200 ms).
• Sorguların açıklıklarla korelasyonu (trace_id).
• Sorunlu sorgu planlarını kaldırın ve geriye dönük olarak nesne depolama alanına kaydedin.

• 'LIMIT <= 50've sıcak kiracı ile' <= 150 ms 'P95 okuyun.
• 1000 satıra kadar gruplarla '<= 200 ms' P95 kayıtları.

13) Güvenlik ve çoklu kiracılık

Erişim kontrol alanlarındaki dizinler ('tenant _ id', 'owner _ id') gereklidir.
Politikalar (RLS/ABAC) ön filtre olmalıdır; Aksi takdirde, optimize edici yanlış planlar.
Hassas alanları açık metinde dizine eklemeyin; Hash/token kullanın.

14) Anti-desenler

Arama imleci alternatifi olmadan derin 'OFFSET'.
"Herkes için bir dizin" - bellek aşırı yüklenmesi ve yazma yolu.
Kritik yollarda 'SEÇ'.
Fonksiyon indeksi olmayan 'WHERE'de sütun üstündeki fonksiyonlar.
Eski istatistikler nedeniyle dengesiz planlar.
Sabit bir düzen beklerken 'ORDER BY' eksik.
Endeksler uğruna endeksler: Pahalı yazma/destek nedeniyle ROI <0.

15) Uygulama kontrol listesi

1. QPS ve zamana göre en iyi N istekleri - 3-5 aday seçin.
2. Planları kaldır 'ANALYZE AÇIKLA', kontrol kardinalite vs gerçek.
3. Tasarım indeksleri: alan düzeni, INCLUDE/kısmi/işlevsel.
4. Büyük tablolar için bölümleme uygulayın (geçici/kiracı anahtarları).
5. Sorguların üzerine yaz: 'SELECT' öğesini kaldır, satır içi basit CTE'ler, kümeyi kısıtla.
6. Butching ve arama pagination etkinleştirin.
7. Önbelleği yapılandır: L1/L2, olaylara göre engellilik.
8. Planların ve yavaş kayıtların izlenmesini, regresyonlar için uyarıları tanıtın.
9. Gerçek veri dağıtımı ile yük testleri gerçekleştirin.
10. Geliştirme yönergelerini güncelleyin (ORM ipuçları, indeksleme, sınırlar).

16) Örneklerden önce/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 ve API protokolleri

N + 1'den kaçının: açgözlü örnekler ('includes', 'JOIN FETCH', 'preload').
Açık alan projeksiyonları, imleç ile paginate.
gRPC/REST: limit 'page _ size', fix 'sort', opak belirteçleri kullanın.
Önbellek planı: parametrelendirme kullanın; Çağrı başına "benzersiz" SQL üretmeyin.

18) Göçler ve İşlemler

Dizinleri çevrimiçi olarak ekleyin ve INVISIBLE/CONCURRENT olarak işaretleyin, planları test edin, sonra değiştirin.
Dizin revizyonları - düzenli sıhhi temizlik: kopyalar, kullanılmayan, eski özellikler için "ölü".
Parti rotasyon planı (eski bırak) ve 'VAKUM/OPTIMIZE' programı.

19) Özet

Sorgu optimizasyonu sistem mühendisliğidir: doğru anahtarlar ve indeksler, düzgün planlar, düşünceli bölümleme ve parçalama, sorgularda ve ORM'de disiplin, önbelleğe alma ve gözlemlenebilirlik. Açıklanan kalıpları izleyerek, veri büyümesine ve yüküne karşı dayanıklı, hızlı, öngörülebilir ve ekonomik bir sistem elde edersiniz.