MariaDB ve MySQL’de Duplicate Kayıtları Bulma ve Silme Sorguları

Tarafındankomut
0 Yorum

Veritabanı yönetiminde en can sıkıcı sorunlardan biri duplicate kayıtlardır. Bir e-ticaret sisteminde aynı müşterinin iki kez kayıtlı olması, bir envanter tablosunda aynı ürünün birden fazla görünmesi ya da log tablolarında milyonlarca tekrarlı satır… Bunların hepsi hem depolama israfına hem de uygulama hatalarına yol açar. Bu yazıda MariaDB ve MySQL üzerinde duplicate kayıtları nasıl bulacağınızı, analiz edeceğinizi ve güvenli bir şekilde sileceğinizi ele alacağız.

Duplicate Kayıt Nedir ve Neden Oluşur?

Duplicate kayıt, bir tabloda aynı verinin birden fazla satırda tekrar etmesi durumudur. Bu durumun birkaç farklı boyutu vardır:

  • Tam duplicate: Tüm kolonlar dahil her şey aynı olan satırlar
  • Kısmi duplicate: Belirli bir veya birkaç kolona göre tekrar eden satırlar
  • Mantıksal duplicate: Teknik olarak farklı ama iş mantığı açısından aynı sayılan kayıtlar

Duplicate kayıtlar genellikle şu nedenlerle oluşur:

  • Uygulama katmanında eksik validasyon
  • Toplu veri import işlemlerindeki hatalar
  • Veritabanı migration süreçleri
  • UNIQUE constraint olmayan tablolara çoklu insert işlemleri
  • ETL (Extract, Transform, Load) hatalar

Önce Ortamı Hazırlayalım

Örnekleri test edebilmek için önce bir test ortamı oluşturalım. Gerçek bir production veritabanına doğrudan müdahale etmeden önce mutlaka test edin.

-- Test veritabanı ve tabloları oluşturuyoruz
CREATE DATABASE duplicate_test;
USE duplicate_test;

-- Müşteri tablosu
CREATE TABLE musteriler (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ad VARCHAR(100),
    soyad VARCHAR(100),
    email VARCHAR(150),
    telefon VARCHAR(20),
    kayit_tarihi DATETIME DEFAULT NOW()
);

-- Örnek veriler ekleyelim (duplicateler dahil)
INSERT INTO musteriler (ad, soyad, email, telefon) VALUES
('Ahmet', 'Yilmaz', '[email protected]', '5551234567'),
('Fatma', 'Kaya', '[email protected]', '5559876543'),
('Ahmet', 'Yilmaz', '[email protected]', '5551234567'),
('Mehmet', 'Demir', '[email protected]', '5554567890'),
('Fatma', 'Kaya', '[email protected]', '5559876543'),
('Fatma', 'Kaya', '[email protected]', '5559876543'),
('Ali', 'Celik', '[email protected]', '5552345678'),
('Zeynep', 'Arslan', '[email protected]', '5558765432');

Duplicate Kayıtları Bulma Yöntemleri

Belirli Kolonlara Göre Duplicate Tespiti

En temel yöntem, GROUP BY ve HAVING kullanarak belirli kolonlarda tekrar eden değerleri bulmaktır. E-posta adresine göre duplicate müşterileri bulmak isteyelim:

-- Email adresine göre duplicate kayıtları listele
SELECT 
    email,
    COUNT(*) AS tekrar_sayisi
FROM musteriler
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY tekrar_sayisi DESC;

Bu sorgu size hangi email adresinin kaç kez tekrar ettiğini gösterir. Ancak genellikle sadece hangi değerin tekrar ettiğini değil, o kayıtların tüm detaylarını görmek istersiniz.

Tüm Duplicate Satırları Detaylı Görme

-- Duplicate kayıtların tüm detaylarını göster
SELECT m.*
FROM musteriler m
INNER JOIN (
    SELECT email, COUNT(*) AS cnt
    FROM musteriler
    GROUP BY email
    HAVING COUNT(*) > 1
) duplar ON m.email = duplar.email
ORDER BY m.email, m.id;

Bu sorgu sayesinde aynı email adresine sahip tüm satırları görürsünüz. Hangi kaydın eski hangi kaydın yeni olduğunu, aralarındaki farkları karşılaştırabilirsiniz.

Birden Fazla Kolona Göre Duplicate Bulma

Gerçek hayatta genellikle tek bir kolona göre değil, birden fazla kolona göre duplicate kontrol yapmanız gerekir. Örneğin hem adı hem soyadı hem de emaili aynı olan kayıtlar:

