MySQL에서 multi row를 insert 하는 방법

대량으로 insert를 수행하다 보면 성능을 고려하게 된다.

MySQL에서는 multi row을 어떻게 빠르게 insert 할 수 있을까?

1. One Transaction 처리
2. Insert ~ Select
3. Bulk Insert
4. LOAD DATA INFILE

구글링을 해 보니 4가지로 요약되는 것 같다.

One Transaction 처리

start transaction; insert into tb_name (a, b, c) values (1, 2, 3); insert into tb_name (a, b, c) values (1, 2, 3); insert into tb_name (a, b, c) values (1, 2, 3); commit transaction;

하나의 트랜젝션으로 묶어서 처리하는 방식이다. SQL로 표현했지만 ibatis 에서 executeBatch() 정도가 될 수 있을 것이다. 이렇게 할 경우 동일 세션을 사용하기 때문에 조금의 성능적 이점을 가질 수 있다.(크게 표나지 않음)

Insert ~ Select

insert into tb_name (a, b, c) select d, e, f from target_tb where {condition}...

SELECT 쿼리를 통해서 여러 row를 한 번에 insert 하는 방법이다. 이 경우 select 되는 대상을 고정시켜야 하기 때문에 READ_LOCK이 잡혀야 한다. 성능적으로는 One Transaction 보다는 빠르고 SQL 도 간단하다.

Bulk Insert

insert into tb_name (a, b, c) values (1, 2, 3), (4, 5, 6), (7, 8, 9);

multi values를 통해 insert 하는 방식이다.(MySQL에서는 Bulk insert 라고 부른다.)

3개의 row가 기본적으로 하나의 transaction에서 처리가 된다. 즉, 3개 중 하나라도 필수값을 지키지 않는다거나 하는 경우 모두 rollback이 됨을 의미한다.

The beauty of this lies in its ability to insert multiple records into MySQL in a single insert statement which is by default in a single transaction, and as such inserting these records either succeed or fail all together.

출처: http://www.geeksengine.com/database/data-manipulation/bulk-insert.php

추가 궁금증.

values 에 몇 개의 row 까지 작성이 가능할까?

MySQL은 client에서 server로 statement를 보낼 때 size 제한이 있다. statement 를 mysqld 로 전송할 때, packet으로 보내게 되는데 그 크기가 max_allowed_packet 보다 큰 경우 오류가 발생한다. (설정 값은 아래와 같이 확인 할 수 있다.)

show variables like ‘max_allowed_packet’;

기본값은 1048576 bytes (1MB)이며, 아래와 같이 설정이 가능하다.

set global max_allowed_packet=1073741824

1G로 설정한 경우이다. 이렇게 하면 서버가 재시작되면 다시 1MB로 초기화 되는데, mysqld 에 설정하여 계속 유지될 수 있도록 할 수 도 있다.(방법은 구글링 해보자)

주의사항.

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

insert 하고자 하는 테이블에 auto-increment 컬럼이 존재한다면 위 링크를 한 번 읽어보도록 하자. auto-increment lock 에 관련된 내용이다.

LOAD DATA INFILE

https://dev.mysql.com/doc/refman/5.7/en/load-data.html

데이터 파일을 로딩하여 테이블을 생성하면서 insert 하는 방식을 의미한다. DBA 또는 관리자가 사용할 수 있는 방법인 듯 하다. 웹 애플리케이션에서는 사용할 수 없다.(권장하지 않음)

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

결론

성능과 사용성 측면에서 보면 bulk insert 가 가장 나아 보인다. insert ~ select 도 괜찮아 보이긴 하나 READ_LOCK 처리가 잘 되어야 할 것이다.

---

참고

• https://stackoverflow.com/questions/34447305/innodb-bulk-insert-using-transaction-or-combine-multiple-queries
• http://www.geeksengine.com/database/data-manipulation/bulk-insert.php