MySQL-Entwicklungsleistungsforschung: Optimierungsmethode zum Batch-Einfügen von Daten

1. Auf welche Probleme sind wir gestoßen?

In Standard-SQL schreiben wir normalerweise die folgende SQL-Insert-Anweisung.

EINFÜGEN IN TBL_TEST (id) WERTE(1);

Natürlich ist dieser Ansatz auch in MySQL möglich. Wenn wir jedoch Daten in Stapeln einfügen müssen, verursachen solche Anweisungen Leistungsprobleme. Wenn Sie beispielsweise 100.000 Daten einfügen müssen, benötigen Sie 100.000 Einfügeanweisungen, von denen jede zur Analyse und Optimierung an die relationale Engine übermittelt werden muss, bevor sie die Speicher-Engine erreichen kann, um die eigentliche Einfügearbeit auszuführen.

Gerade wegen des Leistungsengpassproblems wird in der offiziellen MySQL-Dokumentation die Verwendung der Batch-Einfügung erwähnt, dh das Einfügen mehrerer Werte in eine INSERT-Anweisung. Im Augenblick,

INSERT INTO TBL_TEST (id) WERTE (1), (2), (3)

Dieser Ansatz kann tatsächlich dazu beitragen, das Einfügen von Batches zu beschleunigen. Der Grund dafür ist nicht schwer zu verstehen. Da weniger INSERT-Anweisungen an den Server gesendet werden, wird die Netzwerklast reduziert. Das Wichtigste ist, dass die Analyse- und Optimierungszeit scheinbar zunimmt, aber tatsächlich ist die Anzahl der betroffenen Datenzeilen viel größer. Dadurch wird die Gesamtleistung verbessert. Einigen Behauptungen im Internet zufolge kann diese Methode die Effizienz um ein Dutzend Mal steigern.

Ich habe im Internet jedoch auch mehrere andere Methoden gesehen, wie z. B. die Vorverarbeitung von SQL und die Stapelübermittlung. Wie ist also die Leistung dieser Methoden? In diesem Artikel werden diese Methoden verglichen.

2. Vergleich von Umgebung und Methoden
Meine Umgebung ist ziemlich dürftig, im Grunde ist es nur eine rückwärtsgerichtete virtuelle Maschine. Es hat nur 2 Kerne und 6 GB Speicher. Das Betriebssystem ist SUSI Linux und die MySQL-Version ist 5.6.15.

Wie Sie sich vorstellen können, war mein TPS aufgrund der Leistung dieser Maschine sehr niedrig, sodass alle folgenden Daten bedeutungslos sind. Die Tendenz ist jedoch anders und kann die Leistungsentwicklung der gesamten Einfügung zeigen.

Aufgrund der Eigenschaften unseres Geschäfts ist die von uns verwendete Tabelle sehr groß und enthält insgesamt 195 Felder. Wenn sie vollständig geschrieben ist (jedes Feld ist vollständig ausgefüllt, einschließlich varchar), ist sie etwas weniger als 4 KB groß. Im Allgemeinen beträgt die Größe eines Datensatzes 3 KB.

Aufgrund unserer tatsächlichen Erfahrung sind wir sicher, dass durch die Übermittlung einer großen Anzahl von INSERT-Anweisungen in einer einzigen Transaktion die Leistung erheblich verbessert werden kann. Daher basieren alle folgenden Tests auf der Praxis, jedes Mal ein Commit durchzuführen, wenn 5.000 Datensätze eingefügt werden.

Abschließend ist anzumerken, dass alle folgenden Tests mit der MYSQL C-API durchgeführt wurden und die Speicher-Engine INNODB verwendet wurde.

3. Vergleichsmethode

Idealtypprüfung (I) - Vergleich der Verfahren

Zweck: Finden Sie den am besten geeigneten Einfügemechanismus unter idealen Umständen

Wichtige Methoden:

1. Jeder Prozess/Thread fügt in der Reihenfolge des Primärschlüssels ein

2. Vergleich verschiedener Insertionsmethoden

3. Vergleichen Sie die Auswirkungen unterschiedlicher Anzahl von Prozessen/Threads auf das Einfügen

*„Normale Methode“ bezieht sich auf den Fall, in dem eine INSERT-Anweisung nur einen WERT einfügt.

* „Vorbereitetes SQL“ bezieht sich auf den Fall, in dem die vorverarbeitete MYSQL C-API verwendet wird.

