MySQL 8.0에 추가된 최적화 기능, 스킵 스캔에 대해 살펴보자

주제

MySQL 8.0부터 추가된 최적화 기능인 스킵 스캔에 대해 알아봅니다.

선정 이유

사용 가능한 상황이 그리 범용적으로 보이진 않습니다. 하지만 내용이 재밌어서 한번 정리해봤습니다.

해당 텍스트

인덱스 스킵 스캔

MySQL 8.0 버전부터는 다중 칼럼 인덱스에서 옵티마이저가 특정 칼럼 인덱스를 건너 뛰어서 검색할 수 있도록하는 인덱스 스킵 스캔(index skip scan) 최적화 기능이 도입되었습니다. 이를 통해 인덱스를 통한 검색의 용도가 더 넓어졌습니다.

예시를 통해 알아보겠습니다.

예시 테이블은 다음과 같습니다.

mysql> CREATE TABLE `employees` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `last_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `gender` enum('M','F') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci STATS_PERSISTENT=0 |

다중 칼럼 인덱스를 사용할 경우, where 조건에서 첫 번째 칼럼을 사용하지 않으면 인덱스를 활용하지 못합니다.

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

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

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

각 쿼리의 실행계획은 다음과 같습니다. 우선 인덱스 스킵 스캔 기능을 비활성화한 후 실행 계획을 보겠습니다.

mysql> SET optimizer_switch='skip_scan=off';
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT gender, birth_date FROM employees WHERE birth_date>='1965-02-01';
+----+-------------+-----------+------------+-------+---------------+---------------------+---------+------+--------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key                 | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+---------------------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | employees | NULL       | index | NULL          | ix_gender_birthdate | 4       | NULL | 300719 |    33.33 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+---------------------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT gender, birth_date FROM employees WHERE gender='M' AND birth_date>='1965-02-01';
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+------+----------+--------------------------+
| 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 |   27 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

여기서 type이 index라는 것은 인덱스를 처음부터 끝까지 다 읽었다는 것입니다. 즉 풀 인덱스 스캔이 일어난 것으로, 인덱스를 효율적으로 활용하지 못했음을 나타냅니다.

반면 아래 쿼리의 type은 range 입니다. 타입이 range라는 것은 인덱스에서 꼭 필요한 부분만 읽었다는 것입니다. 인덱스를 잘 활용했다고 평가할 수 있습니다.

이번에는 인덱스 스킵 스캔 기능을 활성화한 후 첫 번째 쿼리를 다시 실행해보겠습니다.

mysql> SET optimizer_switch='skip_scan=on';
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT gender, birth_date FROM employees WHERE birth_date>='1965-02-01';
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+--------+----------+----------------------------------------+
| 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 | 100229 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+--------+----------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

분명 같은 쿼리인데 이번에는 필요한 데이터만 읽은 것을 확인할 수 있습니다.

MySQL 옵티마이저는 우선 gender 칼럼에서 유니크한 값을 모두 조회한 후 주어진 쿼리에 gender 칼럼의 조건을 추가해 쿼리를 다시 실행하는 형태로 처리합니다.

그러나 인덱스 스킵 스캔은 아래의 조건을 만족해야 사용 가능하다는 단점이 있습니다.

WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야함
쿼리가 인덱스에 존재하는 칼럼만으로 처리가 가능해야함.

첫 번째 제약 조건을 봅시다. gender의 경우 유니크한 값의 개수가 두 개입니다. 만약 유니크한 값의 개수가 많으면 MySQL 옵티마이저는 인덱스에서 스캔해야할 시작 지점을 검색하는 작업이 많이 필요해지기 때문에 쿼리의 성능이 떨어지게 됩니다.

유니크한 값이 어느 정도면 적은거고 어느 정도면 많은 건지는 상황에 따라 다르므로 실행 계획을 통해 확인할 수밖에 없을 것 같습니다.

명시적인 수치가 있나 싶어서 공식문서를 찾아봤는데, 이에 대한 언급은 없는 것으로 보입니다.

https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#range-access-skip-scan

두 번째 제약 조건은 쿼리를 다음과 같이 바꿔봄으로써 확인할 수 있습니다.

mysql> EXPLAIN SELECT * FROM employees WHERE birth_date>='1965-02-01';
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 300719 |    33.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

type이 all이라는 것은 테이블 풀 스캔으로 실행 계획을 수립했다는 것입니다. gender와 birth_date만 조회하는 경우 인덱스에 존재하는 칼럼만 조회하면 되기 때문에 테이블을 스캔할 필요가 없습니다.

하지만 모든 컬럼을 조회하려면 테이블을 스캔해야 하기 때문에 테이블 풀 스캔으로 실행 계획이 수립되게 됩니다.

사용 가능한 상황의 특수성이 있긴 하지만 재미있는 내용이라 한 번 정리해봤습니다.

woowacourse-study / 2022-Real-MySQL