Recursive CTE ile Hiyerarşik Veri Sorgulama

Hiyerarşik verilerle uğraşmak, her veritabanı yöneticisinin er ya da geç karşılaştığı klasik bir sorundur. Bir şirketin organizasyon şeması, bir ürün kategorisi ağacı, bir dosya sistemi yapısı veya bir yorum-cevap zinciri düşünün. Bu tür veriler, düz tablo yapısıyla sorgulanmaya çalışıldığında işler hızla çığırından çıkar. İşte tam bu noktada Recursive CTE (Common Table Expression) devreye girer ve hayatınızı kurtarır.

MariaDB 10.2 ve MySQL 8.0 ile birlikte gelen bu özellik, özellikle derin hiyerarşileri tek bir SQL sorgusunda çözmenizi sağlar. Bu yazıda gerçek dünya senaryoları üzerinden recursive CTE’yi tüm ayrıntılarıyla ele alacağız.

CTE Nedir, Recursive CTE Nedir?

Önce temel kavramları netleştirelim. CTE (Common Table Expression), bir sorgu içinde geçici olarak tanımladığınız, isimlendirdiğiniz ve o sorgu boyunca tekrar kullanabildiğiniz bir sonuç kümesidir. WITH anahtar kelimesiyle tanımlanır.

-- Basit bir CTE örneği
WITH aktif_kullanicilar AS (
    SELECT id, ad, soyad, email
    FROM kullanicilar
    WHERE durum = 'aktif'
)
SELECT * FROM aktif_kullanicilar WHERE sehir = 'Istanbul';

Recursive CTE ise bu yapının özyinelemeli versiyonudur. Yani sorgu kendi kendini çağırır. İki temel bileşenden oluşur:

• Anchor Member (Çapa Üyesi): Özyinelemenin başladığı ilk sorgu. Hiyerarşinin kök noktasını döndürür.
• Recursive Member (Özyinelemeli Üye): Anchor member’ın sonucunu girdi olarak alıp daha derin seviyeleri sorgulayan kısım. UNION ALL ile anchor’a bağlanır.

Genel yapı şu şekildedir:

WITH RECURSIVE cte_adi AS (
    -- Anchor Member: Başlangıç noktası
    SELECT ...
    FROM tablo
    WHERE baslangic_kosulu

    UNION ALL

    -- Recursive Member: Kendi kendini çağıran kısım
    SELECT t.*
    FROM tablo t
    INNER JOIN cte_adi c ON t.parent_id = c.id
)
SELECT * FROM cte_adi;

Motor, recursive member’da sonuç kalmayıncaya kadar bu işlemi tekrarlar. Sonra tüm sonuçları birleştirip size sunar.

Test Ortamı: Organizasyon Şeması Tablosu

Tüm örnekler için gerçekçi bir senaryo kullanalım. Bir IT şirketinin çalışan hiyerarşisini modelleyelim.

-- Çalışan tablosunu oluştur
CREATE TABLE calisanlar (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    ad          VARCHAR(100) NOT NULL,
    pozisyon    VARCHAR(100) NOT NULL,
    departman   VARCHAR(100),
    maas        DECIMAL(10,2),
    yonetici_id INT NULL,
    FOREIGN KEY (yonetici_id) REFERENCES calisanlar(id)
);

-- Test verilerini ekle
INSERT INTO calisanlar (id, ad, pozisyon, departman, maas, yonetici_id) VALUES
(1,  'Ahmet Yilmaz',    'CEO',                 'Yonetim',    85000, NULL),
(2,  'Zeynep Kaya',     'CTO',                 'Teknoloji',  72000, 1),
(3,  'Murat Demir',     'CFO',                 'Finans',     70000, 1),
(4,  'Elif Sahin',      'IT Muduru',           'Teknoloji',  58000, 2),
(5,  'Can Ozturk',      'Backend Lead',        'Teknoloji',  52000, 4),
(6,  'Selin Arslan',    'Frontend Lead',       'Teknoloji',  50000, 4),
(7,  'Burak Celik',     'Backend Developer',   'Teknoloji',  42000, 5),
(8,  'Ayse Yildiz',     'Backend Developer',   'Teknoloji',  40000, 5),
(9,  'Emre Kurt',       'Frontend Developer',  'Teknoloji',  39000, 6),
(10, 'Deniz Polat',     'Finans Muduru',       'Finans',     55000, 3),
(11, 'Oya Aksoy',       'Muhasebe Uzmani',     'Finans',     38000, 10),
(12, 'Taner Bulut',     'DevOps Engineer',     'Teknoloji',  48000, 4);

