[MySQL] 인덱스 스킵 스캔 (Index Skip Scan)

MySQL 8.0에 도입된 인덱스 스킵 스캔 기능을 간단히 정리하고 실습 코드를 준비해 봤습니다.

 

목차

1. 인덱스 스킵 스캔이란 무엇인가?

2. 인덱스 스킵 스캔 사용을 위한 조건

3. 인덱스 스킵 스캔 실습 해보기

3.1. Skip Scan: Off

3.2. Skip Scan: On

3.3. 인덱스 선두 컬럼의 유니크한 값 개수에 따른 효율 차이 확인

참고 자료

 

---

1. 인덱스 스킵 스캔이란 무엇인가?

• 인덱스 선두 컬럼이 조건절에 없어도 선두 컬럼을 스킵하며 인덱스 레인지 스캔 하는 것
• MySQL 8.0 버전부터 도입

 

예를 들어 아래와 같이 gender, birth_date 순서로 다중 컬럼 인덱스를 생성했다면

조건절에 gender 컬럼 없이는 인덱스를 사용하지 못합니다 (Index Range Scan을 하지 못합니다).

(아래 예제 쿼리들은  Real MySQL 8.0(백은빈, 이성욱, 2021)의 8.3.4.4 절 인덱스 스킵 스캔, 236~239page에서 가져왔음을 밝힙니다)

 

인덱스 구성 예시

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

-- 선두 컬럼: gender

 

인덱스 레인지 스캔할 수 있는 쿼리와 레인지 스캔 할 수 없는 쿼리 예시

-- 인덱스를 사용하지 못하는 쿼리: 인덱스 선두 컬럼인 gender 컬럼이 where절에 없다.
mysql> SELECT * FROM employees WHERE birth_date >= '1965-02-01';

-- 인덱스를 사용할 수 있는 쿼리: 인덱스 선두 컬럼인 gendor 컬럼이 where절에 있다.
mysql> SELECT * FROM employees WHERE gender = 'M' AND birth_date >= '1965-02-01';

 

 

하지만 인덱스 레인지 스캔의 도입으로 gender 컬럼 없이도 인덱스 레인지 스캔이 가능합니다.

- 아래 그림 처럼 성별 Male에서 데이터를 레인지 스캔 한 후 나머지 데이터는 스킵하여 Female의 데이터 시작점을 찾고 레인지 스캔해서 결과를 반환합니다. 

출처: Real MySQL 8.0(백은빈, 이성욱, 2021), 238page

 

옵티마이저 내부적으로는 다음 2개 쿼리를 실행 하는 것처럼 최적화를 합니다.

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

 

 

2.  인덱스 스킵 스캔 사용을 위한 조건

1. Optimizer의 skip_scan 조건 켜기 (On)

• 기본값이 on 이라고 합니다. (https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html)
• select @@optimizer_switch; 명령어로 optimizer_switch 세팅을 확인할 수 있습니다.

/* Skip Sacn: on */
select @@optimizer_switch;
SET optimizer_switch='skip_scan=on';
commit;

2. 스킵 스캔을 사용할 수 있는 조건으로 쿼리 작성

• A. 쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능해야 함 (커버링 인덱스)
• B. Where 조건절에 조건이 없는 인덱스 선두 컬럼의 유니크한 값의 개수가 적어야 함 (스킵 스캔을 사용할 수 있는 조건이라기보다는 효율 측면에서 인덱스 선두컬럼의 유니크한 값의 개수가 적어야 합니다)

 

* Real MySQL 책에서 저자분이 A 조건 같은 경우는 MySQL의 옵티마이저가 개선되면 충분히 해결될 것 같다고 이야기하셨고, 저도 이에 공감했지만 제가 테스트한 MySQL (ver. 8.0.33) 까지는 아직 A 조건을 만족해야 인덱스 스킵 스캔이 동작합니다.

 

 

3. 인덱스 스킵 스캔 실습 해보기