* „Multi-Table-Value-SQL (10 Datensätze)“ bedeutet das Einfügen von 10 Datensätzen mit einer INSERT-Anweisung. Warum 10? Die anschließende Überprüfung zeigt, dass diese Methode die höchste Leistung aufweist.

Fazit: Aus der Tendenz der drei Methoden geht klar hervor, dass die SQL-Methode mit mehreren Tabellenwerten (10 Elemente) die effizienteste ist.

Idealtest (II) - Vergleich der Anzahl von SQL-Anweisungen bei mehreren Tabellenwerten

Wenn die Datenmenge zunimmt, ist das Einfügen von 10 Datensätzen pro INSERT-Anweisung natürlich der effizienteste Ansatz.

Idealtest (III) - Vergleich der Verbindungszahlen

Fazit: Die Leistung ist am höchsten, wenn die Anzahl der Verbindungen und Operationen doppelt so hoch ist wie die Anzahl der CPU-Kerne

Allgemeine Tests - Tests basierend auf unserem Geschäftsvolumen

Zweck: Was ist der beste Einfügemechanismus für gängige Transaktionssituationen?

Wichtige Methoden:

1. Produktionsdaten simulieren (ca. 3 KB pro Datensatz)

2. Jeder Thread fügt den Primärschlüssel in der falschen Reihenfolge ein

Wenn die Daten in der falschen Reihenfolge gemäß dem Primärschlüssel eingefügt werden, sinkt die Leistung offensichtlich stark. Dies steht tatsächlich im Einklang mit dem Phänomen, das das interne Implementierungsprinzip von INNODB zeigt. Es steht jedoch weiterhin fest, dass der Fall von SQL mit mehreren Tabellenwerten (10 Elemente) der beste ist.

Stresstests

Zweck: Bester Einfügungsmechanismus für extreme Handelssituationen?

Wichtige Methoden:

1. Füllen Sie jedes Feld der Datenzeile aus (jeder Datensatz ist etwa 4 KB groß)

2. Jeder Thread fügt den Primärschlüssel in der falschen Reihenfolge ein

Die Ergebnisse ähneln unseren vorherigen Mustern, mit einem extremen Leistungsabfall. Und hier wird bestätigt, dass mit zunehmender Datensatzgröße (sie kann die Größe einer Seite überschreiten, schließlich belegen immer noch Slot- und Seitenkopfinformationen Speicherplatz) Seitenaufteilungen und andere Phänomene auftreten und die Leistung nachlässt.

IV. Fazit

Basierend auf den obigen Tests und unserem Verständnis von INNODB können wir die folgenden Schlussfolgerungen ziehen.

• Verwenden Sie eine sequenzielle Primärschlüsselstrategie (z. B. automatisch inkrementierende Primärschlüssel oder ändern Sie die Geschäftslogik, um eingefügte Datensätze so sequenziell wie möglich zu machen).

• Am besten eignet sich eine Multi-Value-Tabelle (10 Einträge) zum Einfügen

• Es ist relativ sinnvoll, die Anzahl der Prozesse/Threads auf das Zweifache der Anzahl der CPUs zu begrenzen

V. Anhang

Ich habe festgestellt, dass es im Internet nur sehr wenige vollständige Beispiele für die Vorverarbeitung von SQL-Anweisungen für MySQL gibt. Hier ist ein einfaches Beispiel.

--Tabellenanweisung erstellen CREATE TABLE tbl_test 
(
  pri_key varchar(30), 
  nor_char char(30), 
  max_num DECIMAL(8,0), 
  lange_Zahl DEZIMAL(12, 0), 
  rec_upd_ts ZEITSTEMPEL
);

C-Code

#include <string.h>
#include <iostream>
#include <mysql.h>
#include <sys/time.h>
#einschließen <sstream>
#include <Vektor>
 
Namespace std verwenden;
 
#define STRING_LEN 30
  
char pri_key[STRING_LEN]= "123456"; 
char nor_char [STRING_LEN] = "abcabc"; 
char rec_upd_ts [STRING_LEN] = "JETZT()"; 
 
