인덱스 스캔의 종류

이 글의 모든 내용은 MySQL InnoDB 기준으로 작성했다.

InnoDB에서 인덱스

InnoDB에서 인덱스는 프라이머리 인덱스와 그외(세컨더리 인덱스)로 구분된다.

둘 다 B+Tree로 이루어져 있으며 프라이머리 인덱스는 클러스터 형이다.
원래 인덱스는 데이터 파일의 참조값을 갖고 있는데, 클러스터란 실제 레코드 값을 가지고 있다.

클러스터가 아닌 인덱스의 경우 인덱스를 두번 탐색해서 데이터 페이지 주소를 알게 되는데, 클러스터 인덱스의 경우 데이터 페이지까지 탐색하지 않아도 되서 빠르다는 장점이 있다.

B+Tree

B+Tree는 B-Tree와 비슷하지만, 리프 노드에만 키 값이 있으며, 리프 노드끼리 링크드리스트로 이어져 있다.

인덱스 스캔 종류

인덱스가 있다고 항상 사용하진 않는다. 어떤 상황에서 어떻게 사용되는지 알아보자.

인덱스 레인지 스캔

뒤에서 설명할 다른 방법보다 빠른 방법이다.
루트 노드부터 리프 노드를 찾아서 순서대로 탐색한다.
*커버링 인덱스인 경우 그대로 인덱스 값을 반환하면 되지만, 그렇지 않은 경우 각 노드마다 레코드 값을 읽으려고 재탐색(Random I/O)을 한다.
따라서 전체 레코드의 20~25%를 넘는 스캔을 하면 풀 테이블 스캔이 성능적으로 더 유리하다.

*커버링 인덱스 = 인덱스만 반환해 따로 레코드 탐색이 필요없는경우

인덱스 풀 스캔

인덱스의 처음부터 끝까지 스캔하는 방식이다.
리프 노드끼리 이어져 있어서 풀 테이블 스캔보다 빠르다.
인덱스는 테이블보다 크기가 몇배 작기 때문에 보다 적은 디스크 I/O가 일어난다.

루스 인덱스 스캔

인덱스 레인지 스캔과 비슷하지만, 모든 인덱스를 읽지 않고, 페이지 단위로 스킵한다.
GROUP BY, MIN, MAX 할때 주로 사용된다.

인덱스 스킵 스캔

예를 들어 설명하겠다.
(gender, birth_day) 복합 인덱스가 있고 gender값은 M, F 으로 제한한다. 가정했을 때

SELECT gender, birth_day 
FROM employee
WHERE birth_day >= '1998-09-25'

이 쿼리를 실행하면 인덱스를 사용하지 않는다. 이럴때 인덱스 스킵 스캔이 사용되는데, MySQL 8.0 부터 생긴 기능으로 이런 상황에서 gender에 상관없이 인덱스를 사용하도록 해준다.

스토리지 엔진 내부에선 두개의 쿼리가 실행되는 것과 비슷하게 처리된다.

gender = 'M' and birth_day >= '1998.09.25'
gender = 'F' and birth_day >= '1998.09.25'

굉장히 좋은 기능이라는 생각이 들지만, 생략된 인덱스의 카디널리티가 높은경우, 커버링 인덱스인 경우에만 사용이 가능하다는 단점이 있다.

마무리

DB에서 인덱스 설계는 정말 중요하다. 레코드락, 검색 최적화등 잘된 설계는 성능적 이점을 가져오지만, 그렇지 않다면 오히려 성능저하를 갖고 온다.
복합 인덱스의 순서, 적당한 인덱스 컬럼 선정 등 아직 자세히 감이 안온다. 설계를 하고 사용하며 지표를 보며 실험해보며 나만의 개념을 확립하고 싶다.