Ana içeriğe geç
Kesit SQL
İleri · Sorgu Optimizasyonu

Seçicilik (selectivity) ve istatistikler: motor neden o planı seçti?

Seçicilik hesabı, çarpık dağılım, indeksin işe yaramadığı değerler, ANALYZE, sqlite_stat1, bayat istatistik.

Uygulamada aç, 8 alıştırmayı çözÜcretsiz, hesap gerekmez, kurulum yok.

Bu sayfa dersin okuma sürümüdür. Metin bir kutuyu çalıştırmanı ya da adıma geçmeni söylüyorsa dersi uygulamada aç.

Senaryo: aynı sorgu, iki farklı değer

txn_type sütununa indeks kuruldu. Faiz işlemleri raporu çok hızlandı. Kart harcaması raporu ise hiç hızlanmadı. Aynı sorgu, aynı indeks. Fark değerin seçiciliğinde.

Seçicilik nedir?

Koşulun tabloyu ne kadar daralttığıdır: eşleşen satır / toplam satır. Oran küçükse seçicilik yüksektir. Sütunun kaç farklı değeri olduğu kardinalite (cardinality) adını alır.

SELECT txn_type, COUNT(*) AS adet,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pay
FROM bank_txns
GROUP BY txn_type
ORDER BY adet DESC;

Veri çarpık: kart harcaması tablonun yarısına yakın, faiz ise çok küçük bir pay. Bu yüzden "bu sütunun seçiciliği" değil, bu değerin seçiciliği diye düşünmek gerekir.

Gerçek çıktı: nadir ve yaygın değer

PostgreSQL çıktılarıBu derslerdeki PostgreSQL planları elle yazılmadı. Aynı veriyle PostgreSQL 16.4 üzerinde (PGlite, tarayıcıda çalışan WASM sürümü) üretildi. VACUUM ANALYZE çalıştırıldı, planlama satırları kısalık için çıkarıldı. Sayılar kendi sunucunda farklı olur; okuma biçimi aynıdır.

Tablonun tamamını okumak (Seq Scan) bu veride 391 sayfa tutuyor. Nadir değer:

CREATE INDEX ix_tip ON bank_txns (txn_type);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT txn_id, amount FROM bank_txns WHERE txn_type = 'faiz';

Gerçek çıktı

Bitmap Heap Scan on bank_txns  (cost=17.82..424.20 rows=1230 width=10) (actual rows=1262 loops=1)
  Recheck Cond: ((txn_type)::text = 'faiz'::text)
  Heap Blocks: exact=27
  Buffers: shared hit=30
  ->  Bitmap Index Scan on ix_tip  (cost=0.00..17.52 rows=1230 width=0) (actual rows=1262 loops=1)
        Index Cond: ((txn_type)::text = 'faiz'::text)
        Buffers: shared hit=3

Yaygın değer:

CREATE INDEX ix_tip ON bank_txns (txn_type);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT txn_id, amount FROM bank_txns WHERE txn_type = 'kart_harcama';

Gerçek çıktı

Bitmap Heap Scan on bank_txns  (cost=234.88..881.67 rows=20463 width=10) (actual rows=20520 loops=1)
  Recheck Cond: ((txn_type)::text = 'kart_harcama'::text)
  Heap Blocks: exact=390
  Buffers: shared hit=410
  ->  Bitmap Index Scan on ix_tip  (cost=0.00..229.76 rows=20463 width=0) (actual rows=20520 loops=1)
        Index Cond: ((txn_type)::text = 'kart_harcama'::text)
        Buffers: shared hit=20

Yaygın değerde indeks kullanılıyor ama 410 sayfa okunuyor. Bu sayı tablonun tamamını okumaktan fazladır, çünkü üstüne indeks sayfaları biniyor. Satırların yarısı isteniyorsa neredeyse her sayfaya gidilir. Bu durumda motorun indeksi atlayıp tam tarama seçmesi hata değildir. "İndeks var ama kullanmıyor" şikâyetinin önemli bir kısmı budur.

