MYSQL でのデータのバッチ挿入を最適化する方法の紹介

SQL の前処理やバッチ送信など、他の方法もインターネット上でいくつか見てきました。では、これらのメソッドはどのように実行されるのでしょうか?この記事では、これらの方法を比較します

1. どのような問題が発生しましたか

標準 SQL では、通常、次の SQL 挿入ステートメントを作成します。

INSERT INTO TBL_TEST (id) VALUES(1);

明らかに、この方法は MYSQL でも実行可能です。しかし、バッチでデータを挿入する必要がある場合、そのようなステートメントはパフォーマンスの問題を引き起こす可能性があります。たとえば、100,000 個のデータを挿入する必要がある場合、実際の挿入作業を行うためにストレージ エンジンに到達する前に、解析と最適化のために各ステートメントをリレーショナル エンジンに送信する必要があります。

公式の MYSQL ドキュメントでもバッチ挿入の使用、つまり INSERT ステートメントに複数の値を挿入することについて言及しているのは、まさにパフォーマンスのボトルネック問題のためです。つまり、

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

このアプローチにより、確かにバッチ挿入が高速化されます。その理由を理解するのは難しくありません。最も重要なのは、解析とネットワーク負荷の軽減です。最適化にかかる時間は増加しているように見えますが、実際には影響を受けるデータ行の数はさらに多くなります。したがって、全体的なパフォーマンスが向上します。インターネット上のいくつかの意見によると、この方法は何十回も改善できるとのことです。

ただし、SQL の前処理やバッチ送信など、他の方法もインターネット上でいくつか見かけました。では、これらのメソッドはどのように実行されるのでしょうか?この記事では、これらの方法を比較します。

2. 環境と方法の比較
私の環境は比較的難しく、基本的には後方仮想マシンです。コアは 2 つ、メモリは 6G しかありません。オペレーティング システムはSUSI Linuxで、MYSQLのバージョンは5.6.15です。

このマシンのパフォーマンスが私の TPS を非常に低くしたに違いないと想像できるため、以下のデータはすべて無意味ですが、傾向は異なり、挿入全体のパフォーマンス傾向を示すことができます。

ビジネスの特性により、使用するテーブルは非常に大きく、合計 195 個のフィールドがあり、いっぱいの場合 (varchar を含む各フィールドが入力される)、サイズは 1 KB よりわずかに小さくなります。レコードのサイズも3KBです。

実際の経験に基づいて、1 つのトランザクションで多数の INSERT ステートメントを送信することでパフォーマンスが大幅に向上すると確信しているからです。したがって、以下のすべてのテストは、挿入される 5,000 レコードごとに送信するという慣行に基づいています。

最後に、以下のすべてのテストは MYSQL C API を使用して実行され、INNODB ストレージ エンジンを使用することに注意してください。

3. メソッドの比較

理想的なテスト (1) - メソッドの比較

目的: 理想的な状況下での最適な挿入メカニズムを見つけるため

主要なメソッド:

1. 各エントリ/スレッドを押します。キーの順次挿入

2. さまざまな挿入方法を比較する

3. 挿入に対するさまざまな入力/スレッド数の影響を比較する

* 「通常の方法」とは、INSERT が 1 つの VALUE のみを挿入する状況を指します。

* 「前処理された SQL」とは、前処理された MYSQL C API の使用を指します。

* 「複数テーブル値SQL(10レコード)」とは、INSERT文を使用して10レコードを挿入する状況です。なぜ 10 なのか?後で検証したところ、これが最もパフォーマンスが高いことがわかりました。

結論、3つの手法の傾向から判断すると、明らかに複数テーブル値SQL(10項目)手法が最も効率的です。

理想的なテスト (2) - マルチテーブル値の SQL エントリ数の比較

データ量が増加するにつれて、INSERT ステートメントごとに 10 レコードを挿入するのが最も効率的であることは明らかです。

理想テスト(3) - 接続数の比較

結論: 接続数と操作数がCPUコア数の2倍の場合にパフォーマンスが最も高くなる

一般テスト-私たちによると、ビジネスボリュームをテストします

目的: 最適な挿入メカニズムは通常のトランザクション状況に適していますか?

主要なメソッド:

1. 運用データをシミュレートします (各レコードは約 3KB)

2. 各スレッドに主キーを順不同で挿入します

明らかに、それが順不同で挿入される場合は、主キーの場合、パフォーマンスは直線的に低下します。これは、実際には、INNODB の内部実装原理に示されている現象と一致しています。ただし、複数テーブル値 SQL (10 エントリ) の場合が最適であることは確かです。

ストレステスト

目的: 極端な取引状況に最適な挿入メカニズム?

キーメソッド:

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;  
}

以上がMYSQL でのデータのバッチ挿入を最適化する方法の紹介の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。