멀티 컬럼 인덱스

김태훈·2023년 8월 1일

멀티 컬럼 인덱스란?

인덱스를 생성할 때, 여러 개의 컬럼을 사용해 인덱스를 만드는 기법입니다.

어떤 상황에서 쓸 수 있나요?

단일 인덱스 여러개를 동시에 사용해야 하는 상황에서 고려해볼만 합니다. 예시를 통해 살펴 볼게요.

monthly_salaries라는 테이블이 있습니다.
해당 테이블에서는 직원들이 각 year, month에 얼마만큼의 월급을 받아갔는지를 저장합니다.

위 테이블에서 특정 사원이 해당 부서에서 월급을 얼마나 받아갔는지 조회해보겠습니다.

select * from monthly_salaries where employee_id = 김태훈 and dept_id = 백엔드1팀

해당 쿼리의 결과로 id가 1,2 인 레코드가 반환될 겁니다.
위 쿼리를 예시로 단일 인덱스를 타는 상황과, 멀티 컬럼 인덱스를 타는 상황을 비교해보도록 하겠습니다.

(상황을 하나 더 추가할수도 있음. 하나만 인덱스 걸린 상황)

각각의 컬럼마다 인덱스를 타는 상황

employee_id와 dept_id에 각각 인덱스가 걸려 있다고 가정하겠습니다.
select * from monthly_salaries where employee_id = 김태훈 and dept_id = 백엔드1팀
해당 쿼리를 날리면 어떤 일이 벌어질까요?
DB는 해당 쿼리를 실행하기 가장 효율적인 방법을 찾습니다.
해당 상황에서는 두 컬럼 모두 인덱스가 존재하니 각각 인덱스를 타는 게 효율적일 수 있겠네요.

먼저 dept로 만든 인덱스를 타보겠습니다.

다들 아시겠지만, 포인터에는 자원의 위치를 저장합니다. 포인터를 사용해 자원을 찾아가는 개념이에요.
그리고 인덱스가 두 개 있더라도 하나만 사용하는 게 더 효율적일 수도 있는데, 실제로 mySql이 어떤 기준으로 필터링을 하는지는 아직 잘 모르겠습니다. 학습이 부족한데 아시는 분 있으면 가르쳐주시면 감사하겠습니다.

B+ Tree로 만들어진 인덱스를 타고, dept가 백엔드 1팀인 결과물을 가져왔습니다.

마찬가지로 employee로 만든 인덱스를 타보겠습니다.

앞선 쿼리에서는 and 조건이 걸려있었으니 두 결과물을 Union 연산하면 되겠네요. 포인터를 기준으로 Union 연산 결과 포인터가 1,2를 가리키는 레코드만 남겠네요.

위 연산을 더 빠르게 하고 싶습니다. 어떻게 해야 할까요? 굳이 두 번 인덱스를 타지 않고 한 번에 타는 방법은 없을까요?
멀티 컬럼 인덱스를 사용하면 한 개의 인덱스로 결과를 가져올 수 있는데요. 아래에서 함께 살펴보겠습니다.

멀티 컬럼 인덱스 사용

인덱스를 만들 때 dept를 기준으로 정렬하고, dept가 같다면 employee를 기준으로 정렬하는 방식으로 B+ Tree를 만듭니다.

주의할 점

멀티 컬럼 인덱스는 순서가 중요합니다. 순서에 따라서 B+ Tree의 구조가 전혀 다르게 변경되기 때문이죠. 성능에 직접적인 영향을 끼칩니다.

순서를 어떻게 조합하냐에 따라 성능이 매우 달라질 수 있습니다. 먼저 실행하는 컬럼에서 최대한 컬럼의 개수를 좁히는 게 중요합니다.
아래는 멀티 컬럼 인덱스에서 순서를 정하기 위한 가이드입니다.
멀티 컬럼 인덱스를 만든 이후 꼭 성능 테스트를 해보기를 바랍니다.

동등비교를 하는 컬럼이 있다면 가장 먼저 위치시킨다

정렬에 사용하는 컬럼이 있다면 그 다음에 위치시킨다

범위 검색에 사용하는 컬럼이라면 마지막에 위치시킵니다.

동일한 level이라면 카디널리티가 높은 컬럼을 먼저 위치시킵니다.

그리고 또 하나 중요한 건, 앞에 있는 컬럼일수록 업데이트가 잘 되지 않아야 합니다.