[DAP공부]데이터베이스 설계_3 - 인덱스 설계

인덱스 설계

인덱스 기능

• 인덱스 : 어떤 종류의 검색 연산을 최적화 하기 위해 데이터베이스 상에 레코드들의 정보를 구성하는 데이터 구조

 

인덱스 구조

1. 트리 기반 인덱스

• 대부분의 DBMS는 tree구조를 기반으로 하는 B+tree 인덱스를 주로 사용
• B+tree는 루트에서 리프 노드까지 모든 경로의 깊이가 같은 밸런스 트리 형태로, 다른 인덱스에 비해 대용량 처리의 데이터 입력 삭제 시 좋은 성능 유지
• B+tree에서 노드를 가득 채우는 방법은 데이터 삽입 시 발생하는 분할 횟수를 줄여 삽입 시의 성능은 향상되지만 삭제 시에는 병합이 발생할 가능성이 높아지는데, 삭제가 빈번하게 발생하면 치명적인 문제 발생.
• 성능 저하를 막기 위해 삭제 시 병합 자체가 발생하지 않도록 하는 방법 사용.
• 삭제가 빈번하게 발생하는 테이블의 경우 인덱스 사이즈가 지속적으로 증가되기 때문에 인덱스를 주기적으로 재생성해야 함.

2. 해시 기반 인덱스

• 키 값에 해시 함수를 적용하여 해당 키 값에 대응하는 버킷(Bucket)을 식별하고 탐색.
• 버킷은 처음에 하나의 기본 페이지로 구성되지만 해당 버킷에 새로운 데이터 엔트리를 위한 저장공간이 없을 때 새로운 오버플로우 페이지를 하나 할당하고, 그 페이지에 데이터를 삽입하여 해당 버킷과 연결.
• 오버플로우 체인이 길어지면 버킷을 탐색하는 성능이 저하되므로 초기 생성 시 80%정도 채우고, 주기적으로 다시 해싱해 오버플로우 페이지를 제거

3. 비트맵 인덱스

• 첫 번째와 마지막 비트의 논리적인 주소를 가지고 있으면서, 특정 행에 대한 액세스가 필요할 때 해당 비트가 첫 번째 비트로부터 떨어져 있는 상대적인 거리를 이용해 해당 비트의 위치 계산 가능
• DBMS는 내부적으로 비트의 위치를 가지고 행의 물리적인 위치 계산하는 함수 지원

구분	B-Tree	Bitmap
구조 특징	Root block, Branch block, Leaf block으로 구성되며, 인덱스 깊이를 동일하게 유지하는 트리 구조	키가 가질 수 있는 각 값에 대해 하나의 비트맵을 구성
사용 환경	OLTP	DW(Data Warehouse) DM(Data Mart) 등
검색 속도	처리 범위가 좁은 데이터 검색 시 유리	다중 조건을 만족하는 데이터 검색 시에 유리함(특히 비정형 쿼리)
분포도	데이터 분포도가 좋은 칼럼에 적합	데이터 분포도가 나쁜 칼럼에 적합
장점	입력, 수정, 삭제가 용이	비트 연산으로 OR연산, NULL값 비교 등이 가능
단점	처리 범위가 넓을 때 수행 속도 저하	전체 인덱스 조정의 부하로 입력, 수정, 삭제가 어려움

4. 함수 기반 인덱스

• 함수나 수식으로 계산된 결과에 대해 B+tree인덱스나 비트맵 인덱스를 생성하여 사용할 수 있는 기능 제공
• 함수 기반 인덱스에서 사용되는 함수는 산술식, PL/SQL Function, SQL Function, Package, C Callout이 가능
• 동일한 입력 값에 대해 시간 흐름에 따라 결과 값이 변경되는 함수에는 적용 할 수 없음
• LOB, REF type, Nested Table Colmn 또는 이를 포함하고 있는 Object type에 대해서는 함수 기반 인덱스 생성 불가.

5. 파티션 인덱스

• 각 인덱스 파티션이 담당하는 테이블의 파티션 범위에 따라 글로벌 파티션 인덱스와 로컬 파티션 인덱스로 구분.
• 비 파티션 인덱스
  • 파티션 테이블에 생성된 비 파티션 인덱스는 글로벌 인덱스라 부르며 파티셔닝되지 않은 인덱스가 파티셔닝된 전체 테이블과 대응되는 구조
  • 처리되어야 하는 SQL이 반드시 여러 파티션을 액세스해야 하는 경우 파티션의 인덱스를 각각 액세스하는 비효율이 너무 커서 요구하는 성능을 만족할 수 없을 때 고려
• 로컬 파티션 인덱스
  • 테이블이 파티셔닝된 기준 그대로 파티셔닝된 파티션 인덱스
  • 인덱스 파티션과 테이블 파티션이 1:1로 대응되는 구조
  • 테이블 파티션의 삭제, 추가 등의 작업 시에도 DBMS가 자동으로 관리
• 글로벌 파티션 인덱스
  • 테이블의 파티션과 독립적으로 인덱스 파티션을 구성하는 것
  • 파티셔닝되지 않은 테이블에도 적용 가능

728x90

인덱스 설계 절차

1. 접근 경로 수집

• 테이블에서 데이터 검색하는 방법으로 테이블스캔과 인덱스 스캔 등이 있음
• 접근 유형
  • 반복 수행되는 접근 경로(조인 칼럼)
  • 분포도가 양호한 칼럼(분포도 = 데이터별 평균 row 수 / 테이블의 총 row 수 * 100)
  • 조회 조건에 사용되는 칼럼
  • 자주 결함되어 사용되는 칼럼
  • 데이터 정렬 순서와 그룹핑 칼럼
  • 일련번호를 부여한 칼럼
  • 통계 자료 추출 조건
  • 조회 조건이나 조인 조건 연산자

2. 분포도 조사에 의한 후보 칼럼 선정

• 분포도가 10~15%정도면 인덱스 칼럼 후보 사용(반환점을 인덱스 손익 분기점)
• 분포도는 1차적으로 단일 칼ㄹ머을 대상으로 조사
• 칼럼들에 대한 분포 조사 실시
• 분포도 조사 결과를 만족하는 칼럼은 인덱스 후보로 선정하고 목록 작성
• 기형적으로 분포도가 불규칙한 경우 별도 표시하여 접근 형태에 따라 대책 마련
• 빈번하게 값이 변경되는 칼럼은 가능한 인덱스 후보에서 제외

3. 접근 경로 결정

 

4. 칼럼 조합 및 순서 결정

• 항상 사용되는 칼럼을 선두 칼럼으로 함
• 등치 조건으로 사용되는 칼럼을 선행 칼럼으로 함
• 분포도가 좋은 칼럼을 선행 칼럼으로 함
• order by, group by 순서를 적용

5. 적용 시험