멀티컬럼 인덱스(Multi-Column Index)는 여러 열을 결합하여 하나의 인덱스로 만드는 것입이며, 데이터 검색 성능을 향상시키기 위해 사용됩니다. 또한, 멀티컬럼 인덱스는 다중 조건 검색에서 효율적이다. (2개 이상의 컬럼을 묶어서 설정하는 인덱스)

아래와 같은 user 테이블이 있다고 가정하고,

멀티 컬럼 인덱스는 다음과 같은 쿼리로 생성가능하다.

CREATE INDEX idx_department_name ON user (부서, 이름);

즉, 예제의 멀티 컬럼인덱스 생성후에는 아래와 같이 정렬된 표가 생성된다.

부서를 기준으로 먼저 오름차순으로 정렬한 뒤, 같은 부서의 값을 가진 데이터들 사이에서 이름을 기준으로 오름차순 정렬을 한다.

위와 같이 DB 내부적으로 정렬한다고  파악하면 되며, 이는 유저가 확인하는 표가 아닌 단순히 DB 내부적으로 정렬하는 기준이다.

참고로, 멀티 컬럼 인덱스 생성시에는 괄호안에 있는 컬럼 순서가 매우 중요하다. (괄호안의 숫자에 따라, DB 내부적으로 정렬하는 기준이 달라지기 때문이고, 또 첫번째 순서의 컬럼 인덱스의 경우에는 일반 인덱스처럼 단독으로 where 문에 걸어서 조회해도, 옵티마이저가 알아서 일반 인덱스처럼 최적화 해준다.)

즉, 첫번째 컬럼의 경우에는 아래와 같이 where 조건으로, 제약을 걸어도 옵티마이저가 인덱스를 찾아서 최적화 해준다. 

그렇기 때문에, 굳이 별도의 단독 인덱스를 걸어둘 필요는 없다.

SELECT * FROM users WHERE 부서 = '운영';

하지만, 아래의 쿼리문에서 두번째 컬럼의 멀티 인덱스의 경우에는 첫번째 컬럼의 부서 처럼, 인덱스로 활용할수 없다.

직관적으로 작성된 표를 보았듯이, 두번째 '이름'만 놓고본 경우에는 순차적으로 정렬이 되어 있지 않다.

SELECT * FROM users WHERE 이름 = '이재현';

따라서 옵티마이저는 위의 쿼리문을 최적화 하지 못한다.

멀티 컬럼 인덱스에서 일반 인덱스처럼 활용할 수 있는 건 처음에 배치된 컬럼들뿐이다.

멀티 컬럼인덱스 생성시에는 대분류,중분류, 소분류로 나누어 구성하도록 한다.

멀티 컬럼인덱스에는 넓은 범위의 데이터를 기준으로 대분류, 중분류, 소분류로 나누어 구성하는 것이 효과적이다.

멀티 컬럼 인덱스에서도 배치한 컬럼의 순서대로 데이터를 탐색하기 때문에, 부서의 순서대로 멀티 컬럼 인덱스를 구성했다면 먼저 일치하는 부서를 찾은 뒤, 일치하는 부서에서 이름을 찾는식으로 처리한다.

즉, 위의 정렬표를 보면서 이해하는 것이 빠르다.

따라서 멀티 컬럼 인덱스를 구성할 때는 데이터 중복도가 높은 컬럼이 앞쪽으로 오는 게 좋은 경우가 많다. 데이터 중복도가 높은 컬럼일수록 큼지막하게 분류가 되어 있다.

(간혹 예외의 경우도 있으므로, 쿼리 실행시 실행 계획을 보면서 판단하는것이 올바른 방향이다.)

- 멀티 컬럼 인덱스 컬럼의 순서는 매우 중요하다. 

- 멀티 컬럼 인덱스에서 처음에 배치된 컬럼들은 일반 인덱스처럼 활용할 수 있다.

- 멀티 컬럼 인덱스를 구성할 때 데이터 중복도가 높은 컬럼이 앞쪽으로 오는 게 좋다.

보너스로, 커버링 인덱스에 대해서 다루어보도록 하겠다.

위와 같은 users 테이블이 있고, 인덱스가 있다고 가정한다. 그리고 아래 2개의 SQL문을 실행하면,

1번째 SQL문을 보면 id, created_at 이라는 컬럼만 조회한다고 하더라도 실제 테이블의 데이터에 접근해야 한다.

하지만 2번째 SQL문에서는 id,name 컬럼은 실제 테이블에 접근하지 않고 인덱스에만 접근해서 알아낼 수 있는 정보들이다. 따라서 실제 테이블에 접근하지 않고 데이터를 조회할 수 있다. 실제 테이블에 접근하는 것 자체가 인덱스에 접근하는 것보다 속도가 느리다.

이 상황에서 SQL문을 실행시킬 때 필요한 모든 컬럼을 갖고 있는 인덱스를 보고 커버링 인덱스(Covering Index)라고 표현한다.

참고: js code (박재성)님의 강의 

         업무에 바로 쓰는 SQL 튜닝 책

멀티 컬럼 인덱스(Multi-Column Index)

Body Parser란?

Express JS란?

Request, Response

Resposne - send(), json(), end()

컨트롤러, 라우터

HTML ELEMENT

데이터 타입

실행계획을 통한 SQL 튜닝

인덱스

Package.json

Tick

링크

링크

멀티 컬럼 인덱스(Multi-Column Index)