MySql schnelles Einfügen von zig Millionen großen Datenbeispielen

Im Bereich der Datenanalyse sind Datenbanken unsere guten Helfer. Wir können nicht nur unsere Abfragezeit akzeptieren, sondern darauf basierend auch weitere Analysen durchführen. Daher müssen wir Daten in die Datenbank einfügen. In der Praxis kommen häufig Datenmengen in zweistelliger Millionenhöhe oder noch größer vor. Wenn es keine Methode zur schnellen Einfügung gibt, ist es ineffektiv und nimmt viel Zeit in Anspruch.

Als ich an Alibabas Tianchi Big Data Algorithm Competition (Vorhersage populärer Musiktrends) teilnahm, stieß ich auf ein solches Problem. Vor der Optimierung der Datenbankabfrage und -einfügung habe ich viel Zeit verschwendet. Vor der Optimierung dauerte es unglaubliche 12 Stunden, nur um 15 Millionen Datenelemente einzufügen (mit der einfachsten Einzeleinfügung). Dies hat mich auch dazu veranlasst, darüber nachzudenken, wie ich Datenbankeinfügungen und Abfragevorgänge optimieren könnte, um die Effizienz zu verbessern.

Im Zuge der kontinuierlichen Optimierung wurde die Leistung erheblich verbessert. Beim Abfragen und Aggregieren der Download-, Wiedergabe- und Favoritenzahlen von über 26.000 Songs aus der Datenbank in Zeitreihen wurde die Geschwindigkeit der Abfragegenerierung von den geschätzten 40 Stunden auf knapp über eine Stunde reduziert. In Bezug auf das Einfügen in die Datenbank wurde die Leistung erheblich verbessert. Beim Test mit einem neuen Datensatz wurden in 20 Minuten mehr als 54,9 Millionen Daten eingefügt. Lassen Sie mich unten meine Gedanken mitteilen.

Der Optimierungsprozess gliedert sich in zwei Schritte. Der erste Schritt besteht darin, den experimentellen statischen Reader zum Lesen von Daten aus der CSV-Datei zu verwenden. Wenn die Daten eine bestimmte Menge erreichen, wird die mehrfädige Einfügung in das Datenbankprogramm gestartet. Der zweite Schritt besteht darin, MySQL-Batch-Einfügungsvorgänge zu verwenden.

Der erste Schritt besteht darin, die Datei zu lesen und mit dem Einfügen von Multithreading zu beginnen

Hier ist das Erreichen einer bestimmten Menge eine Frage, die berücksichtigt werden muss. In meinem Experiment begann ich mit 100 W als dieser Menge, aber es trat ein neues Problem auf, der Java-Heap-Speicher lief über und schließlich wurden 10 W als Standard verwendet.

Natürlich können es auch andere Mengen sein, je nach Wunsch.

importiere java.io.BufferedReader;
importiere java.io.FileNotFoundException;
importiere java.io.FileReader;
importiere java.io.IOException;
importiere java.util.ArrayList;
importiere java.util.List;
 
Importieren Sie die Vorverarbeitung.ImportDataBase.
 
öffentliche Klasse MultiThreadImportDB {
 
 /**
  * Java-Multithread-Lesen großer Dateien und Speichern * 
  * @param args
  */
 private statische int m_record = 99999;
 privater statischer BufferedReader br = null;
 private ArrayList<String>-Liste;
 private statische int m_thread = 0;
 statisch {
 versuchen {
  br = neuer BufferedReader(
  neuer FileReader(
  „E:/tianci/IJCAI15 Data/data_format1/user_log_format1.csv“),8192);
 
 } Fang (FileNotFoundException e) {
  e.printStackTrace();
 }
 versuchen {
  br.readLine(); // CSV-Header entfernen
 } Fang (IOException e) {
  e.printStackTrace();
 }
 }
 
 öffentliche Leere start() {
 Schnurlinie;
 int-Anzahl = 0;
 Liste = neue ArrayList<String>(m_record + 1);
 synchronisiert (br) {
  versuchen {
 während ((Zeile = br.readLine()) != null) {
  wenn (Anzahl < m_Datensatz) {
 Liste.Hinzufügen(Zeile);
 zählen++;
  } anders {
 Liste.Hinzufügen(Zeile);
 Anzahl = 0;
 Thread t1 = neuer Thread (neuer MultiThread (Liste), Integer.toString (m_thread++));
 t1.start();
 Liste = neue ArrayList<String>(m_record + 1);
  }
 }
 
 wenn (Liste != null) {
  Thread t1 = neuer Thread (neuer MultiThread (Liste), Integer.toString (m_thread++));
  t1.start();
 }
  } Fang (IOException e) {
 e.printStackTrace();
  }
 }
 }
 
 öffentliche statische void main(String[] args) {
 neuer MuiltThreadImportDB().start();
 } 
}

