[DB] 결합인덱스 및 컬럼 순서 결정 방법

결합인덱스 

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

 

 

 

결합 인덱스 컬럼 결정

 

결합 인덱스를 만들 때 결합 인덱스를 구성하는 컬럼들의 배열 순서는 아주 중요하기에 신중하게 결정해야 한다. 컬럼의 순서를 잘못 배열하면 결합 인덱스의 발동 확률이 매우 낮아질 수 있기 때문이다. 

 

만약 select 문의 where절에 결합 인덱스의 첫 번째 컬럼을 조건에 사용하였다면 그 질의문은 결합 인덱스를 사용할 수 있다. 하지만 개발자가 결합 인덱스의 두번째 컬럼만을 where 절에 조건으로 사용하고 결합 인덱스를 사용하고자 했다면 실행계획은 인덱스를 사용하지 못한다. 

 

따라서 쿼리문 작성 시 결합 인덱스를 사용하고자 한다면 반드시 결합 인덱스의 컬럼 중 선행하는 컬럼부터 조건에 지정하여 사용하여야 한다. 조건은 컬럼 전체를 순서대로 사용할 수도 있고, 아니면 선행하는 일부 컬럼을 순서대로 사용할 수 있다.

 

 

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

 

1. WHERE절 조건에 많이 사용되는 컬럼이 우선시
2. Equal('=')로 사용되는 컬럼 우선
3. 분포도가 좋은 컬럼을 우선
4. 자주 이용되는 순서대로 결합 인덱스 컬럼의 순서 결정

 

 

1. 조건절에서 첫 번째 컬럼을 조건에서 사용하지 않는다면, 그 인덱스는 사용되지 않는 경우가 대부분이다.  그렇기에 많은 쿼리에서 공통적으로 사용된 조건절의 컬럼을 인덱스 선행 칼럼에 주로 사용한다.

 

2. 결합 인덱스에서 선행컬럼이 '=' 조건이 아니라면 후행컬럼 조건에서 '='을 사용하더라도 처리범위는 줄어들지 않는다. 조건절에서 '='이 아닌 사용하는 첫 번째 컬럼까지만 인덱스를 타고 그 이후는 인덱스를 타지 않고 필터 즉, 체크만 한다. 

예시를 보면,

WHERE 컬럼1 = ?
AND   컬럼2 = ?
AND   컬럼3 BETWEEN ? AND ? -- 결합인덱스에서 '='이 아닌 연산자를 사용하는 첫 번째 
AND   컬럼4 = ?

 

결합인덱스 = 컬럼1 + 컬럼2 + 컬럼3 + 컬럼4 라고 하자.

위의 코드에서 컬럼3에서 BETWEEN을 사용했기에 컬럼3 까지만 인덱스를 타고 후행컬럼인 컬럼4는 인덱스를 타지 않고 필터만 한다.

그래서 결합인덱스 = 컬럼1 + 컬럼2 + 컬럼4 + 컬럼3으로 순서를 바꿔줘야한다.

 

 

3. 흔히 분포도가 좋은 컬럼이 처리 범위를 줄여주므로 결합 인덱스의 선행컬럼으로 해줘야 한다고 한다. 하지만 굳이 분포도가 좋은 컬럼이면 단일 인덱스로서 사용되면 되고 굳이 결합인덱스로 사용할 필요가 없다. 결합인덱스를 사용하는 이유중 하나가 하나의 컬럼만으로는 분포도가 좋지 않지만 여러개의 컬럼으로 분포도를 향상시켜 처리범위를 줄여주는데에 있기 때문이다. 

 

또한, 인덱스의 컬럼이 모두 등치조건('=')일 경우, 어떤 칼럼이 먼저오든 블록의 I/O 개수가 많으므로 성능도 같다.

많은 블로그에서 카디널리티가 높을수록 앞쪽에 위치시키는게 유리하다는 글을 많이 보게 되는데 잘못된 정보이다.

 

예를 들면, [성별+이름] 인덱스와 [이름+성별] 인덱스는 블록 I/O 개수가 같다.

 

DBMS가 사용하는 B tree 인덱스는 엑셀처럼 평면 구조가 아니고 다단계 구조이다. 루트에서 브랜치를 거쳐 리프 블록까지 탐색하면서 '여자'이면서 '유관순'인 첫 번째 사원을 바로 찾아간다. 거기서부터 두건을 스캔한다. 정확히 말하면 유관순이 아닌 레코드를 만날때까지 세건을 스캔한다. 인덱스를 이름+성별 순으로 구성해도 마찬가지다.

따라서 어느 컬럼을 앞에 두든 일량에는 차이가 없다.( 친절한 SQL 튜닝 책의 내용중 )

https://richardfoote.wordpress.com/2008/02/13/its-less-efficient-to-have-low-cardinality-leading-columns-in-an-index-right/

 

 

 

 

 

 

 

reference: https://coding-factory.tistory.com/755