희우 위키

HOME ABOUT ME STUDY RSS

InnoDB B-Tree Index Scan Type, 인덱스 탐색 종류에 대해서 알아봅시다.

  • DB

목차

MySQL의 스토리지 엔진 중 InnoDB의 인덱스 스캔 종류에 대해서 간략하게 정리해보자.

스토리지 엔진이 인덱스에 어떻게 접근하여 탐색하는지를 아는 것은, 쿼리의 실행 계획(execute plain)을 분석하는데에 있어서 정말 많은 도움이 될 것이다.

또한 어떠한 컨텍스트에서 인덱스 탐색이 더 유용하게 작용될지 혹은 풀테이브 스캔이 더 적합할지 등에 대한 효율적인 쿼리 작성 의사결정의 근간이 될 것이라고 생각한다.

InnoDB B-Tree Index 탐색 유형

1. 인덱스 범위 탐색, Index Range Scan

인덱스에 접근하여 탐색하는 방법 중 가장 기본적이고 대표가 되는 방법이다.

말 그대로, 인덱스에 접근하여 범위(Range)를 탐색할 때 사용된다. 아래 테이블과 쿼리 그리고 실행계획을 통해 알아보자.

테이블 명세서

create table index_range_scan_table (
    id bigint auto_increment primary key,
    name varchar(20)
)ENGINE = InnoDB;

create index idx_name on index_range_scan_table (name);

아래 쿼리를 통해 실행계획을 확인해보자.

explain select * from index_range_scan_table where name between 'A' and 'C';

스크린샷 2023-06-12 오후 11 32 15

  • 검색을 해야 할 인덱스의 범위(Range)가 특정될 때 사용한다.
    • 루트노드, 브랜치 노드를 거쳐 리프노드에 접근하여 Range Scan이 수행된다.

  • <, >, IS NULL, BETWEEN, IN, LIKE, 인덱스 탐색이 되는 컬럼의 동등비교 연산 중 일부 상황 등에서 나타난다.

  • 보통 실행 계획의 type 컬럼에 const, ref, range가 표기되면 인덱스 레인지 스캔이라고 소통한다.

대부분의 쿼리는 Index Range Scan을 통한 인덱스 접근 및 탐색만으로도 어느정도의 최적화된 성능을 기대해볼 수 있다.

다만, B-Tree 인덱스의 구조상 리프 노드의 범위에 대한 실제 물리적인 스토리지의 레코드를 읽어와야하기 때문에 이 과정에서 Disk Random Access I/O가 일어난다.

Index를 통해서 레코드를 한 건 읽는데에 드는 비용은 테이블에서 직접 한 건을 조회하는 것보다 4~5배의 비용이 든다. - Real MySQL 8.0 -

따라서, 인덱스 스캔만으로 해결할 수 있는 커버링 인덱스(Covering Index) 상황을 제외하고

옵티마이저가 인덱스를 범위 탐색하기로 판단하였으나 스캔의 범위가 테이블 레코드 전체 건수의 20~25% 이상이라면 테이블을 풀스캔하는 방법도 고민해보면 좋을 것 같다.

2. 인덱스 풀 스캔 (Index Full Scan)

이것도 말 그대로 인덱스를 전체(Full) 탐색하는 것이다.

인덱스가 존재하고 인덱스를 탐색할 수 있지만, 특정한 상황에서 어쩔 수 없이 모든 인덱스를 읽어와야 하는 경우를 의미한다.

테이블 명세서

create table index_full_scan_table (
    id bigint auto_increment primary key,
    name varchar(20),
    name_1 varchar(20),
    name_2 varchar(20)
)ENGINE = InnoDB;

create index idx_name on index_full_scan_table (name, name_1, name_2);

아래 쿼리를 통해 실행계획을 확인해보자.

explain select * from index_full_scan_table where name_1 = 'B';

스크린샷 2023-06-12 오후 11 52 35

인덱스가 컬럼 3개 (name, name_1, name_2)로 다중 컬럼 인덱스(Multiple Column Index)로 구성되어 있다.

하지만, where 절의 조건은 name_1 컬럼만을 기입했으므로, idx_name 인덱스에 접근하여 모든 인덱스를 탐색하면서 name_1 컬럼에 대한 조건 일치 여부를 판단해야 한다.

  • 보통 실행계획의 type 컬럼에 index가 표기된다.

  • 대부분의 경우 Index Size < Table Record Size임을 만족시키므로 풀 테이블 탐색보다 인덱스 탐색이 더 효율적으로 동작하는 경우에 사용된다.

  • 위에서 언급했던 커버링 인덱스와 같이 Index Scan 탐색만으로 해결이 가능한 경우, 혹은 별도 정렬 작업을 위한 소트 버퍼(Sort Buffer)를 사용하지 않고도 이미 정렬되어 있는 인덱스를 통해 Ordering 혹은 Grouping 작업을 수행할 수 있는 경우에 사용된다.

이 경우도 마찬가지로 결국 리프노드에 접근하여 최종 스토리지에 랜덤 I/O가 일어나는 상황이라면, 인덱스 풀 스캔을 회피하고 테이블 풀 스캔(Table Full Scan)이 더 효율적일 것이다.

