일반적으로 다중 컬럼 인덱스를 생성하면 해당 인덱스를 사용하기 위해 쿼리의 WHERE 절에는 선행 컬럼 조건이 반드시 포함되어야 한다.
예를 들어 (gender, hire_date) 순서로 복합 인덱스를 생성했다면 gender 조건 없이 hire_date만으로는 인덱스를 사용할 수 없는 것이 기존 MySQL의 동작 방식이었다.
하지만 MySQL 8.0부터는 옵티마이저가 선행 컬럼 조건이 없더라도 인덱스를 활용할 수 있도록 인덱스 스킵 스캔이라는 최적화 기능이 도입되었다.
이 기능은 특히 선행 컬럼의 기수성이 낮을 때 효과적으로 동작한다.
인덱스 스킵 스캔과 선행 컬럼
employees 테이블에 (gender, hire_date) 인덱스를 생성했다고 가정해보자.
기존 방식이라면 아래와 같은 쿼리만 인덱스를 사용할 수 있었다.
SELECT * FROM employees WHERE gender = 'M';
SELECT * FROM employees WHERE gender = 'M' AND hire_date >= '1985-01-01';
반면 선행 컬럼 조건이 빠진 다음 쿼리는 인덱스를 사용할 수 없어 테이블 풀 스캔이 수행되었다.
SELECT * FROM employees WHERE hire_date >= '1985-01-01';
하지만 MySQL 8.0에서는 옵티마이저가 선행 컬럼을 건너뛰고 후행 컬럼만으로도 인덱스를 활용할 수 있다.
이를 가능하게 하는 핵심 조건은 선행 컬럼의 기수성이다. gender 컬럼은 'M', 'F' 두 값만 존재하므로 기수성이 매우 낮다.
옵티마이저는 gender 값을 기준으로 여러 개의 인덱스 레인지 스캔을 수행하는 방식으로 쿼리를 분해한다.
인덱스 스킵 스캔 실행계획 변화
인덱스 스킵 스캔을 비활성화하면 같은 쿼리라도 실행계획은 인덱스 풀 스캔으로 나온다.
이 경우 type=index로 표시되며 인덱스 전체를 순차적으로 읽는다. 커버링 인덱스가 가능하므로 테이블 접근은 발생하지 않지만 효율적인 스캔이라고 보긴 어렵다.
반면 skip_scan 옵션을 활성화하면 실행계획은 눈에 띄게 달라진다.
type은 range로 변경되고 Extra에는 Using index for skip scan이 표시된다.
이는 옵티마이저가 선행 컬럼의 서로 다른 값들을 기준으로 여러 개의 인덱스 레인지 스캔을 나눠 수행했음을 의미한다.
읽어야 할 rows 수도 대폭 줄어든다. 인덱스 풀 스캔이 약 30만 건을 읽던 반면 인덱스 스킵 스캔은 약 10만 건 수준으로 감소한다.
이 차이는 데이터가 많아질수록 더 크게 체감된다.
기수성과 스킵 스캔 동작 원리
인덱스는 항상 선행 컬럼을 기준으로 정렬된다. (gender, hire_date) 인덱스라면 gender 값이 먼저 정렬되고 그 안에서 hire_date가 정렬된다.
gender 컬럼의 값이 'M', 'F' 두 개뿐이라면 옵티마이저는 아래와 같은 쿼리를 내부적으로 분해해 실행할 수 있다.
WHERE gender = 'M' AND hire_date >= '1985-01-01';
WHERE gender = 'F' AND hire_date >= '1985-01-01';
각각은 일반적인 인덱스 레인지 스캔으로 효율적으로 처리할 수 있다.
이 과정을 자동으로 수행하는 것이 바로 인덱스 스킵 스캔이다.
하지만 모든 경우에 항상 유리한 것은 아니다.
선행 컬럼의 기수성이 높거나 커버링 인덱스가 불가능한 경우에는 오히려 인덱스 풀 스캔이나 테이블 풀 스캔이 선택될 수 있다.
결론적으로 인덱스 스킵 스캔은 기수성이 낮은 선행 컬럼과 정렬된 인덱스 구조를 적절히 활용할 수 있을 때 가장 큰 효과를 발휘한다.