bool SubTimeval(Zeitwert &Ergebnis, Zeitwert &Beginn, Zeitwert &Ende)
{
  wenn (beginn.tv_sec>end.tv_sec) false zurückgibt;
 
  wenn ( (begin.tv_sec == end.tv_sec) und (begin.tv_usec > end.tv_usec) )  
    gibt false zurück;
 
  Ergebnis.tv_sec = ( Ende.tv_sec – Beginn.tv_sec );  
  Ergebnis.tv_usec = (Ende.tv_usec – Beginn.tv_usec);  
 
  wenn (Ergebnis.tv_usec<0) {
    Ergebnis.tv_sec--;
    Ergebnis.tv_usec+=1000000;} 
  gibt true zurück;
}
 
int main(int argc, char ** argv)
{
  INT32 ret = 0;
  char errmsg[200] = {0};
  Int SQLCode = 0;
 
  Zeitwerte tBegin, tEnd, tDiff;
   
  const char* precompile_statment2 = "INSERT INTO `tbl_test`(pri_key, nor_char, max_num, long_num, rec_upd_ts) VALUES(?, ?, ?, ?, ?)";
   
  MySQL-Verbindung;
  mysql_init(&conn);
   
  wenn (mysql_real_connect(&conn, "127.0.0.1", "dba", "abcdefg", "TESTDB", 3306, NULL, 0) == NULL)
  {
    fprintf(stderr, "mysql_real_connect, 2 fehlgeschlagen\n");
    Ausfahrt (0);
  }
   
  MYSQL_STMT *stmt = mysql_stmt_init(&conn);
  wenn (!stmt)
  {
   fprintf(stderr, "mysql_stmt_init, 2 fehlgeschlagen\n");
   fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
   Ausfahrt (0);
  }
   
  wenn (mysql_stmt_prepare(stmt, precompile_statment2, strlen(precompile_statment2)))
  {
   fprintf(stderr, "mysql_stmt_prepare, 2 fehlgeschlagen\n");
   fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
   Ausfahrt (0);
  }
   
  } 
  int max_num = 3;
  const int FELD_NUM = 5;
  während (i < max_num)
  {
    //MYSQL_BIND bind[196] = {0};
    MYSQL_BIND bind[FELD_NUM];
    memset(binden, 0, FELD_NUM * Größe von(MYSQL_BIND));
   
    vorzeichenlose lange str_length = strlen(pri_key);
    bind[0].buffer_type = MYSQL_TYPE_STRING;
    bind[0].buffer = (char *)pri_key;
    bind[0].buffer_length = STRING_LEN;
    bind[0].is_null = 0;
    bind[0].length = &str_length;
     
    vorzeichenloser langer str_length_nor = strlen(nor_char);
    bind[1].buffer_type = MYSQL_TYPE_STRING;
    bind[1].buffer = (char *)nor_char;
    bind[1].buffer_length = STRING_LEN;
    bind[1].is_null = 0;
    bind[1].length = &str_length_nor;
     
    bind[2].buffer_type = MYSQL_TYPE_LONG;
    bind[2].buffer = (char*)&max_num;
    bind[2].is_null = 0;
    bind[2].length = 0;
     
    bind[3].buffer_type = MYSQL_TYPE_LONG;
    bind[3].buffer = (char*)&max_num;
    bind[3].is_null = 0;
    bind[3].length = 0;
     
    MYSQL_TIME ts;
    ts.Jahr = 2002;
    ts.Monat = 02;
    ts.Tag = 03;
    ts.Stunde = 10;
    ts.Minute = 45;
    ts.Sekunde = 20;
     
    vorzeichenlose lange str_length_time = strlen(rec_upd_ts);
    bind[4].buffer_type = MYSQL_TYPE_TIMESTAMP;
    bind[4].buffer = (char *)&ts;
    bind[4].is_null = 0;
    bind[4].length = 0;
     
    wenn (mysql_stmt_bind_param(stmt, bind))
    {
      fprintf(stderr, "mysql_stmt_bind_param, 2 fehlgeschlagen\n");
      fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
      Ausfahrt (0);
    }
     
    cout << "vor der Ausführung\n";
    wenn (mysql_stmt_execute(stmt))
    {
     fprintf(stderr, "mysql_stmt_execute, 2 fehlgeschlagen\n");
     fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
     Ausfahrt (0);
    }
    cout << "nach Ausführung\n";
     
    ich++;
  }
   
  mysql_commit(&conn);
   
  : mysql_stmt_close(stmt);
 
  gebe 0 zurück;  
}

Oben ist die Optimierungsmethode für das Einfügen von MySQL-Batchdaten beschrieben. Es wird empfohlen, dass Sie auch weitere frühere Artikel auf 123WORDPRESS.COM lesen.