Analyse der Probleme und Lösungen beim Importieren großer Datenmengen in MySQL

Im Projekt stoßen wir häufig auf das Problem, große Datenmengen in die Datenbank zu importieren, um mithilfe von SQL Datenanalysen durchzuführen. Beim Importieren von Daten treten einige Probleme auf, die gelöst werden müssen. Hier werden die aufgetretenen Probleme und ihre Lösungen anhand der Praxis des Importierens von TXT-Daten von etwa 4G vorgestellt. Einerseits werde ich eine Zusammenfassung für mich selbst erstellen und andererseits hoffe ich, dass sie als Referenz für Freunde dient, die auf dieselben Probleme stoßen.

Bei den Daten, die ich importiert habe, handelt es sich um eine TXT-Datei einer Enzyklopädie. Die Dateigröße beträgt über 4 GB und umfasst über 65 Millionen Daten. Die einzelnen Daten sind durch einen Zeilenumbruch getrennt. Jedes Datum enthält drei Felder, die durch Tabulatoren getrennt sind. Die Methode, die ich zum Abrufen der Daten verwende, besteht darin, eine TripleData-Klasse zum Speichern dieser drei Felder zu verwenden. Alle Felder verwenden String, speichern dann mehrere Daten in List<TripleData> und speichern dann List<TripleData> in der MySQL-Datenbank, wobei alle Daten in Stapeln in der MySQL-Datenbank gespeichert werden.

Das Obige ist eine allgemeine Vorstellung. Im Folgenden sind die Probleme aufgeführt, die während des spezifischen Importvorgangs aufgetreten sind.

1. Unleserliche Zeichen und Kompatibilitätsprobleme bei der Datenbankverbindung.

Wenn die Daten Chinesisch enthalten, achten Sie darauf, die Kodierungsparameter der URL festzulegen, die auf die Datenbank verweist. Die URL sollte wie folgt festgelegt werden.

URL="jdbc:mysql://"+IP+":"+PORT+"/"+DB_NAME+"?useSSL=false&useUnicode=true&characterEncoding=utf-8";

Durch Festlegen der Kodierung auf UTF-8 wird das Problem der verstümmelten Zeichen gelöst, und durch Festlegen von useSSL wird das Kompatibilitätsproblem zwischen JDBC und MySQL gelöst. Wenn useSSL nicht festgelegt ist, wird ein Fehler gemeldet. Ähnlich

Es wird nicht empfohlen, eine SSL-Verbindung ohne Identitätsüberprüfung des Servers herzustellen. Gemäß den Anforderungen von MySQL 5.5.45+, 5.6.26+ und 5.7.6+ muss eine SSL-Verbindung standardmäßig hergestellt werden, wenn keine explizite Option festgelegt ist. Aus Kompatibilitätsgründen mit vorhandenen Anwendungen, die kein SSL verwenden, wird die Eigenschaft „verifyServerCertificate“ auf „false“ festgelegt. Sie müssen SSL entweder explizit deaktivieren, indem Sie useSSL=false festlegen, oder useSSL=true festlegen und einen Truststore für die Überprüfung des Serverzertifikats bereitstellen.

So eine Fehlermeldung. Der Hauptgrund ist, dass die MySQL-Version relativ hoch und die JDBC-Version relativ niedrig ist, sodass Kompatibilität erforderlich ist.

2 UTF8MB4-Kodierungsproblem

Beim Importieren von Daten werden Sie auf ähnliche

SQLException: Falscher Zeichenfolgenwert: „\xF0\xA1\x8B\xBE\xE5\xA2...“ für Spalte „Name“

Diese Fehlermeldung liegt daran, dass das in MySQL festgelegte UTF-8 standardmäßig 3 Byte beträgt, was für allgemeine Daten kein Problem darstellt. Wenn das Datenvolumen jedoch groß ist, enthält es zwangsläufig einige WeChat-Emoticons oder Sonderzeichen, die 4 Byte belegen und von UTF-8 nicht verarbeitet werden können. Daher wird ein Fehler gemeldet. Die Lösung besteht darin, dass MySQL in Versionen nach 5.5.3 eine 4-Byte-UTF-8-Kodierung, nämlich utf8mb4, eingeführt hat und die MySQL-Kodierung zurückgesetzt werden muss.

Sie können die folgenden Schritte ausführen. Sichern Sie zunächst die zu ändernde Datenbank. Obwohl utf8mb4 abwärtskompatibel mit utf8 ist, müssen Sie dennoch Vorsichtsmaßnahmen treffen und Sicherungen erstellen, um Fehlfunktionen zu vermeiden. Die zweite Möglichkeit besteht darin, die Zeichensatzkodierung der Datenbank in utf8mb4 (UTF-8 Unicode) und die Sortierregel in utf8mb4_general_ci zu ändern. Ich habe Navicat verwendet, um die obigen Änderungen vorzunehmen. Sie können herausfinden, wie Sie sie über die Befehlszeile ändern können. Die dritte Möglichkeit besteht darin, die Konfigurationsdatei my.ini im Stammverzeichnis der MySQL-Installation zu ändern. Fügen Sie die folgenden Einstellungen hinzu.

