DB - Bulk Insert

실무를 진행하면서 데이터베이스에 대량으로 데이터를 입력이 필요한 상황이 올 수 있다. 이러한 상황에서 사용할 수 있는 방법은 어떤게 있을까?

 

가장먼저 생각이 나는 방법은, 반복문(for)을 통해서 넣어주는 방법이다.

INSERT INTO users(user_id, user_name, user_age) VALUES (1, '가나다', 30);
INSERT INTO users(user_id, user_name, user_age) VALUES (2, '라마바', 29);
INSERT INTO users(user_id, user_name, user_age) VALUES (3, '사아자', 25);
INSERT INTO users(user_id, user_name, user_age) VALUES (4, '차카타', 44);
INSERT INTO users(user_id, user_name, user_age) VALUES (5, '파하하', 61);
INSERT INTO users(user_id, user_name, user_age) VALUES (6, '나의이름', 55);

-- .......

레코드가 얼마 안된다면 하드웨어 성능이 안좋은 등의 여러가지 상황에서도 문제가 안될 것이다. 그러나 대량(100만건 그이상..)의 데이터를 입력해야 하는 상황이라면 어떨까? 성능상의 이슈가 발생할 것이다.

 

그럴때 사용하는 방법이 Bulk Insert 이다. VALUES 절에 콤마(,)를 사이에 넣어서 연속적으로 레코드 내용을 입력하는 것이다.

INSERT INTO
    users
    (user_id, user_name, user_age)
VALUES
    (1, '가나다', 30),
    (2, '라마바', 29),
    (3, '사아자', 25),
    (4, '차카타', 44),
    (5, '파하하', 61),
    (6, '나의이름', 55);

성능이 향상되는 이유는?

질의(SQL) 수행시, 트랜젝션 시작, 종료, Index 처리 등.. 전후로 발생하는 작업이 있다. 1000을 처리해야할 것들을 1번으로 줄인다고 하고 10,000,000건을 작업한다 했을때, (1000개씩 데이터를 Insert처리 한다고 가정) 1천만번 -> 1만번 으로 줄어드는 것이다.

Bulk Insert 중에는 최대한 다른작업을 덜하게 하는 것이 중요하다.

1] Auto Commit -> Insert 작업이 끝나고 commit을 할 수 있도록 한다. (autocommit을 풀어준다)

2] Index 조정은 Bulk Insert가 끝나고 이루어지는게 훨씬 빠르다.

3] 입력할 데이터가 신뢰할 수 있는 데이터라면, UNIQUE나 FK 검사 등은 꺼두는 것이 좋다.

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

-- Bulk Insert

commit;
SET autocommit=1;
SET unique_checks=1;
SET foreign_key_checks=1;

참고자료

= http://www.tcpschool.com/mysql/mysql_basic_insert

= https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-bulk-data-loading.html