[41번] 인덱스 스킵 스캔(1)

#41

문제

다음 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_birthdate (gender, birth_date)
);

 

 

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

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

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

 

선지

 

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

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

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

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

 

정답

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

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

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

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

 

핵심 해설

 

스킵 스캔이란 복합 인덱스의 선행컬럼 조건이 없더라도 인덱스를 사용할 수 있게 하는 스캔 방식입니다.

 

스킵 스캔을 통해 결과를 반환하는 조건은 다음 두가지입니다.

- 조건절에 생략된 인덱스 선행 컬럼의 유니크한 고유 값의 개수가 적어야 함.

- 커버링 인덱스 : 인덱스에 존재하는 컬럼만으로 처리가능해야 함.

 

해당 상황에서는 idx_gender_birthdate 인덱스에 대하여 birth_date는 조건은 존재하지만 선행컬럼인 gender에 대한 조건이 존재하지 않습니다. 그러나, gender의 유니크한 값의 개수가 2개(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이 내부적으로 선두 컬럼의 개별 값 범위를 순회하며 뒤쪽 컬럼 조건을 이용해 인덱스를 사용할 수 있게 해주는 기능입니다.

 

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

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

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

 

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

스킵 스캔은 선두 컬럼의 개별 값 범위를 순회합니다. 위의 쿼리의 경우 생략된 gender의 범위인 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';

 

그런데 만약 gender가 남자와 여자가 아니라 LGBTQ를 포함하는 범위로 확장되면 어떨까요?

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';
select gender, birth_date from employees where gender = 'L' and birth_date >= '1965-02-01';
select gender, birth_date from employees where gender = 'G' and birth_date >= '1965-02-01';
select gender, birth_date from employees where gender = 'B' and birth_date >= '1965-02-01';
select gender, birth_date from employees where gender = 'T' and birth_date >= '1965-02-01';
select gender, birth_date from employees where gender = 'Q' and birth_date >= '1965-02-01';

 

이 경우, 생략된 gender의 범위가 넓어지며 마치 7개의 쿼리를 순회하는 방식으로 스킵 스캔을 시도할 수 있습니다. 이렇게 생략된 선행 조건의 범위가 넓어, 순회해야하는 값의 범위가 많아지면 옵티마이저는 인덱스를 활용하기보다 아예 테이블을 풀스캔하거나, 인덱스 테이블을 풀 스캔하는 것이 더 저렴하다고 판단할 수 있습니다. 즉, 생략된 선행컬럼의 유니크 값의 범위가 옵티마이저가 순차탐색하더라도 충분히 저렴하다고 느낄만큼 적어야 합니다.

 

 

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

두번째로 커버링 인덱스 여야 합니다. 쿼리가 인덱스에 존재하는 칼럼만으로 처리가 가능해야합니다.

 

 

주어진 쿼리는 스킵 스캔 조건을 만족할까?

그럼 두가지 조건에 입각해 현재 쿼리의 조건 만족여부를 보겠습니다.

조건	설명
첫 번째 컬럼의 가능한 값이 매우 적고 고정적일 때	gender의 범위가 2개로 매우 적음
쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능해야 함(커버링 인덱스)	gender, birth_date만 조회하므로 커버링 인덱스 조건을 만족함

 

즉 해당 상황에서 옵티마이저는 선행조건의 유니크값이 크지 않으므로 다음 두가지 쿼리를 만들어 실행하고, 인덱스를 스킵하며 읽어 결과를 반환합니다.

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';

 

실습으로 확인하기

인덱스 스킵 스캔은 Extra 컬럼에 Using index for skip scan을 통해 확인할 수 있습니다.

+--+-----------+---------+----------+-----+-------------------+-------------------+-------+----+------+--------+--------------------------------------+
|id|select_type|table    |partitions|type |possible_keys      |key                |key_len|ref |rows  |filtered|Extra                                 |
+--+-----------+---------+----------+-----+-------------------+-------------------+-------+----+------+--------+--------------------------------------+
|1 |SIMPLE     |employees|null      |range|ix_gender_birthdate|ix_gender_birthdate|4      |null|100021|100     |Using where; Using index for skip scan|
+--+-----------+---------+----------+-----+-------------------+-------------------+-------+----+------+--------+--------------------------------------+