영우
MySQL에 대용량 데이터를 삽입해 인덱스를 사용해보기 본문
오늘의 목표는 간단합니다. 대용량 데이터를 mysql에 삽입하고, 인덱스를 걸어 실제로 인덱스가 적용이 되면 얼마나 빠른지 알아보려합니다.
목표
- 테이블에 천만개의 데이터 넣기
- 다양한 스캔방법에 대해 결과를 확인해보자.
테이블에 천만개의 데이터 넣기
- mysql공식문서에 innodb 대량 데이터 로딩 팁이 있습니다.
insert로 데이터를 넣는것보다 load data로 삽입하는것의 속도가 약 20배 빠르다고 합니다. load data는 텍스트 파일의 행을 읽어와 테이블로 삽입하는 명령어입니다. 다음과 같은 명령어로 데이터를 불러올 수 있습니다.
데이터를 불러오기 위해서는 우선 데이터 파일이 필요합니다. 데이터파일의 데이터는 행간 구분자, 열간 구분자를 지정하고, 이를 명령어를 통해 파싱할 수 있습니다.
저는 간단하게 js로 다음과 같이 데이터를 만들었습니다. 1924년부터 2024년 사이의 생일을 가진다는 의도로 만들었고, console.log로 작성했기때문에 개행을 각 열간 구분자로 사용했습니다.
for (i = 1; i <= 10000000; i++) {
console.log(Math.floor(Math.random() * (100) + 1924));
}
그리고 표준출력을 임의의 파일로 리다이렉트해 데이터가 들어있는 파일을 만들었습니다.
node test.js > test.csv
mysql에는 다음과 같은 테이블을 만들었습니다.
아까만든 데이터파일을 테이블에 load하는 명령어를 입력했습니다. (year)는 id가 auto_increment기 때문에 삽입할 행을 지정한것입니다.
load data infile '/usr/local/mysql/data/test2/test.csv' into table test.bulk (year);
그러나 다음과 같은 오류가 발생했습니다.
이 오류는 secure_file_priv 변수가 NULL로 설정되어있기때문에 생기는 오류로, 데이터를 가져오고 내보내는 디렉토리를 지정해 주고, 이 디렉토리에서만 작업할 수 있게 제한합니다.
다음과 같이 빈 문자열을 my.cnf에 추가하게 되면 보안적으로 안전한 설정은 아니나, 더이상 데이터를 가져오고 내보내는 디렉토리가 제한되지 않습니다.
이후 mysql서버를 재시작 하게되면 정상적으로 텍스트파일의 데이터를 삽입할 수 있습니다.
이제 다양한 스캔방법에 대해 결과를 확인해봅시다.
풀 테이블 스캔
- 테이블의 모든행을 순차적으로 읽습니다.
- 1000만개의 데이터가 있습니다. 이를 풀 테이블 스캔을 한 결과는 다음과 같습니다. 쿼리 한번에 1초가 넘는 시간이 걸리는것을 알 수 있습니다.
- 실행계획을 살펴봅시다. extra에 using where로 인덱싱이 아닌 풀 테이블 스캔을 사용한 것을 확인할 수 있습니다.
인덱스 스캔
- 아래의 방식들은 인덱스를 사용해서 효율적으로 데이터를 읽을 수 있습니다.
- 인덱스 추가하기
- 인덱스 확인하기.
- PK로 잡혀있는 id뿐만 아니라, year열도 인덱스가 걸려있는것을 확인할 수 있습니다.
인덱스 레인지 스캔
- 인덱스를 사용해 특정 범위의 데이터를 읽습니다.
쿼리하는 범위가 작을때
풀 테이블 스캔에서 1초가 넘게 걸린 쿼리가 0.06초로 크게 줄어든것을 확인할 수 있습니다.
실행계획을 살펴보면 Extra컬럼의 값으로 인덱스를 사용한것을 확인할 수 있습니다.
쿼리하는 범위가 클때(예시에서는 전체의 76%를 서치한 결과임)
- 풀 테이블 스캔(2.18초)
- 인덱스를 추가하고 쿼리를 날렸을때
- 커버링 인덱스가 안되게끔 컬럼을 하나 더 추가하고 비교를했습니다. 인덱스 스캔이 전체 범위의 20~25%가 넘으면 인덱스 스캔이 더 비효율적이라고 생각해서 인덱스를 추가했을때 더 오래걸릴 줄 알았습니다. 그러나 실제로는 쿼리의 범위를 보고 실행계획을 인덱스 스캔이 아닌 풀 테이블 스캔을 사용하는것을 알 수 있었습니다.
인덱스 풀 스캔
- 인덱스 전체범위를 순차적으로 읽습니다.
- 인덱스파일의 크기가 테이블 파일의 크기보다 작기때문에, 인덱스 레인지 스캔보다는 빠르지 않지만, 테이블 풀 스캔보다는 효율적입니다.
- 아래는 인덱스 풀 스캔의 쿼리시간과 실행계획입니다.
- 테이블 풀 스캔의 경우 쿼리시간이 1.97초로 인덱스 풀 스캔의 1.90초에 비해 약간 느린것을 확인하였습니다.
결론
적은양의 데이터로는 체감하지 못했던 인덱스의 필요성을 느낄 수 있었습니다.
대체로 예상한 결과대로 쿼리의 결과가 나왔지만, 인덱스 레인지 스캔에서 인덱싱을 했음에도 풀 테이블 스캔을 해 더 효율적으로 실행계획하는것은 예상하지 못했습니다. 생각보다 스마트해서 어느정도는 MySQL엔진에게 맞겨도 좋다고 생각합니다.
그럼에도 결국 인덱싱을 설정하는 것은 사람이고, 인덱싱을 했을때 삽입, 삭제, 수정의 시간을 늘어나니 무작정 인덱스를 사용하기 보다는 상황과 맥락에 맞는 판단이 필요할것같습니다.
참고자료
- bulk data loading
- load data
- -secure-file-priv
- optimize
- REAL MySQL 8.0
'CS > 데이터베이스' 카테고리의 다른 글
| 클러스터링 인덱스의 사실과 오해 (0) | 2024.02.15 |
|---|---|
| 서브쿼리란 뭘까? (1) | 2024.02.08 |
| MySQL EXPLAIN 명령어는 뭘까? (0) | 2024.02.01 |
| 잠금(LOCK)과 MVCC로 보는 InnoDB의 격리수준 (0) | 2024.01.12 |
| InnoDB 버퍼풀의 LRU 구현해보기 ! (0) | 2024.01.04 |
