Eine Einführung in die Optimierung der Stapeleinfügung von Daten in MYSQL

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

1 Auf welche Probleme sind wir gestoßen

In Standard-SQL schreiben wir normalerweise die folgende SQL-Einfügeanweisung.

INSERT INTO TBL_TEST (id) VALUES(1);

Natürlich ist diese Methode auch in MYSQL machbar. Wenn wir jedoch Daten stapelweise einfügen müssen, führen solche Anweisungen zu Leistungsproblemen. Wenn Sie beispielsweise 100.000 Daten einfügen müssen, benötigen Sie 100.000 Einfügeanweisungen. Jede Anweisung muss zur Analyse und Optimierung an die relationale Engine übermittelt werden, bevor sie die Speicher-Engine erreicht, um die eigentliche Einfügungsarbeit durchzuführen.

Gerade aufgrund des Leistungsengpassproblems wird in der offiziellen MYSQL-Dokumentation auch die Verwendung von Batch-Einfügungen erwähnt, also das Einfügen mehrerer Werte in eine INSERT-Anweisung. Das heißt,

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

Dieser Ansatz kann tatsächlich die Stapeleinfügung beschleunigen. Der Grund ist nicht schwer zu verstehen, da weniger INSERT-Anweisungen an den Server übermittelt werden Die Netzwerklast wird reduziert. Das Wichtigste ist, dass sich die Zeit für das Parsen und Optimieren zu erhöhen scheint, aber tatsächlich werden mehr Datenzeilen verwendet. Dadurch wird die Gesamtleistung verbessert. Einigen Meinungen im Internet zufolge kann diese Methode Dutzende Male verbessert werden.

Ich habe jedoch auch mehrere andere Methoden im Internet gesehen, wie zum Beispiel die Vorverarbeitung von SQL und die Stapelübermittlung. Wie funktionieren diese Methoden? In diesem Artikel werden diese Methoden verglichen.

2. Vergleich von Umgebungen und Methoden
Meine Umgebung ist relativ schwierig, im Grunde eine rückständige virtuelle Maschine. Es gibt nur 2 Kerne und 6G Speicher. Das Betriebssystem ist SUSI Linux und die MYSQL-Version ist 5.6.15.

Wie Sie sich vorstellen können, hat die Leistung dieser Maschine dazu geführt, dass mein TPS sehr niedrig ist, daher sind alle folgenden Daten bedeutungslos, aber der Trend ist anders, was den Leistungstrend der gesamten Einfügung zeigen kann.

Aus geschäftlichen Gründen ist die von uns verwendete Tabelle mit insgesamt 195 Feldern sehr groß. Wenn sie voll ist (jedes Feld ist ausgefüllt, einschließlich Varchar), beträgt die Größe etwas weniger als 4 KB Im Allgemeinen beträgt die Größe eines Datensatzes ebenfalls 3 KB.

Denn aufgrund unserer tatsächlichen Erfahrung sind wir ziemlich sicher, dass die Leistung durch die Übermittlung einer großen Anzahl von INSERT-Anweisungen in einer Transaktion erheblich verbessert werden kann. Daher basieren alle folgenden Tests auf der Praxis, alle 5.000 eingefügten Datensätze einzureichen.

Abschließend sei darauf hingewiesen, dass alle folgenden Tests mit der MYSQL C-API durchgeführt werden und die INNODB-Speicher-Engine verwenden.

3. Methodenvergleich

Idealer Typtest (1) - Methodenvergleich

Zweck: herauszufinden, was am meisten ist unter idealen Umständen geeignet Einfügungsmechanismus

Schlüsselmethoden:

1. Jeder Thread/jeder Thread wird in der Reihenfolge des Primärschlüssels eingefügt

2. Vergleichen Sie verschiedene Einfügungsmethoden

3. Vergleichen Sie die Auswirkung einer unterschiedlichen Anzahl von Eingaben/Threads auf das Einfügen

* „Gewöhnliche Methode“ bezieht sich auf die Situation, in der ein INSERT nur einen WERT einfügt.

* „Vorverarbeitetes SQL“ bezieht sich auf die Verwendung der vorverarbeiteten MYSQL-C-API.

* „Mehrere Tabellenwerte SQL (10 Datensätze)“ ist eine Situation, in der 10 Datensätze mithilfe einer INSERT-Anweisung eingefügt werden. Warum 10? Eine spätere Überprüfung zeigt uns, dass dies die höchste Leistung bietet.

Fazit: Gemessen an den Trends der drei Methoden ist die SQL-Methode mit mehreren Tabellenwerten (10 Elemente) offensichtlich die effizienteste.

Idealer Test (2) – Vergleich der Anzahl der SQL-Einträge mit mehreren Tabellenwerten

Natürlich als Menge Die Datenmenge nimmt zu. In diesem Fall ist es am effizientesten, 10 Datensätze für jede INSERT-Anweisung einzufügen.

Idealtest (3) - Anschlussnummernvergleich

Fazit: Die Leistung stimmt am höchsten bei Verbindung und Betrieb mit der 2-fachen Anzahl an CPU-Kernen

Allgemeiner Test – Test basierend auf unserem Geschäftsvolumen