İstatistikler

Motor kaç satır çıkacağını çalıştırmadan önce istatistiklerden tahmin eder. Plan seçimi bu tahmine dayanır.

SQL türüİstatistik toplamaBakma yeri
SQLiteANALYZE; önerilen yol PRAGMA optimizesqlite_stat1
PostgreSQLANALYZE, otomatik vacuum sürecipg_stats: n_distinct, most_common_vals, histogram_bounds
SQL ServerVarsayılan olarak otomatik, elle UPDATE STATISTICSDBCC SHOW_STATISTICS
MySQLANALYZE TABLEinformation_schema.STATISTICS
OracleDBMS_STATS.GATHER_TABLE_STATSALL_TAB_STATISTICS, ALL_TAB_COL_STATISTICS
BigQueryYönetilir, indeks seçimi yok. Taranan baytı bölüm ve kümeleme belirlerYürütme ayrıntıları

SQLite'ta istatistik tablosunu kendin okuyabilirsin. stat sütunundaki ilk sayı toplam satır, ikincisi indeksin ilk sütununda bir değere düşen ortalama satırdır:

CREATE INDEX ix_tip ON bank_txns (txn_type);
CREATE INDEX ix_hesap_zaman ON bank_txns (account_id, txn_time);
ANALYZE;
SELECT tbl, idx, stat FROM sqlite_stat1 WHERE tbl = 'bank_txns';

Bayat istatistik (stale statistics)

Bir batch işi tablonun yapısını değiştirdi ama istatistik yenilenmedi. Aşağıda kart harcamalarının hepsi tek hesaba taşındı (yalnız bu deney için). ANALYZE çalışmadan önce motor 72 satır bekliyor, 20.562 satır geliyor, üstelik indeks yolunu seçiyor:

Aggregate  (cost=214.69..214.70 rows=1 width=8) (actual rows=1 loops=1)
  Buffers: shared hit=291
  ->  Bitmap Heap Scan on tx_kopya  (cost=4.85..214.51 rows=72 width=0) (actual rows=20562 loops=1)
        Recheck Cond: (account_id = 100019)
        Heap Blocks: exact=273
        Buffers: shared hit=291
        ->  Bitmap Index Scan on ix_kopya_hesap  (cost=0.00..4.83 rows=72 width=0) (actual rows=20592 loops=1)
              Index Cond: (account_id = 100019)
              Buffers: shared hit=18

ANALYZE sonrası tahmin neredeyse tam oturuyor ve plan Seq Scan oluyor:

Aggregate  (cost=1233.81..1233.82 rows=1 width=8) (actual rows=1 loops=1)
  Buffers: shared hit=654
  ->  Seq Scan on tx_kopya  (cost=0.00..1182.55 rows=20502 width=0) (actual rows=20562 loops=1)
        Filter: (account_id = 100019)
        Rows Removed by Filter: 21722
        Buffers: shared hit=654

Dikkat et: bu çıktıda taze planın okuduğu sayfa sayısı daha yüksek (654 ve 291). Toplu UPDATE tabloda ölü satır bıraktı ve bitmap yolu yalnız gerekli sayfaları okudu. Plan tahmine göre seçilir, doğru tahmin her zaman daha az sayfa demek değildir. Tek sorguyla hüküm verme: asıl risk, yanlış tahminin iç içe döngüye ya da küçük bellek ayrılmasına dönüştüğü birleştirmeli sorgulardır. Batch sonrasında ANALYZE iyi bir alışkanlıktır.

Parametreyle ilişkisi

Aynı sorgunun iki değeri farklı planı hak ediyorsa ve motor planı bir kez seçip saklıyorsa sorun çıkar. SQL Server'da buna parametre koklama (parameter sniffing) denir. Ayrıntısı SQL türüne özgü optimizasyon dersinde.

Alıştırmalara geç (8)