CUBRID인덱스 구성 전략

CUBRID인덱스 구성 전략

2009-12-23 컨설팅팀, CUBRID

적용 대상: CUBRID

적용 환경

본 문서는 아래와 같은 환경에서 테스트 되었다.

CUBRID R1.0 이상

 

CUBRID인덱스 개요

CUBRID 에서 제공하는 인덱스의 종류는 2가지 이다.

1.      B+tree Index

대부분의 RDBMS 제공하는 인덱스와 같이 Ascending 으로 정렬되어 저장 되며 CUBRID B+tree 는 prefix B+tree로 구현 되어 있다.

2.      Reverse Index ( DESC index )

기본 인덱스와는 달리 인덱스 저장시 Desending 으로 저장하며 인테넷 서비스에서 자주 사용되는 ORDER BY 절과 같이 소팅이 필요한 부분에 최적화 되어 있다. ( 타 상용 DBMS Reverse Key Index와는 다른 인덱스 임 )

특징

l  Prefix B+tree 로 구현 되어 있으며 결합 인덱스의 경우 mixed type 으로 변형 하여 저장 한다.

l  타 상용 DBMS 와 같이 /*+ INDEX_DESC */ 힌트를 사용 할수 없다. DESC 을 하려면 인덱스를 생성 해야 한다.

l  Next-Key Locking 으로 구현 되어 있다. 자세한 사항은 nforge 기술문서인 “CUBRID B+Tree 관리자”를 참고 바란다.

l  저장시 Non-Leaf 노드와 Leaf 노드로 저장되며 Leaf 노드는 링크드 리스트로 연결 되어 이진 검색을 하므로 범위 검색에 유리 하다.

l  $CUBRID/conf/cubrid.conf 파일에 index_unfill_factor 파라미터로 인덱스 저장시 여유 공간을 지정 할 수 있다.default 값은 0.2 이며 0.35 까지 설정이 가능 하다. Insert 와 update 빈도가 높아 인덱스 PAGE 에 여유가 없어 ( CUBRID 저장 최소 단위 ) index split 이 발생하게 될 경우 여유공간을 높게 설정 하면 index split의 발생 빈도가 줄어 들어 성능상의 잇점이 있다. ( 저장 공간의 낭비가 될수도 있음)

l  데이터 삭제시(delete) 해당 key의 경우 삭제 표시가 되며 추후 해당 영역은 재사용 된다.

인덱스 선정 기준

인덱스는 검색시 성능 향상을 위하여 고안 되었다. 하지만 검색시 성능 향상을 위하여 DML 작업시 인덱스 구조를 변경하는 작업이 필수적으로 필요 하다. 때문에 물리 설계시 읽기 , 쓰기 빈도를 조사하여 적정량의 인덱스를 구성 해야 한다. (일반적으로 인덱스 구성은 5개 이하로 유지 하는 것이 좋다.)

테이블 형태별 적용 기준

l  데이터가 작은 소형 테이블

연간 데이터 증가율이 거의 없으며 UPDATE 연산이 발생이 많지 않은 테이블이라면 조인에 참조 되는 컬럼에 인덱스를 생성 해주는 것이 좋다.

l  주로 참조 되는 중대형 테이블

일반적으로 메인테이블이라고 불리는데 중대형 테이블의 경우 읽기, 쓰기 빈도를 고려 하여 인덱스를 생성 한다. 주로 조회 빈도(SQL 수행 횟수)나 조건절 컬럼의 Access Pattern 등을 고려 하여 설계 하도록 한다.

l  저장용 대형 테이블

주로 저장용 테이블의 경우 로그성 데이터를 관리하는 목적으로 생성 되며 데이터 입력 주기가 상당히 짧다. 저장용 테이블의 경우 인덱스를 생성 하지 않는 방향으로 고려 해야 할 것이다.

일반적으로 인덱스 컬럼의 분포도가 10~15%가 넘는 경우 손익 분기점을 넘기 때문 이다.( 인덱스 스캔을 하는 경우 RANDOM I/O 가 발생하여 인덱스 스캔을 하는 비용이 더 비싸지기 때문)

