인덱스(Index) 정복기 2

모든 내용은 Real MySQL 8.0 1권에서 가져왔습니다!!

인덱스 정복기 1

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

어떤 경우 인덱스를 사용하게 유도할지, 사용하지 못하게 할지 판단하려면 MySQL의 스토리지 엔진이 어떻게 인덱스를 사용해 실제 레코드를 읽는지 알아야 한다. 대표적인 세 가지 방법을 알아보자

인덱스 레인지 스캔

• 인덱스 레인지 스캔은 인덱스 접근 방법 가운데 가장 대표적인 접근 방법으로, 나머지 두 방식보다 빠른 방법이다.(레코드 한 건만 읽는 경우와 한 건 이상 읽는 경우를 모두 묶어 인덱스 레인지 스캔이라 하겠다.)

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

[Real MySQL 8.0 그림 8.8]

• 루트 노드에서부터 비교를 시작해 브랜치 노드를 거쳐 최종적으로 리프 노드까지 찾아 들어가야 레코드의 시작 지점을 찾을 수 있다.
• 시작 위치를 찾으면 리프 노드의 레코드만 순서대로 읽으면 된다.
• 순서대로 읽다 리프 노드의 끝에 다다르면 리프 노드 간의 링크를 이용해 다음 리프 노드를 찾아서 다시 스캔한다.
• 요구된 모든 데이터를 읽으면 사용자에게 읽은 데이터를 반환한다.
• 위 그림 8.8은 실제 인덱스만 읽는 경우를 보여준다. 실제 데이터 파일의 레코드도 읽어 와야 하는 경우도 많은데, 이 과정을 사진을 통해 자세히 살펴보자

[Real MySQL 8.0 그림 8.9]

• 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는 과정이 필요하다.

인덱스 레인지 스캔은 다음 3단계 과정을 거친다
1. 인덱스 탐색(Index seek): 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다.
2. 인덱스 스캔(Index scan): 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례로 쭉 읽는다.
3. 2번에서 읽은 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 Page를 가져오고, 최종 레코드를 읽어온다.

쿼리가 필요로 하는 데이터에 따라 3번 과정은 필요하지 않을 수도 있는데, 이를 커버링 인덱스라고 한다. 커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아 성능이 빨라진다.

인덱스 풀 스캔

• 인덱스를 사용하지만, 인덱스의 처음부터 끝까지 모두 읽은 방식
• 쿼리의 조건절에 사용된 column이 인덱스의 첫 번째 column이 아닌 경우 인덱스 풀 스캔 방식이 사용된다. 예를 들어, 인덱스는 (A, B, C) column 순서로 만들어져 있지만 쿼리의 조건절은 B 혹은 C column으로 검색하는 경우이다.
• 일반적으로 인덱스의 크기는 테이블의 크기보다 작아 테이블 풀 스캔보다 인덱스 풀 스캔이 효율적이다.
• 쿼리가 인덱스에 명시된 column만으로 조건을 처리하는 경우 주로 사용되는 방식이다. 인덱스뿐만 아니라 데이터 레코드까지 모두 읽어야 한다면 절대 이 방식으로 처리되지 않는다.

[Real MySQL 8.0 그림 8.10]

• 인덱스 리프 노드의 제일 앞으로(혹은 제일 뒤) 이동한 뒤, 인덱스의 리프 노드를 연결하는 Linked-List를 따라 처음부터 끝까지 스캔하는 방식
• 인덱스 레인지 스캔보단 느리지만 테이블 풀 스캔보단 효율적
• 인덱스에 포함된 column만으로 쿼리를 처리할 수 있는 경우 테이블 풀 스캔 할 필요가 없어 사용되는 방식이다.

루스(Loose) 인덱스 스캔

• 오라클의 '인덱스 스킵 스캔'과 비슷함
• 듬성듬성하게 인덱스를 읽는 것
• 인덱스 레인지 스캔과 비슷하게 작동하지만, 중간에 필요없는 인덱스 키 값은 무시(SKIP) 하고 다음으로 넘어가는 형태로 처리됨
• 일반적으로 GROUP BY 또는 MAX(), MIN() 함수를 최적화 하는 경우 사용됨

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

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

