[MySQL] B-Tree 인덱스 - 2

인덱스를 적재적소에 활용하려면, 결국 MySQL이 어떻게 인덱스를 타고 실제 레코드를 읽어내는지에 대해 좀 더 깊이 살펴봐야 한다.

 

인덱스 레인지 스캔

인덱스 레인지 스캔은, 인덱스의 접근 방법 가운데 가장 대표적인 접근 방식이다.

B-Tree 인덱스의 필요한 영역을 스캔하는데에는 어떤 작업이 필요할까 ? 다음 쿼리를 예제로 살펴보자

SELECT * FROM employees WHERE first_name BETWEEN 'Ebbe' AND 'Gad';

인덱스를 이용한 레인지 스캔

 

인덱스 레인지 스캔은, 검색해야할 범위가 결정되었을 때 사용하는 방식이다.

위 그림은 "인덱스"를 읽는 경우에 대한 그림인데, 크게 두 가지 과정이 수반된다. 이는 먼저 브랜치 노드를 거쳐 리프노드의 레코드 시작지점을 찾는 과정, 그리고 해당 시작 지점부터 리프노드의 레코드를 순서대로 읽는 과정이고, 이들을 인덱스 탐색, 인덱스 스캔이라 한다.

(만약 하나의 리프 노드를 끝까지 읽으면, 리프 노드 간 링크를 이용해 다음 리프노드를 스캔한다)

 

최종적으로 스캔을 멈춰야 할 위치에 다다르면 지금까지 읽은 레코드를 사용자에게 리턴하고 쿼리를 끝낸다.

위의 경우는 인덱스만 읽는 경우이고(특정상황), 실제 데이터파일의 레코드까지 읽어야하는 경우가 많다.

 

 

인덱스 레인지 스캔을 통한 데이터 레코드 읽기

 

위 그림은 인덱스 탐색 과정을 거치고, 스캔 시작 위치에서부터 인덱스를 읽어나가는 과정이다. 앞전 그림과 다르게 실제 레코드를 읽는다.

눈여겨볼 점은, 스캔 방식에 상관 없이 해당 인덱스를 구성하는 칼럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져온다는 것이다.

당연하게도 만약 3건의 레코드가 검색 조건에 일치한다고 가정하면, 최대 3번의 디스크 입출력 (랜덤 I/O)가 발생할 것이다.

이 랜덤 I/O가 발생한다는 사실은 인덱스를 통해 읽어야할 데이터가 20~25%가 넘어가면 그냥 직접 읽는게 효율적인 이유와 연결된다.

 

쿼리가 필요로 하는 데이터에 따라 실제 데이터 레코드를 읽는 과정이 필요하지 않을 수도 있다. (조금만 생각해보면 간단하다)

이를 커버링 인덱스라고 하는데, 커버링 인덱스는 한마디로 SELECT, WHERE, ORDER BY, GROUP BY 등의 쿼리에 사용되는 모든 컬럼이 포함된 인덱스의 구성요소인 경우를 뜻한다.

이 경우는 당연히 해당 쿼리에 필요한 모든 데이터가 인덱스 키 값에 포함되어있으므로 굳이 실제 레코드를 읽을 필요는 없다.

 

인덱스 풀 스캔

인덱스 레인지 스캔과 마찬가지로 인덱스를 사용하지만, 인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다.

예를 들어 인덱스는 (A,B,C) 컬럼의 순서로 만들어져있지만 쿼리의 조건절은 B, C 컬럼으로 검색하는 경우이다.

 

이게 뭔말이냐, 기본적으로 인덱스의 컬럼이 A,B,C 순서로 되어있으면 B는 A에 의존하고, C는 B에 의존하여 정렬되게 된다.

인덱스 정렬의 우선순위가 A,B,C 순서라는 뜻이다. 근데 이 상황에서 A가 아닌 B컬럼으로 조회를 하게 된다면 당연히 해당 인덱스는 B로 정렬된게 아니기 때문에 레인지스캔을 할 수 가 없다.

자세히 말하면, 인덱스의 선행 컬럼이 조건절에 포함되지 않은 경우에 인덱스 풀 스캔을 할지 해당 인덱스를 사용하지 않을지는 상황에 맞게 옵티마이저가 결정한다.

 

일반적으로 인덱스의 크기는 테이블의 크기가 작으므로 직접 테이블을 다 읽는것보다 인덱스만 읽는 것이 효율적이다.
쿼리가 인덱스에 명시된 칼럼만으로 조건을 처리할 수 있는 경우 주로 인덱스 풀 스캔 방식이 사용된다.

(만약 인덱스뿐만아니라 데이터 레코드까지 읽어야하는 경우 절대 인덱스 풀 스캔 방식으로 처리되지 않는다)

 

인덱스 풀 스캔

 

그림을 보자. 먼저 인덱스 리프 노드의 제일 앞 또는 뒤로 이동한 후, 노드 간 링크를 이용해 인덱스를 처음부터 끝까지 스캔한다.

상기했듯이 이 방식은, 테이블 풀 스캔보다 효율적이다.
만약 인덱스에 포함된 컬럼만으로 쿼리를 처리할 수 있는 경우 레코드를 직접 읽을 필요가 없기 때문에, 이러한 경우에서는 충분히 테이블 풀 스캔보다 적은 디스크I/O로 쿼리를 처리할 수 있다. (물론 선행조건은 인덱스의 선행 칼럼이 조건절에 포함되지 않은 경우)

 

루스 인덱스 스캔

