SQL İndeksleme ve EXPLAIN

Sorgun yavaş ve veritabanı tabloyu baştan sona okuyor. İlk akla gelen “indeks yok” olur, ama iki ihtimal daha var ve çözümleri farklı.

İndeks ne yapar, ne yapmaz?

Bir indeks sorguyu hızlandırmaz. Okunması gereken sayfa sayısını azaltır.

Milyon satırlık bir tablo 10.000 sayfaya yayılmışsa, indekssiz her sorgu 10.000 sayfa okur. B-Tree ise sıralı bir yapıdır: her seviye aralığı yüzlerce kat daraltır, bu yüzden hedefe 3-4 sayfa okuyarak ulaşılır.

kök           [A .......................... Z]     1 sayfa
iç düğüm          [H ........ M]                   1 sayfa
yaprak                [Izmir]  → satırın yeri      1 sayfa

Kendin gör

SELECT id, total, created_at
  FROM orders
 WHERE customer_id = 4711;

CREATE INDEX ON orders (customer_id);
100.000 satır · 1.000 heap sayfası

Sırayla dene — her adım farklı bir dersi gösteriyor:

1. (customer_id) + customer_id = 4711. İndeks seçiliyor. 1000 sayfa yerine ~23 sayfa.
2. Tablo boyutunu 500 bine çıkar. Sayfa sayısı neredeyse hiç değişmiyor — ağacın derinliği aynı kaldı. İndeksin asıl gücü bu.
3. İndeksi (status, customer_id) yap, koşulu customer_id bırak. Seq Scan. İndeks duruyor ama kullanılamıyor.
4. Koşulu status = 'PENDING' yap. Yine Seq Scan — ama bu sefer sebep farklı: indeks kullanılabilir, sadece çok satır eşliyor.
5. “Yalnızca indeksteki sütunlar” anahtarını aç. Aynı sorgu artık Index Only Scan.

İndeks neden kullanılmaz? Üç sebep

Dizin alfabetiktir: ilk sütun kuralı

Bileşik bir indeks, sütunları soldan sağa sıralar. Önce status’a göre sıralı, her status içinde customer_id’ye göre.

1
CREATE INDEX ON orders (status, customer_id);
2

3
WHERE status = 'PENDING' AND customer_id = 4711   -- ✅ seek edilir
4
WHERE status = 'PENDING'                          -- ✅ ilk sütun var
5
WHERE customer_id = 4711                          -- ❌ ağaçta inilecek yol yok

Soyada göre sıralı bir telefon rehberinde “adı Ahmet olanları” bulmak için rehberi baştan sona okumak gerekir.

Sütun sırasını nasıl seçersin.

• Eşitlikle sorgulananlar önce, aralıkla sorgulananlar sonra. Bir aralık koşulundan (>, BETWEEN, LIKE 'x%') sonraki sütunlar artık seek için kullanılamaz.
• En seçici sütun öne. customer_id binlerce farklı değer alır, status beş. Öne customer_id gelir.
• Sorgularını say, sütunlarını değil. İndeks tasarımı tablo şemasından değil, gerçekten çalışan sorgulardan çıkar.

Seçicilik?Terimin tanımını açseçicilikBir koşulun satırların ne kadarını elediği. Sadece birkaç farklı değer alan bir sütunda indeks çoğu zaman hiç kullanılmaz.Tablonun beşte birini okumak, indeksten gidip her satır için tabloya dönmekten ucuzdur — planlayıcı bu hesabı yapar ve indeksi reddeder.Sözlükte gör → — indeksin işe yaramadığı yer. Simülatörde 4. adım tam olarak bunu gösteriyor: status = 'PENDING' koşulu satırların beşte birini eşliyor.

Her sayfada zaten birkaç eşleşme var; indeksten gitmek tüm sayfaları okumak + bir de indeksi okumak demek. Planlayıcı haklı olarak tam taramayı seçiyor.

Az sayıda farklı değer alan bir sütunu (durum, aktif/pasif) tek başına indekslemek işe yaramaz. Seçici bir sütunun arkasına eklenirse işe yarar: (customer_id, status).

Covering index?Terimin tanımını açcovering indexSorgunun ihtiyaç duyduğu tüm sütunları içeren indeks. Veritabanı tabloya hiç gitmeden cevabı indeksten okur — index-only scan.Sözlükte gör →. Simülatörde 5. adımda ne değişti: sorgunun istediği tüm sütunlar indekste olduğu için heap’e hiç gidilmedi.

