MySQL 20 Millionen Datenoptimierung und -migration

Kürzlich gibt es eine Datentabelle mit 20 Millionen Datensätzen, die optimiert und migriert werden muss. 20 Millionen Daten sind für MySQL sehr peinlich, da die Geschwindigkeit der Indexerstellung immer noch sehr hoch ist und die Geschwindigkeit unabhängig von der Optimierung nicht wesentlich verbessert werden kann. Diese Daten weisen jedoch eine große Anzahl redundanter Felder und Fehlerinformationen auf, was für Statistiken und Analysen äußerst unpraktisch ist. Ich muss also eine neue Tabelle erstellen, die Daten einzeln aus der alten Tabelle herausnehmen, sie optimieren und sie wieder in die neue Tabelle einfügen.

1 Löschen Sie redundante Daten und optimieren Sie sie Feldstruktur

In den 2000W-Daten können wir die Felder vorhersagen, die als Abfragebedingungen verwendet werden können. Erstellen Sie daher für diesen Teil der Daten separat ein neues Feld und ändern Sie die Feldstruktur für reguläre Daten sinnvoll, z. B. ID-Karte ist varchar (18). Bei unwichtigen Daten führen wir diese zusammen und es entsteht ein Feld mit Textstruktur.

Wir müssen einige verwandte Daten berechnen, z. B. den Personalausweistyp, um das genaue Geschlecht, den Geburtsort, den Geburtstag und das Alter zu ermitteln.

2. Datenmigration

Wir entnehmen ein altes Datenstück aus der Datenbank, erhalten dann durch Berechnung und Verarbeitung die neuen Daten, die wir wollen, und fügen sie schließlich ein Neue Daten in die neue Tabelle einfügen. Bei der Beschaffung neuer Daten traten jedoch die folgenden Probleme auf.

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

   Wir können sie stapelweise über MySQL abrufen Beschränken Sie die Syntax. Beispielsweise lautet die SQL-Anweisung jedes Mal, wenn Sie 50.000 erhalten, wie folgt:

   select * from table_name limit 15000000,50000;

   Nach dem Login kopieren

   Diese Methode kann das Problem eines zu großen Datenvolumens lösen, aber je größer der erste Parameter des Grenzwerts wird, desto größer wird der Die Abfragegeschwindigkeit wird langsamer sein. Es ist beängstigend (die Ausführung des oben genannten SQL dauert 35 Sekunden). Zeit ist Leben, also haben wir mit der Optimierung der SQL-Anweisung begonnen. Nach der Optimierung wurde Folgendes angezeigt:

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

   Nach dem Login kopieren

   2000-W-Daten können durch Dichotomie aufgeteilt werden. Nach der Optimierung wurde die SQL-Ausführungseffizienz erheblich verbessert, von 35 Sekunden auf 9 Sekunden, aber sie ist immer noch sehr langsam, Zeit ist Leben ... Glücklicherweise haben wir eine selbsterhöhende ID (das erste Gesetz). Um eine Datentabelle zu erstellen, müssen Felder mit automatischer Inkrementierung vorhanden sein. Die optimierte SQL lautet wie folgt:

   Zur intuitiven Demonstration habe ich zwei SQLs mit derselben Funktion geschrieben. Im Vergleich zur ersten Grenze führt die zweite Grenze dazu, dass der SQL-Index-Treffer schlechter wird und auch die Effizienz abnimmt. Die Ausführungszeit des ersten SQL beträgt 2 Millisekunden und die Ausführungszeit des zweiten 5 Millisekunden (der Durchschnittswert, den ich genommen habe). Die Abfragegeschwindigkeit der einzelnen Daten sank direkt von 35 Sekunden auf 2 Millisekunden...

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

   Nach dem Login kopieren

Die Datenmenge ist zu groß und die Daten können nicht geschätzt werden Import schlägt fehl;

2. Wir haben drei Möglichkeiten, neue Daten in einer neuen Tabelle zu speichern, wie folgt:

Daten einzeln einfügen;

1. Sie werden auf jeden Fall am Anfang darüber nachdenken. Diese Lösung wird definitiv nicht funktionieren, da jedes Einfügen einen Datenbank-IO-Vorgang verursacht. Ein Vorteil dieser Lösung besteht jedoch darin, dass sie problematische Daten rechtzeitig erkennen und die Ausführung nach der Änderung fortsetzen kann. Die Verwendung von „Variablen binden“ in Oracle kann die Leistung verbessern, und MySQL bietet auch die Funktion „Variablen binden“. Versuchen Sie also, die Datenspeichergeschwindigkeit zu optimieren, ohne die Logik zu ändern. Der Code lautet wie folgt:

   Der Endeffekt ist nicht sehr gut. Die „Bind-Variable“ von MySQL bringt keine offensichtliche Geschwindigkeitsverbesserung, kann aber die SQL-Injection wirksam verhindern 🎜 >

   public function actionTest(array $data)
   {
       $mysqli = new mysqli("192.168.1.106", "username", "password", "test");
       $sql = "insert into table_name(name,identity) values (?,?)";
   
       $stmt = $connection->prepare($sql);
       $name = "";
       $identity = "";
       //使用绑定变量
       $stmt->bind_param("si", $name, $identity);
       foreach($data as $val)
       {
           $name = $val[name];
           $identity = $val[card_id];
           //执行
           $stmt->execute();
       }
       $stmt->close();
   }

   Nach dem Login kopieren
   Fügen Sie 50.000 Daten gleichzeitig ein.
   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; Datei und schließlich über MySQL Der integrierte Tool-Import ist ebenfalls hervorragend. 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. Zusammenfassung

Durch verschiedene Optimierungen konnte die Skriptausführungszeit schließlich auf weniger als 20 Minuten reduziert werden. Nach der Optimierung ist die Datenqualität in hohem Maße garantiert. Beim nächsten Mal werden wir versuchen, 200 Millionen Daten zu optimieren und zu migrieren ...

Das Obige ist der Inhalt der 20-Millionen-Datenoptimierung und -migration von MySQL Weitere Informationen zu verwandten Inhalten finden Sie auf der chinesischen PHP-Website (m.sbmmt.com)!