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

 

데이터 베이스를 다루면서 성능 향상을 위해 인덱스의 사용과 개념은 전 페이지에서 설명하였다. 특정 컬럼을 기준으로 정렬해 놓은 목차 같은 것이라고 할 수 있고 분류 대상과 분류 정보를 분리했을 때 분류 정보가 인덱스라고 할 수도 있다. 

여기서 확인할 것은 특정 컬럼이 하나가 아닐 수 있다는 것이다. 이것은 여러 개의 인덱스를 만든다는 말이 아니라 하나의 인덱스에 여러 컬럼이 기준이 될 수 있다는 것이다. 이것이 바로 결합 인덱스이다. 

이 글에서는 결합인덱스에 대해 간단히 설명하고 컬럼 선정 방법에 대해서도 설명할 것이다. 

참고로 '개발자를 위한 인덱스 생성과 SQL 작성 노하우(이병국)'을 참고하여 작성하였다. 

 

결합 인덱스는 하나의 인덱스에서 기준 컬럼이 하나가 아닌 인덱스이다. 

결합 인덱스 1=  컬럼1+ 컬럼2 + 컬럼3 + 컬럼4

위와 같은 형식으로 하나의 인덱스가 4개의 컬럼으로 이루어져있다. 하나의 컬럼만으로는 분포도가 높을 수 있지만 여러개의 컬럼으로는 분포도가 낮아져서 효율이 더 좋을 수 있다. 보통 결합인덱스는 아래와 같은 조건에서 자주 생성한다. 

1. WHERE 조건으로 사용되는 컬럼
2. 조인 절에 연결고리로서 사용되는 컬럼
3. ORDER BY절에서 사용되는 컬럼

 

아래는 결합 인덱스 생성 코드 예시이다. 

 

CREATE INDEX test_idx ON test(A, B, C);

 

 

위 코드로 결합 인덱스를 생성하면 A + B + C 순서로 결합 인덱스가 생성된다. 

결합 인덱스에서 컬럼의 순서는 매우 중요하다. 위에서 처럼 A - B - C 순으로 생성한다면 A에 대해 정렬을 한 후 정렬된 상태에서 B에 대해 정렬을 한 후 C에 대해 정렬을 하는 것이다. 즉, A를 먼저 찾고 B를 찾은 후 C를 찾아야 하는 것이다. B나 C를 먼저 정렬을 한 것 하고는 매우 큰 차이가 있다. 그렇기에 정렬 순서를 잘 생각해야 하고 정렬 순서가 잘못됐으면 의미 없는 인덱스일 뿐이다. 

결합 인덱스의 컬럼 순서를 결정하는 방법은 여러 가지 의견이 있겠지만 이 글에서는 '개발자를 위한 인덱스 생성과 SQL 작성 노하우(이병국)'을 참고하겠다. 

1. 공통적으로 사용하는 필수 조건절 컬럼을 우선한다.
2. '=' 조건의 컬럼을 다른 연산자 컬럼보다 우선한다.
3. 대분류 -> 중분류 -> 소분류 컬럼순으로 구성한다.
4. WHERE 조건절 컬럼은 ORDER BY조건절 컬럼보다 우선한다. 

 

 

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

 

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

 

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

 

 

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

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

 

그렇기에 아래와 같이 결합 인덱스를 다시 짜는 것이 좋을 것이다. 

결합인덱스 = 컬럼1 + 컬럼2 + 컬럼4 + 컬럼5 + 컬럼3

 

3. 흔히 분포도가 좋은 컬럼이 처리 범위를 줄여주므로 결합 인덱스의 선행컬럼으로 해줘야 한다고 한다. 하지만 굳이 분포도가 좋은 컬럼이면 단일 인덱스로서 사용되면 되고 굳이 결합인덱스로 사용할 필요가 없다. 결합인덱스를 사용하는 이유중 하나가 하나의 컬럼만으로는 분포도가 좋지 않지만 여러개의 컬럼으로 분포도를 향상시켜 처리범위를 줄여주는데에 있기 때문이다. 그렇기에 분포도가 좋은 컬럼은 단일 인덱스의 컬럼선정의 요소이지 결합인덱스의 선정 요소는 아니다. 오히려 분포도는 결합 인덱스에서 전혀 상관이 없다. 자세한 내용은 https://zinlee.tistory.com/entry/%EC%9D%B8%EB%8D%B1%EC%8A%A4-%EC%BB%AC%EB%9F%BC%EC%9D%98-%EB%B6%84%ED%8F%AC%EB%8F%84-%EB%B0%8F-%EC%88%9C%EC%84%9C%EC%98%A4%EB%9D%BC%ED%81%B4-%EC%9D%B8%EB%8D%B1%EC%8A%A4 를 참고하길 바란다.

인덱스 컬럼의 분포도 및 순서(오라클 인덱스)

 

결합 인덱스는 여러 컬럼을 합쳐서 처리범위를 줄인다는 의미도 있지만, 다수의 단일 인덱스를 대체하는 공통의 인덱스라는 의미도 있다. 

결합인덱스는 분포도로 접근하기보다는 분류의 개념으로 접근하는 것이 더 좋다.

위에서 말했듯이 결합 인덱스에서는 선행컬럼에서 걸러진 범위에서 그 다음 컬럼이 걸러지고 연속해서 걸러진 범위에서 값들이 걸러지는 것이다. 그렇기에 걸러지는 범위는 큰 범위부터 점점 작은 범위로 걸러지는 것이 더 좋은 효율을 낼 수 있는 것이다.  그렇기에 컬럼 순서가 대분류부터 소분류로 정하는 것이 좋다. 

 

4. 인덱스는 책의 목차나 색인의 역할처럼 빨리 찾는 의미로 인식되고 있는데, 인덱스는 기본적으로 위치(조건) 정보와 순서(정렬) 정보의 특성을 동시에 갖고 있다. 따라서 결합 인덱스에는 위치정보를 갖고 있는 컬럼도 있고, 순서 정보를 갖고 있는 컬럼도 있다. 순서 정보 즉, 정렬에 사용되는 컬럼으로는 처리범위가 그대로인 상태로 다음 컬럼으로 넘어가는 것이다. 그렇기에 위치 정보 즉, 조건절에 사용되는 컬럼에 우선순위를 둬서 처리 범위를 줄이는 것이 더 좋을 것이다. 

 

 

 

참고

새로쓴 대용량 데이터베이스 솔루션 - 이화식

개발자를 위한 인덱스 생성과  Sql 작성 노하우 - 이병국

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

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

 

http://www.gurubee.net/lecture/2229 

결합 인덱스를 선정하는 우선순위

 

https://zinlee.tistory.com/entry/%EC%9D%B8%EB%8D%B1%EC%8A%A4-%EC%BB%AC%EB%9F%BC%EC%9D%98-%EB%B6%84%ED%8F%AC%EB%8F%84-%EB%B0%8F-%EC%88%9C%EC%84%9C%EC%98%A4%EB%9D%BC%ED%81%B4-%EC%9D%B8%EB%8D%B1%EC%8A%A4 

인덱스 컬럼의 분포도 및 순서(오라클 인덱스)