MySQL에서 벌크 인서트(Bulk Insert): 대량 데이터를 효율적으로 삽입하는 방법

MySQL에서 벌크 인서트(Bulk Insert)는 대량의 데이터를 데이터베이스 테이블에 삽입할 때 사용하는 효율적인 방법입니다. 일반적인 단일 INSERT 문에 비해 성능이 뛰어나며, 특히 대규모 데이터 작업이 필요한 상황에서 매우 유용합니다. 이번 글에서는 MySQL에서 벌크 인서트의 개념, 사용 방법, 장단점, 그리고 최적화 팁을 자세히 알아보겠습니다.

벌크 인서트란?

벌크 인서트는 한 번의 SQL 명령으로 여러 행의 데이터를 테이블에 삽입하는 기법입니다. 일반적인 단일 INSERT 문을 여러 번 실행하는 대신, 한 번의 명령으로 다수의 행을 처리하여 성능을 극대화할 수 있습니다.

예시: 단일 INSERT 문

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25);
INSERT INTO users (id, name, age) VALUES (2, 'Bob', 30);
INSERT INTO users (id, name, age) VALUES (3, 'Charlie', 35);

벌크 인서트

INSERT INTO users (id, name, age) 
VALUES 
  (1, 'Alice', 25), 
  (2, 'Bob', 30), 
  (3, 'Charlie', 35);

벌크 인서트의 장점

1. 성능 향상

• 여러 INSERT 문을 개별적으로 실행하면 각 INSERT마다 네트워크와 디스크 I/O가 발생합니다.

• 벌크 인서트는 이러한 작업을 한 번에 처리하여 I/O 및 네트워크 오버헤드를 줄입니다.

2. 코드 간결성

• 여러 행을 삽입하는 코드를 간결하고 읽기 쉽게 작성할 수 있습니다.

3. 트랜잭션 처리 간소화

• 한 번의 트랜잭션으로 다수의 데이터를 처리하므로 트랜잭션 관리가 간단해집니다.

대용량 데이터 삽입을 위한 최적화 팁

1. 트랜잭션 사용

• 벌크 인서트를 트랜잭션으로 묶으면 삽입 속도가 크게 향상됩니다.

START TRANSACTION;
INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25), (2, 'Bob', 30), (3, 'Charlie', 35);
COMMIT;

2. AUTO_INCREMENT 컬럼 주의

• AUTO_INCREMENT 컬럼이 있는 경우, 해당 컬럼 값을 명시하지 않으면 MySQL이 자동으로 증가된 값을 삽입합니다.

INSERT INTO users (name, age) 
VALUES 
  ('Alice', 25), 
  ('Bob', 30), 
  ('Charlie', 35);

3. 테이블 잠금 활용

• 대규모 데이터를 삽입할 때 테이블 잠금을 사용하면 삽입 속도가 향상될 수 있습니다.

ALTER TABLE users DISABLE KEYS;
INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25), (2, 'Bob', 30);
ALTER TABLE users ENABLE KEYS;

4. 인덱스 비활성화

• 대량 삽입 전에 인덱스를 비활성화하면 성능이 향상됩니다.

ALTER TABLE users DISABLE KEYS;
INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25), (2, 'Bob', 30);
ALTER TABLE users ENABLE KEYS;

5. 배치 크기 조절

• MySQL은 한 번의 INSERT 문에서 처리할 수 있는 데이터 크기에 제한이 있으므로, 대량 데이터를 삽입할 때는 적절한 배치 크기로 나누어 실행합니다.

• 예: 1000개의 행씩 나누어 삽입.

6. LOAD DATA INFILE 사용

• 벌크 인서트보다 더 빠른 성능을 원한다면 LOAD DATA INFILE 명령을 사용할 수 있습니다.

LOAD DATA INFILE '/path/to/data.csv' 
INTO TABLE users 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
(id, name, age);

벌크 인서트의 단점 및 주의점

1. 메모리 사용량 증가

• 한 번에 많은 데이터를 삽입하면 메모리 사용량이 증가할 수 있습니다. 데이터 크기를 적절히 조절해야 합니다.

2. 충돌 발생 가능성

• 중복 키 또는 제약 조건 위반 시 전체 INSERT 문이 실패할 수 있습니다. 이를 방지하려면 INSERT IGNORE 또는 ON DUPLICATE KEY UPDATE를 사용합니다.

INSERT INTO users (id, name, age) 
VALUES (1, 'Alice', 25), (1, 'Bob', 30) 
ON DUPLICATE KEY UPDATE name=VALUES(name), age=VALUES(age);

3. 트랜잭션 크기 관리

• 트랜잭션이 너무 커지면 성능 저하나 락 경쟁이 발생할 수 있으므로 배치 크기를 조정해야 합니다.

벌크 인서트의 활용 사례

1. 데이터 마이그레이션

• 기존 데이터베이스에서 새로운 데이터베이스로 데이터를 이관할 때 사용합니다.

2. 로그 데이터 삽입

• 대량의 로그 데이터를 수집하고 저장할 때 효율적입니다.

3. ETL 작업

• 데이터 웨어하우스로 데이터를 로드할 때 대규모 데이터 삽입 작업에 유용합니다.

4. 백업 및 복원

• 대량 데이터를 백업 후 복원하는 과정에서 사용됩니다.

MySQL에서 벌크 인서트는 대량 데이터를 효율적으로 삽입하기 위한 강력한 방법입니다. 성능 향상, 코드 간소화, 트랜잭션 처리 등 다양한 장점을 제공하며, 대량 데이터 작업에서 필수적인 기술로 활용됩니다. 그러나 데이터 크기, 트랜잭션 관리, 제약 조건 등의 단점을 고려하여 적절히 최적화하면 더욱 효율적인 데이터 삽입 작업을 수행할 수 있습니다.