데이터의 분포 기준

인덱스 선정시 주의해야 할 부분이 있다. 단일 컬럼 인덱스 생성시 분포도가 낮을 경우나 (예로 0,1 값으로 만 채워져 있는 경우 ) 데이터 분포도는 높으나 데이터가 skew( 컬럼의 특정 데이터에 편향 되어 있는 A:80%, B:10%, C,D,E,F,…:10%) 되어 있을 경우는 인덱스 생성시 제외 하는게 좋다.

결합 인덱스 선정 기준

실제 프로젝트에서 인덱스 선정시 가장 어려운 부분이 결합 인덱스를 생성 하는 것이다. 결합 인덱스의 경우 컬럼의 생성 순서 및 조건절의 상수 조건 이나 조인시 연결 고리 역할을 하기 때문에 상당히 중요하다.

결정 기준

1.     조건절에 항상 사용되는지 조사

2.     조건 비교에 동등비교(=) 사용 빈도 조사

3.     좋은 분포도를 가지는 지 확인

4.     자주 정렬 되는 순서 확인

5.     SQL 실행 빈도 조사

인덱스 사용시 주의 사항

인덱스 컬럼의 외부적 변형

잘못된 유형 SQL	외부적 유형을 제거한 개선 SQL
SELECT  * FROM  EMP                                               WHERE  SUBSTR(DNAME,1,3) = 'ABC' SELECT *  FROM  EMP WHERE  SAL * 12 = 12000000	SELECT  *  FROM  EMP                                               WHERE  DNAME LIKE 'ABC%' SELECT  *  FROM  EMP                                               WHERE  SAL = 12000000 / 12
SELECT  * FROM  EMP                                                WHERE  EMPNO  BETWEEN 100 AND 200        AND  NVL(JOB,'X') = 'CLERK'	SELECT  * FROM  EMP                                               WHERE  EMPNO  BETWEEN 100 AND 200        AND JOB = 'CLERK'
SELECT  *  FROM  EMP WHERE  DEPTNO || JOB  =  '10SALESMAN'	SELECT *  FROM  EMP WHERE  DEPTNO =  '10‘   AND JOB  =  'SALSMAN'

부정형 비교 <>, NOT IN 과 같은 부정형 비교

잘못된 유형 SQL	개선된 SQL
SELECT * FROM EMP WHERE   EMPNO <>   ‘1234’	SELECT * FROM EMP WHERE   EMPNO >  ‘1234’   OR   EMPNO <  ‘1234’

NULL 값 비교 인덱스에 NULL 데이터는 저장 되지 않으므로 비교 불가

잘못된 유형	개선된 SQL	비고
SELECT  * FROM   EMP                                    WHERE  ENAME IS  NOT NULL	SELECT * FROM EMP WHERE ENAME > ‘’	ENAME은 문자형이므로 NOT NULL 비교 보다는 ‘’ 를 이용하여 우회 한다.
SELECT * FROM EMP WHERE  DEPTNO IS NOT NULL	SELECT * FROM EMP WHERE DEPTNO > 0	DEPTNO 컬럼이 데이터 유형이 양수값만 존재 할 때 > 비교 연산자를 사용하여 우회 한다.
	CREATE TABLE EMP ( .. DEPTNO INTEGER DEFAULT 0 )	테이블 생성시 default 값을 지정 하여 NULL 비교를 하지 않도록 설계 한다.

부적절한 LIKE 비교

잘못된 유형 SQL	개선 SQL	비고
SELECT * FROM EMP WHERE ENAME LIKE ‘%SALES%’	SELECT * FROM EMP WHERE ENAME LIKE ‘SALES%’	LIKE 조건의 시작에 % 비교를 하는 경우 인젝스 사용이 불가 하다.

 

참고 자료

l  대용량 데이터베이스 솔루션 1권

l  새로쓴 대용량 데이터베이스 솔루션 2권

l  CUBRID B+Tree 관리자