200억건의 데이터를 MySQL로 마이그레이션 할 때 고려했던 개념과 튜닝 방법 정리

개요

본 포스팅은 "200억건의 데이터를 MySQL로 마이그레이션 할 때 고려했던 개념과 튜닝 방법" 인프런 강의의 내용을 정리한 글입니다. 

Prepare

CREATE TABLE test1(
	seq INT PRIMARY KEY AUTO_INCREMENT,
    one_field BIGINT,
    two_field BIGINT,
    hash_email VARCHAR(500),
    INDEX idx_hash_email(hash_email),
    INDEX idx_col1_col2(one_field, two_field)
);

Multiple Column Index

Multiple Column Index란 복합 인덱스를 의미한다. 복합 인덱스에서 가장 중요한 것은 명시된 컬럼의 순서에 따라 정렬이 된다는 점이다. 따라서 복합 인덱스를 설계하는 경우 컬럼의 순서가 굉장히 중요하다고 볼 수 있다. 
예를 들어 one_field, two_field 2개의 컬럼을 복합 인덱스로 설정했다고 가정해보자. 이 상황에서 two_field에 대해서만 쿼리를 구성하는 경우 설정한 복합 인덱스를 사용할 수 없다. 

• one_field, two_field, three_field
• one_field, two_field, three_field, four_field

실무에서는 위처럼 사용되는 쿼리에 따라 중복되는 복합 인덱스를 생성하는 케이스도 있다. 이러한 인덱스들은 일반적인 상황에서는 큰 문제가 되지 않지만 Index dive 상황에서는 굉장히 큰 오버헤드가 발생한다. 

EXPLAIN SELECT hash_email, one_field, two_field FROM test1 WHERE two_field = 3;

위 쿼리를 수행해보면 type이 ALL로 표시되는 모습을 확인할 수 있다. 이는 인덱스를 타지 못하고 테이블 풀 스캔이 되었다는 뜻이다. 현재 우리는 one_field, two_field 2개의 컬럼에 복합 인덱스를 걸어두었는데 SELECT 문에서 hash_email이라는 인덱스가 아닌 컬럼도 추가로 조회를 요청했기 때문이다. 따라서 인덱스만을 통해 쿼리를 가져올 수 없다.

EXPLAIN SELECT seq, one_field, two_field FROM test1 WHERE two_field = 3;

반면에 PK값인 seq로 변경하여 조회하는 경우 type이 index로 변경된걸 볼 수 있다. 이런 경우 인덱스를 사용은 하였지만 인덱스에 대해 풀 스캔이 일어났다는 것을 의미한다.

EXPLAIN SELECT one_field, two_field FROM test1 WHERE one_field = 2 AND two_field = 3;

위 쿼리는 이전과는 다르게 type이 ref로 뜬다. 또한 rows 또한 굉장히 적어진다. 이는 WHERE절에 정확히 = 를 통해 한 개씩 매칭시켜 검색했기 때문인데 WHERE절에 >= 처럼 범위 검색을 수행하는 경우 type은 range로 나오게 된다. 결론부터 이야기하자면 type이 ref또는 range가 나오도록 인덱스를 구성하는것이 굉장히 중요하다. 물론 실무에서는 모든 쿼리를 최적화시킬 수 없기에 type = index라면 어느정도 괜찮다고 볼 수 있다.
복합 인덱스는 보통 카디널리티를 고려하여 설정한다. 중복도가 낮은 경우 카디널리티가 높다고 표현하고 중복도가 높은 경우 카디널리티가 낮다고 표현한다. 

Covering Index