실습용 데이터 준비 (Python, Jupyter Notebook 파일: https://github.com/menthamin/code-snippets/blob/main/database/create_mysql_index_skip_scan_data.ipynb)

데이터 생성 예시

위의 파일을 다운 받아 Local에 실습용 테이블 2개를 생성할 수 있습니다.

 

테이블 1: product_category_100

• -상품 카테고리가 100개 있고 (상품 카테고리, 상품 ID) 순서로 인덱스가 생성된 테이블

테이블 2: product_category_10000

• 상품 카테고리가 10000개 있고 (상품 카테고리, 상품 ID) 순서로 인덱스가 생성된 테이블

두 테이블 모두 Rows는 100만 행으로 동일합니다.

테이블 2번의 경우 선두 컬럼의 유니크한 값의 개수에 따라 인덱스 레인지 스캔의 효율이 달라지는 모습을 확인하고자 생성해봤습니다.

우선 테이블 1번으로 Skip Scan 기능을 확인해 보고 이후 테이블 1, 2번을 비교하며 인덱스 선두 컬럼의 유니크 값 개수에 따른 스킵 스캔 효율을 확인해 보겠습니다.

 

 

 

3.1. Skip Scan: Off

인덱스: (product_category_id, product_id) 순서

 

Skip Scan을 Off 하고 테스트해본 결과입니다.

인덱스 선두 컬럼인 product_category_id를 사용한 조회는 인덱스 Range Scan으로 데이터를 조회했고 약 0.02초 (20ms)가 걸렸습니다(Covering index Lookup Scan: 커버링 인덱스로 인덱스 레인지 스캔).

하지만 인덱스 선두 컬럼 없이 product_id를 이용해 데이터를 조회한 경우 Index Full Scan으로 데이터 조회에 약 0.26초 (265ms)가 걸렸습니다(Covering index Scan: 커버링 인덱스 인덱스 풀 스캔).

/* Skip Sacn: Off */
select @@optimizer_switch;
SET optimizer_switch='skip_scan=off';
commit;

-- 선두 컬럼 사용
explain analyze
select * from real_mysql.product_category_100
where product_category_id = 50;

-- 실행 계획 및 실행 시간: 인덱스 Range 스캔
-- -> Covering index lookup on product_category_100 using idx_product_category_product_id (product_category_id=50)  (cost=1908 rows=18710) (actual time=0.0213..10.8 rows=10090 loops=1)
-- [2023-06-04 22:43:12] 1 row retrieved starting from 1 in 57 ms (execution: 20 ms, fetching: 7 ms)

-- 선두 컬럼 미사용
explain analyze
select * from real_mysql.product_category_100
where product_id = 50000;


-- 실행 계획 및 실행 시간: Table Full Scan
-- -> Filter: (product_category_100.product_id = 50000)  (cost=100610 rows=99798) (actual time=16.8..244 rows=10 loops=1)
--     -> Covering index scan on product_category_100 using idx_product_category_product_id  (cost=100610 rows=997984) (actual time=0.031..219 rows=1e+6 loops=1)
-- [2023-06-04 22:43:22] 1 row retrieved starting from 1 in 282 ms (execution: 265 ms, fetching: 17 ms)

* explain analyze: 통계 정보 기반의 실행 계획(explain)이 아니라 실제 실행 결과를 반환, MySQL 8.0.18 부터 도입 

 

 

3.2. Skip Scan: On

Skip Scan을 활성화하고 테스트해본 결과입니다.

선두 컬럼인 product_category_id를 생략한 쿼리의 실행계획에서 Covering index skip scan을 확인할 수 있습니다.

기존 0.26초 (265ms)에서 0.007초 (7ms)로 40배 이상 쿼리가 빨라진 걸 확인할 수 있습니다.

/* Skip Scan: On */
select @@optimizer_switch;
SET optimizer_switch='skip_scan=on';
commit;

explain analyze
select product_category_id, product_id from real_mysql.product_category_100
where product_category_id = 50;

-- 실행 계획 및 실행 시간: 인덱스 Range 스캔
-- -> Covering index lookup on product_category_100 using idx_product_category_product_id (product_category_id=50)  (cost=1908 rows=18710) (actual time=0.0324..9.6 rows=10090 loops=1)
-- [2023-06-04 22:55:12] 1 row retrieved starting from 1 in 40 ms (execution: 15 ms, fetching: 25 ms)


explain analyze
select product_category_id, product_id from real_mysql.product_category_100
where product_id = 50000;

-- 실행 계획 및 실행 시간: 인덱스 Skip Scan
-- -> Filter: (product_category_100.product_id = 50000)  (cost=25351 rows=99798) (actual time=0.0853..1.33 rows=10 loops=1)
--    -> Covering index skip scan on product_category_100 using idx_product_category_product_id over product_id = 50000  (cost=25351 rows=99798) (actual time=0.0843..1.33 rows=10 loops=1)
-- [2023-06-04 22:56:10] 1 row retrieved starting from 1 in 53 ms (execution: 7 ms, fetching: 46 ms)

 

3.3. 인덱스 선두 컬럼의 유니크한 값 개수에 따른 효율 차이 확인

인덱스 선두 컬럼의 유니크한 값의 개수가 각각 100개, 10000개인 2개 테이블에서 인덱스 스킵 스캔의 효율을 확인해 봤습니다.

- 테이블: product_category_100: product_category_id 100개

- 테이블: product_category_1000: product_category_id 1000개

 

2개 테이블의 실행 계획 모두 Covering index skip scan으로 결과를 가져옵니다.

하지만 product_category_id의 유니크한 값의 개수가 100개인 product_category_100의 경우 9 ms , 10000개인 product_category_10000의 경우 45 ms가 (5배 차이) 걸렸습니다.

-- 1. 선두 컬럼인 product_category_id가 100개인 경우
explain analyze
select product_category_id, product_id from real_mysql.product_category_100
where product_id = 50000;

-- -> Filter: (product_category_100.product_id = 50000)  (cost=25351 rows=99798) (actual time=0.073..2.47 rows=10 loops=1)
--    -> Covering index skip scan on product_category_100 using idx_product_category_product_id over product_id = 50000  (cost=25351 rows=99798) (actual time=0.0719..2.47 rows=10 loops=1)
-- [2023-06-04 23:02:34] 1 row retrieved starting from 1 in 43 ms (execution: 9 ms, fetching: 34 ms)

-- 2. 선두 컬럼인 product_category_id가 10000개인 경우
explain analyze
select product_category_id, product_id from real_mysql.product_category_10000
where product_id = 50000;

-- -> Filter: (product_category_10000.product_id = 50000)  (cost=45101 rows=99798) (actual time=21.9..44.2 rows=4 loops=1)
--     -> Covering index skip scan on product_category_10000 using idx_product_category_product_id over product_id = 50000  (cost=45101 rows=99798) (actual time=21.9..44.1 rows=4 loops=1)
-- [2023-06-04 23:02:52] 1 row retrieved starting from 1 in 61 ms (execution: 45 ms, fetching: 16 ms)

 

인덱스 선두 컬럼의 유니크한 각 값들 마다 인덱스의 시작점을 찾고 Range Scan 후 그 결과 값들을 합쳐야 하니, 인덱스 선두 컬럼의 유니크한 개수가 많아질 때 속도가 느려지는 것은 어찌 보면 당연한 결과입니다. (쉽게 이해하자면 아래처럼 100번을 실행하는가 10000번을 실행하는가 차이)

 

-- product_category_id가 100개인 경우
select product_category_id, product_id from real_mysql.product_category_100
where procut_category_id = 1 and product_id = 50000;
...
select product_category_id, product_id from real_mysql.product_category_100
where procut_category_id = 100 and product_id = 50000;

-- product_category_id가 10000개인 경우
select product_category_id, product_id from real_mysql.product_category_10000
where procut_category_id = 1 and product_id = 50000;
...
...
...
select product_category_id, product_id from real_mysql.product_category_10000
where procut_category_id = 10000 and product_id = 50000;

 

실행 계획에서 Covering index skip scan을 만나도 인덱스 선두 컬럼의 유니크한 값의 개수가 많다면

비효율이 발생할 수 있기에 경우에 따라 조건절에서 사용한 컬럼을 선두로 한 새로운 Index를 새로 구성하는 게 좋을 수 있을 것 같습니다.

 

기타 궁금하신 사항은 편하게 남겨주세요.

 

감사합니다.

 

 

참고 자료

1. Real MySQL 8.0(백은빈, 이성욱), 위키북스(2021), 8.3.4.4 절: 인덱스 스킵 스캔 (236 ~ 240page)

2. MySQL 8.0 Reference Manual (Switchable Optimizations): https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html