[MySQL] LOAD DATA

LOAD DATA 를 왜 썼을까에 대한 설명은 아래와 같다.

수십만건의 데이터를 특정 테이블에 입력해 달라는 요청이 왔다.

요청온 데이터는 그 많은 양의 insert 문이 담긴 sql 파일과 csv 파일이었다.

처음에 sql 파일을 보고 실행했더니 오랜 시간이 소요되어 LOAD DATA를 알아보게 되었다.

.

.

.

LOAD DATA 실행문은 텍스트 파일의 행을 빠른 속도로 테이블에 데이터를 입력한다.

MySQL 서버의 LOAD DATA 명령도 내부적으로 MySQL 엔진과 스토리지 엔진의 호출 횟수를 최소화하고 엔진이 직접 데이터를 적재하기 때문에 일반적인 SELECT 명령과 비교했을 때 매우 빠르다고 한다.

내가 알고 있는 가능한 파일은 txt, csv 지만 더 다양한 파일을 활용할 수 있는지는 확인을 해봐야 할 것 같당

 

단점

단일 스레드로 실행

• 단일 스레드로 실행되기 때문에 적재해야 할 데이터 파일이 매우 크다면 시간이 매우 길어질 수 있으며, 다른 온라인 트랜잭션 쿼리들의 성능이 영향을 받을 수 있다.

 

단일 트랜잭션으로 실행

• LOAD DATA 문장이 시작한 시점부터 언두로그(Undo Log)가 삭제되지 못하고 유지되어야 한다.
  이는 언두로그를 디스크로 기록해야 하는 부하를 만들기도 하지만, 언두로그가 많이 쌓이면 레코드를 읽는 쿼리들이 필요한 레코드를 찾는 데 더 많은 오버헤드를 만들어 내기도 한다.

 

따라서 데이터 파일이 크다면 여러 개의 파일로 준비해서 LOAD DATA 문장을 동시에 여러 트랜잭션으로 나뉘어 실행하는 것이 좋다.

 

진행 환경

✔️ v8.0 환경이다

✔️ csv 파일을 활용하여 진행하였다.

 

진행 내용

먼저 LOAD DATA 를 실행하려면 secure-file-priv 을 설정해줘야 한다.

• my.cnf 파일에 secure-file-priv 경로를 설정하면 된다.

그렇지 않으면 아래와 같은 에러가 발생한다.

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement.

 

 

이 기능을 사용하면서 아래와 같은 에러가 발생하였다.

Error Code: 1261. Row [행번호] doesn't contain data for all columns

이 에러가 발생할 수 있는 조건은 아래 중 하나라도 해당하면 발생한다. (내가 테스트 한 기준)

• 텍스트 파일의 컬럼과 데이터 베이스의 테이블의 컬럼이 매칭이 안될 경우 (컬럼 수 또는 타입)
• 텍스트 파일 중 빈 값이 있는 경우

 

 

위와 같은 이슈를 해결하였더니 또 다른 황당한 이슈가 있었다.

나는 LOAD DATA 실행문을 통해 OK 리턴을 받았다.

하지만 OK 사인에 따른 디스크립션이 아래와 같이 나온 것이다.

0 row(s) affected Records: 0 Deleted: 0 Skipped: 0 Warnings: 0

 

대체 왜 테이블에 아무런 변화가 없었을까 !!!!!

구글링으로 찾아낸 피드백을 모두 적용해봤지만 나의 해결 방법은 아니었던 것 같았다🥲

 

뭔가 위에서 발견되었던 에러와 연관이 있는 것 같았던 게, 텍스트 파일 중 빈 값이 있는 경우 를 해결하니까 테이블에 데이터가 정상적으로 입력되었다.

 

따라서 LOAD DATA 진행 시 빈 값일 경우를 꼭 체크하면 좋을 것 같다.

(텍스트 파일의 빈 값이 있는 경우에 대한 처리는 꼭 아래와 같은 방법만 있는 건 아닐 것 같다. 또 다른 방법을 찾으면 작성해보자!)

LOAD DATA INFILE '파일 경로' 
INTO TABLE database.table
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
(@vid, @vname)
SET id = NULLIF(@vid, null),
name = NULLIF(@vname, null);

 

마지막으로,

성능을 고려한다면 INSERT 될 데이터들을 프라이머리 키 값 기준으로 미리 정렬하면 좋다.

다음 INSERT 할 레코드의 프라이머리 키 값이 직전에 INSERT 된 값보다 항상 크기 때문에 메모리에는 프라이머리 키의 마지막 페이지만 적재되어 있으면 새로운 페이지를 메모리로 가져오지 않아도 레코드를 저장할 위치를 찾을 수 있다.

 

만약 프라이머리 키 값 기준으로 정렬되어 있지 않으면 LOAD DATA 실행문이 레코드를 INSERT 할 때마다 InnoDB 스토리지 엔진은 프라이머리 키를 검색해서 레코드가 저장될 위치를 찾아야 한다.

따라서 프라이머리 키의 B-Tree에서 이곳저곳 랜덤한 위치의 페이지를 메모리로 읽어와햐 하기 때문에 처리하는 속도가 더 느리다.

 

참고

Real MySQL8.0

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