SQL türüne özgü optimizasyon: parametre koklama, EXPLAIN ANALYZE, bayt maliyeti

Bu dersin sınırı

Bu uygulamanın motoru SQLite. SQL Server, MySQL, Oracle ve BigQuery yazımları taklit edilir ama planları üretilemez. Aşağıdaki bilgiler bu ürünlerin resmî belgelerine dayanır, burada çalıştırılmamıştır. Bu yüzden o ürünler için plan çıktısı örneği yazmıyoruz. PostgreSQL çıktıları gerçek motordan (PGlite) gelir.

SQL Server: parametre koklama (parameter sniffing)

Belge bunu şöyle tanımlar: motor derleme sırasında parametrenin o anki değerine bakar ve iyileştiriciye verir. Saklı yordamın planı ilk çalıştırmada derlenir ve önbellekte kaldığı sürece yeniden kullanılır. Aynı plan başka değerler için kötü olabilir.

Senaryo: Ilgaz Bank'ta bir şube diğerlerinden çok büyük. Şube başına müşteri sayısına bak:

SELECT branch_id, COUNT(*) AS musteri
FROM bank_customers
GROUP BY branch_id
ORDER BY musteri DESC;

Yordam önce küçük şubeyle çağrılırsa plan az satıra göre kurulur. Sonra büyük şube aynı planı kullanır ve yavaşlar. Tersi de olabilir. "Dün hızlıydı, bugün yavaş, kod değişmedi" denince akla gelecek ilk sebeplerden biridir.

CREATE OR ALTER PROCEDURE dbo.usp_sube_hareket @sube INT
AS
SELECT c.customer_id, COUNT(*) AS islem
FROM dbo.bank_customers c
JOIN dbo.bank_accounts a ON a.customer_id = c.customer_id
JOIN dbo.bank_txns t ON t.account_id = a.account_id
WHERE c.branch_id = @sube
GROUP BY c.customer_id;

Belge sorgu ipuçlarını (hint) deneyimli geliştirici ve yöneticiler için son çare olarak önerir. Önce indeks ve yazımı düzelt.

Mantıksal okuma

SET STATISTICS IO, TIME ON;
SELECT COUNT(*) FROM dbo.orders WHERE order_date >= '2024-01-01';

Mesajlar sekmesinde her tablo için okuma sayıları gelir. Değişikliğin etkisini logical reads ile karşılaştır. Süre sunucu yüküne göre oynar.

PostgreSQL

• EXPLAIN yalnız planı gösterir. EXPLAIN ANALYZE sorguyu çalıştırır ve gerçek satırları ekler. Değişiklik yapan sorguda BEGIN ... ROLLBACK kullan.
• BUFFERS okunan sayfaları gösterir. Sayılar birikimlidir: üst düğüm altındakileri içerir. PostgreSQL 18 belgesine göre ANALYZE bunu kendiliğinden açar. Eski sürümlerde açıkça yaz.
• Toplu yüklemeden sonra ANALYZE çalıştır.

Tek hesabın hareketleri, bank_txns üzerinde hazır indeksle:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT a.customer_id, t.amount FROM bank_accounts a JOIN bank_txns t ON t.account_id = a.account_id WHERE a.account_id = 100019;

Gerçek çıktı

Nested Loop  (cost=4.94..153.18 rows=49 width=10) (actual rows=72 loops=1)
  Buffers: shared hit=71
  ->  Index Scan using bank_accounts_pkey on bank_accounts a  (cost=0.28..8.29 rows=1 width=8) (actual rows=1 loops=1)
        Index Cond: (account_id = 100019)
        Buffers: shared hit=3
  ->  Bitmap Heap Scan on bank_txns t  (cost=4.67..144.40 rows=49 width=10) (actual rows=72 loops=1)
        Recheck Cond: (account_id = 100019)
        Heap Blocks: exact=66
        Buffers: shared hit=68
        ->  Bitmap Index Scan on bank_txns_account_id_idx  (cost=0.00..4.66 rows=49 width=0) (actual rows=72 loops=1)
              Index Cond: (account_id = 100019)
              Buffers: shared hit=2

MySQL

EXPLAIN ANALYZE 8.0.18'den beri vardır ve sorguyu gerçekten çalıştırır. Çıktı ağaç biçimindedir ve her düğümde tahmini maliyet, tahmini satır, gerçek süre, gerçek satır ve döngü sayısı yazar. Düz EXPLAIN tablosunda type sütununa bak: ALL tam taramadır. Tahmin ve gerçek çok ayrışıyorsa belge ANALYZE TABLE önerir.

Oracle

EXPLAIN PLAN sorguyu çalıştırmaz, seçilecek planı tahmin eder. Belge bu planın gerçek yürütmedekinden farklı olabileceğini söyler. Gerçekte kullanılan planı DBMS_XPLAN.DISPLAY_CURSOR gösterir. Çalışma zamanı istatistikleri varsa E-Rows ve A-Rows sütunlarını karşılaştır.

EXPLAIN PLAN FOR SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM orders WHERE customer_id = 42;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

BigQuery: süre değil bayt

İsteğe bağlı fiyatlandırmada okunan bayt faturayı belirler. Klasik indeks yerine bölümleme ve kümeleme vardır. Belgelerde şunlar yazar:

• Kuru çalıştırma (dry run) sorguyu çalıştırmadan işlenecek baytı söyler.
• Kümelenmemiş tabloda LIMIT okunan baytı azaltmaz. Kümelenmiş tabloda azaltabilir.
• SELECT * yerine gereken sütunları seç. SELECT * EXCEPT istemediğin sütunları düşürür.
• Maximum bytes billed ayarı tahmin sınırı aşarsa sorguyu ücret almadan başarısız yapar.
• Bölüm eleme için bölüm sütununu sabit ifadeyle süz. Alt sorgu sonucuyla karşılaştırmak ve OR ile başka sütuna bağlamak eleme yapmaz. Bölüm filtresini zorunlu kılan ayar da vardır.

-- Kuru çalıştırma: sorgu çalışmaz, işlenecek bayt yazılır
bq query --use_legacy_sql=false --dry_run \
  'SELECT user_id FROM analytics.events WHERE event_date = "2024-12-01"'

Karşılaştırma