[MySQL] 복합 인덱스 동작 방식

☕ 개요

---

이번 글에서는 2개 이상의 컬럼을 묶어서 사용하는 복합 Index의 동작 방식에 대해서 알아보도록 하겠다.

복합 Index를 사용하는 이유

Index를 사용하는 이유는 당연하게도 데이터 조회의 성능 향상을 위함이다. 복합 Index를 사용하는 이유 또한 데이터를 조회할 때, 빈번하게 같이 조회되는 컬럼들에 대해서 조회 성능을 향상시키기 위해서이다.

굳이! 복합 Index를 사용하는 이유

성능 향상을 가져오는 것은 알겠지만, 각 컬럼에 Index를 거는 것과는 어떠한 차이가 있을까?

각각의 컬럼에 따로 인덱스가 걸려있다면, MySQL 옵티마이저는 두 가지 전략 중 하나를 선택한다.

• Index Merge Optimization: 여러 인덱스를 동시에 사용하고 결과를 병합한다.
• 가장 제한적인 인덱스 선택: 가장 많은 행을 제외할 수 있는 인덱스 하나만 사용해 행을 조회한다.

반면 복합 인덱스는 선행 컬럼 기준으로 정렬된 상태에서 후행 컬럼까지 함께 저장하기 때문에, 두 조건을 하나의 인덱스 스캔으로 처리할 수 있다. 이로 인해 스캔 범위가 줄어들고 더 나은 조회 성능을 제공할 수 있다.

예시 테이블 준비

CREATE TABLE user_name (
	id         BIGINT NOT NULL,
	last_name  VARCHAR(20) NOT NULL,
	first_name VARCHAR(20) NOT NULL,
	PRIMARY KEY (id),
	INDEX full_name (last_name,first_name)
);

먼저 이렇게 '이름'을 저장하는 테이블이 있다고 가정해보자. last_name과 first_name을 기록하는 컬럼이 있고, last_name, first_name 순서대로 복합 인덱스가 걸려있는 상태이다.

아래부터는 위 테이블을 예를 들어서 이야기하도록 하겠다.

🖥️ 복합 인덱스의 동작

---

복합 인덱스가 저장되는 법

복합 인덱스는 명시된 컬럼들의 순서대로 데이터를 정렬시켜서 저장한다.
즉, user_name테이블의 데이터는 last_name 오름차순으로 정렬이 되고, 같은 last_name을 갖고 있는 데이터들은 first_name 오름차순으로 또 다시 정렬되는 것이다.

복합 인덱스가 사용되는 경우 (Leftmost Prefix 규칙)

MySQL은 인덱스의 가장 왼쪽 컬럼부터 순서대로 포함하는 경우에만 인덱스를 사용할 수 있다. 이를 leftmost prefix 규칙이라고 한다.

쿼리	인덱스 사용 여부
WHERE last_name = 'Kim'	✅ 사용 가능
WHERE last_name = 'Kim' AND first_name = 'Juhyeon'	✅ 사용 가능
WHERE last_name = 'Kim' AND first_name >= 'J' AND first_name < 'K'	✅ 사용 가능
WHERE first_name = 'Juhyeon'	❌ 사용 불가
WHERE last_name = 'Kim' OR first_name = 'Juhyeon'	❌ 사용 불가

🧐 복합 인덱스 컬럼 순서의 중요성

---

복합 인덱스를 생성할 때 컬럼 순서는 매우 중요하다. 어떤 기준으로 순서를 정하면 좋을까?

순서를 정하는 방법

기준 1: 쿼리 패턴 (가장 중요)
가장 우선시해야 할 기준은 실제 쿼리 패턴이다. 단독으로 자주 조회되는 컬럼을 앞에 두어야 한다.

예를 들어 last_name만으로 조회하는 쿼리가 자주 있다면, (last_name, first_name) 순서가 적합하다. 반대로 (first_name, last_name) 순서라면 first_name 단독 조회는 인덱스를 활용할 수 있지만, last_name 단독 조회는 인덱스를 사용하지 못한다.

대표적인 예시로 '대분류', '소분류'와 같은 컬럼이 있는 경우 '대분류' 선행 컬럼으로 복합 인덱스를 생성해야한다.

기준 2: 카디널리티 (선택적 고려)
카디널리티(Cardinality)란 해당 컬럼에 존재하는 고유한 값의 수를 의미한다. 카디널리티가 높을수록 중복이 적고, 인덱스가 데이터를 더 세밀하게 구분할 수 있다.

쿼리 패턴의 관점과 카디널리티의 관점은 충돌할 수 있는데, 두 기준이 충돌할 때는 쿼리 패턴을 우선으로 하는 것이 좋다. 카디널리티가 높아도 단독 조회가 필요 없는 컬럼이라면 굳이 앞에 둘 이유가 없기 때문이다. 즉, 실제 서비스 쿼리를 분석한 후 인덱스 순서를 결정하는 것이 가장 바람직하다.

☕ 정리

---

복합 인덱스에 대해서 최종적으로 정리해보자면 이렇게 정리할 수 있을 것 같다.

1. 선행 컬럼(leftmost prefix)은 단독 인덱스처럼 활용할 수 있다.
2. 선행 컬럼이 WHERE 절에 없으면 복합 인덱스는 사용되지 않는다.
3. 컬럼 순서는 쿼리 패턴을 최우선으로, 카디널리티를 보조 기준으로 결정한다.
4. 인덱스 컬럼의 순서는 매우 중요하므로 실제 쿼리를 분석한 후 설계해야 한다.

🙏 참고

---

• MySQL - Multiple-Column Indexes
• 결합인덱스 및 컬럼 순서 결정 방법
• 다중 컬럼 인덱스 사용 시 주의할 점
• 복합 인덱스 생성시 컬럼 나열 순서
• B-Tree인덱스와 다중 컬럼 인덱스