[42번] 인덱스 스킵 스캔(2)

#42

문제

다음 employees 테이블이 있다

CREATE TABLE employees (
  emp_no INT NOT NULL,
  birth_date DATE NOT NULL,
  first_name VARCHAR(14) NOT NULL,
  last_name VARCHAR(16) NOT NULL,
  gender ENUM('M','F') NOT NULL,
  hire_date DATE NOT NULL,
  PRIMARY KEY (emp_no),
  INDEX idx_gender_birth_date (gender, birth_date)
);

 

다음 쿼리에 대한 실행계획 중 가장 알맞은 것을 고르시오.

(단, MySql 8.0 이상이며 디폴트 설정을 따른다)

select * from employees where birth_date >= '1965-02-01';

 

선지

A) 테이블 스캔 : 인덱스를 활용하지 않고 전체 테이블을 스캔함

B) 인덱스 풀 스캔 : 인덱스 테이블을 전체 스캔함

C) 인덱스 스킵 스캔 : 인덱스 테이블을 건너 뛰면서 읽음

D) 인덱스 스캔 후, 테이블 스캔

정답

A) 테이블 스캔 : 인덱스를 활용하지 않고 전체 테이블을 스캔함

B) 인덱스 풀 스캔 : 인덱스 테이블을 전체 스캔함

C) 인덱스 스킵 스캔 : 인덱스 테이블을 건너 뛰면서 읽음

D) 인덱스 스캔 후, 테이블 스캔

 

핵심 해설

 

해당 쿼리에서는 인덱스 컬럼인 gender, birth_date 뿐만 아니라 first_name, last_name 등의 컬럼을 조회해야 합니다. 인덱스 테이블만으로 결과반환이 가능한 커버링 인덱스 상황이 아니므로, 테이블 접근이 필수적으로 필요해집니다.

 

따라서 옵티마이저는 스킵스캔이나 인덱스 테이블을 접근하고 테이블을 2차적으로 접근하는 것이 아니라 테이블을 모두 순회하여 조건에 맞는 결과를 반환하는 것이 훨씬 저렴하다고 판단합니다.

 

상세 해설

인덱스 스킵 스캔의 2가지 조건 

인덱스 스킵스캔은 옵티마이저가 스킵스캔을 사용하는 것이 저렴할 때, 활용하게 됩니다.

그리고 주로 스킵스캔을 사용하는 것이 저렴한 경우는 2가지 조건이 만족될 때입니다.

 

첫번째 조건) 생략된 선행 컬럼의 범위가 넓지 않아야 합니다.

두번째 조건) 커버링 인덱스

 

주어진 위의 쿼리는 두번째 조건인 커버링 인덱스 조건을 위반하므로 스킵스캔을 활용하지 못합니다.

 

인덱스 스캔 후 테이블 접근은 가능하지 않나요?

현재 인덱스에는 gender와 birth_date 컬럼만 있습니다. 따라서 결과반환을 위해서는 테이블 데이터의 접근이 필수적으로 필요해집니다.

만약 인덱스 스캔 후, 반환된 클러스터링 인덱스마다 각각 개별 Random I/O를 거치면 디스크 비용이 증가하고 실행시간이 오히려 증가할 우려가 있습니다.

 

따라서 옵티마이저는 테이블 풀 스캔을 통한 한번의 탐색으로 결과를 반환하는 것이 훨씬 저렴하다고 판단하게 됩니다.

 

실습으로 알아보기

type : ALL로 idx_gender_brithdate를 활용하지 않고 테이블 풀스캔을 활용하는 모습을 볼 수 있습니다.

+--+-----------+---------+----------+----+-------------+----+-------+----+------+--------+-----------+
|id|select_type|table    |partitions|type|possible_keys|key |key_len|ref |rows  |filtered|Extra      |
+--+-----------+---------+----------+----+-------------+----+-------+----+------+--------+-----------+
|1 |SIMPLE     |employees|null      |ALL |null         |null|null   |null|300094|33.33   |Using where|
+--+-----------+---------+----------+----+-------------+----+-------+----+------+--------+-----------+