결합 컬럼 인덱스와 단일 컬럼 인덱스

아직도 많은 사이트에서 단일 컬럼 인덱스만을 고집하고 있는 것을 보았다. 과연 데이터를 액세스하기 위해 우리는 단일 컬럼 인덱스를 이용해야 하는 것일까? 단일 컬럼 인덱스만으로 복잡해진 업무와 대용량 데이터를 모두 처리할 수 있겠는가? 이제는 더 이상 단일 컬럼 인덱스만으로 모든 것을 해결할 수 없게 되었다.
결합 컬럼 인덱스로 생성하여 처리 범위와 랜덤 액세스를 감소시키는 것이 인덱스를 효과적으로 사용하는 것이다. 최근에 어떤 사이트를 지원하면서 해당 시스템의 중요 테이블에 존재하는 인덱스를 확인해 보았다. 하나의 테이블에 평균 열 개 이상의 인덱스가 존재했으면 각각의 인덱스는 단일 컬럼 인덱스로 구성되어 있었다.
이 얼마나 어리석고 비효율적인 현상인가? 이제라도 우리는 단일 컬럼 인덱스가 아닌 결합 컬럼 인덱스를 생성하여 해당 시스템을 최적화시켜야 할 것이다.

인덱스를 이용하면서 우리는 처리 범위를 감소시키기 위해 연산자에 의해 결합 컬럼 인덱스의 컬럼을 선정하는 내용과 랜덤 액세스를 감소시키는 방법, 정렬을 제거하는 방법에 대해 확인해 봤다. 이번 호에서는 동일한 점 조건에 대해 인덱스를 선정함에 있어 어떤 컬럼이 먼저 위치해야 하는지 동일한 성분 조건들 중에서 인덱스를 선정함에 있어 어떤 컬럼이 앞에 위치해야 하는지를 확인해보자.

단일 컬럼의 분포도를 고려한 결합 컬럼 인덱스의 컬럼 선정하기

인덱스를 이용해 성능 향상을 기대하려면 해당 인덱스를 이용해 처리 범위와 랜덤 액세스를 최대한 감소시켜야 한다. 그러기 위해서는 결합 인덱스를 구성하는 컬럼은 반드시 다음과 같이 구성돼야만 처리 범위 및 랜덤 액세스를 최소화시킬 수 있다.

·1순위 - 컬럼이 사용한 연산자에 의한 인덱스 컬럼 선정
·2순위 - 랜덤 액세스를 고려한 인덱스 컬럼 선정
·3순위 - 정렬 제거를 위한 인덱스 컬럼 선정
·4순위 - 단일 컬럼의 분포도를 고려한 인덱스 컬럼 선정

위의 내용은 이미 결합 컬럼 인덱스와 단일 컬럼 인덱스에 대해 언급하면서 처음에 제시한 내용이다. 이 중 4순위의 단일 컬럼의 분포도를 고려한 인덱스 컬럼 선정에 대해 확인해 보자.

여기서 의아한 점이 한가지 있을 것이다. 그것은 컬럼의 분포도와 결합 컬럼 인덱스의 컬럼 순서와는 상관이 없다고 처음에 언급하였다. 하지만, 4순위에 컬럼의 분포도를 고려해야 한다고 되어 있다. 이는 과연 무엇을 의미하는 것일까? 이는 점 조건과 점 조건 사이 또는 선분 조건과 선분 조건 사이에만 적용되는 사항이다. 결국, 결합 컬럼 인덱스를 선정함에 있어 컬럼의 분포도를 고려한다는 것은 다음과 같은 경우에만 해당된다.

Where 절의 조건 중 점 조건이 두 개 이상 또는 선분 조건이 두 개 이상인 SQL에 대한 결합 컬럼 인덱스를 선정하는 경우 컬럼의 분포도를 고려하며 선분 조건 사이에는 컬럼 자체의 분포도가 아니라 컬럼과 연산자에 의한 분포도를 의미한다.

점 조건이 두 개 이상 존재하는 경우에는 컬럼의 분포도를 고려한다.

결합 컬럼 인덱스를 생성하다 보면 Where 조건에 점 조건이 두 개 이상인 경우는 워낙 흔한 일이다. 그렇다면 점 조건이 두 개 이상 사용된 경우 결합 컬럼 인덱스의 선정은 어떻게 해야 하는가? 물론, 두 컬럼이 모두 점 조건으로 사용되었으므로 선분 조건보다는 앞에 위치해야 하며 점 조건+점 조건은 순서가 변경되어도 처리 범위가 동일하게 된다. 다음의 예제를 확인해 보자.