Zweck: Ist der beste Einfügemechanismus für normale Transaktionssituationen geeignet?

Schlüsselmethoden:

1. Produktionsdaten simulieren (jeder Datensatz ist etwa 3 KB groß)

2 Reihenfolge

Wenn die Einfügung basierend auf dem Primärschlüssel in einer anderen Reihenfolge erfolgt, sinkt die Leistung natürlich. Dies steht tatsächlich im Einklang mit dem Phänomen, das im internen Implementierungsprinzip von INNODB gezeigt wird. Es ist jedoch immer noch sicher, dass der Fall von SQL mit mehreren Tabellenwerten (10 Einträge) optimal ist.

Stresstest

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

Schlüsselmethoden:

1. 将数据行的每一个字段填满（每条记录约为4KB）

2. 每个线程主键乱序插入

结果和我们之前的规律类似，性能出现了极端下降。并且这里验证了随着记录的增大（可能已经超过了一个page的大小，毕竟还有slot和page head信息占据空间），会有page split等现象，性能会下降。

四、结论

根据上面的测试，以及我们对INNODB的了解，我们可以得到如下的结论。

•采用顺序主键策略（例如自增主键，或者修改业务逻辑，让插入的记录尽可能顺序主键）

•采用多值表（10条）插入方式最为合适

•将进程/线程数控制在2倍CPU数目相对合适

五、附录

我发现网上很少有完整的针对MYSQL 预处理SQL语句的例子。这里给出一个简单的例子。

--建表语句
CREATE TABLE tbl_test 
(
  pri_key varchar(30), 
  nor_char char(30), 
  max_num DECIMAL(8,0), 
  long_num DECIMAL(12, 0), 
  rec_upd_ts TIMESTAMP
);

c代码

#include <string.h>
#include <iostream>
#include <mysql.h>
#include <sys/time.h>
#include <sstream>
#include <vector>
 
using namespace std;
 
#define STRING_LEN 30
  
char    pri_key            [STRING_LEN]= "123456"; 
char    nor_char           [STRING_LEN]= "abcabc"; 
char    rec_upd_ts          [STRING_LEN]= "NOW()"; 
 
bool SubTimeval(timeval &result, timeval &begin, timeval &end)
{
  if ( begin.tv_sec>end.tv_sec ) return false;
 
  if ( (begin.tv_sec == end.tv_sec) && (begin.tv_usec > end.tv_usec) )  
    return  false;
 
  result.tv_sec = ( end.tv_sec - begin.tv_sec );  
  result.tv_usec = ( end.tv_usec - begin.tv_usec );  
 
  if (result.tv_usec<0) {
    result.tv_sec--;
    result.tv_usec+=1000000;} 
  return true;
}
 
int main(int argc, char ** argv)
{
  INT32 ret = 0;
  char errmsg[200] = {0};
  int sqlCode = 0;
 
  timeval tBegin, tEnd, tDiff;
   
  const char* precompile_statment2 = "INSERT INTO `tbl_test`( pri_key, nor_char, max_num, long_num, rec_upd_ts) VALUES(?, ?, ?, ?, ?)";
   
  MYSQL conn;
  mysql_init(&conn);
   
  if (mysql_real_connect(&conn, "127.0.0.1", "dba", "abcdefg", "TESTDB", 3306, NULL, 0) == NULL)
  {
    fprintf(stderr, " mysql_real_connect, 2 failed\n");
    exit(0);
  }
   
  MYSQL_STMT  *stmt = mysql_stmt_init(&conn);
  if (!stmt)
  {
   fprintf(stderr, " mysql_stmt_init, 2 failed\n");
   fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
   exit(0);
  }
   
  if (mysql_stmt_prepare(stmt, precompile_statment2, strlen(precompile_statment2)))
  {
   fprintf(stderr, " mysql_stmt_prepare, 2 failed\n");
   fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
   exit(0);
  }
   
  int i = 0; 
  int max_num = 3;
  const int FIELD_NUM = 5;
  while (i < max_num)
  {
    //MYSQL_BIND  bind[196] = {0};
    MYSQL_BIND  bind[FIELD_NUM];
    memset(bind, 0, FIELD_NUM * sizeof(MYSQL_BIND));
   
    unsigned long 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;
     
    unsigned long 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.year= 2002;
    ts.month= 02;
    ts.day= 03;
    ts.hour= 10;
    ts.minute= 45;
    ts.second= 20;
     
    unsigned long 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;
     
    if (mysql_stmt_bind_param(stmt, bind))
    {
      fprintf(stderr, " mysql_stmt_bind_param, 2 failed\n");
      fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
      exit(0);
    }
     
    cout << "before execute\n";
    if (mysql_stmt_execute(stmt))
    {
     fprintf(stderr, " mysql_stmt_execute, 2 failed\n");
     fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
     exit(0);
    }
    cout << "after execute\n";
     
    i++;
  }
   
  mysql_commit(&conn);
   
  mysql_stmt_close(stmt);
 
  return 0;  
}

Das obige ist der detaillierte Inhalt vonEine Einführung in die Optimierung der Stapeleinfügung von Daten in MYSQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!