60% 정도의 시간은 쿼리보다 적재 과정에 소요된 것 같다.
대용량 데이터를 적재하는 프로세스를 정리해놔야겠다.
1. 저장공간이 충분한지 확인한다.
의외로 MySQL에 데이터가 들어갈 때 용량이 많이 증가하는 경우가 있다.
또 아무 생각 없이 데이터를 넣다가 하드 용량이 모자랄 때는 곤란한 경우가 생긴다.
특히 MyISAM 엔진일 경우는 그나마 상황이 좀 나은데, InnoDB 엔진을 사용했을 경우는 대책없다.
이런 경우는 DB를 덤프해서 다른 저장소를 지정한 다음 다시 로드하는 수밖에 없는 것 같다.
MyISAM 엔진인 경우는 파일을 다른 하드에 복사해놓고 경로만 잘 맞춰주면 된다.
2. 파일 구조에 맞도록 테이블 스키마를 생성한다.
테이블 스키마를 생성할 때 유의하도록 한다. 나중에 옵션 하나를 변경하려고 해도 엄청 많은 시간이 걸린다.
특히 MySQL은 기본적으로 영문 대소문자를 구분하지 않는다. 대소문자를 구분하는 테이블 옵션이 있으므로 반드시 미리 지정해주어야 한다. Characterset 옵션과 더불어 Collate라는 옵션이 있는데, 이 옵션의 끝에 ci가 붙으면 case-insensitive, cs가 붙으면 case-sensitive, bin이 붙으면 binary라는 뜻이다. 잘 지정해주어야 한다.
3. 필요한 인덱스 키를 걸어둔다.
4. 인덱스 키를 DISABLE시킨다.
데이터를 Insert할 때 인덱스가 걸려 있으면 속도가 엄청나게 느려진다.
그렇다고 '인덱스는 나중에 걸지' 하고 인덱스를 걸어놓지 않는 경우가 있는데,
대용량 데이터의 경우 나중에 인덱스를 생성하여 걸게 되면 시간이 많이 소요된다.
특히 MySQL은 인덱스를 생성할 때 기존의 A 테이블 파일을 새 B 테이블 파일로 복사한 후, 인덱스를 걸고 이 B 테이블 파일의 이름을 A로 바꾸는 과정을 거치기 때문에, 파일 복사의 시간만큼이 더 걸린다.
때문에 인덱스는 미리 걸어놓되 키를 사용하지 않도록 지정하고 데이터를 로드한 뒤에 키를 ENABLE시키면 시간을 절약할 수 있다. (MySQL의 LOAD DATA문을 사용하면 자동으로 이미 걸려있는 키를 DISABLE, ENABLE시키면서 데이터를 로드하는 것 같다.)
5. 파일로부터 데이터를 DB로 적재한다.
이 과정에서 몇 가지 방법을 사용할 수 있는데, 가장 빠른 방법은 LOAD DATA INFILE 명령을 사용하는 것이다.
LOAD DATA INFILE 'filename'와 같은 문법을 따른다.
INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(column1, @dummy, column3, column2)
그런데 이번에 작업 도중 생긴 문제가 한글 처리였다. LOAD DATA문을 사용해서 데이터를 적재하고 나니, 시간은 빠른데 한글 인코딩이 모두 깨져서 나왔다.
파일 인코딩을 모두 바꾸자니 2~20GB나 되는 파일을 모두 바꾸기도 그렇고 해서 한글이 포함된 파일은 Python을 사용해서 인코딩을 변환하면서 insert를 했다. Python의 codecs 모듈을 사용하면 인코딩을 지정해서 파일을 불러들일 수 있다. Python을 사용해서 MySQL에 INSERT를 할 때는 MySQLdb 모듈을 사용했고, 특히 executemany라는 함수가 있는 것이 특징적이었다. 딕셔너리를 사용해서 간단하게 multiple insertion을 사용할 수 있었다.
6. 인덱스 키를 ENABLE 시킨다.
당연한 이야기이지만, 각 과정에서 조그만 데이터셋을 가지고 미리 수행을 해보고 결과를 확인한 후에 실제 데이터를 집어넣어야 한다.