데이터베이스 - 결합 인덱스

Hoonkii·2022년 12월 22일

오랜만의 데이터베이스 관련 포스팅이다. 친한 개발자와 인덱스에 관련된 토론을 하다가 결합 인덱스에 대해서 정확히 정리가 안된 것 같아서 정리해보고자 한다.

인덱스를 생성할 때 두 개 이상의 컬럼을 결합해서 인덱스를 만드는 것을 결합 인덱스라고 한다. 하나의 컬럼만으로는 분포도가 나빠서 여러 개의 컬럼으로 분포도를 향상시켜 처리 범위를 줄이기 위해서 사용한다.

예를 들어보자.

사용자(users) 테이블에 100만 개의 데이터가 있고, 절반은 남자 절반은 여자로 구성된다고 가정하자. 그리고 사용자의 이름은 비교적 다양하게 분포되어 있다고 가정하자.

SELECT *
FROM users
WHERE name = 'hoonki'
AND sex = 'M';

인덱스 없이는 데이터를 조회하는 데 시간이 많이 걸릴 수 있다. 성능을 개선시키기 위해서 name, sex 컬럼을 가지고 결합 인덱스를 구성하여 성능을 개선시킬 수 있다.

CREATE INDEX idx_name_sex on users(name, sex);

위와 같이 결합 인덱스를 생성할 수 있다.

결합 인덱스를 어떻게 생성하는지는 알겠는데, 그러면 어떤 컬럼을 결합 인덱스로 선택해야 할까? 이게 사실 중요하다. 결합 인덱스 설계를 잘못하면 성능에 큰 영향을 미칠 수 있기 때문이다. 우선 결합 인덱스 컬럼을 선택하는 기준은 다음과 같다.

WHERE 절에서 AND 조건으로 자주 결합되어 사용되며, 각 컬럼의 단일 분포도 보다 두 개 이상의 컬럼이 결합될 때 분포도가 좋아지는 컬럼들

→ AND 조건이 아니면 결합 인덱스를 탈 수 없으며, 단일 컬럼으로도 분포도가 좋다면 굳이 결합 인덱스를 사용할 필요가 없다.

결합 인덱스를 만들 때 컬럼간의 순서를 지정할 수 있다. 쿼리 조건이 어떠냐에 따라서 컬럼 간 순서를 잘 조정해야 좋은 쿼리 성능을 이끌어낼 수 있다. 컬럼 설정시 우선순위는 다음과 같다.

1번은 자명하다.
그리고 일반적으로 대부분의 사람들이 분포도가 좋은 컬럼이 결합 인덱스의 우선순위를 결정하는데 제일 중요하다고 생각하나 (나도 그랬다), 사실 그것보다 중요한 조건은 2번 조건이다.

다음의 예제를 보자. 거래내역 이라는 테이블에 카드번호와, 사용액 거래 일자가 포함되어 있다. 카드번호 는 분포도가 좋고 거래일자는 분포도가 좋지 않다고 가정해보자.

SELECT 카드번호, 사용액
  FROM 거래내역
 WHERE 카드번호 = '111'
   AND 거래일자 BETWEEN '20080501'
                   AND '20080510';

그러면 카드번호를 앞순위로 두고 거래일자 를 뒷순위로 두어 결합 인덱스를 설정하면, 분포도가 좋은 카드번호 로 먼저 필터링을 하기 때문에 실제로 봐야할 데이터가 적어져(Disk I/O가 줄음) 성능이 좋아질 것이다.

CREATE INDEX idx_num_date on 거래내역(카드번호, 거래일자);

이렇게 보면 당연히 분포도가 높은 컬럼의 우선순위를 높이는 것이 유리해보인다.

한 가지 예제를 더 살펴보자. 테이블과 데이터 분포 가정은 똑같다.

SELECT 카드번호, 사용액
  FROM 거래내역
 WHERE 카드번호 BETWEEN '111'
                   AND '555'
   AND 거래일자 = '20080515';

위의 경우와 같이, 단순 분포도 순으로 카드번호, 거래일자 순으로 인덱스를 걸면 여전히 성능이 좋을까? 전혀 그렇지 않다. 그 이유는 카드번호의 조건이 동치 (’=’)가 아닌 BETWEEN … AND 이기 때문이다.

결합 인덱스에서 선행 컬럼의 조건이 동치(’=’)가 아니라면 후속 컬럼의 인덱스 효과를 전혀 받지 못하게 된다. Disk로 부터 모든 데이터들을 읽어와 필터링 해야 하기 때문에 성능 저하가 일어난다.

다시말하면 위의 쿼리에서 111 카드 번호 부터 555 카드 번호까지 2008년 5월 15일 데이터 만을 인덱스에서 거를 수 있는 것이 아니라, 111 부터 555 까지 모든 데이터를 Disk로 부터 읽어와 필터링 해야하기 때문이다.

결국 분포도가 중요한 때는 모든 쿼리 조건이 동치일 때이고, 그 것보다 중요한 것은 쿼리에 사용되는 컬럼의 조건이 중요하다.

결합 인덱스를 설계 할 때는 컬럼을 잘 선택하고, 쿼리에 따라 컬럼 순서를 잘 고려해서 최적의 설계를 하도록 하자. 쿼리 성능은 결국 Disk I/O를 줄이는 데 있기 때문에, 상황에 맞게 설계하는 것이 중요하다.

개발 공부 내용 정리