[MySQL] 다중 컬럼 Index의 동작 방식

☕ 개요

---

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

다중 컬럼 Index를 사용하는 이유

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

굳이! 다중 컬럼 Index를 사용하는 이유

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

각각의 컬럼에 따로 인덱스가 걸려있다면, 어떤 인덱스를 먼저 사용하는 것이 더 빠른 것인지 DB에서 판단 후, 순서대로 인덱스를 적용하게 된다.

반면에 다중 컬럼 인덱스는 상위 인덱스 값에 대한 하위 인덱스 값을 같이 저장하고 있기 때문에 어떤 인덱스를 먼저 사용할지를 판단하는 과정이 없고, 상위 인덱스로 한번 필터링된 데이터에서 하위 인덱스의 조건에 맞는 데이터를 찾아낸다.

즉, 인덱스 성능을 비교하는 과정이 생략되고, 스캔해야하는 데이터의 개수가 줄어든다. 이러한 이유 때문에 개별 인덱스보다 좀 더 나은 조회 성능을 제공할 수 있게 된다. (단, 올바른 순서대로 다중 컬럼 인덱스를 생성했을 때)

예시 테이블 준비

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 오름차순으로 또 다시 정렬되는 것이다.

다중 컬럼 인덱스가 사용되는 경우

다중 컬럼 인덱스는 아래의 경우에 사용된다.

1. 선행 컬럼(last_name)만 where 절에 사용된 경우

select * from user_name where last_name = 'hello';

2. 선행 컬럼(last_name)과 N번째에 명시된 컬럼이 where 절에 사용된 경우

select * from user_name where last_name = 'hello' and first_name = 'world';

그리고, where 절에 last_name이 사용되지 않으면, 다중 컬럼 인덱스는 사용되지 못하고 last_name과 first_name이 같이 사용되었어도 or 문으로 묶여있을 때도 복합 인덱스는 사용될 수 없다.

🧐 다중 컬럼 Index 순서의 중요성

---

위에서도 간단히 언급했듯이, 다중 컬럼 인덱스를 사용할 때는 어떤 순서대로 컬럼을 명시하는 지가 매우 중요하다.
물론 상황에 맞게 순서를 설정하면 되지만, 일반적으로 어떤 순서로 걸면 좋을까?

순서를 정하는 방법

우리가 도서관에서 책을 찾는 과정에 비유를 하면 정답을 쉽게 유추 해볼 수 있다.
도서관에서 어떤 책을 찾을 때, 모든 섹션을 하나 하나를 뒤지면서 내가 찾고자 하는 책을 제목을 찾진 않는다.
보통은 책이 속한 카테고리가 어떤 섹션에 있는지 파악하고, 그 섹션의 몇 번 트레이에 내 책이 있을지 파악한다. 그러고나서 그 트레이를 쫙 훑어보면서 책을 찾곤 한다.

즉, 다중 컬럼 인덱스를 선언할 때는 상대적으로 대분류에 해당하는 컬럼이 앞쪽으로 오는 것이 바람직하다.

☕ 정리

---

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

1. 선행 컬럼은 일반 인덱스처럼 활용할 수 있다.
2. 인덱스 컬럼의 순서는 상대적으로 대분류에 해당하는 컬럼일수록 앞쪽에 오는 것이 좋다.
3. 인덱스 컬럼의 순서는 매우 중요하다.

🙏 참고

---

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