Doppelte Einträge in SQL-Datenbank effizient löschen

veröffentlicht von gngn am Mi., 06.01.2021 - 16:46

Wir haben eine Tabelle "menschen" mit ID, Nachname und Vorname mit doppelten (genauer: mehrfachen) Einträgen für Nachame/Vorname.

+----+----------+---------+
| id | nachname | vorname |
+----+----------+---------|
| 1  | Schmidt  | Deniz   |
| 2  | Yücel    | Anna    |
| 5  | Meier    | Helga   |
| 9  | Schmidt  | Deniz   |
| 10 | Yücel    | Anna    |
| ...

Mit Hilfe von GROUP BY und HAVING COUNT(*) > 1 stellen wir zunächst fest, ob es überhaupt mehrfache Einträge gibt:

SELECT Nachname, Vorname, COUNT(*) AS anzahl FROM menschen
GROUP BY Name, Vorname
HAVING COUNT(*) > 1
ORDER BY anzahl DESC;

Das liefert dann etwa:

+----------+---------+--------+
| nachname | vorname | anzahl |
+----------+---------+--------+
| Schmidt  | Deniz   | 326    |
| Yücel    | Anna    | 297    |
| ...

Wir wollen die Zeile mit der jeweils niedrigsten ID behalten, die anderen sollen gelöscht werden.

 

Ansatz 1:

DELETE FROM menschen
WHERE id NOT IN (
  SELECT * FROM (
    SELECT MIN(pid) FROM menschen
    GROUP BY nachname, vorname
  )
  AS ua
);

Das innerste SELECT wählt dabei die jeweils niedrigste ID aus (mit gleichen Nach- und Vor-Namen).
Diese ID wird dann beim DELETE ausgeschlossen, so dass die jweils niedrigste ID bestehen bleibt
(mit MAX(id) könnte die höchste behalten werden).

 

 

Optimierte Version:
Das obige Vorgehen funktioniert - kann aber bei wirklich vielen Zeilen (sowas wie 2 Million und mehr) recht lange dauern
und viel Last auf den Server bringen (wir hatten eine Stunde Laufzeit bei konstant 100% CPU-Nutzung durch mysql).
In solchen Fällen kann es helfen, das "grosse" DELETE aufzuteilen auf mehrere DELETEs, die jeweils nur Untermengen der Zeilen bearbeiten.
Dazu schränken wir "sinnvoll" ein, hier zum Beispiel auf Anfangsbuchstaben:

DELETE FROM menschen
WHERE nachname LIKE 'a%' AND id NOT IN (
  SELECT * FROM (
    SELECT MIN(pid) FROM menschen
    WHERE nachname LIKE 'a%'
    GROUP BY nachname, vorname
  )
  AS ua
);

was dann wiederholt wird mit LIKE 'b%', LIKE 'c%', usw. (in *beiden* WHERE-Klauseln!).
Das verringert die Ausführungszeit nach unserer Erfahurng massiv.
Im Beispiel hatten wir anstatt einem Aufruf mit einer Stunde Laufzeit neun Aufrufe, die zusammen unter sechs Minuten brauchten!

 

 

Echtes Beispiel: Drupal's url_alias
Wir hatten das Problem der mehrfachen Einträge mit Drupal 8.7 und der Tabelle url_alias:

  • Aus einer externen Datenquelle werden (per drush und Cronjob) jede Nacht Entities neu erstellt und vorhandene Entities aktualisiert.
  • Die Entity hat einen URL-Alias, dieser wurde nicht aktualisiert, sondern es wurde - fälschlicherweise - jedes Mal ein neuer Eintrag in Tabelle url_alias erzeugt (jeweils identisch zu den bereits vorhandenen).
  • Über die Zeit hatten die ca. 10.000 Entities fast 3 Millionen Einträge in url_alias.
  • Zum Problem wurde das beim Core-Update auf 8.8, bei dem url_alias in das neue path_alias umgewandelt wird.
    Dieses Umwandeln dauert dann ewig ..., d.h. ich habe es nach einer Stunde abgebrochen.

Rasch war die Ursache gefunden - nämlich die fast 3 Millionen Einträge in url_alias.
Ein einfaches SELECT COUNT(*) FROM url_alias lieferte 2928467 Einträge.

Mehrfache Einträge waren zu sehen mit:

SELECT source, alias, langcode, COUNT(*) AS anzahl
FROM url_alias
WHERE source LIKE '/admin/structure/et_wzb_aktiv%'
GROUP BY source, alias, langcode
ORDER BY anzahl DESC, source, alias, langcode;

Dabei stellte sich heraus, dass der absolut überwiegende Teil der Einträge zu unserer Entity gehört,
deren Pfad /my/path/XYZ lautete, wobei XYZ eine eindeutige Zahl ist.

Losgeworden sind wir sie dann mit:

DELETE FROM url_alias
WHERE source LIKE '/my/path/%' AND pid NOT IN (
  SELECT * FROM (
    SELECT MIN(pid) FROM url_alias
    WHERE source LIKE '/my/path/%'
    GROUP BY source, alias, langcode
  )
  AS ua
);

was aber (wie oben erwähnt) eine gute Stunde brauchte.
Besser war es, die DELETEs wie oben aufzuteilen:

DELETE FROM url_alias
WHERE source LIKE '/my/path/1%' AND pid NOT IN(
  SELECT * FROM(
    SELECT MIN(pid) FROM url_alias
    WHERE source LIKE '/my/path/1%'
    GROUP BY source, alias, langcode
  )
AS ua);

und dass dann mit LIKE '/my/path/2%', LIKE '/my/path/3%' bis LIKE '/my/path/9%' zu wiederholen (die eindeutige Zahl hat in unserem Beispiel keine führende Null).

 

Andere Lösungen

Zu dem Problem finden sich etliche Empfehlungen im Netz, z.B.:

Zum Löschen werden dabei häufig die Werte "per Hand" verglichen (anstatt das mittels GROUP der Datenbank zu überlassen).
Mit dem obigen Beispiel:

DELETE FROM menschen M1
WHERE EXISTS (
  SELECT * FROM menschen M2
  WHERE M1.nachname = M2.nachname AND M2.vorname = M2.vorname AND M1.id < M2.id
);

Auf https://dbwiki.net/wiki/SQL:_Doppelte_Datens%C3%A4tze_l%C3%B6schen findet sich der hier beschriebene Ansatz (allerdings ohne die "Optmierung" durch Aufteilen auf mehrere DELETEs).

Neuen Kommentar hinzufügen