MySQL – Tabellen kopieren (+ Refactoring Tip für große Tabellen)

0
284

Vor kurzem bin ich auf die Herausforderung gestoßen, eine große Datenbank umzuziehen.

Bei der Prüfung, ob alle Daten korrekt kopiert bzw. per Dump eingespielt wurden, viel mir die lange Laufzeit der Select-Queries auf. Es handelte sich um Tabellen, die mehr als 1 Million Einträge hatten und z. T. waren keine Indexe vorhanden.

Es gibt grundsätzlich sicher mehr Möglichkeiten, hier möchte ich auf eine nicht so optimale und eine – für mich sehr schnelle Möglichkeit eingehen.

Möglichkeit 1: Die nicht so gute Lösung

Warum erwähne ich diese? Einige würden jetzt sicherlich direkt auf der Tabelle die Indexe erstellen – und sich dann erst mal entspannt zurücklehnen. Dieser Vorgang dauert extrem lange.

Also – Möglichkeit 1 wäre, die Indexe direkt auf den bestehenden Tabellen anzulegen, da dies mit dem SQL Client wie PHPMyAdmin sehr schnell und einfach durchzuführen ist.

Möglichkeit 2: Die elegantere Lösung!

Schritt 1: Als erstes lege ich eine leere Kopie der bereits vorhandenen Tabelle an:

CREATE TABLE old_tablename_temp LIKE old_tablename

Somit wird erstmal nur die Struktur kopiert.

Schritt 2: Nun können, auf die neue und leere Tabelle, die Indexe korrekt angelegt werden

Schritt 3: Sobald die Indexe vollständig vorhanden sind, kopieren wie die Daten wie folgt:

INSERT INTO old_tablename_temp SELECT * FROM old_tablename

Dieser Vorgang ist in der Regel recht rasch (abhängig von dem Inhalt / Größe der Tabelle abhängig).

ACHTUNG: Jetzt könnten die ersten Fehler auffallen! Denn jetzt sind alle PKs (Primary Keys) und FK (Foreign Keys) aktiviert und es fällt auf, wenn auf eine andere Tabelle referenziert wird – dort aber die IDs nicht existieren. Ist dies der Fall, werden keine Daten kopiert.

Tipp: Prüfen sie, bspw. wie folgt beschrieben, welche IDs fehlen:

SELECT old_tablename_id, count(referenz_tabelle_id)
FROM old_tablename
LEFT JOIN referenz_tabelle on referenz_tabelle.referenz_tabelle_id = old_tablename_id
GROUP BY old_tablename_id
HAVING (count(referenz_tabelle_id) < 1)

Optimierungsvorschläge sind willkommen ,-)

Schritt 4: Wurden alle Daten korrekt kopiert, prüfen sie noch mal die Zeilenlänge – ob die neue – wie auch die alte Tabelle die gleichen Inhalte haben. Die alte Tabelle kann nun gelöscht, sowie die neue wieder umbenannt werden:

DROP old_tablename
RENAME TABLE old_tablename_temp TO old_tablename

Falls euch das Tutorial geholfen hat oder ihr Verbesserungs- / Optimierungsvorschläge habt – teilt es uns doch bitte über die Kommentarfunktion mit. Vielen Dank hierfür vorab!