분류 본능을 활용하라 인덱스 끝장리뷰

원문

인덱스 수는 적정해야 한다

인덱스의 개수가 많으면 많을수록 처리에서 많은 부하가 발생할 것 이고 그 대신에 조회에서는 빠른 결과를 얻을 수가 있다. 이럴땐 테이블 성격에 따라서 접근하는 것이 좋다. 처리성 테이블이면 적게 만들고 조회성 테이블이라면 많이 만들어도 부담이 없을 것 이다.

1. 코드성 테이블: 필요한 만큼 인덱스 생성 가능
2. 처리성 테이블: 최소한으로 사용
3. 집계성 테이블: 필요한 만큼 적정하게 사용
4. 로그성 테이블: 필요 없음(필요에 따라서 하나 정도는 존재할 수도 있음)

1번 코드성 테이블은 조회성 테이블에 가까우며 테이블 생성 초반에 필요로 하는 데이터가 적재될 가능성이 많고 수시로 변화하거나 추가되는 일은 거의 없다. 따라서 필요한 만큼 만들어서 사용하면 된다.

2번 처리성 테이블은 인덱스가 많을 수록 부하가 많이 발생할 것이다. 보통 동사적인 성격을 가지고 있다. 주문 테이블, 계약 테이블, 게시판 테이블 등이 있다. 이러한 동사적인 성격의 테이블은 처리에 비중이 많아 인덱스를 최소화해야한다.

3번 집계성 테이블은 야간 배치에 발생하는 경우가 많다. 처리에 대한 부담보다는 조회에 비중이 있으므로 인덱스가 많아도 무방하다.

4번 로그성 테이블은 조회의 비중이 없는 테이블이다. 인덱스가 없는 경우가 대부분이고 필요에 따라 PK 정도는 있을 수 있다.

인덱스는 만들기는 쉬워도 삭제하기는 어렵다. 만들기전에 반드시 필요한 것인지 충분히 고민해야한다. 만일 인덱스가 너무 많다면 다음과 같은 내용도 인덱스 생성 여부를 결정하는 요소가 될 수 있다.

1. 쿼리 구동 시간이 낮인지 밤인지에 따라 인덱스 생성 여부를 결저앟ㄹ 수 있다.
2. 누가 사용하는지에 따라서 인덱스 생성 여부를 결정할 수 있다.
3. 얼마나 많이 구동되는지에 따라서 인덱스 생성 여부를 결정할 수 있다.

인덱스는 위치정보와 순서정보로 구성됐다

조건에 관하여 ASC로 분류할지라도 찾을 때는 DESC로 찾을 것이다.

WHERE 구획 = '식품코너'
  AND 제품 = '우유'
ORDER BY 제조일자 DESC

구획 컬럼과 제품 컬럼은 위치(조건) 정보를 가지고 있고 제조일자 컬럼은 순서(정렬) 정보를 가지고 있다. 이런 경우 다음 두가지의 인덱스를 고렿라 수 있다.

인덱스 1 = 구획 + 제품

인덱스 2 = 구획 + 제품 + 제조일자(DESC)

두가지 인덱스가 모두 가능하지만 많을 수록 DB에 부하가 올라가므로 동시에 만들면 안된다.

일반적으로 CBO(Cost Based Optimizer)에서는 조건에 따른 Get Block 비용(부하)보다 소트 비용(부하)이 몇 배 더 높다. 따라서 소트 비용을 감내할 수 있으면 인덱스1이 좋고 감수할 수 없다면 인덱스2가 좋다.

WHERE 구획 = '식품코너'
  AND 제품 = '우유'
  AND 제조일자 BETWEEN '20140701' AND '20140707'
ORDER BY 제조일자 DESC, 제조번호 ASC

위 쿼리에서 구획, 제품, 제조일자, 제조번호 컬럼은 후보 컬럼이다. 만일 여러 컬럼으로 인덱스를 생성하고자 한다면 고려해야할 요소 중 하나가 인덱스 컬럼 순서다. 다음 규칙으로 컬럼 순서를 정하면 된다.

