Background Image
기타
2009.12.31 19:36

CUBRID인덱스 구성 전략

조회 수 22193 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄

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 관리자


  1. CUBRID DB와 Broker 분리방안

  2. 데이터 저장 구조와 테이블 재구성

  3. CUBRID 복제 구성하기 - 단일 서버에 구성 방법 포함

  4. 윈도우즈 환경에서 배치화일을 이용하여 백업 또는 데이터베이스 정리 수행하기

  5. CUBRID 복제 설계 가이드

  6. CUBRID 사용 포트와 iptables(방화벽) 설정

  7. 자동증가 속성 사용 방법 및 주의 사항

  8. CUBRID 관련 유용한 기술 자료...

  9. Server HA 구성 시 CUBRID 설정과 방법

  10. Vista_IIS_CUBRID_ASP연동

  11. CUBRID2008 R2.1 업그레이드시 serial 관련 문제 해결 방법

  12. CUBRID 데이터베이스와 연동하여 Qt어플리케이션 개발하기 - 2

  13. CUBRID 데이터베이스와 연동하여 Qt어플리케이션 개발하기 - 1

  14. CUBRID인덱스 구성 전략

  15. 조인 방법(Join Method) 설명

  16. 스칼라 서브쿼리 사용 가이드

  17. CUBRID 에 텍스트큐브 설치하기

  18. 예제로 배우는 CUBRID 스터디

  19. 독자적 테이블 사용되는 복제 재구성

  20. SELECT ~ FOR UPDATE 대체 방법

Board Pagination Prev 1 2 3 4 5 6 7 8 9 Next
/ 9

Contact Cubrid

대표전화 070-4077-2110 / 기술문의 070-4077-2113 / 영업문의 070-4077-2112 / Email. contact_at_cubrid.com
Contact Sales