SQL> SELECT 카드번호, 사용액
FROM 거래내역
WHERE 성별 = ‘남’
AND 고객번호 = ‘1111’;

위의 예제에서 인덱스는 어떻게 생성해야 하는가? Where 절에 존재하는 조건이 모두 점 조건이므로 성별+고객번호 인덱스 또는 고객번호+성별 인덱스 모두 동일한 처리 범위를 보장하게 되다. 따라서, 성별+고객번호 인덱스 또는 고객번호+성별 인덱스 어느 인덱스나 동일하다는 의미가 된다. 그렇다면 어떤 인덱스를 선정해야 하는 것일까?

SQL> SELECT 카드번호, 사용액
FROM 거래내역
WHERE 성별 = ‘남’;
SQL> SELECT 카드번호, 사용액
FROM 거래내역
AND 고객번호 = ‘1111’;

이와 같이 두 가지 SQL이 거래내역 테이블에 수행된다면 어떠한가?
첫 번째로 성별+고객번호 인덱스로 선정했다면 Where 절에 성별 조건만 설정된 SQL의 경우에는 해당 인덱스를 이용하게 될 것이다. 하지만, 성별 조건을 만족하는 데이터는 거래내역 테이블에서 추출되는 데이터의 양이 많기 때문에 인덱스를 이용하는 것이 더 불리하게 된다. 고객번호 조건만 설정된 경우에는 일반적으로는 해당 인덱스를 이용할 수 없기 때문에 SQL을 튜닝하거나 또는 고객번호 인덱스를 추가해야 할 것이다.

두 번째로 고객번호+성별 인덱스로 인덱스를 선정했다면 Where 절에 성별 조건만 설정된 SQL의 경우에는 해당 인덱스를 이용할 수 없게 된다. 어차피 성별 조건을 만족하는 데이터는 해당 테이블에서 많은 데이터에 해당되므로 인덱스를 이용하지 않는 것이 더욱 유리하다. 고객번호 조건만 설정된 경우에는 고객번호+성별 인덱스를 효과적으로 이용할 수 있게 된다.

고객번호+성별 인덱스가 유리하며 이는 해당 테이블을 액세스하는 다른 SQL을 고려한 것이다. 결국, 분포도가 좋은 컬럼을 동일한 점 조건의 컬럼 중 인덱스의 앞에 위치시키는 것이 더욱 유리하게 된다.

선분 조건이 두 개 이상 경우에는 연산자에 의한 컬럼의 분포도를 고려한다.

SQL의 Where 절에 선분 조건이 두 개 이상 존재한다면 어떻게 인덱스를 구성해야 하는가? 다음의 예제를 통해 확인해 보자.

SQL> SELECT 카드번호, 사용액
FROM 거래내역
WHERE 거래일자 BETWEEN ‘20080901’ AND ‘20080910’
AND 구분 > ‘2’;

SQL이 이와 같다면 인덱스는 거래일자+구분 인덱스 또는 구분+거래일자 인덱스로 인덱스를 생성하게 될 것이다. 거래일자+구분 인덱스로 생성한다면 처리 범위는 거래일자 컬럼에 의해서만 감소하게 되며 구분+거래일자 인덱스로 인덱스를 생성한다면 구분 컬럼에 의해서만 처리 범위가 감소하게 된다.

최적의 성능을 보장받기 위해서는 처리 범위를 최소화시킬 수 있는 인덱스를 생성해야 하며 그러므로 연산자에 의해 분포도가 좋은 컬럼이 인덱스의 앞에 위치해야만 할 것이다. 따라서. 선분 조건이 두 개 이상 경우에는 연산자에 의한 컬럼의 분포도를 고려하여 결합 인덱스를 생성해야 한다.

단일 컬럼 인덱스로 구성하는 것보다는 결합 컬럼 인덱스로 인덱스를 구성하는 것이 유연성 및 성능을 보장할 수 있다. 이와 같은 점에 유의하여 인덱스를 선정해야 할 것이다.

필자소개

권순용 kwontra@hanmail.net ｜

DBA로 시작하여 SQL 튜닝, 데이터베이스 아키텍처 및 모델링 업무를 주로 수행했다. 현재는 DB 컨설팅 사업을 시작했다. 데이터베이스 교육에도 많은 관심을 가지고 있으며 저서로는 『Perfect! 오라클 실전 튜닝』, 『초보자를 위한 오라클 10g』 및 『INSIDE SQL』이 있다. 또한, 데이터 액세스 최적화에 대한 특허를 출원했다.

제공 : DB포탈사이트 DBguide.net