MySQL Bulk Insert(LOAD DATA INFILE)

내용 정리

MySQL에서 'Bulk Insert'는 한 번에 여러 행을 데이터베이스 테이블에 삽입하는 방법이다. 이를 사용하면 대량의 데이터를 빠르고 효율적으로 처리할 수 있다. 일반적으로 이러한 작업은 CSV 파일 또는 다른 형태의 텍스트 파일에서 데이터를 불러오는 경우에 많이 사용된다. 이 작업을 수행하는 기본 명령어는 LOAD DATA INFILE이다.

 

다음은 기본적인 LOAD DATA INFILE 구문이다:

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

이 구문은 'data.txt'라는 파일에서 데이터를 읽어와 'my_table'이라는 테이블에 삽입한다. 이 명령어에는 다양한 옵션들이 있다. 예를 들어, 필드를 구분하는 구분자, 행을 구분하는 구분자, 문자열을 감싸는 문자 등을 지정할 수 있다.

 

이렇게 하면 CSV 파일뿐만 아니라 다양한 형태의 텍스트 파일을 처리할 수 있다.

예를 들어, 각 필드가 콤마로 구분되고, 각 행이 줄 바꿈으로 구분되며, 문자열이 큰따옴표로 감싸져 있는 CSV 파일을 처리한다면 다음과 같이 작성할 수 있다:

LOAD DATA INFILE 'data.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\\n';

 

더불어 LOAD DATA INFILE은 로컬 파일에 접근할 수 있다. 하지만, MySQL 서버에서 로컬 파일에 접근하는 것을 허용하도록 설정해야 한다. 이를 위해서는 LOCAL 키워드를 사용하면 된다.

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

 

마지막으로, 파일에 있는 모든 열이 테이블에 있는 열과 정확히 일치하지 않는 경우, SET 절을 사용하여 어떤 방식으로 데이터를 적재할 것인지 명시할 수 있다. 예를 들어, 테이블에는 'created_at'이라는 열이 있지만, 파일에는 이에 해당하는 데이터가 없는 경우, 현재의 날짜와 시간을 사용하도록 다음과 같이 작성할 수 있다:

LOAD DATA INFILE 'data.txt' INTO TABLE my_table
SET created_at = CURRENT_TIMESTAMP;

 

LOAD DATA INFILE와 LOAD DATA LOCAL INFILE의 차이점

1. LOAD DATA INFILE
LOAD DATA INFILE는 MySQL 서버가 있는 컴퓨터의 파일을 불러오는 명령어이다.
MySQL 서버가 실행되는 컴퓨터에서 데이터 파일을 찾는다.

2. LOAD DATA LOCAL INFILE
LOAD DATA LOCAL INFILE는 클라이언트(즉, 당신이 MySQL 명령을 내리는 컴퓨터)의 파일을 불러오는 명령어이다. 이 경우, 파일은 MySQL 서버가 아닌 당신의 컴퓨터에서 찾는다.

간단히 말해, 'LOCAL'이 있는지 없는지에 따라 파일을 불러올 위치가 달라진다. 'LOCAL'이 있으면 당신의 컴퓨터에서 파일을 찾고, 없으면 MySQL 서버가 있는 컴퓨터에서 파일을 찾는다.

그러나 LOAD DATA LOCAL INFILE을 사용하려면 MySQL 서버가 이 기능을 허용하도록 설정되어야 한다. 일부 시스템에서는 보안 문제로 인해 이 기능이 비활성화되어 있을 수 있다. 이 경우, MySQL 서버의 설정을 변경하거나 다른 방법을 사용하여 데이터를 삽입해야 한다.

마지막으로, 두 명령 모두 데이터 파일의 접근 권한이 적절히 설정되어야 한다. MySQL 서버 또는 클라이언트가 데이터 파일을 읽을 수 없는 경우, 명령은 실패한다.

---

효율성과 튜닝

Bulk insert는 일반적으로 행 하나씩 삽입하는 것보다 훨씬 효율적이다. 그러나 특히 대규모 데이터를 다루는 경우에는 여전히 느릴 수 있다. 이런 경우, 다음과 같은 방법들을 통해 성능을 향상할 수 있다:

• 인덱스 제거: Bulk insert를 수행하기 전에 관련된 테이블의 인덱스를 임시로 제거하면 성능을 향상시킬 수 있다. 이는 새로운 행을 삽입할 때마다 인덱스가 업데이트되는 것을 방지하기 때문이다. 그러나 이 작업이 끝난 후에는 인덱스를 다시 생성해야 한다.
• autocommit 비활성화: SET autocommit=0;를 사용하여 autocommit을 비활성화하면, 모든 행이 삽입된 후에만 커밋이 이루어진다. 이는 삽입 중에 발생할 수 있는 불필요한 디스크 I/O를 줄여 성능을 향상할 수 있다.
• 다중 Value 삽입: 단일 INSERT 문에 여러 개의 VALUES 절을 사용하여 여러 행을 한 번에 삽입할 수 있다. MySQL은 이런 식의 삽입을 일반적인 여러 개의 INSERT 문보다 빠르게 처리한다.

 

주의 사항

Bulk insert를 수행할 때는 특히 에러 처리와 데이터 유효성에 주의를 기울여야 한다.

• 에러 처리: LOAD DATA INFILE은 기본적으로 에러를 발생시키면 작업을 중단한다. 따라서, IGNORE 키워드를 사용하여 잘못된 행을 무시하고 가능한 한 많은 행을 삽입하도록 할 수 있다. 하지만 이렇게 하면 데이터의 일관성을 해칠 수 있으므로 주의가 필요하다.
• 데이터 유효성: Bulk insert는 파일의 데이터를 그대로 삽입하므로, 파일에 유효하지 않은 데이터가 포함되어 있지 않은지 확인해야 한다. LOAD DATA INFILE 문의 SET 절을 사용하여 일부 데이터 유효성 검사를 수행할 수 있지만, 이는 매우 기본적인 수준이다. 따라서 가능한 한 데이터를 삽입하기 전에 미리 검사하고 정제하는 것이 좋다.