인덱스 스캔 방식

Index Range Scan

인덱스 레인지 스캔은 인덱스의 접근 방법 가운데 가장 대표적인 접근 방식으로, 밑에서 설명할 나머지 2가지 접근 방식보다는 빠른 방법이다.

SELECT * FROM employees WHERE first.name BETWEEN 'Ebbe' AND 'Gad';

아래 그림과 같이 루트 노드에서부터 브랜치 노드를 거쳐 최종적으로 리프 노드까지 찾아 들어가 필요한 레코드의 시작점을 찾는다. (두꺼운 화살표는 실제 스캔하는 범위)

시작 지점을 찾으면 그때부터는 리프 노드의 레코드만 차례대로 쭉 읽으면 된다. 만약 리프 노드의 끝까지 읽으면 리프 노드 간 링크를 이용해 다음 리프 노드를 찾아 다시 스캔을 시작한다.

위 그림은 실제 인덱스만 읽는 경우를 나타낸다. 하지만 B-Tree 인덱스의 리프 노드를 스캔하면서 실제 데이터 파일의 레코드를 읽어와야 하는 경우도 많은데, 이 과정을 더 자세히 나타내면 다음과 같다.

인덱스는 정렬되어있기 때문에 인덱스의 컬럼의 정순 또는 역순으로 레코드를 가져온다. 또한, 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는데 이때 한 건당 랜덤 I/O가 발생한다.

커버링 인덱스
인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고 최종 레코드를 읽어오는 과정이 필요 없는 인덱스다. 디스크를 읽지 않으므로 랜덤 읽기가 줄어들어 성능이 빨라진다.

Index Full Scan

인덱스의 처음부터 끝까지 모두 읽는 방식을 말한다.
대표적으로 쿼리 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우 Index Full Scan 방식이 사용된다.

(A, B, C)라는 인덱스가 있다고 가정하자.
SELECT * FROM employees WHERE B = 'b' AND C = 'c';
--> 조건절에 사용된 컬럼이 인덱스의 첫 번째(A) 컬럼이 아니므로 인덱스 풀 스캔이 일어난다.

1. 인덱스 리프 노드의 제일 앞 또는 제일 뒤로 이동한다.
2. 해당 위치에서 리프 노드를 연결하는 링크드 리스트를 따라 처음부터 끝까지 스캔한다.

Index Full Scan은 테이블 전체를 읽는 것보다 적은 디스크 I/O가 발생하기 때문에 테이블 전체를 읽는 것보다는 낫다고 볼 수 있지만 인덱스를 사용하는 측면에서는 효율적인 방식은 아니다. 즉, 인덱스를 효율적으로 사용하지 못하는 것으로 볼 수 있다.

Loose Index Scan

Loose Index Scan은 Oracle의 Index Skip Scan과 작동 방식이 비슷하다. Loose Index Scan은 말 그대로 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 의미한다.

Index Range Scan과 비슷하게 작동하지만 중간에 필요하지 않은 인덱스 키 값은 무시하고 넘어가는 형태로 처리된다. 일반적으로 GROUP BY 또는 MAX(), MIN() 함수에 대해 최적화를 하는 경우 사용한다.

MySQL 5.7 버전까지는 기능이 많이 제한적이었지만, MySQL 8.0 버전부터 최적화를 지원하기 시작했다.

Index Skip Scan

Index Skip Scan은 MySQL 8.0 버전에 추가된 최적화 기능으로 조건절에 첫 번째 인덱스가 없어도 두 번째 인덱스만으로 인덱스를 검색할 수 있게 해주는 기능이다.

예시로 앞에서 예로 들었던 쿼리를 다시 한번 살펴보자.

(A, B, C)라는 인덱스가 있다고 가정하자.
SELECT * FROM employees WHERE B = 'b' AND C = 'c';

Index Full Scan의 경우 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우 인덱스를 사용할 수 없었다. 하지만 MySQL 8.0 버전에 인덱스 스킵 스캔 최적화 기능이 도입되면서 옵티마이저가 A 컬럼을 건너뛰어서 B, C 컬럼만으로도 인덱스 검색이 가능하게 되었다.

