[DB] 데이터베이스 결합 인덱스에 대하여

결합 인덱스란?

결합 인덱스란 두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것을 말합니다. 주로 단일 컬럼으로는 나쁜 분포도를 가지지만 여러 개의 컬럼을 합친다면 좋은 분포도를 가지고, Where절에서 AND 조건에 많이 사용되는 컬럼들을 결합 인덱스로 구성합니다.

 

결합 인덱스 컬럼 선택

1. where절에서 and 조건으로 자주 결합되어 사용되면서 각각의 분포도 보다 두 개 이상의 컬럼이 결합될 때 분포도가 좋아지는 컬럼들 

2. 다른 테이블과 조인의 연결고리로 자주 사용되는 컬럼들

3. order by에서 자주 사용되는 컬럼들

4. 하나 이상의 키 컬럼 조건으로 같은 테이블의 컬럼들이 자주 조회될 때

 

결합 인덱스의 컬럼 순서 결정

결합 인덱스를 만들 때 결합 인덱스를 구성하는 컬럼들의 배열 순서는 아주 중요하기에 신중하게 결정하여야 합니다. 컬럼의 순서를 잘못 배열하면 결합 인덱스의 발동 확률이 매우 낮아질 수 있기 때문입니다. 만약 select 문의 where절에 결합 인덱스의 첫 번째 컬럼을 조건에 사용하였다면 그 질의문은 결합 인덱스를 사용할 수 있습니다. 하지만 개발자가 결합 인덱스의 두번째 컬럼만을 where 절에 조건으로 사용하고 결합 인덱스를 사용하고자 했다면 실행계획은 인덱스를 사용하지 못합니다. 따라서 쿼리문 작성 시 결합 인덱스를 사용하고자 한다면 반드시 결합 인덱스의 컬럼 중 선행하는 컬럼부터 조건에 지정하여 사용하여야 합니다. 조건은 컬럼 전체를 순서대로 사용할 수도 있고, 아니면 선행하는 일부 컬럼을 순서대로 사용할 수 있습니다. 

 

결합 인덱스 컬럼의 설정 시 고려해야 할 우선순위

1. where절 조건에 많이 사용되는 컬럼이 우선시

2. Equal('=')로 사용되는 컬럼 우선

3. 분포도가 좋은 컬럼을 우선

4. 자주 이용되는 순서대로 결합 인덱스 컬럼의 순서 결정

 

 결합 인덱스 사용 예시 

결합 인덱스 생성

create index emp_pay_idx on emp_pay(급여년월, 급여코드, 사원번호);

emp_pay 테이블에서 급여년월, 급여코드, 사원번호 컬럼으로 emp_pay_idx라는 결합 인덱스를 생성하였습니다.

 

결합 인덱스 사용

select * from emp_pay where 급여년월 = '202107';
select * from emp_pay where 급여년월 = '202107' and 급여코드 ='정기급여';
select * from emp_pay where 급여년월 = '202107' and 급여코드 = '정기급여' and 사원번호 = '20210401';

select 문장의 where 절에서는 다음과 같은 조건 조합에서 인덱스가 사용되게 됩니다.

 

결합 인덱스가 사용되지 않을 때

select * from emp_pay where 급여코드 = '정기급여';
select * from emp_pay where 사원번호 = '20210401';
select * from emp_pay where 사원번호 = '20210401' and 급여코드 = '정기급여';

위와 같이 where 조건문을 나열할 때 결합 인덱스의 첫 번째 컬럼인 급여년월의 조건식이 없으면 B*Tree 구조인 결합 인덱스를 검색할 수 없기 때문에 결합 인덱스가 무용지물이 되니 주의하여야 합니다.

 

결합 인덱스의 효율성이 떨어지는 경우

결합 인덱스도 일반적인 인덱스와 마찬가지로 데이터들이 정렬되어 보관되기 때문에 소수의 데이터를 빠르게 찾는 것에는 유리하지만 아래와 같이 스캔이 많이 생기게 된다면 효율성이 떨어지게 됩니다. 아래의 예시들은 emp_pay_idx 인덱스를 사용하기는 하지만 스캔이 많이 생기는 경우로 인덱스의 효율성이 떨어지는 경우들의 예시입니다.

select * from emp_pay where 급여년월 LIKE '2021%' and 급여코드 = '정기급여';

위 조건절의 경우 결합 인덱스의 첫 번째 컬럼인 급여년월의 조건이 있더라도 Equal(=)이 아닌 범위 연산자인 LIKE '2021%' 조건을 사용했으므로, 세개의 칼럼이 모두 필요한 emp_pay_idx 인덱스를 찾을 때 두번째 칼럼인 급여코드에 대한 조건을 B*Tree에서 쉽게 찾을수가 없게 됩니다. 이는 결합 인덱스가 각 칼럼별로 정렬이 되어 있는 것이 아니라 첫번째, 두번째, 세번째 칼럼이 결합이 되어 정렬이 되어있기 때문입니다. 이때 급여코드에 대한 조건은 인덱스를 찾아가는 검색조건이 아니라 인덱스 값이 조건에 맞는지 여부를 검증하는 체크 조건이 됩니다.

 

select * from emp_pay where 급여년월 = '202107' and 사원번호 = '20210401';

위 조건절의 경우는 결합 인덱스의 첫번째 칼럼인 급여년월의 조건이 equal(=)이더라도 두번째 컬럼인 급여코드에 대한 조건이 없으므로 세번째 칼럼인 사원번호 조건을 검색 조건이 아닌 체크 조건으로 밖에 사용할 수 없게 됩니다. 즉 결합 인덱스에서 급여년월인 모든 데이터를 찾아서 사원번호 조건에 맞는지 일일이 확인하는 풀 테이블 스캔이 일어나고 있는 셈입니다.