Şimdi bu veriyle farklı sorgu senaryolarını inceleyelim.

Senaryo 1: Tüm Hiyerarşiyi Düz Liste Olarak Getirmek

En temel kullanım, bir kişinin altındaki tüm çalışanları bulmaktır. CEO’nun altındaki tüm çalışanları hiyerarşik sırayla getirelim:

WITH RECURSIVE organizasyon AS (
    -- Anchor: CEO'yu başlangıç noktası olarak al
    SELECT
        id,
        ad,
        pozisyon,
        yonetici_id,
        0 AS seviye,
        CAST(ad AS CHAR(1000)) AS hiyerarsik_yol
    FROM calisanlar
    WHERE yonetici_id IS NULL

    UNION ALL

    -- Recursive: Her çalışanın altındakileri bul
    SELECT
        c.id,
        c.ad,
        c.pozisyon,
        c.yonetici_id,
        o.seviye + 1,
        CONCAT(o.hiyerarsik_yol, ' > ', c.ad)
    FROM calisanlar c
    INNER JOIN organizasyon o ON c.yonetici_id = o.id
)
SELECT
    CONCAT(REPEAT('    ', seviye), ad) AS isim_gosterimi,
    pozisyon,
    seviye,
    hiyerarsik_yol
FROM organizasyon
ORDER BY hiyerarsik_yol;

Bu sorguda dikkat etmeniz gereken birkaç nokta var:

• seviye alanı, kişinin hiyerarşideki derinliğini gösterir. CEO 0, CTO 1, IT Müdürü 2 gibi.
• REPEAT(' ', seviye) ile girintili görünüm elde ediyoruz.
• hiyerarsik_yol alanı, kişinin tam hiyerarşik konumunu string olarak tutar. Hem görsel hem de sıralama için kullanışlıdır.
• CAST(ad AS CHAR(1000)) kullanımı önemli. Recursive CTE’de string birleştirme yapacaksanız MariaDB/MySQL bazen tip uyumsuzluğu verebilir. Bu cast bunu engeller.

Senaryo 2: Belirli Bir Çalışanın Üstlerini (Raporlama Zincirini) Bulmak

Bazen yukarı doğru gitmeniz gerekir. Bir çalışanın CEO’ya kadar olan raporlama zincirini bulmak istiyorsunuz:

-- Emre Kurt'un (id=9) raporlama zincirini bul
WITH RECURSIVE raporlama_zinciri AS (
    -- Anchor: Hedef çalışandan başla
    SELECT
        id,
        ad,
        pozisyon,
        yonetici_id,
        0 AS seviye
    FROM calisanlar
    WHERE id = 9

    UNION ALL

    -- Recursive: Bir üst yöneticiye çık
    SELECT
        c.id,
        c.ad,
        c.pozisyon,
        c.yonetici_id,
        r.seviye + 1
    FROM calisanlar c
    INNER JOIN raporlama_zinciri r ON c.id = r.yonetici_id
)
SELECT
    ad,
    pozisyon,
    seviye AS hiyerarsideki_uzaklik
FROM raporlama_zinciri
ORDER BY seviye;

Bu sorgu şöyle okur: “Emre Kurt’tan başla, her adımda yöneticiyi bul, yönetici kalmayana kadar devam et.” Sonuç olarak Emre Kurt, Frontend Lead Selin, IT Müdürü Elif, CTO Zeynep ve CEO Ahmet listesini alırsınız.

Senaryo 3: Belirli Bir Derinliğe Kadar Sorgulama

Bazı durumlarda tüm hiyerarşiyi değil, sadece belirli bir seviyeye kadar olan kısmı isteyebilirsiniz. Örneğin sadece iki seviye aşağısına kadar olan çalışanlar:

WITH RECURSIVE kismi_hiyerarsi AS (
    SELECT
        id,
        ad,
        pozisyon,
        yonetici_id,
        0 AS seviye
    FROM calisanlar
    WHERE id = 2  -- CTO Zeynep'ten başla

    UNION ALL

    SELECT
        c.id,
        c.ad,
        c.pozisyon,
        c.yonetici_id,
        k.seviye + 1
    FROM calisanlar c
    INNER JOIN kismi_hiyerarsi k ON c.yonetici_id = k.id
    WHERE k.seviye < 2  -- Maksimum 2 seviye aşağı in
)
SELECT
    CONCAT(REPEAT('  ', seviye), '|-- ', ad) AS hiyerarsi,
    pozisyon,
    seviye
FROM kismi_hiyerarsi
ORDER BY seviye, ad;