다만, MySQL 옵티마이저는 생각보다 똑똑한 녀석이기에, 애초에 그런 상황이라면 테이블 풀 스캔으로 안내했을 것이다.

3. 루스 인덱스 스캔 (Loose Index Scan)

루스 인덱스 스캔과 비교되는 스캔 방식은 타이트 인덱스 스캔(Tight Index Scan)이다.

먼저, 타이트 인덱스 스캔은 위에서 언급했던 인덱스 레인지 스캔(Index Range Scan)과 인덱스 풀 스캔(Index Full Scan)이라고 알고 있으면 좋을 것 같다.

그럼 루스 인덱스 스캔은 뭔가? 쉽게 말해서, 인덱스를 풀 스캔하지만 필요한 부분만 건너 뛰면서 읽는 방식이다.

이러한 인덱스 스캔 방식은 주로 Group By 혹은 집계함수와 같은 쿼리 패턴에서 나타나는데,

MySQL 공식 문서 중 Group By 구문에 대한 최적화와 관련된 문서에 상세하게 설명이 되어있으니 참고해보면 좋을 것 같다.

테이블 명세서

create table index_loose_scan_table (
    id bigint auto_increment primary key,
    name varchar(20),
    age int(20)
)ENGINE = InnoDB;

create index idx_name_age on index_loose_scan_table (name, age);

아래 쿼리를 통해 실행계획을 확인해보자.

explain select name, min(age), max(age) from index_loose_scan_table where age >= 5 group by name;

스크린샷 2023-06-13 오전 12 24 21

인덱스는 다중 컬럼 인덱스 (name, age)으로 구성되어 있으며, 각 이름에 대해 각 나이의 최소/최댓값을 구하려고 한다.

이 경우, name을 기준으로 그루핑을 하지만, age의 최소/최대값을 구해야하므로 먼저 name으로 인덱스 탐색을 시도하면서 조건에 맞는(age의 최소값을 찾고 최대값을 찾고) 레코드를 찾은 뒤 그다음 인덱스로 넘어간다.

이를 루스 인덱스 스캔(loose index scan)이라고 한다.

  • 실행계획의 Extra 컬럼에 'using index for group-by' 가 출력된다.
    • 다만, 위 실행계획에서는 표기되지 않았으며 풀 인덱스 스캔이 발생되었다. 그 이유를 아래에서 살펴보겠다.
  • 주로, Group By절과 집계함수가 같이 쓰이는 쿼리 패턴에서 이 스캔 방식이 사용된다.

위에서의 예시는 왜 루스 인덱스 스캔(Loose Index Scan)을 사용하지 못 했을까?

아무래도, WHERE 조건절에 의해 탐색된 레코드 건수가 적어서 인 것 같다. 검색된 레코드 건수가 적은 경우에는, 루스 인덱스 스캔을 통해 탐색하는 것보다 그냥 풀 스캔을 하더라도 빠르게 처리될 수 있기 때문이다.

그 외의 몇 가지 상황이 더 존재하기도 한다. 이는 다음에 실행 계획 분석 포스팅에서 살펴보도록 하자.

4. 인덱스 스킵 스캔, Index Skip Scan (Mysql 8.0)

테이블 명세서

create table index_skip_scan_table (
    id bigint auto_increment primary key,
    name varchar(20),
    age int(20)
)ENGINE = InnoDB;

create index idx_name_age on index_skip_scan_table (name, age);

아래 쿼리를 통해 예시를 살펴보자.

select * from index_skip_scan_table where age >= 5;
  • 인덱스가 다중 컬럼 인덱스 (name, age)로 구성되어 있으므로, name을 주지 않는 이상 풀 인덱스 스캔(full index scan)이 일어날 것이다.

아래와 같이 skip_scan을 활성화하고 다시 스캔을 해보자.

SET optimizer_switch ='skip_scan=on';

  • 실행계획의 Extra 컬럼에 Using index for skip scan 이 표기된다.

  • 인덱스에서 스캔해야 할 지점을 탐색하고, 두 번째 컬럼에 대한 조건을 찾는다.
    • 첫 번째 컬럼에 대해 스캔 지점을 건너뛰는 것이 핵심이다.

  • 쿼리가 인덱스에 존재하는 컬럼만으로 처리가 가능한 커버링 인덱스(Covering Index)를 사용할 수 있어야 한다.

  • 선행된 컬럼에 대한 유니크한 값이 적어야 한다.
    • 그래야, 처음 스캔 지점을 건너뛰면서 두번째 인덱스 컬럼에 대해서만 Index Range Scan이 가능해진다.

정리하며

인덱스 탐색 방식과 종류를 이해하는 것은 실행 계획 분석과 쿼리 튜닝에 필수적인 요소입니다.

또한, InnoDB의 동시성 제어 메커니즘인 Index Record 기반의 잠금을 이해할 때, 불필요한 인덱스 레코드에 잠금이 걸리지 않도록하기 위해서도 Index Scan 방식은 반드시 이해하고 있어야만 합니다.

다음 DB 포스팅에서는 쿼리의 실행 계획을 분석하는 방법에 대해서 소개하겠습니다.

읽어주셔서 감사합니다 :)

참고 문서

Previous May 11, 2023
« 리드 어헤드 (Read Ahead)