GROUP BY의 인덱스 처리에만 사용할 수 있었던 루스 인덱스 스캔과는 다르게 인덱스 스킵 스캔은 WHERE 조건절의 검색에 사용 가능해져, 그 용도가 훨씬 넓어졌다.

다중 컬럼 인덱스

지금까지 살펴본 인덱스는 모두 1개의 컬럼만 포함된 인덱스였다. 하지만 실제 서비스에서는 2개 이상의 컬럼을 포함하는 인덱스가 더 많이 사용된다.

두 개 이상의 컬럼으로 구성된 인덱스를 다중 컬럼 인덱스라고 하며, 그 구조는 다음과 같다.

리프노드의 인덱스를 자세히 살펴보면 다중 컬럼 인덱스의 중요한 포인트가 나온다.

바로 인덱스의 두 번째 컬럼이 첫 번째 칼럼에 의존해 정렬되어 있다는 것이다. 이는 두 번째 컬럼이 첫 번째 컬럼이 똑같은 레코드에서만 의미가 있다는 것을 나타낸다. 이처럼 다중 컬럼 인덱스에서는 컬럼의 위치가 상당히 중요하기 때문에 이를 아주 신중히 결정해야 한다.

B-Tree 인덱스의 정렬과 스캔방향

인덱스를 생성할 때 설정한 정렬 규칙에 따라 인덱스의 키 값이 정렬된다. 하지만 인덱스가 오름차순으로 생성됐다고 해서 항상 오름차순으로만 읽는 것은 아니다. 인덱스를 어느 방향으로 읽을지는 쿼리에 따라 옵티마이저가 실시간으로 만들어내는 실행 계획에 따라 결정된다.

인덱스 스캔 방향

MySQL 옵티마이저는 인덱스의 스캔 방향을 전환해서 사용하도록 실행 계획을 만들어 낸다. 인덱스는 생성 시점에 오름차순 또는 내림차순으로 정렬 방향이 결정되지만 쿼리가 그 인덱스를 사용하는 시점에 인덱스 스캔 방향에 따라 오름차순 또는 내림차순 정렬 효과를 얻을 수 있다.

인덱스는 항상 정렬되어있으므로 최솟값부터 스캔을 시작하면 오름차순으로, 최댓값부터 읽으면 내림차순으로 값을 가져올 수 있기 때문이다. 따라서 ORDER BY 처리나 MIN() 또는 MAX() 함수 등의 최적화가 필요한 경우 인덱스를 읽는 순서만 변경해서 인덱스 생성 시 지정한 정렬 규칙에 대한 문제점을 해결할 수 있다.

내림차순 인덱스

InnoDB 스토리지 엔진에서는 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느릴 수밖에 없는 두 가지 이유가 있다.

1. 페이지 잠금이 인덱스 정순 스캔에 적합한 구조이기 때문
2. 페이지 내에 인덱스 레코드가 단방향으로만 연결된 구조이기 때문
   

InnoDB 스토리지 엔진에서 정순 스캔과 역순 스캔은 페이지 간의 양방향 연결 고리를 통해 전진하느냐 후진하느냐의 차이만 있지만 위와 같은 이유로 인해 인덱스 정순 스캔이 더 빠른 성능을 가지게 되는 특징을 가지고 있다.

마무리

우리는 어떤 조건에서 인덱스를 사용할 수 있을지 없을지를 알고서 인덱스를 최적에 맞게 생성해야한다.

특히 다중 컬럼 인덱스를 사용하게 되면, 2번째 컬럼이 1번째 컬럼에 의존해 다시 정렬되기 때문에, 순서가 매우 중요해질 수 있다.

순서에 따라 작업의 범위를 줄이는 것이 아닌 단순 필터링의 기능만 하게 되어 더 많은 데이터를 찾아야할 수도 있기 때문이다.

작업 범위를 결정하는 조건은 많으면 많을수록 쿼리의 처리 성능을 높여준다. 하지만 필터링 조건은 많다고 해서 성능을 높이지 못하고 오히려 느리게 만들 때가 있으니 주의해서 작성을 해야겠다고 생각했다.