꿈꾸는 개발자, DBA 커뮤니티 구루비

아직도 많은 사이트에서 단일 컬럼 인덱스만을 고집하는 경우가 많다.

과연 데이터를 액세스하기 위해 우리는 단일 컬럼 인덱스를이용해야 하는 것일까? 단일 컬럼 인덱스만으로 대용량의 데이터를 모두 처리할 수 있는가?

데이터베이스의 용량이 점점커져가는 지금 더 이상 단일 컬럼 인덱스만으로는 해결하기 어려운 상황이 되었다. 이제는 결합 컬럼 인덱스를 생성해야만 대용량 데이터베이스의 성능을 해결할 수 있다.

"결합칼럼 인덱스와 단일칼럼 인덱스 강좌1" 에서 결합 컬럼 인덱스를 어떻게 구성하는가에 따라 처리 범위가 변한다고 언급하였다. 이번 강좌에서는 이와 같은 현상에 대해 정확히 분석하여 결합 인덱스를 선정하는 우선순위에 대해 확인해 보자.

인덱스를 이용하여 성능 향상의 효과를 기대할 수 있으려면 먼저 해당 인덱스를 이용하여 처리 범위를 최대한 감소시켜야 한다. 성능을 향상시키기 위해서는 결합 인덱스를 구성하는 컬럼은 반드시 다음의 순서에 맞도록 생성해야 한다.

이 4단계 우선 순위에 의해 우리는 결합 인덱스를 생성해야한다. 이 우선 순위를 지키지 않는다면 애당초 해당 인덱스를 이용한 성능 향상은 기대하기 조차 힘들어질 것이다.

물론, 결합 인덱스를 생성하는 우선 순위에서 WHERE 조건에 사용하는 컬럼으로 인덱스를 구성한다는 사실은 너무나 자명하기 때문에 제외한 것이다. 해당 SQL에서 사용되지 않는 컬럼을 특별한 이유 없이 인덱스에 추가하는 경우는 그다지 많지 않다.

이런 결합 인덱스 구성 규칙은 왜 생긴 걸까? 조금만 생각해보면 그 답은 쉽게 얻을 수 있다. 디스크 I/O을 가장 적게 발생시키기 위해서다.

결국, 이런 우선 순위로 인덱스를 생성한다면 우리가 원하는 데이터를 추출할 때 최소의 디스크 I/O를 발생시키게 되어 성능을 보장 받을 수 있게 된다.

안타깝게도 그 동안 필자가 지원했던 사이트들 중에는 이러한 인덱스 선정 기준을 준수하던 사이트가 거의 없었다.

이러한 것이 추후 해당 시스템을 이용하여 서비스를 개시하는 순간 어느 누구도 책임질 수 없는 성능 저하로 다가온다는 것을 이해하는가?

이는 지난 강좌에서도 이미 언급한 내용이다. 인덱스를 생성하는 경우 해당 컬럼의 분포도를 고려하는 경우가 많다. 이는 잘못된 지식에서 시작된 오류다.

다음 예제를 통해 인덱스를 생성한다고 가정해보자.

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

예제와 같이 SQL을 수행하는 경우에는 어떠한가? 카드번호 컬럼의 분포도는 매우 좋으며 거래일자 컬럼의 분포도는 매우 안 좋다고 가정하자.

분포도가 좋다는 뜻은 해당 테이블의 데이터에서 카드번호의 값이 '111'을 만족하는 데이터는매우 적다는 의미다. 분포도가 나쁘다는 의미는 이와 반대의의미를 가지게 된다.

따라서. 분포도가 좋은 컬럼인 카드번호 컬럼을 인덱스의 가장 앞에 두고 분포도가 나쁜 거래일자 컬럼을 뒤로해서 인덱스를 구성했다고 가정하자.

그렇다면 인덱스는 카드번호+거래일자 인덱스가 되며 이는 컬럼의 분포도를 고려하여 인덱스를 생성한 경우다.

이와 같이 인덱스를 구성한다면 카드번호 컬럼의 값에 의해 처리 범위는 감소하게 되며 또한 거래일자 컬럼에 의해서도 처리 범위가 감소하게 되므로 원하는 데이터에 대해 최소의 액세스로 결과를 추출할 수 있게 된다.

이 예제를 통해 분포도가 좋은 컬럼을 앞으로 하여 인덱스를 구성하면 되지 않냐고 이야기할 수도 있을 것이다.

이번에는 다음 예제를 확인해 보자.

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

이번에는 컬럼의 분포도만을 생각하여 카드번호+거래일자 인덱스를 생성한다면 카드번호 컬럼에 의해서만 처리 범위가 감소하게 되어 우리는 성능 저하를 경험할 수 밖에 없게된다.

'111' 카드번호부터 '555'카드번호까지 2008년 5월 15일 데이터만을 액세스하는 것이 아니라 '111' 카드번호부터 '555' 카드번호까지 모든 데이터를 액세스하기 때문이다.

결국, 거래일자 컬럼은 처리 범위를 감소시키지 못하게 된다.

분명히 컬럼의 분포도가 좋은 컬럼을 인덱스의 앞에 위치시켰지만 처리 범위는 카드번호 컬럼에 의해서만 감소하게 된다.

이와 같은 현상이 발생하는 이유는 컬럼의 분포도가 결합인덱스를 구성하는 중요한 요소가 되지 못한다는 것을 의미하기도 한다. 이는 우리가 항상 이야기하는 분포도에 우리가 스스로 함정에 빠지기 때문이다.

카드번호 값을 유일한 값(UNIQUE)으로 관리하는 카드_MASTER 테이블에는 카드번호 컬럼의 분포도가 좋다고 이야기 한다.

이와 같이 이야기하는 그 안에는 깊은 의미가 숨겨져 있다.

우리가 항상 이야기하는 분포도라는 것은 = 로 조회할 경우다. 카드_MASTER테이블의 카드번호 컬럼이 아무리 분포도가 좋더라도 카드번호 LIKE' 1%'라고 하고 해당 카드번호 값들은 모두 1로 시작한다면 이는 절대 분포도가 좋을 수가 없다.

이와 같은 이유에서 결합 컬럼 인덱스 선정에서는 LIKE 등의 선분 조건도 많이 사용되기 때문에 분포도가 아니라 연산자가 중요하게 되는 것이다.

또한, 앞의 값이 미지수라면 우리는 뒤의 값을 알더라도 인덱스를 제대로 이용할 수 없게 된다.

예를 들어, 사전에서'G??L'이라는 단어를 찾고 싶다면 어떻게 할까?

G라는 앞 단어에 의해 우리는 G로 시작하는 단어만 액세스할수 있지만 뒤에 명시되어 있는 L은 액세스 범위를 감소시키지 못하고 확인하는 역할을 수행하게 된다.

데이터베이스의 인덱스도 이와 다르지 않다. 따라서 우리는 지난 강의에서 언급한것과 같이 다음과 같은 인덱스를 생성해야 할 것이다.

위와 같이 연산자에 의해 처리 범위가 결정되며 우리는 최소의 처리 범위를 보장받기 위해서는 점 조건(=,IN) 앞에 선분 조건(=, IN을 제외한 연산자)이 존재하면 안 될 것이다.

결합 인덱스의 컬럼 순서는 해당 컬럼이 사용하는 연산자가 매우 중요한 역할을 수행하게 된다. 다음 강좌에서는 두 번째 우선 순위인 랜덤 액세스를 고려한 인덱스 컬럼 선정에 대해 자세히 확인해 보자.