집 >데이터 베이스 >MySQL 튜토리얼 >MYSQL에서 데이터 일괄 삽입을 최적화하는 방법 소개
MYSQL에서 데이터 일괄 삽입을 최적화하는 방법 소개
- 黄舟원래의
- 2017-07-18 15:22:582374검색
인터넷에서 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 중국어 웹사이트의 기타 관련 기사를 참조하세요!