-- Çoklu kolon kombinasyonuna göre duplicate tespiti
SELECT 
    ad,
    soyad,
    email,
    COUNT(*) AS tekrar_sayisi,
    MIN(id) AS ilk_kayit_id,
    MAX(id) AS son_kayit_id,
    MIN(kayit_tarihi) AS ilk_kayit_tarihi
FROM musteriler
GROUP BY ad, soyad, email
HAVING COUNT(*) > 1
ORDER BY tekrar_sayisi DESC;

Burada MIN(id) ile en eski kaydı, MAX(id) ile en yeni kaydı tespit edebilirsiniz. Bu bilgi silme işlemi sırasında hangi kaydı tutacağınıza karar vermenizi sağlar.

ROW_NUMBER() ile Gelişmiş Duplicate Analizi

MariaDB 10.2 ve MySQL 8.0 ile birlikte window fonksiyonları kullanılabilir hale geldi. ROW_NUMBER() fonksiyonu duplicate analizi için çok güçlü bir araçtır.

-- ROW_NUMBER() ile her grubun içindeki sıra numarasını bul
-- (MariaDB 10.2+ ve MySQL 8.0+ için)
SELECT 
    id,
    ad,
    soyad,
    email,
    kayit_tarihi,
    ROW_NUMBER() OVER (
        PARTITION BY email 
        ORDER BY id ASC
    ) AS sira_no
FROM musteriler
ORDER BY email, sira_no;

Bu sorgu çıktısında sira_no değeri 1’den büyük olan satırlar duplicate kayıtlardır. İlk kayıt (sira_no = 1) tutmak istediğimiz kayıt, diğerleri silinecek adaylardır.

Daha da ileri giderek sadece silinecek kayıtları listeleyebiliriz:

-- Sadece silinecek duplicate kayıtları listele
SELECT id, ad, soyad, email, kayit_tarihi
FROM (
    SELECT 
        id,
        ad,
        soyad,
        email,
        kayit_tarihi,
        ROW_NUMBER() OVER (
            PARTITION BY email 
            ORDER BY id ASC
        ) AS sira_no
    FROM musteriler
) siralanmis
WHERE sira_no > 1;

Duplicate Kayıtları Silme Stratejileri

Silme işlemine geçmeden önce mutlaka yedek alın. Bu konuda ne kadar vurgulasam azdır.

-- Önce yedeği al
mysqldump -u root -p duplicate_test musteriler > musteriler_yedek_$(date +%Y%m%d_%H%M%S).sql

Yöntem 1: Self Join ile Silme (Eski ve Yeni Versiyon Uyumlu)

Bu yöntem MariaDB ve MySQL’in tüm versiyonlarında çalışır. Mantık şu: aynı email adresine sahip kayıtlar arasında küçük ID’yi tut, büyük ID’lileri sil.

-- Self join ile duplicate kayıtları sil
-- Küçük ID'yi tut, büyük ID'li duplicate kayıtları sil
DELETE m1
FROM musteriler m1
INNER JOIN musteriler m2
WHERE 
    m1.id > m2.id AND
    m1.email = m2.email;

Bu sorgu şöyle çalışır: m1 ve m2 aynı tabloyu temsil eder. Aynı email’e sahip iki satır bulunduğunda, ID’si büyük olan (yani m1.id > m2.id) kayıt silinir. Bu sayede her email için sadece en küçük ID’li kayıt hayatta kalır.

Yöntem 2: Subquery ile Silme

Bazı durumlarda self join yerine subquery kullanmak daha okunabilir olabilir:

-- Subquery ile duplicate silme
-- Her email için MIN(id)'yi tut, diğerlerini sil
DELETE FROM musteriler
WHERE id NOT IN (
    SELECT min_id FROM (
        SELECT MIN(id) AS min_id
        FROM musteriler
        GROUP BY email
    ) AS tutulacaklar
);

Dikkat: MySQL ve MariaDB’de aynı tabloyu hem silme hem de subquery’de kullanamayabilirsiniz. Bu nedenle sorguyu iç içe yazarak geçici bir türetilmiş tablo oluşturduk. Bu teknik “derived table trick” olarak bilinir ve bu tür durumlarda hayat kurtarır.

Yöntem 3: Geçici Tablo Kullanarak Güvenli Silme

Büyük tablolarda veya karmaşık duplicate senaryolarında en güvenli yöntem geçici tablo kullanmaktır:

