MYSQL에서 데이터 일괄 삽입을 최적화하는 방법 소개

인터넷에서 SQL 전처리, 일괄 제출 등 여러 가지 다른 방법도 보았습니다. 그렇다면 이러한 방법은 어떻게 수행됩니까? 이 기사에서는 이러한 방법을 비교해 보겠습니다

1. 어떤 문제가 발생했습니까?

표준 SQL에서는 일반적으로 다음과 같은 SQL 삽입 문을 작성합니다.

INSERT INTO TBL_TEST (id) VALUES(1);

분명히 이 방법은 MYSQL에서도 가능합니다. 그러나 일괄적으로 데이터를 삽입해야 하는 경우 이러한 명령문은 성능 문제를 일으킬 수 있습니다. 예를 들어, 100,000개의 데이터를 삽입해야 한다면 100,000개의 삽입 문이 필요합니다. 각 문은 실제 삽입 작업을 수행하기 위해 스토리지 엔진에 도달하기 전에 구문 분석 및 최적화를 위해 관계형 엔진에 제출되어야 합니다.

공식 MYSQL 문서에서도 일괄 삽입, 즉 INSERT 문에 여러 값을 삽입하는 사용을 언급하는 것은 바로 성능 병목 현상 문제 때문입니다. 즉,

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

이 방법을 사용하면 실제로 일괄 삽입 속도를 높일 수 있습니다. 그 이유는 서버에 제출되는 INSERT 문이 적기 때문에 가장 중요한 것은 구문 분석과입니다. 최적화 시간이 늘어나는 것 같지만 실제로는 사용되는 데이터 행 수가 훨씬 더 많습니다. 따라서 전반적인 성능이 향상됩니다. 인터넷의 일부 의견에 따르면 이 방법은 수십 번 개선될 수 있다고 합니다.

그러나 인터넷에서 SQL 전처리 및 일괄 제출과 같은 여러 가지 다른 방법도 보았습니다. 그렇다면 이러한 방법은 어떻게 수행됩니까? 이 기사에서는 이러한 방법을 비교해 보겠습니다.

2. 환경과 방법 비교
내 환경은 기본적으로 낙후된 가상 머신으로 상대적으로 어렵습니다. 코어는 2개, 메모리는 6G뿐입니다. 운영 체제는 SUSI Linux이고 MYSQL 버전은 5.6.15입니다.

이 기계의 성능으로 인해 TPS가 매우 낮아졌을 것이라고 상상할 수 있으므로 아래의 모든 데이터는 의미가 없지만 추세가 다르기 때문에 전체 삽입의 성능 추세를 보여줄 수 있습니다.

비즈니스 특성상 우리가 사용하는 테이블은 총 195개 필드로 매우 큽니다. 가득 차면(varchar를 포함하여 각 필드가 채워짐) 일반적으로 크기는 4KB보다 약간 작습니다. 레코드 크기도 3KB입니다.

실제 경험을 바탕으로 한 트랜잭션에서 다수의 INSERT 문을 제출하면 성능이 크게 향상될 수 있다고 확신합니다. 따라서 아래의 모든 테스트는 삽입된 레코드 5,000개마다 제출하는 관행을 기반으로 합니다.

마지막으로 아래의 모든 테스트는 MYSQL C API를 사용하여 수행되었으며 INNODB 스토리지 엔진을 사용한다는 점에 유의해야 합니다.

3. 방법 비교

이상적인 테스트(1) - 방법 비교

목적: 이상적인 상황에서 가장 적합한 삽입 메커니즘을 찾는 것

주요 방법:

1. 키 순차 삽입

2. 다양한 삽입 방법 비교

3. 다양한 입력/스레드 수가 삽입에 미치는 영향 비교

* "일반적인 방법"은 INSERT가 하나의 값만 삽입하는 상황을 의미합니다.

* "전처리된 SQL"은 전처리된 MYSQL C API를 사용하는 것을 의미합니다.

* "다중 테이블 값 SQL(10개 레코드)"은 INSERT 문을 사용하여 10개의 레코드가 삽입되는 상황입니다. 왜 10입니까? 나중에 확인하면 이것이 가장 높은 성능을 발휘한다는 것을 알 수 있습니다.

결론적으로 세 가지 방법의 추세로 볼 때 당연히 다중 테이블 값 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 중국어 웹사이트의 기타 관련 기사를 참조하세요!