3. 자료처리/데이터모델링
인덱스
SWExpert
2022. 10. 25. 23:19
I. 인덱스
-. 데이터베이스의 검색 속도 향상을 위해 레코드 키값, 레코드 주소 쌍을 체계적으로 수집하여 관리하는 보조 데이터 도구
-. 특징: 조회성능향상, 독립성, 알고리즘, 성능
| 특징 | 설명 |
| 조회 성능향상 (Query Performance) |
- 데이터베이스 테이블에 접근하는 트랜잭션의 성능 향상이 목적임 - 인덱스의 생성에 대한 시스템 부하 고려 - 별도의 인덱스를 위한 저장 공간 고려 |
| 독립성 (Independence) |
- 테이블에 저장구조와 별도로 인덱스만 저장할 수 있음 - 독립적 인덱스 저장구조 |
| 알고리즘 (Algorithm) |
- Tree 구조, Hash 함수 등 적용, 알고리즘을 적용하여 생성 - 데이터의 저장 형태에 따른 알고리즘 고려 필요 |
| 성능 (trade-off) |
- 조회 vs 입력/수정/삭제성능을 고려하여 인덱스 생성요구 - 조회 성능 향상이 주목적,입력/수정/삭제 성능은 저하됨 |
- 인덱스의 필요성
| 관점 | 필요성 | 설명 |
| 개발/운용자 | 개발생산성 향상 | 응용시스템 개발 속도 증가 |
| 유지보수 비용절감 | 장기적 관점 비용절감 효과 | |
| 사용자 | 응답속도 향상 | 조회 성능의 향상(수행속도) |
| 고객만족도 개선 | 고객만족도 향상 | |
| 시스템 | 가용성 향상 | 사용 요구에 대한 적시 사용 지원 |
| 신뢰성 증진 | 요구기능에 대한 정확한 응답 |
II. 인덱스 기본 구조
가. 인덱스에서 원하는 값 찾는 과정
- 1단계. 브랜치 블록의 가장 왼쪽 값이 찾고자 하는 값보다 작거나 같으면 왼쪽 포인터로 이동
- 2단계. 찾고자 하는 값이 브랜치 블록의 값 사이에 존재하면 가운데 포인터로 이동
- 3단계. 오른쪽에 있는 값보다 크면 오른쪽 포인터로 이동
나. 인덱스의 스캔방식
| 스캔방식 | 특징 | 설명 |
| Index Range Scan | - 수직적 탐색 - 범위(Range) 스캔 - 테이블 액세스 횟수 - 선두컬럼이 조건절에 사용 - Index Range Scan Ascending - Index Range Scan Descending |
- 인덱스를 이용하여 한 건 이상의 데이터를 추출하는 방식 |
| Index Full Scan | - 리프 블록을 처음부터 끝까 지 수평적 탐색 - 최적인덱스없을시선택 - 소트연산대체 |
- 일부에 대해서만 테이블 액세스 발생 시 Table Full Scan 보다 Index Full Scan 방식 적용 |
| Index Unique Scan | - Unique Index - 중복허용 불가 - ‘=’조건으로 탐색 - 검색결과 최대 1건 - 수직적 탐색 |
- Unique Index를 사용하여 단 하나의 데이터를 추출하는 방식 |
| Index Skip Scan | - Distinct Value 개수가 적은 경우 - 후행 컬럼의 Distinct Value개 수가 많을 경우 유용 |
- 루트 또는브랜치 블록에서 읽은 컬럼 값 정보 이용해 부합하는 레코드를 포함할 “가능성 있 는” 하위블록만 골라서 액세스하는 방식 |
| Index Fast Full Scan | - 세그먼트 전체 스캔 - 결과집합 순서 보장 안됨 - Multiblock I/O - 병렬스캔 가능 - 인덱스에 포함된 컬럼만으로만 조회할 때 사용 가능 |
- Index Full Scan 보다 빠름 - 인덱스 트리 구조를 무시하고 세그먼트 전체를 Multiblock Read 방식으로 스캔 |
| Index Range Scan Descending | - 내림차순 정렬 결과집합 - Max값 쉽게 찾음 - Index Range Scan의 일종 |
- Index Range Scan과 기본적으로 동일한 스캔방 식으로 인덱스를 뒤에서부터 앞쪽으로 스캔 |
III. 인덱스 유형 및 구조
가. 인덱스 분류
나. 인덱스 분류별 구조
| 구분 | 인덱스 구조 | 설명 |
| 형태 | 트리 기반 인덱스 | - 인덱스 (RDBMS 는 대부분 B tree) |
| 해쉬 기반 인덱스 | - Hash 테이블 을 이용하여 데이터 검색(=, <=, => 연산자만 사용 가능) | |
| 비트맵 인덱스 | - 컴퓨터에서 사용하는 최소 단위인 비트를 이용하여 컬럼값을 저장하고 이를 이용하여 ROWID를 자동으로 생성하는 인덱스 | |
| 목적 | 함수기반 인덱스 | - 사용자 정의 함수 결과를 인덱스로 사용 - 데이터 타입이 상이한 컬럼 간 사용 |
| 조인 인덱스 | - DW 에서 조인 쿼리의 처리가 가능 | |
| 도메인 인덱스 | - 사용자 정의의 인덱스 타입을 사용 - 텍스트 , 카테고리 인덱스 등 |
|
| 구조 | 정적 인덱스 | - 데이터 파일에 레코드가 삽입되거나 삭제됨에 따라 인덱스의 내용은 변하지만 인덱스 구조 자체는 변경되지 않게 하는 인덱싱 기법 |
| 동적 인덱스 | - 인덱스와 데이터 파일을 블록으로 구성하고 , 각 블록에는 나중에 레코드가 삽입될 것을 감안하여 빈 공간을 미리 준비해두는 인덱싱 기법 | |
| 논리 | 논리적 인덱스 | - 인덱스가 생성될 때 컬럼의 속성과 유형에 따라 분리 |
| 물리적 인덱스 | - 인덱스가 저장되는 데이터 구조에 따라 나누어지는 방법 |
다. 인덱스 유형
| 구분 | 유형 | 설명 |
| 물리적 인덱스 | B* Tree Index | - 빈번한 노드의 분열을 줄이고자 하는 것으로 모든 키와 그에 관련된 레코드들 이 잎 노드에 존재하며,B 트리 인덱스로 검색되는 자료 구조 |
| 비트맵 인덱스 (Bitmap Index) |
- Bit로 맵을 만드는 인덱스(칼럼기반의 DBMS) - Cardinality(한 릴레이션을 구성하는 투플의 수)의 개수로 맵을 만듦 |
|
| Reverse Key Index | - Skew 현상 최소화를 위해 Index Key값의 Byte를 반대로 배열해 B*Tree 구조로 저장 | |
| Descending Index | - 일반적인 인덱스는 기본적으로 오름차순이나 내림차순으로 데이터 정렬 가능 | |
| 분할 인덱스 (Partition Index) |
- 큰 테이블의 인덱스 엔트리를 저장하는데 사용 - 인덱스까지 화일별 다르게 저장 -> 대용량 테이블에서만 효과 - 인덱스 값에 따라 여러 세그먼트에 나누어 보관 |
|
| 논리적 인덱스 | Clustered Index | - 데이터 레코드의 논리적/물리적 순서가 동일한 인덱스. (고속조회가 가능) |
| Non-Clustered Index | - 데이터 레코드의 논리적/물리적 순서가 상관없이 저장되는 인덱스 | |
| Dense Index | - 데이터 레코드 단위로 하나의 인덱스 엔트리가 만들어지는 인덱스 | |
| Sparse Index | - 데이터 파일의 데이터 블록단위로 엔트리가 만들어지는 인덱스 | |
| Unique Index | - 기본키와 고유키 제약조건을 정의하면 인덱스가 묵시적으로 생성(중복불가) | |
| Non-Unique Index | - 인덱스 키는 연관된 테이블의 여러 행을 가리킬 수 있음(중복허용) | |
| Single Index | - 하나의 컬럼으로 구성된 인덱스 | |
| Composite Index | - 여러 개의 컬럼 조합으로 생성된 인덱스 - 조합 인덱스는 최대 32개의 컬럼까지 구성가능 |
|
| Function-based Index | - 함수나 표현식의 계산 값으로 인덱스 생성 - 함수의 리턴 값은 DETERMINISTICS로 선언되어야 함 - Cost-based에서만 사용 가능함 |
IV. 인덱스 선정절차 및 사용 장단점
가. 인덱스 선정절차
| 절차 | 설명 |
| 1. 테이블의 액세스 형태를 최대한 수집 |
|
| 2. 인덱스 대상 컬럼의 선정 및 분포도 조사 |
|
| 3. 특수한 액세스 형태에 대한 인덱스 선정 |
|
| 4. 클러스터링 검토 |
|
| 5. 결합 인덱스 구성 및 순서의 결정 |
|
| 6. 시험 생성 및 테스트 |
|
| 7. 수정이 필요한 어플리케이션 조사 및 선정 |
|
| 8. 일괄 적용 |
|
나. 인덱스 사용 장단점
| 구분 | 설명 |
| 장점 | 검색 성능 최적화 : FTS(Full Table Scan)에 비해 물리적 디스크 I/O 적음, 별도의 정렬 없이 인덱스 순서대로 결과 추출 가능 |
| 단점 |
|