1. 위치정보 컬럼만으로 구성할 수 있다. 혹은 순서정보 컬럼만으로 구성할 수 있다.
2. 위치정보 컬럼과 순서정보 컬럼 순으로 구성할 수 있다.
3. 위치정보 컬럼과 순서정보 컬럼의 순서는 혼재돼서는 안 되며 뒤바꿔어서도 안 된다.

구획: 위치정보 컬럼 제품: 위치정보 컬럼 제조일자: 위치정보 + 순서정보 컬럼 제조번호: 순서정보 컬럼

따라서 규칙을 적용하면 다음과 같이 인덱스를 생성할 수 있다.

• 인덱스 = 구획 + 제품 + 제조일자 + 제조번호

순서정보 컬럼은 반드시 위치정보 후행컬럼의 있어야한다.

조건절에 사용하는 인덱스와 조인절에 사용하는 인덱스

오라클 쿼리에서 테이블간 관계를 연결해주는 조인 방법에는 Nested Loop Join, Sort Merge Join, Hash Join 세가지가 있다. 우리가 대부분 접하는 조인은 Nested Loop Join이다. 이 조인에서는 오로지 하나의 조건절 인덱스와 다수의 조인절 인덱스로 구성된다. 따라서 우리가 접하는 대부분 쿼리에서는 조건절 인덱스가 최초로 접근하는 테이블을 결정한다고 해도 틀린말은 아니다.

조인절에 인덱스가 없는 것은 조건절에 없는 것보다 성능 면에서 더 치명적이고 튜닝 관점에서 본다면 조인절에 인덱스가 없는 것은 성능 저하를 가져올 수 있다는 점을 잊지말아야 한다.

인덱스 생성/삭제 시 고려사항

인덱스를 만들었다고 모두 이용되는 것도 사용하지 않는다고 삭제 가능한것도 아니다. 여러가지 발생 가능한 문제점에 대한 충분한 검토가 필요하다.

1. 인덱스 생성 전 유사 인덱스가 존재하는지 확인한다.
2. 신규 인덱스 생성하기 전에 Index Split을 유발하지 않는지 확인한다.
3. 신규 인덱스를 생성하기 전에 CBO 방식에서의 통계정보가 최신인지 확인한다.
4. 기존 인덱스를 삭제하기 전에 사용하지 않는 미사용 인덱스인지 반드시 확인한다.

1번에 해당하는 경우는 인덱스끼리 경합이 발생할 수 있고 기존 쿼리의 실행계획이 변동해 성능상 문제가 발생할 수 있어서 주의가 요구된다.

2번의 경우는 중요하다. 인덱스 분류 작업이 한 곳으로 집준돼서 동일한 leaf block에 대해 과도한 split이 발생한다면 성능상에 문제가 발생한다. split이 발생하는 대표적인 컬럼으로는 등록일시와 같은 컬럼이며 해결방안으로 reverse index 이용이 있다.

index split: B-Tree index에서 새로운 index key가 들어왔을 때 기존에 할당된 블록 내에 저장할 영역이 없어 새로운 블록을 할당하는 것이다.

reverse index: index구성 column 순서는 유지하면서 column의 data를 byte 단위로 역순으로 indexing한다.

3번의 경우 통계정보와 실제 정보간의 갭이 용인 가능한지 아닌지에 대한 문제다. 급격하게 데이터가 증가하는 테이블에는 더욱더 확인이 필요하다. 만일 차이가 많이 난다면 인덱스가 생성되어도 이용되지 않을 가능성이 높다.

4번의 경우 어떤 쿼리에서도 사용되지 않는다는 확신이 있어도 결코 삭제하기 쉽지 않다. 때문에 부담이 있는 작업이다.

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

결합인덱스를 만들때 가장 신경써야하는 부분은 컬럼의 순서 결정이다. 컬럼 순서 결정 방법은 다음과 같다.

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