MySQL-Techniken zum schnellen Datenvergleich

Im MySQL-Betrieb und bei der Wartung möchte ein F&E-Kollege die Daten zweier verschiedener Instanzen vergleichen und die Unterschiede feststellen. Neben dem Primärschlüssel muss jedes Feld verglichen werden. Wie geht das?

Die erste Lösung besteht darin, ein Programm zu schreiben, das jede Datenzeile aus den beiden Instanzen zum Vergleich extrahiert. Dies ist theoretisch möglich, der Vergleich dauert jedoch lange.

Die zweite Lösung besteht darin, alle Felder in jeder Datenzeile zusammenzuführen, den Prüfsummenwert zu nehmen und dann entsprechend dem Prüfsummenwert zu vergleichen. Das scheint machbar, also probieren Sie es aus.

Zuerst müssen wir die Werte aller Felder zusammenführen und die von MySQL bereitgestellte CONCAT-Funktion verwenden. Wenn die CONCAT-Funktion NULL-Werte enthält, ist das Endergebnis NULL. Daher müssen wir die IFNULL-Funktion verwenden, um die NULL-Werte zu ersetzen, beispielsweise:

CONCAT(WENNNULL(C1,''),WENNNULL(C2,''))

Die Tabelle, die Sie verknüpfen, hat viele Zeilen und es ist mühsam, ein Skript manuell zu erstellen. Keine Sorge, Sie können information_schema.COLUMNS verwenden, um damit umzugehen:

## Holen Sie sich die zusammengesetzte Zeichenfolge der Spaltennamen SELECT
GROUP_CONCAT('IFNULL(',SPALTENNAME,','''')')
VON information_schema.COLUMNS 
WHERE TABLE_NAME='Tabellenname';

Angenommen, wir haben eine Testtabelle:

Tabelle erstellen t_test01
(
 ID INT AUTO_INCREMENT PRIMARY KEY,
 C1 INT,
 C2 INT
)

Wir können dann das folgende SQL herausspleißen:

WÄHLEN
Ausweis,
MD5(VERBINDUNG(
WENNNULL(id,''),
WENNNULL(c1,''),
WENNNULL(c2,''),
)) AS md5_Wert
VON t_test01

Führen Sie es auf zwei Instanzen aus und vergleichen Sie dann die Ergebnisse mit Beyond Compare. Es ist einfach, die unterschiedlichen Zeilen und Primärschlüssel-IDs zu finden.

Bei Tabellen mit großen Datenmengen ist auch die Ergebnismenge groß und schwer zu vergleichen. Versuchen Sie daher zunächst, die Ergebnismenge zu reduzieren. Sie können die MD5-Werte mehrerer Zeilen kombinieren, um den MD5-Wert zu berechnen. Wenn die endgültigen MD5-Werte gleich sind, sind diese Zeilen gleich. Wenn sie unterschiedlich sind, beweist dies, dass es Unterschiede gibt. Vergleichen Sie diese Zeilen dann Zeile für Zeile.

Angenommen, wir vergleichen in Gruppen von 1.000 Zeilen. Wenn wir die gruppierten Ergebnisse zusammenführen müssen, müssen wir die Funktion GROUP_CONCAT verwenden. Beachten Sie, dass wir in der Funktion GROUP_CONCAT eine Sortierung hinzufügen müssen, um die Reihenfolge der zusammengeführten Daten sicherzustellen. Das SQL lautet wie folgt:

WÄHLEN
min(id) als min_id,
max(id) als max_id,
count(1) als Zeilenanzahl,
MD5(GROUP_CONCAT(
MD5(VERBINDUNG(
WENNNULL(id,''),
WENNNULL(c1,''),
WENNNULL(c2,''),
)) ORDER BY ID
))AS md5_value
VON t_test01
GRUPPE NACH (ID Div 1000)

Die Ausführungsergebnisse sind:

min_id max_id Zeilenanzahl MD5_Wert
0 999 1000 7d49def23611f610849ef559677fec0c
1000 1999 1000 95d61931aa5d3b48f1e38b3550daee08
2000 2999 1000 b02612548fae8a4455418365b3ae611a
3000 3999 1000 fe798602ab9dd1c69b36a0da568b6dbb

Wenn es weniger unterschiedliche Daten gibt, können wir, selbst wenn wir zig Millionen Daten vergleichen müssen, problemlos die 1.000 Daten mit Unterschieden basierend auf min_id und max_id lokalisieren und dann die MD5-Werte zeilenweise vergleichen, um schließlich die unterschiedlichen Zeilen zu finden.

Abschließende Vergleichstabelle:

PS:

Wenn Sie GROUP_CONCAT verwenden, müssen Sie die MySQL-Variable group_concat_max_len konfigurieren. Der Standardwert ist 1024, und der Überschuss wird bereitgestellt.