So optimieren und migrieren Sie 20 Millionen Daten in MySQL

Kürzlich gibt es eine Datentabelle mit 20 Millionen Datensätzen, die optimiert und migriert werden muss. 2000-W-Daten sind für MySQL aufgrund der vernünftigen Erstellung von Index peinlich Die Geschwindigkeit ist immer noch recht hoch, egal wie viel Optimierung durchgeführt wird, diese Daten enthalten jedoch viele redundante Felder und Fehlermeldungen, was für Statistiken und Analysen sehr unpraktisch ist , also muss ich eine neue Tabelle erstellen, die Daten einzeln aus der alten Tabelle herausnehmen, sie optimieren und sie wieder in die neue Tabelle einfügen; 🎜>1. Bereinigen Sie redundante Daten und optimieren Sie die Feldstruktur
In den

2000W-Daten kennen wir bereits die Felder, die als Bedingungen für die

Abfrage verwendet werden können, also erstellen wir Neue Felder für diesen Teil der Daten und sinnvolle Änderung der Feldstruktur für reguläre Daten, z. B. varchar (18). Nach dem Zusammenführen der unwichtigen Daten haben wir ein Feld mit einer Textstruktur 🎜> Für einige verwandte Daten müssen wir berechnen, z. B. den Ausweistyp, um das genaue Geschlecht und den Geburtsort, den Geburtstag und das Alter zu erhalten 🎜>Wir entnehmen alte Daten aus der Datenbank und erhalten dann durch Berechnung und Verarbeitung neue Daten. Beim Abrufen neuer Daten treten jedoch die folgenden Probleme auf >

Die Datenmenge ist zu groß, um auf einmal abgerufen zu werden (2000-W-Daten in den Speicher zu werfen ist ziemlich beängstigend);

Wir können sie über die Limit-Syntax von MySQL stapelweise abrufen Um beispielsweise jedes Mal 50.000 zu erhalten, lautet die SQL-Anweisung wie folgt:

Diese Methode kann das Problem einer zu großen Datenmenge lösen, wird aber immer größer , wird die Abfragegeschwindigkeit furchtbar langsam sein (die Ausführung des oben genannten SQL dauert 35 Sekunden), also haben wir mit der Optimierung der SQL-Anweisung begonnen und nach der Optimierung sieht sie wie folgt aus:

2000W Daten können Wenn 1000-W-Daten ausgeführt werden, wird die SQL-Ausführungseffizienz erheblich verbessert, von 35 Sekunden auf 9 Sekunden ... Glücklicherweise haben wir eine Auto-Inkrement-ID (das erste Gesetz zum Erstellen von Datentabellen, es müssen Auto-Inkrement-Felder vorhanden sein). Die optimierte SQL lautet wie folgt:

Aus Gründen der Intuition Zur Veranschaulichung habe ich zwei SQLs mit derselben Funktion geschrieben. Das Limit des zweiten SQL führt zu einer schlechteren Indextrefferrate und die Ausführungszeit des ersten SQL beträgt 2 Millisekunden. Die Ausführungszeit der beiden Elemente beträgt 5 Millisekunden (der durchschnittliche Wert, den ich genommen habe) sank direkt von 35 Sekunden auf 2 Millisekunden...

1. Die Die Datenmenge ist zu groß und die Daten können nicht vorhergesagt werden. Es wird geschätzt, dass einige spezielle Daten dazu führen, dass der Datenimport fehlschlägt.

   Wir haben drei Möglichkeiten, neue Daten in der neuen Tabelle zu speichern:

   select * from table_name limit 15000000,50000;

   select * from table_name order by id desc limit 5000000,50000;

   Einer, wenn ein

   Daten einfügt

   ; wird einen Datenbank-E/A-Vorgang haben. Ein Vorteil dieser Lösung besteht jedoch darin, dass sie problematische Daten rechtzeitig erkennen und die Ausführung nach der Änderung fortsetzen kann. Mithilfe von „Bind

   Variable

   “ in Oracle kann die Leistung verbessert werden, und MySQL bietet auch die Funktion „Variable binden“. Versuchen Sie also, die Datenspeichergeschwindigkeit zu optimieren, ohne die Logik zu ändern. Der Code lautet wie folgt:

   1. select * from table_name where id>15000000 and id<15050000； 2. select * from table_name where id>15000000 limit 50000;

   Der Endeffekt ist nicht sehr gut. Die „Bind-Variable“ von MySQL bringt keine offensichtliche Geschwindigkeitsverbesserung, kann aber die SQL-Injection wirksam verhindern 🎜 >
Fügen Sie 50.000 Daten gleichzeitig ein.

2. Dies ist die Lösung, die ich letztendlich gewählt habe. Erstens können problematische Daten rechtzeitig erkannt werden, und zweitens sind die importierten Daten sehr stabil. Genau wie bei der Unterstützung der Wiederaufnahme des Haltepunkts können Sie die Auswirkung bei jedem Schritt sehen. Beim Ausführen des Skripts können Sie auch gleichzeitig mit dem Schreiben der Analyselogik beginnen;

wird in eine SQL-Datei zusammengestellt und schließlich einheitlich importiert;

Es ist auch großartig, eine große SQL-Datei zusammenzustellen und sie schließlich über das mit MySQL gelieferte Tool zu importieren. Wenn jedoch ein Problem mit einer der SQL-Anweisungen auftritt, müssen Sie das Skript möglicherweise erneut ausführen. Weil es sehr mühsam ist, ein Symbol in einer 9G-Textdatei zu ändern...

3 Eine Art Optimierung reduzierte schließlich die Skriptausführungszeit auf weniger als 20 Minuten. Nach der Optimierung ist die Datenqualität höchst garantiert. Beim nächsten Mal werden wir versuchen, 200 Millionen Daten zu optimieren und zu migrieren...

Das obige ist der detaillierte Inhalt vonSo optimieren und migrieren Sie 20 Millionen Daten in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!