개요
다중 컬럼 인덱스를 인덱스 설계 원칙, Index Range Scan과 함께 이해하고, 실무에서 무엇을 기준으로 인덱스를 설계해야하는지 판단할 수 있는 눈을 길러보자.
다중 컬럼 인덱스(Multi-column Index)란?
다중 컬럼 인덱스란 2개 이상의 컬럼으로 구성된 인덱스를 말한다. 인덱스를 사용하면 내부적으로 B+Tree가 생성됨과 동시에 인덱스 컬럼 기준으로 정렬된다는 사실을 알것이다. 다중 컬럼 인덱스도 마찬가지로 정렬된다. 단, 각 컬럼은 앞 컬럼에 의존되어 정렬된다. 첫번째 컬럼을 기준으로 1차 정렬되고, 인덱스의 두번째 컬럼은 첫번째 컬럼을, 세번째 컬럼은 두번째 컬럼을 의존해 정렬되는 것이다. 예시를 통해 이해해보자.
CREATE TABLE TBL_TEST (
id integer NOT NULL AUTO_INCREMENT,
dept_no varchar(100) NOT NULL,
emp_no varchar(100) NOT NULL,
PRIMARY KEY ('id')
);
위 테이블에 대해 (dept_no, emp_no) 를 다중 컬럼 인덱스로 설정하면 첫번째 컬럼인 dept_no으로 1차 정렬되고, 두번째 컬럼인 emp_no은 첫번째 컬럼인 dept_no에 의존되어 정렬된다. order by dept_no, emp_no 쿼리를 날린것처럼 말이다.
인덱스 설계 원칙
인덱스 설계 원칙 몇가지가 있는데, 그 중 두 가지 설계 원칙이 오늘 공부하는 다중 컬럼 인덱스와 관련이 있어 가져왔다. 이왕이면 설계 원칙과 함께 이해하는것이 좋지않겠는가?!
1. 선택도가 높은 컬럼을 선두 컬럼에 배치해라.
2. 비교 컬럼은 선두 컬럼에, 범위 컬럼은 뒤쪽 컬럼에 배치해라.
선택도 (Selectivity)
특정 컬럼의 값이 얼마나 다양하게 분포되어 있는지를 나타내는 지표. 선택도가 높다는 건 카디널리디가 많다는 뜻.
비교 컬럼은 선두 컬럼에, 범위 컬럼은 뒤쪽 컬럼에 배치해라.
1번 설계원칙을 잘 이해하기 위해서는 2번 설계 원칙부터 이해하는 것이 중요하다 생각되어 먼저 설명하도록 하겠다. 이 설계 원칙은 다중 컬럼 인덱스와 직접적으로 연관된 설계 원칙이다. '비교 연산자 컬럼 > 범위 연산자 컬럼' 순서로 인덱스를 설계해야한다는 것인데, 아래 예시를 봐보자.
gender = 비교 컬럼
created_at = 범위 컬럼
gender는 성별을 나타낸다. M과 F 값으로만 구성된다. (카디널리티는 2개, 선택도는 낮다). 이 컬럼을 조회 쿼리에 사용할때 일반적으로 비교(=)조건을 사용하므로 비교 컬럼으로 구분된다.
SELECT * FROM TBL_USER WHERE gender = 'M' -- 비교 조건 컬럼
SELECT * FROM TBL_USER WHERE gender IN ('M') -- 비교 조건 컬럼
created_at는 생성 일시를 나타낸다. 매우 다양한 값으로 구성된다. (카디널리티 다양, 선택도도 높음). 이 컬럼을 조회 쿼리에 사용할땐 일반적으로 범위(>=) 건을 사용하므로 범위 컬럼으로 구분된다.
SELECT * FROM TBL_USE WHERE created_at >= '2025-01-01' -- 범위 조건 컬럼
이때 두 컬럼을 인덱스로 구성해야한다면, 비교 컬럼인 gender를 앞쪽, 범위 컬럼인 created_at를 뒤쪽으로 하여 (gender, created_at) 형태로 인덱스를 설계해야 한다는 것이다. 정말 그럴까?
(gender, created_at) 인덱스 설정
CREATE INDEX idx_example ON TBL_USER (gender, created_at);
인덱스를 설정하면 아래와 같이 gender로 선 정렬된 후, created_at가 gender에 의존되어 정렬된다.
(F, 2025-01-01)
(F, 2025-01-01)
(F, 2025-01-02)
(F, 2025-01-02)
(F, 2025-01-03)
(M, 2025-01-01)
(M, 2025-01-01)
(M, 2025-01-02)
(M, 2025-01-02)
(M, 2025-01-03)
이 상태에서 조회쿼리를 날려보자.
SELECT * FROM TBL_USER WHERE gender = 'M' AND created_at >= '2025-01-02'
그럼 gender = 'M'인 레코드를 찾기 위해 트리 탐색을 진행할것이다. 정렬되어 있으니 M 으로 시작하는 레코드는 금방 찾을 수 있을 것이다. 그렇게 탐색한 데이터는 아래와 같은 범위에 해당할것이다.
(M, 2025-01-01)
(M, 2025-01-01)
(M, 2025-01-02)
(M, 2025-01-02)
(M, 2025-01-03)
이후 created_at >= '2025-01-02' 인 레코드를 찾는다. 그런데 created_at 데이터를 보면 정렬이 이미 되어있다. 정렬되어있으니 내부적으로 트리탐색이 쓰일것이라 생각할 수도 있지만, 그렇지않다. 순차적으로 스캔한다. 다중 컬럼 인덱스에서는 첫 인덱스에 대해서는 트리탐색을, 그 이후부터는 순차 탐색을 하는데, 이러한 방식을 Index Range Scan이라 한다.
위 예시에서 첫번째 인덱스인 gender에 대해서는 트리탐색을, created_at에 대해서는 순차 탐색이 진행된다.
Index Range Scan
트리 탐색을 통해 인덱스의 일부 구간을 탐색하고, 탐색된 범위에 해당하는 리프 노드들만을 순차적으로 스캔하는 방식이다. 다중 컬럼 인덱스에서 사용된다.
(created_at, gender) 인덱스 구조 이해하기
CREATE INDEX idx_example ON TBL_USER (created_at, gender);
다음은 인덱스 설계 원칙과 반대로 (created_at, gender) 순으로 인덱스를 설정했다. 아래와 같이 created_at로 선 정렬된 후, gender가 created_at에 의존되어 정렬된다.
(2025-01-01, F)
(2025-01-01, F)
(2025-01-01, M)
(2025-01-01, M)
(2025-01-02, F)
(2025-01-02, F)
(2025-01-02, M)
(2025-01-02, M)
(2025-01-03, F)
(2025-01-03, M)
이 상태에서 아래 쿼리를 실행한다. (인덱스를 사용을 위해 WHERE 절의 조건 컬럼 순서를 변경했다.)
SELECT * FROM TBL_USER WHERE created_at >= '2025-01-02' AND gender = 'M'
최초로 created_at >= '2025-01-02' 인 레코드를 찾기 위해 트리 탐색을 진행할것이다. 정렬되어 있으니 '2025-01-02' 으로 시작하는 레코드들은 금방 찾을 수 있을 것이다.
(2025-01-02, F)
(2025-01-02, F)
(2025-01-02, M)
(2025-01-02, M)
(2025-01-03, F)
(2025-01-03, M)
이후 gender = 'M' 인 레코드를 찾는다. 마찬가지로 순차 탐색이 진행될것이다.
데이터를 늘려보자!
데이터가 많아지면 어떨까? gender는 카디널리티가 2개인데 반해, created_at는 수천, 수만개이다. 설계 원칙에 따라 (gender, created_at)순으로 설계하면, 첫번째 트리 탐색을 통해 탐색된 레코드가 굉장히 많아진다. 레코드가 10만개일 때, 성별 분포도가 반반이라면 첫번째 트리 탐색을 통해 탐색된 레코드가 그 중 절반인 5만개 일것이다. 그럼 5만개에 대해서는 순차 탐색이 진행된다. 5만번의 스캔이 발생하는것이다.
반대로 (created_at, gender) 순으로 설계하면 어떨까? created_at를 통해 첫번째 트리 탐색을 통한 레코드 개수를 확 줄일 수 있다. 순차 탐색 횟수를 줄이게되는 것이다. 결국 비교 컬럼을 선두 컬럼에 두는 것보다, 범위 컬럼을 선두 컬럼에 두는 것이 더 좋은 케이스인것이다.
그럼 잘못된 원칙이야?
그렇지 않다. 일반적으로는 비교 컬럼을 선두 컬럼에 두는 것이 좋다. 단, 조건이 있는데 선두 컬럼이 너무 낮은 카디널리티를 갖지 않아야 한다는것이다. gender 의 경우 카디널리티가 2로 매우 낮기때문에 선두컬럼으로 부적합하다. 다른 예로 email을 생각해보자. email은 비교 컬럼이다. 카디널리티도 매우 많다. 이런 경우에는 (email, created_at)가 당연히 효율적이다. 1차 트리탐색을 통해 필터링된 레코드의 양이 적기 때문이다.
결국 중요한건 선택도
email이 인덱스의 선두 컬럼에 위치하는게 효율적인 이유는 단순 비교 컬럼이기 때문이 아니다. 카디널리티가 많은, 즉, 선택도가 높은 컬럼이기 때문이고, 선택도가 높기 때문에 순차 스캔하는 부담이 줄어들게 된다. 즉, 비교 컬럼이냐 범위 컬럼이냐보다 선택도가 높은것인가가 더 중요하다. 이 설계 원칙이 첫번째 설계원칙 "선택도가 높은 컬럼을 선두컬럼에 배치해라" 이다.
회고
인덱스를 공부하면 함께 딸려오는 말이 바로 카디널리티이다. 이번 포스팅을 통해 카디널리티와 선택도, 다중 컬럼 인덱스, Index Range Scan 을 연계해서 이해할 수 있었고, 인덱스에서 카디널리티가 얼마나 중요한지를 확실히 알게된 경험이었다.
'DB > MySQL' 카테고리의 다른 글
[MySQL] 인덱스 키 추가 / 삭제 / 검색 / feat. LIKE, 부등호 검색 시 인덱스가 사용되는 이유 쉽게 이해해기 (4) | 2025.07.03 |
---|---|
[MySQL] MySQL과 B-Tree 함께 이해하기 / B-Tree / B+Tree / 클러스터링 인덱스 / 논 클러스터링 인덱스 (3) | 2025.06.11 |
[MySQL] InnoDB 스토리지 엔진 잠금 / 레코드, 갭, 넥스트 키 락 / 팬텀리드를 방지할 수 있는 이유 (0) | 2025.05.27 |
[MySQL] MySQL 엔진 잠금 / 글로벌 락 / 테이블 락/ 네임드 락/ 메타데이터 락 (1) | 2025.05.26 |
[MySQL] Slow Query Logging 설정하기 / 슬로우 쿼리 로깅 설정 (0) | 2025.05.26 |