Bulk Insert - Insert 쿼리 최적화

oracle 이나 MySql과 같은 개발환경을 사용하여 개발을 했을때  다량의 데이터를 생성해야 될 때가 있다. Bulk Insert는 편하고 빠르게 다수의 건의 데이터를 넣을때 사용된다. 예시와 함께 MySql을 기준으로 설명을 해보겠다.

 

1. 테이블 생성

CREATE TABLE BULK_INSERT_SAMPLE (A int, B int, C int);

 

insert 구문을 한건씩 사용하게 된다면 생성이 필요한 개수만큼 쿼리를 실행하게 된다. 

하지만 쿼리를 반복하는 만큼 실행하는 것은 자원을 많이 소모하는 작업이며, 작업시간도 조금 더 소요될 수도 있다. 또 도중 Connection에 문제가 생긴다면 그에 따른 문제가 생길 수도 있다. 

insert into BULK_INSERT_SAMPLE VALUES(1,1,1);
insert into BULK_INSERT_SAMPLE VALUES(2,2,2);
insert into BULK_INSERT_SAMPLE VALUES(3,3,3);
insert into BULK_INSERT_SAMPLE VALUES(4,4,4);
                    .
                    .
                    .
insert into BULK_INSERT_SAMPLE VALUES(10000,10000,10000);

 

하지만 Bulk Insert를 사용하는 경우 다음과 같이 Insert가 가능하다.

주의할 점은 Bulk Insert중 한건이라도 오류가 난다면 전체건에 대한 INSERT가 ROLLBACK 된다.

INSERT INTO BULK_INSERT_SAMPLE (A, B, C) VALUES
(1,1,1),
(2,2,2),
(3,3,3),
   .
   .
   .
(10000,10000,10000);

벌크연산시 성능이 향상되는 이유

- DB의 다양한 요소들로 인해 쿼리한번이 이루어질 때, 그 전후로 이루어지는 작업이 생각보다 있다. Transaction을 실행하고 Index를 검사하고 실행계획을 세우는 등의 과정을 그 예로 들 수 있다. 그래서 DB에 1개의 데이터를 넣을때 N, 쿼리 전후에 M이라는 값이 소모된다고 해보자. 그럼 1,000,000개의 튜플을 INSERT 한다고 했을때 소모값은 다음과 같을것이다.

(1,000,000 * N + 1,000,000 * M)

하지만 쿼리하나에 1,000,000개의 튜플을 1000개씩 끊어서 INSERT한다면 그 소모값은 다음과 같게 될 것이다. 

(1,000,000 * N + 1,000 * M)

1건씩 넣을때와 속도차이가 거의 반정도 줄을 것이다.

여기서 더 속도를 줄이고 싶으면, INSERT 실행중 INDEXING 처리를 비활성화 하고 INSERT 작업 완료후 INDEXING 처리를 하면 1개씩 INDEXING 처리하는 비효율을 줄일 수 있다. 

ALTER TABLE BULK_INSERT_SAMPLE DISABLE KEYS;
INSERT INTO BULK_INSERT_SAMPLE VALUES (1,1,1);
....
COMMIT;
ALTER TABLE BULK_INSERT_SAMPLE ENABLE KEYS;

 

상대적으로 부담을 덜 준다는 이점때문에 마이크로서비스 환경에서 다른 서버에 부하를 주지 않기 위해서 사용되는 경우도 있다.

 

 

참고

mysql 대량 Insert의 속도 개선을 위한 다양한 방법

https://sailer.tistory.com/entry/Mysql-%EB%8C%80%EB%9F%89-INSERT%EC%9D%98-%EC%86%8D%EB%8F%84-%EA%B0%9C%EC%84%A0%EC%9D%84-%EC%9C%84%ED%95%9C-%EB%8B%A4%EC%96%91%ED%95%9C-%EB%B0%A9%EB%B2%95

bulk inserting - mysql 다량의 데이터 넣기

https://dev.dwer.kr/2020/04/mysql-bulk-inserting.html