8.인덱스 - B-Tree Index를 통한 데이터 읽기

이번 포스트에서는 B-Tree Index를 통한 데이터 읽기에 대해서 알아보도록 하겠습니다.

 

인덱스 레인지 스캔

검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식입니다. 인덱스의 접근 방법 가운데 가장 대표적인 방법이고 밑에서 설명할 두 방식보다 빠른 방식입니다.

 

루트 노드부터 브랜치 노드와 최종 리프 노드까지 찾아 들어가 필요한 레코드의 시작점을 찾고 리프 노드의 레코드를 순서대로  읽습니다. 만약 스캔하다가 리프 노드의 끝까지 읽으면 리프 노드간의 링크를 이용해 다음 리프 노드를 찾아서 스캔하고 최종적으로 스캔을 멈춰야 할 위치에 다다르면 지금까지 읽은 레코드를 사용자에게 반환하고 쿼리를 종료합니다.

인덱스만 읽는 경우의 그림

위 사진은 인덱스만 읽는 경우를 보여주는 사진입니다. 하지만 B-Tree 인덱스의 리프 노드를 스캔하면서 실제 데이터 파일의 레코드를 읽어 와야 하는 경우도 많습니다.

인덱스 레인지 스캔을 통해 실제 데이터 레코드 읽는 경우의 그림

리프 노드에 저장된 레코드 주소로 데이터 파일에서 레코드를 읽어오는데, 레코드 한 건 단위로 랜덤 I/O가 발생하기 때문에 비용이 많이 드는 작업으로 분류됩니다. 인덱스를 통해 읽어야 할 레코드가 테이블 전체 레코드의 20~25%가 넘어서면 인덱스를 통한 읽기보다 테이블의 데이터를 직접 읽는 것이 더 효율적인 처리 방식이 될 수 있습니다.

 

인덱스 레인지 스캔방식을 정리하자면

1. 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾음. 이 과정을 index seek라고 함
2. 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 읽음. 이 과정을 index scan이라고 함
3. 2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고 최종 레코드를 읽음. (커버링 인덱스로 처리되는 쿼리는 3번 과정이 생략될 수 있음)

커버링 인덱스란 쿼리가 인덱스에 존재하는 컬럼만으로 처리가 가능한 것을 의미합니다.

 

인덱스 풀 스캔

쿼리가 인덱스에 명시된 컬럼만으로 조건을 처리할 수 있는 경우에 주로 사용 되며 인덱스뿐만 아니라 데이터 레코드까지 모두 읽어야 하는 경우에는 절대 이 방식으로 처리되지 않습니다. 인덱스 레인지 스캔과 달리 인덱스의 처음부터 끝까지 모두 읽는 방식입니다.

 

대표적으로 쿼리 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우 인덱스 풀 스캔이 일어납니다.

예를 들어 다중 컬럼 인덱스(A, B, C)가 있고 쿼리 조건절에 B, C, A 순으로 검색을 한다던가 B, C만 사용해 검색하는 경우에 발생합니다.

인덱스 풀 스캔

 

루스 인덱스 스캔

느슨하게 또는 듬성듬성하게 인덱스를 읽는 방식입니다.

인덱스 레인지 스캔과 비슷하게 동작하지만 중간에 필요치 않은 인덱스 키 값을 무시하고 다음으로 넘어가는 형태로 처리됩니다.

일반적으로 GROUP BY 또는 집합 함수 가운데 MAX(), MIN() 함수에 대해 최적화를 하는 경우에 사용됩니다.

루스 인덱스 스캔

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

dept_emp 테이블은 dept_no, emp_no 두 개의 컬럼으로 인덱스가 생성되어 있고 (dept_no, emp_no) 조합으로 오름차순 정렬이 돼 있어서 dept_no 그룹 별로 첫 번째 레코드의 emp_no만 읽으면 됩니다. 즉 인덱스에서 WHERE 조건을 만족하는 범위 전체를 다 스캔할 필요가 없다는 것을 옵티마이저가 알고 있기 때문에 조건에 만족하지 않는 레코드는 무시하고 다음 레코드로 이동하게 됩니다. 

 

루스 인덱스 스캔을 사용하려면 여러 가지 조건을 만족해야 하는데 이 조건은 10장 실행 계획에서 자세히 다루어 보도록 하겠습니다.

 

인덱스 스킵 스캔

데이터베이스 서버에서 인덱스의 핵심은 값이 정렬돼 있다는 것이며, 이로 인해 인덱스를 구성하는 컬럼의 순서가 매우 중요합니다.

mysql> ALTER TABLE employees
ADD INDEX ix_gender_birthdate(gender, birthdate);

이렇게 인덱스를 생성하게 되면 WHERE 조건절에 gender 컬럼에 대한 비교 조건이 필수로 들어가야 인덱스 레인지 스캔이 가능합니다.

 

MySQL8.0 이전 버전에서는 gender 컬럼을 쓰지않고 루스 인덱스 스캔을 이용하여 최적화 할 수 있었지만 GROUP By 작업을 처리하기 위해  인덱스를 사용하는 경우에만 적용할 수 있었습니다. 하지만 MySQL 8.0부터 인덱스 스킵 스캔이 도입되어 WHERE 조건절에 필수 인덱스가 빠져도 인덱스 레인지 스캔이 가능하게 되었습니다.

 

SET optimizer_switch='skip_scan=on'; 명령어로 인덱스 스킵 스캔 기능 활성이 가능하며 MySQL 옵티마이저는 필수 인덱스 컬럼에서 유니크한 값을 모두 조회해서 주어진 쿼리에 필수 인덱스 컬럼의 조건을 추가해서 쿼리를 다시 실행하는 형태로 처리됩니다.

이때 인덱스의 유니크한 값의 개수가 매우 많다면 옵티마이저가 인덱스에서 스캔해야 할 시작 지점을 검색하는 작업이 늘어나게 되어 성능이 더 떨어질 수 있습니다. 그러므로 가능하면 인덱스의 유니크한 값의 개수가 적을 때 사용하는 것이 효율적입니다. 또한 인덱스의 존자해는 컬럼(커버링 인덱스)만으로 처리가 가능하지 못 하면 테이블 풀 스캔이 발생하여 인덱스 스킵 스캔이 불가능하므로 이점도 주의해야 합니다.