WHERE k.seviye < 2 koşulu özyinelemenin derinliğini kontrol eder. Bu, büyük ve derin hiyerarşilerde performans açısından kritik önem taşır.

Senaryo 4: Hiyerarşik Toplam Hesaplama (Maaş Toplamları)

Recursive CTE’nin gerçek gücü, hiyerarşik agregasyon işlemlerinde ortaya çıkar. Her yöneticinin ekibindeki toplam maaş maliyetini hesaplayalım:

WITH RECURSIVE ekip_yapisi AS (
    SELECT
        id,
        ad,
        pozisyon,
        maas,
        yonetici_id,
        id AS kok_yonetici_id
    FROM calisanlar

    UNION ALL

    SELECT
        c.id,
        c.ad,
        c.pozisyon,
        c.maas,
        c.yonetici_id,
        e.kok_yonetici_id
    FROM calisanlar c
    INNER JOIN ekip_yapisi e ON c.yonetici_id = e.id
    WHERE c.id != e.kok_yonetici_id
),
yonetici_maliyetleri AS (
    SELECT
        kok_yonetici_id,
        SUM(maas) AS toplam_ekip_maliyeti,
        COUNT(*) AS ekip_buyuklugu
    FROM ekip_yapisi
    GROUP BY kok_yonetici_id
)
SELECT
    c.ad,
    c.pozisyon,
    c.maas AS kendi_maasi,
    ym.toplam_ekip_maliyeti,
    ym.ekip_buyuklugu
FROM calisanlar c
JOIN yonetici_maliyetleri ym ON c.id = ym.kok_yonetici_id
ORDER BY ym.toplam_ekip_maliyeti DESC;

Bu sorgu biraz daha karmaşık ama son derece güçlü. Her çalışanı kendi “kök” ID’siyle etiketleyerek, daha sonra bu kök altındaki tüm maaşları toplayabiliyoruz.

Senaryo 5: Ürün Kategorisi Hiyerarşisi

Farklı bir senaryo olarak e-ticaret sistemlerinde sıkça karşılaşılan ürün kategorisi ağacını ele alalım:

CREATE TABLE kategoriler (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    ad          VARCHAR(200) NOT NULL,
    ust_id      INT NULL,
    slug        VARCHAR(200),
    FOREIGN KEY (ust_id) REFERENCES kategoriler(id)
);

INSERT INTO kategoriler VALUES
(1,  'Elektronik',           NULL, 'elektronik'),
(2,  'Bilgisayar',           1,    'bilgisayar'),
(3,  'Telefon',              1,    'telefon'),
(4,  'Laptop',               2,    'laptop'),
(5,  'Masaustu',             2,    'masaustu'),
(6,  'Oyun Bilgisayari',     2,    'oyun-bilgisayari'),
(7,  'Akilli Telefon',       3,    'akilli-telefon'),
(8,  'Klasik Telefon',       3,    'klasik-telefon'),
(9,  'Gaming Laptop',        4,    'gaming-laptop'),
(10, 'Is Laptopu',           4,    'is-laptopu');

-- Tam kategori yolu oluşturma sorgusu
WITH RECURSIVE kategori_agaci AS (
    SELECT
        id,
        ad,
        ust_id,
        slug,
        0 AS derinlik,
        CAST(slug AS CHAR(2000)) AS tam_yol,
        CAST(ad AS CHAR(2000)) AS breadcrumb
    FROM kategoriler
    WHERE ust_id IS NULL

    UNION ALL

    SELECT
        k.id,
        k.ad,
        k.ust_id,
        k.slug,
        ka.derinlik + 1,
        CONCAT(ka.tam_yol, '/', k.slug),
        CONCAT(ka.breadcrumb, ' > ', k.ad)
    FROM kategoriler k
    INNER JOIN kategori_agaci ka ON k.ust_id = ka.id
)
SELECT
    id,
    CONCAT(REPEAT('-- ', derinlik), ad) AS kategori_adi,
    tam_yol AS url_yolu,
    breadcrumb,
    derinlik
FROM kategori_agaci
ORDER BY tam_yol;

Bu sorgunun çıktısı size hem URL yapısı hem de breadcrumb navigasyonu için hazır veri sağlar. Gerçek bir e-ticaret projesinde bu sorguyu önbelleğe alarak kullanabilirsiniz.

Senaryo 6: Döngü Tespiti ve Güvenlik Önlemleri

Recursive CTE kullanırken en büyük risk sonsuz döngüdür. Eğer verinizde bir çember varsa (A, B’yi yönetiyor; B de A’yı yönetiyor gibi bir veri bütünlüğü hatası) sorgunuz sonsuza kadar çalışabilir.