커버링 인덱스란 쿼리에서 필요한 모든 컬럼들을 실제 데이터가 아닌 인덱스만을 통해 가져올 수 있는 인덱스를 의미한다. 보통 쿼리가 실행되면 데이터베이스는 트리 구조에서 데이터를 찾고 결과를 반환한다. 물론 이 트리는 기본적으로 인덱스를 사용하여 구성이 되어있는 트리이다. 만약 인덱스 트리에 데이터가 없는 경우 실제 테이블에 접근하여 데이터를 가져오기 때문에 테이블 풀 스캔이 발생하는 것이다. 
MySQL에서 다루는 인덱스는 여러 종류가 있다. 기본적으로 PK인 경우 클러스터 인덱스라고 불린다. PK는 항상 순서에 따라 데이터가 물리적으로 정렬이 되어있다. 그렇기 때문에 PK에 따로 인덱스를 걸지 않는 것이다. 반면 우리가 추가하는 보조 인덱스의 경우 B-Tree라고 불리는 별도의 데이터 구조에 저장이 된다. 트리에는 인덱싱이 된 열의 복사본과 클러스터 인덱스 행에 대한 포인터가 포함되어 있다. MySQL에서 쿼리가 실행되는 순서를 간단하게 보자면 다음과 같다.

1. 쿼리가 들어온다.
2. MySQL 옵티마이저가 쿼리를 분석한다. 
3. 분석을 통해 적합한 인덱스를 도출한다.
4. 인덱스가 존재하는 경우 B-Tree에서 위치를 찾고 데이터를 가져온다.
5. 인덱스가 존재하지 않는 경우 테이블에 직접 접근하여 풀 스캔을 하게 된다.

Order by

Order by 쿼리는 신경쓸게 많은 쿼리이다. 인덱스가 잘못 설정되어있는 경우 데이터 정렬하는데 많은 시간과 리소스를 잡아먹기 때문이다. MongoDB에서 데이터를 마이그레이션 할 당시 다음과 같은 쿼리가 있었다. (예시를 위해 MySQL 쿼리로 작성한다.)

SELECT * FROM <테이블> FROM first_name = "abc" OR second_name = "abc" ORDER BY desc;

MongoDB는 단일 컬렉션에 대해 굉장히 빠른 성능을 보장하고 있었는데도 20초라는 시간이 걸렸었다. MongoDB쪽에서 EXPLAIN을 통해 쿼리를 확인해보니 인덱스가 원활하게 설정되어있지 않았고 데이터의 크기도 너무 컸기 때문이다. (대략 10억건 정도) 또한 쿼리에서 OR 조건이 들어가는 경우 2개의 컬럼에 대해 값을 모두 체크해야 하기 때문에 인덱스를 타기 힘들어진다. 따라서 이를 MySQL로 마이그레이션하며 테이블 정규화 및 인덱스 재설정을 통해 어느정도 해소했다.

인덱스가 설정되어있지 않은 상황에서 Order by를 수행하는 경우 MySQL은 기본적으로 File sort 형태로 동작하게 된다. 

CREATE TABLE users(
	id INT AUTO_INCREMENT PRIMARY KEY,
    a VARCHAR(255),
    b VARCHAR(255),
    c VARCHAR(255),
    INDEX a_b(a, b)
);

EXPLAIN SELECT a, b FROM users ORDER BY a, b;

위 쿼리를 수행하면 type이 index로 뜨게 된다.

EXPLAIN SELECT * FROM users ORDER BY c, a;

그리고 위 쿼리는 type이 all로 뜬다. 1번 쿼리의 경우 당연히 인덱스가 설정되어있기 때문에 인덱스를 사용한 것이고 2번 쿼리의 경우 인덱스가 설정되어있지 않기 때문에 File sort로 정렬이 수행되는 것이다.

물론 실무에서는 인덱스가 설정되어있는 경우도 있고 설정되어있지 않은 경우도 있다. 이러한 경우 방금처럼 File sort로 정렬이 수행된다. File sort라는 것이 무조건적으로 나쁜것은 아니다. 기본적으로 메모리로 동작하기 때문에 디스크를 사용하는것 보다는 훨씬 빠르게 동작한다. 하지만 File sort가 동작할 때 sort_buffer_size라는 설정값을 넘어가는 메모리를 사용하는 경우 디스크를 사용하게 되고 이는 더 많은 IO자원을 소모하게 된다. 

위에서 만약 a컬럼이 무조건 10글자 이하인 경우 255가 아닌 10으로 길이 제한을 변경하는것이 좋다. 그렇게 된다면 보다 적은 메모리를 사용해 데이터를 가져올 수 있고 sort_buffer_size에 대해서도 최적화가 가능하다. 

Auto Increment 락

Auto Increment에도 락이 존재한다. 하지만 Auto Increment 필드는 카운트를 따로 저장하는 공간이 있기 때문에 트랜잭션과 관계없이 별도로 락이 동작하게 된다. 따라서 트랜잭션의 성공/실패 유무에 상관없이 Auto Increment값은 증가하게 된다. 락 모드에는 다음과 같이 3가지가 있다.

• innodb_autoinc_lock_mode = 0 - 트랜잭션과 같이 동작한다. 따라서 트랜잭션이 실패하는 경우 증가하지 않는다. 하지만 동시성이 보장되지 않는다. 따라서 성능이 떨어진다.
• innodb_autoinc_lock_mode = 1 - 기본값으로써 트랜잭션과 별도로 동작한다. 따라서 트랜잭션 성공/실패 유무에 상관없이 증가한다.
• innodb_autoinc_lock_mode = 2 - 여러 스레드가 동시에 

Index Dive

PostgreSQL의 경우 쿼리가 들어오면 쿼리 계획 수립을 캐시를 활용하여 스킵하기도 한다. 하지만 MySQL의 경우 이러한 계획 수립 단계를 재활용하지 않는다. (물론 동일 커넥션에서는 재활용한다) 이처럼 쿼리 계획을 수립하는데 많은 리소스가 소모되는 상황을 Indev Dive 상황이라고 한다. 일반적인 쿼리에서 자주 발생하는 상황은 아니지만 IN쿼리를 사용할때는 꽤나 빈번히 발생하기도 한다.

CRETE TABLE test(
	name VARCHAR(255),
    second_name VARCHAR(255),
    one INT,
    two INT,
    INDEX name_name(name, second_name),
    INDEX name_name_one(name, second_name, one);
    INDEX name_name_two(name, second_name, two)
);

SELECT * FROM test WHERE name IN("1", "2", ... "150") AND second_name IN("1", "2", ... "150");

예를 들어 위와 같은 쿼리의 경우 첫 번째 조건에서 150개까지 IN쿼리가 수행되고 두 번째 조건에서 또 150개까지의 IN쿼리가 수행된다. 이 때 인덱스를 살펴보면 name, second_name에 걸려있는 복합 인덱스가 여러개 존재한다. 이러한 경우 MySQL 서버입장에서는 어떤 인덱스를 사용해야 최적화할 수 있는지 확인하기 위해 각 인덱스 설정값에 대해 모두 한번씩 쿼리 실행 계획을 수립해본다. 

Prefix Index

Prefix Index란 MySQL의 전체적인 성능을 개선할 수 있는 방법 중 하나이다. 단어에서도 알 수 있듯이 컬럼의 일부만을 인덱스로 설정을 하는 기능을 말한다. 이렇게 일부만 설정하게되면 인덱스의 사이즈를 줄일 수 있기 때문에 인덱스 자체가 가벼워진다. 

ALTER TABLE test ADD KEY (name(3));

예를 들어 위처럼 쿼리하게 된다면 test 테이블의 name 컬럼에 대해 앞에서부터 3글자만 인덱스로 설정한다. 단점으로는 추가적인 Order by나 Group by와 같은 쿼리를 사용할 수 없다. 이는 당연하게도 전체 데이터가 아닌 앞 몇글자만 가지고 있는 인덱스이기 때문이다. 

강사의 경우 로그인 세션 토큰에 대해 프리픽스 인덱스를 적용했다고 한다. 일반적으로 UUID, Token, Hash처럼 고르게 분포되어있는 값에 사용을 자주한다. 이러한 값들은 랜덤한 값들이 들어가는 것이기에 프리픽스 인덱스로 설정을 하는것과 그냥 인덱스로 설정을 하는 것에 큰 차이가 없다. 해당 값을 기준으로 정렬하거나 정합성을 체크하거나 하지 않기 때문이다. 

SELECT FOR Update using Session

• NO WAIT: 쿼리 실행 후 락이 걸려있다면 대기하지 않고 바로 실패
• SKIP LOCKED: 락이 걸려있는 로우는 결과에 반환하지 않음