-- Adım 1: Tutulacak kayıtların ID'lerini geçici tabloya al
CREATE TEMPORARY TABLE tutulacak_idler AS
SELECT MIN(id) AS id
FROM musteriler
GROUP BY email;

-- Adım 2: Geçici tabloda olmayan kayıtları sil
DELETE FROM musteriler
WHERE id NOT IN (SELECT id FROM tutulacak_idler);

-- Adım 3: Geçici tabloyu temizle
DROP TEMPORARY TABLE tutulacak_idler;

-- Adım 4: Kaç kayıt kaldığını doğrula
SELECT COUNT(*) AS kalan_kayit_sayisi FROM musteriler;

Bu yöntemin avantajı her adımı ayrı ayrı çalıştırıp kontrol edebilmenizdir. Özellikle production ortamında bu adım adım ilerleme yaklaşımı çok değerlidir.

Büyük Tablolarda Performanslı Duplicate Yönetimi

Milyonlarca kayıt içeren bir tabloda toplu silme işlemi hem uzun sürebilir hem de tabloya uzun süre lock koyabilir. Bu durumu yönetmek için batch silme stratejisi kullanmalısınız.

-- Batch silme için döngü yaklaşımı
-- Bu scripti bir stored procedure veya shell script içinde çalıştırın

-- Önce duplicate sayısını öğren
SELECT COUNT(*) 
FROM musteriler m1
INNER JOIN musteriler m2
WHERE m1.id > m2.id AND m1.email = m2.email;

-- 1000'er kayıt halinde sil (production lock süresini kısaltır)
DELETE m1
FROM musteriler m1
INNER JOIN musteriler m2
WHERE 
    m1.id > m2.id AND
    m1.email = m2.email
LIMIT 1000;

-- Bu DELETE sorgusunu affected rows = 0 olana kadar tekrarlayın
-- Shell script ile:
-- while [ $(mysql -u root -p... -e "DELETE ... LIMIT 1000; SELECT ROW_COUNT();" | tail -1) -gt 0 ]; do sleep 0.1; done

İndeks ile Duplicate Önleme

Duplicate kayıtları sildikten sonra tekrar oluşmasını engellemek için UNIQUE index eklemeniz gerekir. Bu hem veri bütünlüğünü korur hem de uygulama hatalarını daha erken yakalamanızı sağlar.

-- Duplicate silme sonrası UNIQUE index ekle
ALTER TABLE musteriler
ADD UNIQUE INDEX idx_unique_email (email);

-- Birden fazla kolon kombinasyonu için composite unique index
-- ALTER TABLE musteriler
-- ADD UNIQUE INDEX idx_unique_ad_email (ad, soyad, email);

-- Index'in eklendi mi kontrol et
SHOW INDEX FROM musteriler;

-- Var olan duplicate'leri görmezden gelerek index eklemek için (dikkatli kullanın)
-- ALTER IGNORE TABLE musteriler ADD UNIQUE INDEX idx_unique_email (email);

ALTER IGNORE TABLE komutu hakkında önemli bir not: Bu komut MariaDB’de çalışır ancak MySQL 5.7 ve sonrasında kaldırılmıştır. MySQL’de önce duplicate’leri silmek, sonra index eklemek daha güvenli yaklaşımdır.

Gerçek Dünya Senaryosu: E-ticaret Sipariş Tablosu

Diyelim ki bir e-ticaret platformunun sipariş tablosunda aynı siparişin birden fazla kayıtlı olduğu tespit edildi. Bu durum genellikle ödeme gateway callback’lerinin birden fazla çalışması sonucu oluşur.

-- Sipariş tablosu örneği
CREATE TABLE siparisler (
    id INT AUTO_INCREMENT PRIMARY KEY,
    siparis_no VARCHAR(50),
    musteri_id INT,
    toplam_tutar DECIMAL(10,2),
    durum ENUM('bekliyor', 'odendi', 'iptal'),
    olusturma_tarihi DATETIME DEFAULT NOW()
);

-- Sipariş numarasına göre duplicate analizi
SELECT 
    siparis_no,
    COUNT(*) AS tekrar,
    MIN(id) AS ilk_kayit,
    MAX(id) AS son_kayit,
    MIN(olusturma_tarihi) AS ilk_tarih,
    MAX(olusturma_tarihi) AS son_tarih,
    GROUP_CONCAT(durum ORDER BY id) AS durumlar
FROM siparisler
GROUP BY siparis_no
HAVING COUNT(*) > 1;

Bu örnekte GROUP_CONCAT kullanarak duplicate kayıtların farklı durumlarda olup olmadığını da görebilirsiniz. Eğer bir sipariş hem “bekliyor” hem “odendi” durumundaysa silmeden önce manuel inceleme yapmanız gerekebilir.

