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' |
잘못된 유형 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 비교를 하지 않도록 설계 한다. |
잘못된 유형 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 관리자