[MySQL] B-Tree 인덱스 - 2

이전 글에 이어서 B-Tree 인덱스를 MySQL 관점에서 살펴보겠습니다.

[MySQL] B-Tree 인덱스 - 1

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

인덱스를 잘 사용하기 위해서는 MySQL이 인덱스를 통해 어떻게 실제 레코드를 읽는지 알아야 합니다. 여기서는 MySQL이 인덱스를 이용하는 대표적인 방법을 살펴보겠습니다.

인덱스 레인지 스캔

인덱스를 사용하는 가장 대표적이고 가장 빠른 방법입니다. 인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정되었을 때 사용하는 방식입니다. 다음 쿼리를 살펴봅시다.

mysql> SELECT * FROM employees WHERE first_name BETWEEN 'Eden' AND 'Charlie';

인덱스 레인지 스캔

employees 테이블은 first_name에 대하여 인덱스를 가지고 있기 때문에 탐색해야 할 인덱스의 범위를 아래의 방식으로 좁힐 수 있습니다. 

 

1. 필요한 레코드의 시작 리프 노드를 찾는다.

2. 리프노드를 순서대로 읽는다.

3. 만약 페이지를 모두 읽으면 다음 페이지 링크를 이용해 이동한다.

4. 위 과정을 반복하다가 스캔을 멈춰야할 위치에 다다르면 지금까지 읽은 레코드를 반환한다.

 

인덱스 레인지 스캔을 하더라도 데이터 파일에서 레코드를 읽어오는 과정은 필요합니다. 레코드 한 건당 랜덤 I/O가 발생하므로 인덱스를 통한 검색은 비용이 많이 드는 작업입니다.

인덱스 풀 스캔

인덱스의 처음부터 끝까지 모두 읽는 방식입니다. 대표적은 사용 예는 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우입니다. (A, B, C) 칼럼 순서로 만들어진 인덱스를 예로 들면 B나 C 칼럼으로 검색하는 경우입니다.

 

일반적으로 인덱스의 크기는 테이블의 크기보다 작습니다. 때문에 테이블 대신 인덱스를 읽는 것이 효율적입니다. 만약 쿼리가 인덱스의 칼럼만으로 처리할 수 있는 경우 이 방식이 사용됩니다. 반대의 경우 절대 인덱스 풀 스캔 방식을 사용하지 않습니다.

루스 인덱스 스캔

인덱스 레인지 스캔과 비슷하게 동작하지만 중간에 필요치 않은 인덱스 키 값은 무시(SKIP)하고 다음으로 넘어가는 형태입니다. 주로 GROUP BY 또는 집계 함수인 MAX(), MIN()은 최적화하는 경우에 사용됩니다.

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

dept_emp 테이블에는 (dept_no, emp_no) 조합으로 구성된 인덱스가 있습니다. 즉, dept_no로 정렬되어 있고 그다음 emp_no로 정렬되어 있다는 뜻입니다. dept_no에서 최소인 emp_no를 구하는 쿼리이므로 dept_no 범위 중 emp_no가 가장 작은 레코드만 읽으면 됩니다.

루스 인덱스 스캔

인덱스 스킵 스캔

데이터베이스 서버에서 인덱스의 핵심은 값이 정렬돼 있다는 것이며, 이로 인해 인덱스를 구성하는 칼럼의 순서가 매우 중요해집니다. 다음과 같은 인덱스를 생성해 보겠습니다.

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

이 인덱스를 사용하기 위해선 WHERE 절에 gender 칼럼에 대한 비교는 필수적입니다. 그렇지 않으면 인덱스 풀 스캔을 사용하게 됩니다. gender에 대해 정렬되어 있고 그 다음 birth_date에 대해 정렬되어 있어서 그렇습니다. 아래 쿼리는 인덱스 풀 스캔을 합니다.

mysql> SELECT gender, birth_date
       FROM employees
       WHERE birth_date>='1965-02-01';

 

MySQL 8.0 버전부터 옵티마이저가 gender 칼럼을 건너뛰어서 birth_date 칼럼만으로 인덱스 검색을 가능하게 해주는 인덱스 스킵 스캔 최적화 기능이 도입됐습니다.

 

이전에 gender 칼럼에 대한 조건 없이 birth_date 칼럼의 비교 조건만 가진 쿼리는 인덱스 풀 스캔을 사용했습니다. 하지만 인덱스 스킵 스캔이 도입되면서 다음과 같은 쿼리로 최적화합니다.

// gender 칼럼이 ENUM('M', 'F') 타입인 경우
mysql> SELECT gender, birth_date FROM employees WHERE gender='M' birth_date>='1965-02-01';
mysql> SELECT gender, birth_date FROM employees WHERE gender='F' birth_date>='1965-02-01';

gender 조건이 없으므로 gender 조건을 추가하여 실행합니다.

인덱스 스킵 스캔

인덱스 스킵 조건에는 새로 도입된 기능이라 다음과 같은 단점이 있습니다.

 

1. WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야함

2. 쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능해야함(커버링 인덱스)

 

첫 번째 단점을 설명하자면, 만약 유니크한 값이 많다면 스캔을 시작하는 지점을 검색하는 작업이 많아집니다. 바꿔말하면 선행 칼럼의 유니크한 값으 개수만큼 스캔 시작위치를 탐색해야합니다.

 

두 번째 단점을 설명하기 위해 다음 쿼리를 살펴봅시다.

mysql> SELECT *
       FROM employees
       WHERE birth_date>='1965-02-01';

SELECT 절에 인덱스에 포함되지 않은 칼럼도 조회하도록 변경했습니다. 이 쿼리는 인덱스에 포함되지 않은 나머지 칼럼도 필요하기 때문에 인덱스 스킵 스캔을 사용하지 못하고 풀 테이블 스캔을 사용합니다.

다중 칼럼 인덱스

두 개 이상의 칼럼으로 구성된 인덱스를 다중 칼럼 인덱스(또는 복합 칼럼 인덱스)라고 합니다. 다중 칼럼 인덱스에서 중요한 점은 두 번째 칼럼이 첫 번째 칼럼에 의존해 정렬돼 있다는 것입니다. 죽, 두 번째 칼럼의 정렬은 첫 번째 칼럼이 똑같은 레코드에서만 의미가 있습니다. 그래서 다중 칼럼 인덱스는 인덱스 내의 순서가 상당히 중요합니다.

다중 칼럼 인덱스