-- Sadece aynı durumdaki duplicate siparişleri sil
DELETE s1
FROM siparisler s1
INNER JOIN siparisler s2
WHERE 
    s1.id > s2.id AND
    s1.siparis_no = s2.siparis_no AND
    s1.durum = s2.durum;

-- Farklı durumdaki şüpheli kayıtları raporla
SELECT siparis_no, GROUP_CONCAT(id), GROUP_CONCAT(durum)
FROM siparisler
GROUP BY siparis_no
HAVING COUNT(DISTINCT durum) > 1;

Duplicate Analizi İçin Yararlı Yardımcı Sorgular

Günlük DBA rutininizde kullanabileceğiniz birkaç pratik sorgu:

-- Tüm tablolarda potansiyel duplicate oranını göster
-- (Information schema üzerinden)
SELECT 
    table_name,
    table_rows AS tahmini_satir_sayisi,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb
FROM information_schema.tables
WHERE table_schema = 'duplicate_test'
ORDER BY table_rows DESC;

-- Bir tablodaki duplicate oranını yüzde olarak hesapla
SELECT 
    COUNT(*) AS toplam_kayit,
    COUNT(DISTINCT email) AS benzersiz_email,
    COUNT(*) - COUNT(DISTINCT email) AS duplicate_sayisi,
    ROUND(
        (COUNT(*) - COUNT(DISTINCT email)) * 100.0 / COUNT(*), 
        2
    ) AS duplicate_yuzdesi
FROM musteriler;

Silme İşlemi Öncesi Doğrulama Checklist

Production ortamında duplicate silme yapmadan önce şu adımları takip edin:

  • Yedek al: Hem full dump hem de etkilenecek tablonun yedeğini al
  • Row count doğrula: Silme öncesi ve sonrası satır sayısını kaydet
  • Foreign key kontrol: Silinecek kayıtlara başka tablolardan referans var mı?
  • Test ortamında dene: Aynı sorguyu önce test ortamında çalıştır
  • Maintenance window: Yoğun saatlerden kaçın, gerekirse uygulama trafiğini durdur
  • Transaction kullan: Mümkünse transaction içinde çalış, bir şeyler yanlış giderse ROLLBACK yap
-- Transaction ile güvenli silme
START TRANSACTION;

-- Silme öncesi kayıt sayısı
SELECT COUNT(*) AS silme_oncesi FROM musteriler;

-- Duplicate silme işlemi
DELETE m1
FROM musteriler m1
INNER JOIN musteriler m2
WHERE m1.id > m2.id AND m1.email = m2.email;

-- Silinen kayıt sayısı
SELECT ROW_COUNT() AS silinen_kayit_sayisi;

-- Silme sonrası kayıt sayısı
SELECT COUNT(*) AS silme_sonrasi FROM musteriler;

-- Her şey doğruysa commit, yoksa rollback
-- COMMIT;
-- veya
-- ROLLBACK;

Sonuç

Duplicate kayıt yönetimi, veritabanı administrasyonunun rutin ama kritik bir parçasıdır. Bu yazıda öğrendiklerimizi özetleyelim:

  • Tespit için GROUP BY ve HAVING kombinasyonu en basit ve evrensel yaklaşımdır
  • Detaylı analiz için ROW_NUMBER() window fonksiyonu çok güçlü bir araçtır (MariaDB 10.2+, MySQL 8.0+)
  • Silme işlemi için self join yöntemi eski versiyonlarla da uyumludur ve oldukça güvenilirdir
  • Büyük tablolarda batch silme stratejisi lock sürelerini kısaltır ve production etkisini azaltır
  • Kalıcı çözüm için UNIQUE index eklemek şarttır, aksi halde duplicate’ler tekrar oluşur

Her silme işlemi öncesinde yedek almayı, test ortamında denemeyi ve transaction kullanmayı alışkanlık haline getirin. Bir kere yanlışlıkla silinen production verisi geri getirmenin ne kadar stresli olduğunu yaşayan her DBA bu kuralların önemini çok iyi bilir.

Son olarak, eğer duplicate kayıtlarla sık sık karşılaşıyorsanız bu bir semptom olabilir. Asıl sorun uygulama katmanındaki eksik validasyon veya veri import süreçlerindeki hatalar olabilir. Duplicate’leri silmek geçici bir çözümdür, kalıcı çözüm için veri akışının başında sorunu yakalamak gerekir.

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir