#036. 쿼리개선: insert 성능과 테이블 구조 (Index 정리하기)

개념

테이블을 구성하거나 인덱스를 추가할 때는 이 테이블에 쓰기(INSERT, UPDATE, DELETE) 쿼리가 많은 지 읽기(SELECT) 쿼리가 많은지에 따라 테이블 성격을 구분해야한다.

(1) InnoDB 스토리지에서 프라이머리 키(Primary Key)의 역할:
Mysql , PostGresql 같이 InnoDB 스토리지 엔진을 사용하는 테이블의 프라이머리 키는 클러스터링 키(인덱스 페이지 위치를 찾을 수 있는 키)이다. 프라이머리 키가 랜덤하게 덤프된 데이터 파일의 경우에는 각 레코드의 프라이머리 키가 너무 다른 값을 가지고 있어서 InnoDB 스토리지 엔진이 레코드를 저장할 때 마다 프라이머리 키의 B-Tree 에서 이곳저곳 랜덤한 위치의 페이지를 메모리로 읽어와야 하기 때문에 처리가 더 느리다. 따라서 데이터가 프라이머리 키로 정렬되어 있는가는 INSERT 쿼리 성능에 영향을 많이 준다.

책에 나와있는 예시를 이용해 직접 실험해보면 다음과 같이 프라이머리 키로 정렬되어있는 INSERT가 조금 더  빠르다는 것을 알 수 있다. 실습하는 방법은 깃헙에 간단하게 정리해두었다. 💻 (https://github.com/erie0210/query-improvement/blob/main/036-insert-primary-key.kt)

-- 랜덤으로 정렬된 데이터를 INSET 할 때: 7.06 sec
mysql> load data local infile  '/tmp/rand_salaries.csv'
    -> INTO TABLE salaries_temp
    -> FIELDS  TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 2844047 rows affected (7.06 sec)
Records: 2844047  Deleted: 0  Skipped: 0  Warnings: 0


-- 프라이머리 키로 정렬된 데이터를 INSET 할 때: 6.64 sec
mysql> load data local infile  '/tmp/sorted_salaries.csv'
    -> INTO TABLE salaries_temp
    -> FIELDS  TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 2844047 rows affected (6.64 sec)
Records: 2844047  Deleted: 0  Skipped: 0  Warnings: 0

INSERT 쿼리의 성능은 프라이머리 키의 정렬 여부가 결정적이긴 하지만 세컨더리 인덱스도 INSERT 쿼리에 영향을 준다. 테이블에 세컨더리 인덱스가 많을 수록 그리고 테이블이 클수록 인덱스 테이블의 크키가 무거워지고 INSERT 성능을 떨어진다. 반면 잘 만들어진 세컨더리 인덱스는 SELECT 문장의 성능을 높일 수 있다. 따라서 테이블의 세컨더리 인덱스를 적절하게 설정하고 너무 남용하는 것은 성능상 좋지 않다.

(2) 인덱스를 추가할 지 여부: 
여기서 생각해볼 수 있는 것이 대부분의 온라인 트랜젝션 처리를 위한 테이블들은 쓰기(INSERT, UPDATE, DELETE) 보다는 읽기(SELECT) 쿼리의 비율이 압도적으로 높다는 것이다.

- INSERT가 많이 실행되는 케이스: 로그 테이블
예외적인 경우가 로그 테이블인데, SELECT는 거의 실행되지 않고 INSERT가 매우 많이 실행되기 때문이다. 이런 류의 테이블에 대해서는 프라이머리키를 단조증가, 감소 하는 패턴으로 설정하는 것이 좋고 인덱스의 개수를 최소화 하는 것이 좋다.

- SELECT가 많이 실행되는 케이스: 중요 정보를 가진 대부분의 테이블
하지만 상품이나 주문, 사용자 정보와 같이 중요 정보를 가진 테이블들은 쓰기에 비해 읽기 비율이 압도적으로 높은 경우가 많다. 이러한 류의 테이블에 대해서는 INSERT 보다는 SELECT 쿼리를 빠르게 만드는 방향으로 프라이머리 키를 선정해야한다. 일반적으로 SELECT 에 최적화된 프라이머리 키는 단조증가나 단조 감소 패턴과는 거리가 먼 경우가 많지만 여전히 빈번하게 실행되는 SELECT 쿼리의 조건을 기준으로 프라이머리 키를 선택하는 것이 좋다.

따라서 SELECT가 많지 않고 INSERT가 많은 테이블에 대해서는 인덱스의 갯수를 최소화 하는 것이 좋다. 반면 INSERT가 많지 않고 SELECT 가 많은 테이블에 대해서는 쿼리에 맞게 필요한 인덱스들을 추가해도 시스템 전반적으로 영향도가 크지 않다. 물론 SELECT가 많은 테이블에 대해서는 자연적으로 세컨더리 인덱스가 많아진다.

 

적용

인덱스를 적용/삭제할 때 고려할 것들이 여러가지 있지만 최근 작업을 했거나 하시는 것을 본 아래 두 케이스를 위의 개념을 적용시킨 케이스로 기록해두려고 한다.

(1) 불필요한 인덱스 삭제: 중복된 인덱스는 삭제하기
아래와 같이 bookmark 라는 테이블의 예시가 있다. 빨간색으로 네모 친 부분을 보면 user_id를 index 테이블로 생성하고 있는데, 이는 두번 째 인덱스의 user_id, post_id를 인덱스로 사용하고 있는 인덱스와 중복된다. 만약 user_id로 인덱스를 타야하는 상황이라면 user_id_post_id로 된 인덱스를 사용할 수 있다. 따라서 네번째 인덱스는 삭제해주는 것이 좋다. 

(2) 인덱스 추가: cardinality 가 높은 인덱스를 추가하기
위와 같이 필요하지 않은 인덱스를 삭제와 함께 인덱스를 추가할 때 고려해볼 문제가 컬럼의 커디널리티이다. 인덱스가 많이 있더라도 cardinality 가 낮으면 옵티마이저에 의해 선택되지 않을 가능성이 높다. 예를 들면 deleted_at과 같이 null이 대부분인 컬럼은 index에 추가하더라도 선택되지 않을 가능성이 높은 반면 created_at과 같은 데이터는 각 데이터마다 고유의 값을 가지고 있을 가능성이 높기 때문에 index로 선택될 가능성이 높다.

아래는 일전에 4s 대의 슬로우 쿼리를 66ms 로 개선한 예시이다. 4s가 걸리는 쿼리를 보면 풀스캔에 데이터의 99%가 filter로 처리되어 느렸다. 어떤 값으로 filter 되었는지 보니 deleted_at가 filter조건에 있어 deleted_at으로 index를 설정해봤는데 옵티마이저가 이를 선택하지 않았다. 반면 created_at으로 인덱스를 설정하니 아래와 같이 created_at 인덱스를 선택했고 여전히 filter를 하고 있지만 (커버링 index가 아님) 1% 미만으로 줄어들어 슬로우 쿼리가 개선 된 것을 확인할 수 있었다.