글을 작성하게 된 계기
영중소 차액정산 프로젝트를 진행하며 MySQL LOAD DATA 를 사용해 수 천만 건의 데이터를 삽입 하는 작업을 했습니다. 이 과정에서 중복 데이터 삽입 방지 에 대해 고려하게 되었고, 알게된 방식을 정리하기 위해 글을 작성하게 되었습니다.
대용량 데이터의 정의는 사람마다 다를 수 있습니다. 저는 이번 프로젝트에서
RDB를 사용해약 3천만 건의 데이터를 핸들링 했는데, 평소 사용하던 방식으로는 처리가 불가능했기 때문에 대용량 이라는 표현을 사용했습니다.
1. LOAD DATA 옵션
대용량의 데이터를 삽입할 때, 중복 데이터 체크 는 중요합니다. 데이터가 삽입된 후, 데이터의 무결성을 검증하는 것 과 정합성이 깨졌을 때 바로 잡는 것 이 정말 힘들기 때문입니다. 따라서 처음부터 잘못된 데이터가 들어갈 수 없게 만드는 것 이 중요한데요, 이를 위해 LOAD DATA 를 사용할 때 적용할 수 있는 몇 가지 옵션을 살펴보겠습니다.
- IGNORE 옵션
- REPLACE 옵션
1-1. IGNORE 옵션
LOAD DATA LOCAL INFILE 명령어에 IGNORE를 붙이면, CSV 데이터를 테이블에 넣는 과정에서 중복 키(UNIQUE 제약조건이나 PRIMARY KEY) 충돌이 발생한 행은 그냥 건너뛰고, 나머지 행만 정상적으로 삽입합니다. 즉, 이미 테이블에 존재하는 데이터가 또 들어오더라도 에러를 발생시키지 않고 무시하며, 기존 데이터는 그대로 유지됩니다. 새로운 데이터 중에서 중복되지 않은 것만 추가되는 방식입니다.
1
2
3
4
5
LOAD DATA LOCAL INFILE 'data.csv'
IGNORE
INTO TABLE my_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
1-2. REPLACE 옵션
REPLACE 옵션을 붙이면 중복 키 충돌이 발생했을 때 기존 행을 삭제하고 새로 들어온 행으로 교체합니다. 즉, UNIQUE 제약조건을 가진 컬럼 값이 겹치면 기존 데이터를 버리고 새로운 데이터가 그 자리를 차지하게 됩니다. 이 과정은 실제로는 DELETE 후 INSERT와 동일하게 동작하므로, AUTO_INCREMENT 같은 값이 초기화될 수 있고, 외래키 제약조건이 있다면 주의해야 합니다.
1
2
3
4
5
LOAD DATA LOCAL INFILE 'data.csv'
REPLACE
INTO TABLE my_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
2. 임시 테이블 사용
추가로 임시 테이블 을 사용할 수도 있습니다. 운영중인 테이블에 바로 데이터를 삽입하면 중복 제약조건에 걸려 에러가 나거나, 삽입 속도가 느려지는 문제가 생길 수 있습니다. 따라서 운영 테이블과 동일한 구조를 가진 임시 테이블을 하나 만든 뒤, 외부에서 받은 CSV 파일을 이 임시 테이블에 빠르게 적재합니다. 임시 테이블은 제약조건 충돌을 신경 쓰지 않아도 되어 속도가 매우 빠르기 때문입니다.
1
CREATE TABLE tmp LIKE my_table;
그 다음 단계에서는 임시 테이블의 데이터를 운영 테이블로 옮기면서 중복을 처리합니다. 이때 INSERT ... ON DUPLICATE KEY UPDATE 구문을 사용하면, 키가 겹칠 경우 원하는 컬럼만 업데이트할 수 있으며, 이를 통해 중복 데이터를 안전하게 저장할 수 있습니다.
1
2
3
4
5
6
7
8
9
10
11
LOAD DATA LOCAL INFILE 'data.csv'
INTO TABLE tmp
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
INSERT INTO my_table (id, col1, col2)
SELECT id, col1, col2
FROM tmp
ON DUPLICATE KEY UPDATE
col1 = VALUES(col1),
col2 = VALUES(col2);
3. 정리
단순히 LOAD DATA 명령어만 사용하면 대참사가 날 수도 있는데요, 제가 처음에 그랬습니다. 천 만 건의 데이터를 INSERT 한 후, 모든 데이터를 초기화 했고, 다시 넣었거든요. 단순히 하나의 기술을 안다고 바로 적용했다가는 큰일이 날 수 있습니다. 🤔