MariaDB ve MySQL bunun için max_recursive_iterations değişkenini sunar:

-- Varsayılan değeri kontrol et
SHOW VARIABLES LIKE 'max_recursive_iterations';

-- Oturum bazında güvenli bir limit belirle
SET SESSION max_recursive_iterations = 100;

-- Döngü tespiti için ziyaret edilen ID'leri takip eden sorgu
WITH RECURSIVE guvenli_hiyerarsi AS (
    SELECT
        id,
        ad,
        yonetici_id,
        CAST(id AS CHAR(1000)) AS ziyaret_edilen,
        0 AS derinlik
    FROM calisanlar
    WHERE yonetici_id IS NULL

    UNION ALL

    SELECT
        c.id,
        c.ad,
        c.yonetici_id,
        CONCAT(g.ziyaret_edilen, ',', c.id),
        g.derinlik + 1
    FROM calisanlar c
    INNER JOIN guvenli_hiyerarsi g ON c.yonetici_id = g.id
    WHERE FIND_IN_SET(c.id, g.ziyaret_edilen) = 0  -- Daha önce görüldüyse ekleme
      AND g.derinlik < 50  -- Maksimum derinlik limiti
)
SELECT * FROM guvenli_hiyerarsi;

FIND_IN_SET ile ziyaret edilen ID’leri string olarak saklayıp kontrol ediyoruz. Bu yöntem küçük ve orta büyüklükteki hiyerarşiler için oldukça etkilidir.

Senaryo 7: Closure Table ile Recursive CTE Karşılaştırması

Büyük sistemlerde hiyerarşik veriyi modellemek için Closure Table deseni de kullanılır. Recursive CTE ile birlikte kullanıldığında son derece güçlü sonuçlar verir:

-- Closure table: Her ata-torun ilişkisini açıkça sakla
CREATE TABLE hiyerarsi_closure (
    ata_id      INT NOT NULL,
    torun_id    INT NOT NULL,
    derinlik    INT NOT NULL DEFAULT 0,
    PRIMARY KEY (ata_id, torun_id),
    FOREIGN KEY (ata_id) REFERENCES calisanlar(id),
    FOREIGN KEY (torun_id) REFERENCES calisanlar(id)
);

-- Recursive CTE ile closure table'ı otomatik doldur
WITH RECURSIVE closure_builder AS (
    SELECT id AS ata_id, id AS torun_id, 0 AS derinlik
    FROM calisanlar

    UNION ALL

    SELECT cb.ata_id, c.id AS torun_id, cb.derinlik + 1
    FROM calisanlar c
    INNER JOIN closure_builder cb ON c.yonetici_id = cb.torun_id
)
INSERT INTO hiyerarsi_closure (ata_id, torun_id, derinlik)
SELECT DISTINCT ata_id, torun_id, derinlik FROM closure_builder;

-- Artık closure table ile hızlı sorgulama
-- CTO'nun tüm torunlarını bul (tek JOIN ile!)
SELECT c.ad, c.pozisyon, hc.derinlik
FROM hiyerarsi_closure hc
JOIN calisanlar c ON c.id = hc.torun_id
WHERE hc.ata_id = 2
  AND hc.torun_id != 2
ORDER BY hc.derinlik, c.ad;

Closure table’ın avantajı okuma sorgularının çok hızlı olmasıdır. Dezavantajı ise her yapısal değişiklikte (yeni çalışan, yönetici değişikliği) tablonun güncellenmesi gerektiğidir. Recursive CTE ile periyodik olarak bu tabloyu yenileyebilirsiniz.

Performans İpuçları

Recursive CTE kullanırken dikkat etmeniz gereken birkaç önemli nokta var:

• max_recursive_iterations: Bu değişkeni iş ihtiyacınıza göre makul bir değere çekin. 1000 gibi bir değer çoğu senaryo için yeterlidir.
• İndeksleme: parent_id veya benzeri yabancı anahtar sütunlarını mutlaka indeksleyin. Recursive üye her iterasyonda bu sütun üzerinden join yapar.

-- Performans için kritik indeksler
CREATE INDEX idx_calisanlar_yonetici ON calisanlar(yonetici_id);
CREATE INDEX idx_kategoriler_ust ON kategoriler(ust_id);

-- EXPLAIN ile sorgu planını incele
EXPLAIN WITH RECURSIVE org AS (
    SELECT id, ad, yonetici_id, 0 AS seviye
    FROM calisanlar WHERE yonetici_id IS NULL
    UNION ALL
    SELECT c.id, c.ad, c.yonetici_id, o.seviye + 1
    FROM calisanlar c JOIN org o ON c.yonetici_id = o.id
)
SELECT * FROM org;