Der zweite Schritt besteht darin, Multithreading zu verwenden, um Daten in Stapeln einzufügen

Klasse MultiThread implementiert Runnable {
 private ArrayList<String>-Liste;
 
 öffentliche MultiThread(ArrayList<String> Liste) {
 diese.liste = Liste;
 }
 
 öffentliche Leere ausführen() {
 versuchen {
  ImportDataBase einfügen = neue ImportDataBase(Liste);
  einfügen.start();
 } Fang (FileNotFoundException e) {
  e.printStackTrace();
 }
 Anzeige(diese.Liste);
 }
 
 öffentliche void Anzeige(Liste<String> Liste) {
 // für (String str : Liste) {
 // System.out.println(str);
 // }
 System.out.print(Thread.currentThread().getName() + " :");
 System.out.println(Liste.Größe());
 }
 
}

Bei Batchoperationen wird die PrepareStatement-Klasse von MySQL verwendet, und natürlich werden auch die Batchoperationen der Anweisungsklasse verwendet, aber die Leistung ist nicht so gut wie bei der ersteren. Erstere können eine Einfügegeschwindigkeit von über 10.000 pro Sekunde erreichen, während letztere nur über 2.000 erreichen können.

öffentliche int insertUserBehaviour(ArrayList<String> sqls) wirft SQLException {
 
 String SQL = "In Benutzerverhaltensprotokoll einfügen (Benutzer-ID, Artikel-ID, Katalog-ID, Händler-ID, Marken-ID, Zeitstempel, Aktionstyp)"
 + " Werte(?,?,?,?,?,?,?)";
 preStmt = conn.prepareStatement(sql);
 für (int i = 0; i < sqls.size(); i++) {
  Benutzerprotokoll log = neues Benutzerprotokoll(sqls.get(i));
  preStmt.setString(1, log.getUser_id());
  preStmt.setString(2, log.getItem_id());
  preStmt.setString(3, log.getCat_id());
  preStmt.setString(4, log.getMerchant_id());
  preStmt.setString(5, log.getBrand_id());
  preStmt.setString(6, log.getTimeStamp());
  preStmt.setString(7, log.getActionType());
  preStmt.addBatch();
  wenn ((i + 1) % 10000 == 0) {
 preStmt.executeBatch();
 conn.commit();
 preStmt.clearBatch();
  }
 }
 preStmt.executeBatch();
 conn.commit();
 Rückgabe 1;
 }

Natürlich haben wir auch mit verschiedenen MySQL-Speicher-Engines, InnoDB und MyISM, experimentiert. Die experimentellen Ergebnisse zeigten, dass InnoDB schneller ist (etwa dreimal), was möglicherweise mit der neuen Version von MySQL zusammenhängt. Die MySQL-Version des Autors ist 5.6.

Lassen Sie uns abschließend die Methoden zur Verbesserung der Einfügegeschwindigkeit bei großen Datenmengen zusammenfassen.

Verwenden Sie für Java-Code die mehrfädige Einfügung und Batchübermittlung.

Verwenden Sie in Bezug auf die Datenbank beim Einrichten der Tabellenstruktur keine Indizes, da sonst der Index B + -Baum während des Einfügevorgangs beibehalten werden muss. Ändern Sie die Speicher-Engine. Im Allgemeinen ist InnoDB die Standardeinstellung (die neue Version kann die Standardeinstellung verwenden, die alte Version erfordert dies jedoch möglicherweise).

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird für jedermanns Studium hilfreich sein. Ich hoffe auch, dass jeder 123WORDPRESS.COM unterstützen wird.