1
-- indeks customer_id ve status tutuyor, sorgu başka sütun istemiyor
2
SELECT customer_id, status FROM orders WHERE status = 'PENDING';
3
--> Index Only Scan

Normal bir Index Scan iki adımdır: indeks satırın yerini söyler, sonra satır heap’ten okunur. Covering index ikinci adımı kaldırır; bedeli daha büyük bir indeks ve daha yavaş yazmalardır.

Aşağıdaki indeksler bir bankanın hesap hareketleri ekranından ve bekleyen EFT kuyruğundan çıkarıldı.

1
-- Statement screen: one account, a date range, newest first.
2
-- Equality column first, range column second; the columns the screen shows are
3
-- carried in the index so the heap is never read (Index Only Scan).
4
CREATE INDEX idx_movement_account_booked
5
    ON account_movement (account_id, booked_at DESC, id DESC)
6
    INCLUDE (amount, currency, description);
7

8
-- Outgoing EFT queue: only a tiny share of rows is ever PENDING, so index just those.
9
-- An index on status alone would match too many rows to be used for the other values.
10
CREATE INDEX idx_eft_pending
11
    ON outgoing_eft (created_at)
12
    WHERE status = 'PENDING';
13

14
-- On a large live table these run as CREATE INDEX CONCURRENTLY, outside a
15
-- transaction, so writes are not blocked while the index builds.

1
interface MovementRepository extends Repository<AccountMovement, Long> {
2

3
    // Keyset pagination: "older than the last row I showed", not OFFSET, so page 40
4
    // costs the same as page 1. booked_at ties are broken by id.
5
    @Query(value = """
6
            SELECT id, booked_at, amount, currency, description
7
              FROM account_movement
8
             WHERE account_id = :accountId
9
               AND booked_at >= :from
10
               AND (booked_at, id) < (:beforeBookedAt, :beforeId)
11
             ORDER BY booked_at DESC, id DESC
12
             LIMIT :pageSize
13
            """, nativeQuery = true)
14
    List<MovementRow> page(long accountId, Instant from, Instant beforeBookedAt, long beforeId, int pageSize);
15

16
    // Matches the partial index: the WHERE clause repeats its predicate exactly.
17
    @Query(value = """
18
            SELECT id FROM outgoing_eft
19
             WHERE status = 'PENDING'
20
             ORDER BY created_at
21
             LIMIT :batchSize
22
            """, nativeQuery = true)
23
    List<Long> nextPendingEfts(int batchSize);
24
}

EXPLAIN (ANALYZE, BUFFERS) on the statement page query, expected shape:

Limit
  -> Index Only Scan using idx_movement_account_booked on account_movement
       Index Cond: ((account_id = $1) AND (booked_at >= $2))
       Filter: (ROW(booked_at, id) < ROW($3, $4))
       Heap Fetches: 0            <- every column came from the index

What to check, not what to expect:
- estimated rows vs actual rows: a large gap means stale statistics, run ANALYZE
- Heap Fetches above 0 on a busy table: the visibility map is behind, VACUUM
- a Sort node above the scan: the ORDER BY no longer matches the index order

1
-- Counter-example: the same columns, the other way round.
2
CREATE INDEX idx_movement_booked_account ON account_movement (booked_at, account_id);
3

4
-- The range column leads, so the tree can only be entered by date: every account's
5
-- movements in that range are walked to find one account's rows.
6
SELECT * FROM account_movement
7
 WHERE account_id = 42 AND booked_at >= now() - interval '30 days';
8

9
-- And an index on a column with a handful of values rarely helps on its own:
10
CREATE INDEX idx_eft_status ON outgoing_eft (status);   -- 'SENT' is almost every row

Tuzaklar ve EXPLAIN okumak

Index Scan using idx_orders_customer on orders
  (cost=0.42..8.45 rows=20 width=48)
  (actual time=0.03..0.09 rows=20 loops=1)

• rows=20 (tahmin) ile rows=20 (gerçek) arasındaki fark. Büyük sapma, istatistiklerin bayatladığını söyler — ANALYZE çalıştırmak gerekir. Kötü plan seçimlerinin en yaygın sebebi budur.
• loops=1. Büyük bir sayı, iç içe döngüde tekrar tekrar çalışan bir plan demektir.
• Plan adı değil, satır sayısı. Seq Scan görmek kötü değildir; 20 satır dönerken Seq Scan görmek kötüdür.

EXPLAIN sorguyu çalıştırmaz, yalnızca planı gösterir. Gerçek süreleri görmek için EXPLAIN ANALYZE gerekir — ve bu sorguyu gerçekten çalıştırır.

Kendini sına