인덱스 풀 스캔은, 테이블 풀 스캔에 비해 좋은 케이스인 것이지 절대 효율적인 방식이 아니며 일반적으로 인덱스를 생성하는 목적도 아니다.

앞의 두 가지 스캔 (인덱스 레인지 스캔, 인덱스 풀 스캔) 은 루스 인덱스 스캔과 반대로 타이트 인덱스 스캔으로 분류된다.

루스 인덱스 스캔은 인덱스 레인지 스캔과 비슷하게 작동하지만 말그대로 중간에 불필요한 인덱스 키 값은 스킵하고 넘어가는 형태이다.

 

SELECT dept_no, MIN(emp_no)
FROM dept_emp
WHERE dept_no BETWEEN 'd002' AND 'd004'
GROUP BY detp_no;

 

만약 위 테이블에 dept_no, emp_no 두 개의 칼럼으로 인덱스가 생성돼있고, (dept_no, emp_no) 조합으로 정렬이 되어있다고 해보자.

동일한 dept_no에 대해서는 emp_no으로 정렬되어있기 때문에, 옵티마이저는 각 dept_no 그룹의 첫 번째 레코드의 emp_no 값만 읽으면 된다. 옵티마이저는 동일 dept_no 그룹 내의 다른 레코드들이 불필요하다는걸 알기 때문에 스킵한 것이다,

(루스 인덱스 스캔을 사용하기 위해서는 여러 조건을 만족해야하는데, 실행계획 편에서 자세히 알아볼 예정)

 

인덱스 스킵 스캔

인덱스 루스 스캔은 GROUP BY에서만 이용할 수 있지만 인덱스 스킵 스캔은 WHERE 조건절 검색을 위해서도 사용 가능하도록 용도가 넓어졌다. 기존에는 WHERE 조건절에 인덱스의 첫 번째 칼럼이 포함되어야 효율적으로 인덱스를 이용할 수 있다고 하였다.

 

여기 employees 테이블에 (gender, birth_date) 순의 칼럼을 가지는 인덱스가 있다고 해보자. 이 상황에서

인덱스 스킵 스캔기능을 비활성화하고 birth_date만을 WHERE 조건으로 가지는 gender, birth_date 조회 쿼리를 해보자.

(gender는 M,F 두 개의 값만 가지는 enum타입이다)

type 값을 보면, 인덱스 풀 스캔이 일어났다. 인덱스 풀 스캔이 일어난 이유는 위에서 말한 (A,B,C) 예제와 일치하는데,

별도의 인덱스 스킵 스캔 설정 없이는 인덱스의 첫번째 칼럼이 WHERE 조건절에 없기 때문에 기본적으로 인덱스를 효율적으로 이용하지 못한다고 판단하고 테이블 풀 스캔 or 인덱스 풀 스캔이 일어나게 된다.

(여기서는 인덱스 키 값에 쿼리에서 요구하는 gender, birth_date가 모두 있기 때문에 테이블 풀 스캔은 일어나지 않았다)

 

여기까지만 봐도 만약 인덱스 스킵 스캔기능을 활성화한다면 어떻게 될지 감이 잡힐 것이다.

결국 저 쿼리를 효율적으로 실행하려면 정렬된 birth_date를 조회해야하는데 생각해보면 (gender, birth_date)로 생성된 인덱스는

gender = m 인 경우에 대해 birth_date로 쭉 정렬, gender = f 인 경우에 대해 birth_date로 정렬이 되어있을 것이다.

 

따라서 인덱스 스킵 스캔을 활성화 시 먼저 gender 칼럼에 대한 유니크 값을 모두 조회하고, 원래 쿼리에 없던 gender 칼럼을 추가해서 쿼리를 실행한다. 이게 뭔소리냐면 

SELECT gender, birth_date FROM employees WHERE gender = 'M' AND birth_date >= '~';
SELECT gender, birth_date FROM employees WHERE gender = 'F' AND birth_date >= '~';

 

위와 같은 형태의 최적화를 하게 된다. gender = M, F 인 경우에 대해 새로 조건을 추가한 쿼리를 실행하여 인덱스를 효율적으로 활용한다.

그렇다고 gender가 Enum 타입이기 때문에 인덱스 스킵 스캔이 가능하게 된 것은 아니다. 앞에서 말했듯이 기본적으로 gender의 타입과 상관없이 gender 칼럼의 유니크한 값을 루스 인덱스 스킨과 같은 방식으로 먼저 조회한다.

 

아쉽게도 이 방식의 단점이 존재하는데, 위 예시에서 gender 칼럼의 유니크한 값의 개수가 적다면, 문제가 발생한다.

 

먼저 Enum타입의 gender가 아니라, 카디널리티가 높은 컬럼이였다고 가정해보면, 레인지 스캔 시작 지점을 검색하는 작업(= 선행컬럼의 유니크 값을 조회하는 작업) 의 비용이 높아지므로 레인지 스캔을 시작하기도 전부터 성능이 떨어질 것이다.

 

또한 WHERE 조건절 뿐만 아니라 조회해야하는 컬럼에도 영향을 받는다. 어쩌면 당연한 소리지만 만약 gender, birth_date 만 조회하는 것이 아니라, 동일 조건에서 "SELECT *" 를 하는 상황이였다고 해보면 당연히 테이블 풀 스캔으로 넘어갈 것이다.

(인덱스 키 값에는 gender, birth_date 만 있기 때문에 인덱스 스킵 스캔이 발생하더라도 효율적인 인덱스 사용이 불가능함)