[Kunde]
Standardzeichensatz = utf8mb4
[mysqld]
Zeichensatzserver = utf8mb4
Sortierserver = utf8mb4_general_ci
[mysql]
Standardzeichensatz = utf8mb4

Nachdem die Änderung abgeschlossen ist, müssen Sie MySQL neu starten, damit die Änderung wirksam wird.

Importieren Sie dann die Daten. Der Import sollte normal erfolgen.

3 Zeiteffizienzprobleme bei Importen im großen Maßstab

Da unser Datenvolumen relativ groß ist, haben wir die Daten segmentiert. Ich habe die 65 Millionen Daten in 500 Dateien mit jeweils etwa 110.000 Datenelementen aufgeteilt. Diese 110.000 Datenelemente habe ich in ArrayList<TripleObject> eingefügt und dann stapelweise importiert. Die allgemeine Idee besteht darin, die Methode „insert into tb (...) values(...),(...)...;“ zu verwenden und „insert“ zum Einfügen auf einmal zu verwenden, was viel Zeit spart. Eine Beispielmethode ist wie folgt.

public static void insertSQL(String sql,List<TripleObject> tripleObjectList) throws SQLException{
    Verbindung conn=null;
    Vorbereitete Anweisung psts=null;
    versuchen {
      conn = DriverManager.getConnection(Common.URL, Common.DB_USERNAME, Common.DB_PASSWORD);
      conn.setAutoCommit(false); // Manuelles Commit festlegen // SQL-Suffix speichern StringBuffer suffix = new StringBuffer();
      int-Anzahl = 0; 
      psts=conn.prepareStatement("");
      Zeichenfolge s="";
      Zeichenfolge p="";
      Zeichenfolge o="";
      während (Anzahl<tripleObjectList.size()) {
        s=tripleObjectList.get(Anzahl).getSubject().replaceAll(",", ".").replaceAll("\\(", "").replaceAll("\\)", "").replaceAll("\'", "").replaceAll("\\\\", "");
        p=tripleObjectList.get(Anzahl).getPredicate().replaceAll(",", ".").replaceAll("\\(", "").replaceAll("\\)", "").replaceAll("\'", "").replaceAll("\\\\", "");
        o=tripleObjectList.get(Anzahl).getObject().replaceAll(",", ".").replaceAll("\\(", "").replaceAll("\\)", "").replaceAll("\'", "").replaceAll("\\\\", "");
        Suffix.Anhängen("('" +s +"','"+p+"','"+ o+"'),");
        zählen++;
      }
      // Vollständiges SQL erstellen
      Zeichenfolge allsql = sql + suffix.substring(0, suffix.length() - 1);
      //Ausführungs-SQL hinzufügen
      psts.addBatch(allsql);
      psts.executeBatch(); // Stapelverarbeitung ausführen conn.commit(); // Commit } catch (Exception e) {
      e.printStackTrace();
    }Endlich{
      wenn(psts!=null){
        psts.schließen();
      }
      wenn(conn!=null){
        Verbindung schließen();
      }
    }
  }

Der Vorteil dieser Methode liegt darin, dass der Datenimport sehr schnell geht. Für den Import von 65 Millionen Datensätzen wurde genau eine Stunde benötigt. Der Nachteil besteht darin, dass bei langen Sätzen in den Daten die darin enthaltenen Kommas, Klammern, Backslashs usw. verarbeitet werden müssen. Hier muss abgewogen werden, ob diese Methode verwendet werden soll.

Wenn Sie Daten normal einfügen, also die Form „insert into tb (...) values(...);insert into tb (...) values(...);...“ verwenden, müssen Sie sich nicht mit Sonderzeichen befassen, aber es wird lange dauern. Ich habe es getestet und es hat ungefähr 12 Minuten gedauert, um 110.000 Datensätze zu importieren, und ungefähr 100 Stunden, um 65 Millionen Datensätze zu importieren.

Wir verwenden die erste Methode, bei der nur eine grobe Überprüfung der Daten erforderlich ist und keine strengen Anforderungen an die Daten gestellt werden, wodurch Zeit gespart wird.

Oben sind die Probleme aufgeführt, die beim Importieren großer Datenmengen in MySQL auftraten, und die Lösungen, die mir einfielen. Wenn Sie eine bessere Lösung haben oder auf andere Probleme stoßen, hoffe ich, diese gemeinsam zu besprechen.