더미데이터 100만개를 넣기위한 여정

인덱스를 통해 쿼리 실행계획을 개선하려고 한다.

 

실제 서비스를 운영하는 DB라고 가정하고 실행계획을 보기 위해 더미데이터 100만개를 각 테이블에 넣어야 했다.

테이블마다 100만개의 더미데이터를 넣는 것은 쉽지 않다. 더미데이터를 생성하고 넣으면서 시도했던 방법을 정리해봤다.

 

실제 성공한 방법은 방법 6 이다.

방법 1 - mockaroo를 이용해서 데이터를 생성하고 삽입

랜덤 데이터를 자동으로 만들어주는 mockaroo라는 사이트가 있다.
이 사이트를 이용하면 내가 원하는 형태로 랜덤 데이터를 생성할 수 있다.
적절한 Type이 없다면 Regular expression 타입을 고르고 내가 원하는 문자열을 만들 수도 있다.

참고 : Mockaroo - regular-expressions

 

그러나 무료 버전은 한번에 최대 1,000개의 데이터만 생성할 수 밖에 없다는 단점이 있다.

방법 2 - 엑셀로 랜덤 데이터를 생성하고 insert문을 생성해서 삽입

엑셀 함수를 이용해 랜덤데이터를 생성한 뒤 insert문으로 하나하나 넣는 방법이다.
엑셀 최대 행의 수가 1,048,576이므로 100만개의 랜덤데이터를 한 시트에 생성할 수 있다.

아래 함수들을 주로 이용했다.

INT(RAND() * 10) => 0~9 사이의 난수 생성
INDEX($A$1:$A$5, RANDBETWEEN(1,5)) => A1부터 A5까지의 문자열 중 랜덤으로 하나를 고르는 함수
CONCATENATE => 문자열 조합

이 방법의 단점은 시간이 너무 오래걸린다는 것이다.
row당 0.01초만 잡아도 2시간 46분이 걸린다!

방법 3 - bulk insert를 사용

bulk insert를 사용하면 여러 row를 한번에 넣을 수 있다.
값들을 하나의 insert문 안에서 ,로 구분해서 넣으면 된다.

INSERT INTO table VALUES (1, "hello");
INSERT INTO table VALUES (2, "world");
INSERT INTO table VALUES (3, "!");

// bulk insert
INSERT INTO table VALUES (1, "hello"), (2, "world"), (3, "!");

방법 2에 비해 삽입 시간이 훨씬 빠르지만 직접 쿼리문을 생성해야하는 비용이 여전히 크고 비효율적이라는 단점이 있다.

방법 4 - 엑셀로 랜덤 데이터를 생성하고 mysql workbench에서 wizard import

Mysql workbench에는 외부 csv 파일을 테이블에 넣는 방법이 있다.
db의 테이블을 클릭하고 import wizard를 선택하면 된다.
그러나 이 방법 역시 insert문을 하나씩 실행하기 때문에 시간이 오래 걸린다.

방법 5 - mysql workbench에서 LOAD DATA LOCAL INFILE

Mysql에는 LOAD DATA LOCAL INFILE 구문으로 csv 파일을 빠르게 넣는 방법이 있다.
mysql workbench에서 이 구문을 실행해보았다.

LOAD DATA LOCAL INFILE "/Users/heoyeong-un/Documents/shedule_data3.csv" # 파일 경로 입력
INTO TABLE teatime.schedule
FIELDS TERMINATED BY ","   # csv 구분자 입력
LINES TERMINATED BY "\n"
IGNORE 1 ROWS;

이 방법의 단점은 초기 세팅에 시간이 걸린다는 점이다.
아래 에러를 비롯해 여러 에러들이 발생했고, 100만개의 데이터를 실제로 넣으려고 했을 때 mysql 서버로의 커넥션이 자꾸 끊기는 문제가 발생했다.

Error Code: 2068. LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

방법 6 - 엑셀로 랜덤 데이터를 생성 후 FileZilla로 서버에 직접 csv 파일을 옮겨주어 LOAD DATA LOCAL INFILE 구문 실행

이 방법이 우리가 유일하게 성공했던 방법이다.

mysql이 설치된 우분투 서버로 직접 csv 파일을 옮겨 서버 내부에서 LOAD DATA LOCAL INFILE 구문을 실행했다.
100만개 데이터를 한번에 넣는데 단 1분 30초밖에 걸리지 않았다.

방법 7 - JdbcTemplate을 이용해서 batch insert

가장 성능 좋고 간편한 방법이다.
실제로 사용한 방법은 아니지만, 다른 크루가 사용한 방법 중 가장 시간이 빠르고 효율적인 방법이라 생각한다.
아마 다음에 더미데이터를 넣는다면 이 방법을 사용할 것 같다.