[MySQL] INDEX 최적화 - 1

인덱스를 왜 써야 할까?

• 데이터베이스의 성능은 디스크 I/O 영향이 크다
• 디스크와 메모리 I/O의 속도 차이는 10만 ~ 15만 배 정도로 차이가 크다. 요즘은 성능이 좋은 SSD도 보편화되었지만 그래도 메모리에 비해 많이 느리다. 즉, 성능 개선은 디스크 I/O를 줄이는 게 핵심이다.
• 인덱스를 사용하면 조회는 빨라지지만 데이터 생성, 수정, 삭제는 느려지는데 그럼에도 불구하고 우리는 왜 인덱스를 써야 할까? 웹 서비스에서 CUD와 R의 비율은 9:1 또는 8:2 정도라고 한다. GET 요청이 압도적으로 발생하기 때문에 전체적인 성능 이점을 취하기 위해 조회 성능 개선에 중점을 둔다.

player table

create index idx_game_nickname on player (game, nickname); // game, nickname 결합인덱스

예시를 위에 이미지와 같이 player 테이블에 geme, nickname, rank 값들이 있고 game과 nickname으로 결합 인덱스를 설정했다.

ORDER BY

SELECT * FROM player WHERE nickname > '무리' AND nickname < '타이' ORDER BY nickname;

쿼리를 살펴보면 nickname 조건을 걸고 nickname을 정렬시켰는데 인덱스가 없었다면 데이터를 전체 조회 후 DB에서 정렬을 했어야 했다. 하지만 인덱스는 이미 정렬이 되어 있기 때문에 인덱스 순서대로 파일을 읽기만 하면 된다.

GROUP BY

SELECT game, MAX(nickname) FROM player GROUP BY game;

인덱스를 설정한 후 GROUP BY를 하는 경우에는 game 컬럼의 첫 번째 LOL 값을 읽고 중간은 무시되고 첫 번째 OW 값을 읽기 때문에 디스크 I/O를 많이 줄일 수 있게 된다.

 

Scan 실행 계획

실행계획이란 사용자가 SQL을 실행하여 데이터를 추출하려고 할 때 옵티마이저가 수립하는 작업 절차인데 중요한 실행 계획 3가지에 대해서 정리해보자.

1. Full Table Scan (ALL)

테이블에 존재하는 모든 데이터를 하나씩 읽어 가면서 조건에 맞으면 결과로 추출하고 조건에 맞지 않으면 버리는 방식으로 진행된다. 위에 계속해서 언급한 디스크 I/O는 시간이 많이 걸리는 작업이기 때문에 이 스캔 방식은 당연히 성능이 좋지 않다.

Full Table Scan이 일어나는 경우는

• Index가 없을 때
• Index가 있음에도 Full Table Scan이 일어나는 경우가 있는데 데이터 수가 많지 않거나 읽고자 하는 데이터 수가 전체의 25%를 넘겼을 경우이다. 

2. Index Range Scan

가장 일반적인 Index Scan 방식으로 Index를 트리구조에서 수직 탐색 후 필요한 범위까지만 탐색하는 방식이다. Index 데이터는 정렬된 상태로 저장이 되기 때문에 범위에 대한 탐색의 효율성이 좋다. 즉, 탐색할 범위가 줄어들기 때문에 디스크 I/O를 줄일 수 있다.

3. Index Full Scan

수직 탐색 후 Index 전체를 탐색하는 방식이다 Index는 데이터 파일보다는 크기가 작기 때문에 Full Table Scan 보다는 성능이 좋다.  그러나 이 방법은 생성된 Index가 없어 사용된 방법으로 Index Range Scan을 할 수 있도록 유도하는 것이 좋다.

 

어떻게 인덱스를 걸어야 좋을까?

어떤 조회가 많이 일어나는지 파악이 되어야 하고 카디널리티(특정 데이터 집합의 유니크한 값의 개수) 높은 컬럼부터 인덱스 생성해야 한다.

위의 테이블 정보에서 nickname이 중복이 일어나지 않기 때문에 카디널리티가 높다.

game의 경우에는 LOL과 OW라는 중복 값을 가지고 있기 때문에 카디널리티가 2이며 카디널리티가 낮다.

그렇다면 nickname에 인덱스를 거는 것이 탐색했을 때 성능상 유리하다.

create index idx_nickname on player (nickname);

인덱스 걸기 전에는 Full Table Scan(ALL)에서 Index Range Scan으로 실행 계획이 변경됨으로 조회 시간도 단축되었음을 확인할 수 있다.

복합인덱스는 어떨까?

두 개 이상의 컬럼을 합쳐서 인덱스를 설정하는 것인데 하나의 컬럼으로 인덱스를 만들었을 때 보다 탐색할 데이터 수가 줄어들게 된다.

create index idx_rank_nickname on player (rank, nickname);

복합 인덱스로 rank와 ninkname으로 설정하게 하였고 같은 rank 값이어도 rank 안에서 nickname은 가나다 순으로 정렬이 될 것이다.

인덱스가 정렬된 기준에서 검색 쿼리를 날렸을 때 탐색 범위를 줄일 수 있을지 생각하면 복합 인덱스를 쓸 수 있을지 없을지 알 수 있다.

SELECT * FROM player WHERE rank > 3 AND nickname > '무지';

rank 순으로 정렬되어 있고 nickname 순으로도 정렬되어 있어서 rank가 3 이상 nickname이 '무지' 이후인 데이터만 가져오면 되기 때문에 탐색 범위가 줄어든다.

이번에는 nickname 기준으로만 검색을 해보자.

SELECT * FROM player WHERE nickname > '가라';

가라 같은 경우에는 인덱스 정렬이 5번째 위치한 nickname 값이고 '가라' 이후의 nickname들이 앞에도 뒤에도 있기 때문에 이 정렬 기준으로는 원하는 만큼 데이터 탐색 범위를 줄일 수 없어서 Full Table Scan이 일어나게 된다.

 

추가로 커버링 인덱스와 인덱스 컨디션 푸시다운에 대해서 정리하려고 했는데 시간이 없어서 다음 포스팅 글 [MySQL] INDEX 최적화 - 2에 정리해보자.