• Materializasyon: MariaDB 10.3+ sürümlerinde WITH RECURSIVE sorguları materialized CTE olarak değerlendirilebilir. EXPLAIN çıktısına bakarak bunu kontrol edin.
• Gereksiz sütun çekmeyin: Recursive üyede sadece ihtiyaç duyduğunuz sütunları seçin. Her iterasyonda çekilen veri, bellek kullanımını doğrudan etkiler.
• UNION ALL vs UNION: Recursive CTE’de UNION ALL kullanın, UNION kullanmayın. UNION her seferinde duplicate kontrolü yapar ve bu büyük hiyerarşilerde ciddi performans kaybına yol açar.

MariaDB vs MySQL Farklılıkları

Her iki veritabanında da recursive CTE desteklenir ancak bazı küçük farklılıklar mevcuttur:

• MariaDB 10.2+: WITH RECURSIVE tam destek. max_recursive_iterations sistem değişkeni ile kontrol edilir.
• MySQL 8.0+: WITH RECURSIVE tam destek. cte_max_recursion_depth değişkeni kullanılır. Eski MySQL sürümlerinde bu özellik yoktur.

-- MariaDB için
SET SESSION max_recursive_iterations = 500;

-- MySQL için
SET SESSION cte_max_recursion_depth = 500;

-- Versiyonu kontrol et
SELECT VERSION();

Eğer daha eski MySQL sürümleriyle çalışmak zorundaysanız, recursive CTE yerine stored procedure ile döngüsel sorgulama veya uygulama katmanında hiyerarşi çözümlemesi yapmanız gerekir.

Stored Procedure ile Recursive CTE Entegrasyonu

Sık kullanılan hiyerarşik sorguları stored procedure içine almak, kodun yeniden kullanılabilirliğini artırır:

DELIMITER //

CREATE PROCEDURE ekip_raporunu_getir(IN baslangic_id INT, IN max_derinlik INT)
BEGIN
    WITH RECURSIVE ekip AS (
        SELECT
            id,
            ad,
            pozisyon,
            maas,
            yonetici_id,
            0 AS seviye
        FROM calisanlar
        WHERE id = baslangic_id

        UNION ALL

        SELECT
            c.id,
            c.ad,
            c.pozisyon,
            c.maas,
            c.yonetici_id,
            e.seviye + 1
        FROM calisanlar c
        INNER JOIN ekip e ON c.yonetici_id = e.id
        WHERE e.seviye < max_derinlik
    )
    SELECT
        CONCAT(REPEAT('  ', seviye), ad) AS calisan,
        pozisyon,
        FORMAT(maas, 2, 'tr_TR') AS maas,
        seviye
    FROM ekip
    ORDER BY seviye, ad;
END //

DELIMITER ;

-- Kullanımı
CALL ekip_raporunu_getir(2, 3);  -- CTO'nun 3 seviye altına kadar

Bu yapıyla uygulama kodunuzdan sadece procedure ismini çağırarak farklı yöneticiler ve derinlik seviyeleri için raporlar alabilirsiniz.

Sonuç

Recursive CTE, hiyerarşik veriyle çalışırken kod karmaşıklığını ciddi ölçüde azaltan ve veritabanı katmanında çözüm üretmenizi sağlayan güçlü bir araçtır. Uygulama tarafında döngülerle çözmeye çalıştığınız şeyi tek bir SQL sorgusuna sığdırabilirsiniz.

Özetlemek gerekirse:

• Hiyerarşi aşağı sorgulama için anchor’ı kök node’dan başlatıp alt düğümlere doğru ilerleyin.
• Hiyerarşi yukarı sorgulama için anchor’ı yaprak node’dan başlatıp parent’a doğru çıkın.
• Döngü koruması için mutlaka derinlik limiti veya ziyaret edilen node takibi ekleyin.
• Performans için parent_id sütunlarını indeksleyin ve gereksiz sütun çekmeyin.
• Sürüm kontrolü yapın. MySQL 8.0 altındaki sürümlerde bu özellik çalışmaz.

Organizasyon şeması, kategori ağacı, yorum zinciri, dosya sistemi, ürün bileşen listeleri gibi hiyerarşik veri modellerinin olduğu her yerde recursive CTE’yi aklınızın bir köşesinde tutun. Bir kez alıştıktan sonra bu sorgular olmadan nasıl çalıştığınızı merak edeceksiniz.