인덱스 스킵 스캔

• 인덱스의 핵심 값은 정렬돼있고, 이로 인해 인덱스를 구성하는 column의 순서는 매우 중요하다.

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

위와 같은 인덱스를 사용하려면 WHERE절에 gender column에 대한 비교 조건이 필수이다.

// 인덱스를 사용하지 못하는 쿼리
SELECT * FROM employees WHERE birth_date >= '1965-02-01';

// 인덱스를 사용할 수 있는 쿼리
SELECT * FROM employees WHERE gender = 'M' AND birth_date >= '1965-02-01';

하지만 MySQL 8.0부터 도입된 인덱스 스킵 스캔(Index skip scan) 기능으로 옵티마이저가 gender column을 건너뛰어 birth_date column만으로 인덱스 검색을 할 수 있게 되었다.
이전에 이와 비슷한 루스 인덱스 스캔(Loose index scan)이란 기능이 있었지만 group by 작업을 처리하기 위해 인덱스를 사용하는 경우에만 적용할 수 있었다.
인덱스 스킵 스캔은 WHERE 조건절의 검색을 위해 사용 가능하도록 용도가 훨씬 넓어진 것이다.

인덱스 스킵 스캔을 끄고 아래 쿼리의 실행계획을 살펴보자.

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

index라는 type은 '풀 인덱스 스캔'을 의미한다. 인덱스를 효율적으로 활용하지 못했다는 의미가 된다. 만약 SELECT 문에 요구하는 column이 인덱스에 없는 column을 요구했다면 '테이블 풀 스캔'이 일어났을 것이다.

인덱스 스킵 스캔을 활성화하고 다시 쿼리 실행계획을 살펴보자.

range라는 type으로 실행계획이 변경된 것을 알 수 있다. 이는 인덱스에서 꼭 필요한 부분만 읽었다는 것을 의미한다. MySQL 옵티마이저는 인덱스 스킵 스캔을 실행하기 위해 gender column에서 유니크한 값을 모두 조회하고 gender column 조건을 쿼리에 추가해 다시 실행하는 형태로 처리한다. 아래 그림을 보고 어떻게 처리되는지 살펴보자.

[Real MySQL 8.0 1권 그림 8.12]

gender column은 'M'과 'F' 두 가지 값만 가진다. 따라서 아래와 같이 변형된 쿼리를 이용해 탐색하도록 옵티마이저가 최적화해준다.

SELECT gender, birth_date FROM employees WHERE gender = 'M' AND birth_date >= '1965-02-01';
SELECT gender, birth_date FROM employees WHERE gender = 'F' AND birth_date >= '1965-02-01';

MySQL 서버는 루스 인덱스 스캔과 동일한 방식으로 인덱스를 읽으면서 인덱스에 존재하는 모든 값을 먼저 추출하고, 그 결과를 이용해 인덱스 스킵 스캔을 실행한다.

하지만 아직 MySQL 8.0에 도입된 인덱스 스킵 스캔은 아래와 같은 단점이 있다.

• WHERE 절에 조건이 없는 인덱스의 선행 column의 유니크한 값 개수가 적어야 함
• 쿼리가 인덱스에 존재하는 column만으로 처리 가능해야 함(커버링 인덱스)

첫 번째 단점은 만약 유니크한 값의 개수가 많다면 인덱스에서 스캔해야 할 시작 지점을 검색하는 작업이 많이 필요하게 된다(유니크한 값이 1000개라면 시작 지점을 1000번 찾아야 함). 따라서 인덱스 스킵 스캔은 인덱스 선행 column이 가진 유니크한 값의 개수가 소량일 때만 적용 가능한 최적화이다.

두 번째 제약 조건은 아래와 같은 쿼리를 사용했을때 발생할 수 있는 문제이다.

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

인덱스에 포함된 gender, birth_date 이외의 column을 SELECT 절에서 요구했기 때문에 실행계획의 type이 'all'로 테이블 풀 스캔이 일어났음을 알 수 있다. 하지만 이 문제는 MySQL 옵티마이저가 개선되면 해결될 수 있는 문제이다.

출처

Real MySQL 8.0 1권