인덱스(Index)의 개요
- 인덱스는 데이터 레코드를 빠르게 접근하기 위해 <키 값, 포인터> 쌍으로 구성되는 데이터 구조이다.
- 학생 릴레이션의 학번 속성에 대한 인덱스
- 위의 인덱스에서 키 값은 '학번'이고 포인터는 해당 '학번'이 저장된 레코드의 물리적인 주소이다. 키 값인 '학번'이 정렬되어 있기 때문에 인덱스를 통해 레코드를 빠르게 접근할 수 있다.
- 인덱스는 데이터가 저장된 물리적 구조와 밀접한 관계가 있다.
- 인덱스는 레코드가 저장된 물리적 구조에 접근하는 방법을 제공한다.
- 인덱스를 통해서 파일의 레코드에 대한 액세스를 빠르게 수행할 수 있다.
- 레코드의 삽입과 삭제가 수시로 일어나는 경우에는 인덱스의 개수를 최소로 하는 것이 효율적이다.
- 데이터 정의어(DDL)를 이용하여 사용자가 생성, 변경, 제거할 수 있다.
- 인덱스가 없으면 특정한 값을 찾기 위해 모든 데이터 페이지를 확인하는 TABLE SCAN이 발생한다.
- 기본키를 위한 인덱스를 기본 인덱스라 하고, 기본 인덱스가 아닌 인덱스들을 보조 인덱스라고 한다. 대부분의 관계형 데이터베이스 관리 시스템에서는 모든 기본 키에 대해서 자동적으로 기본 인덱스를 생성한다.
- 레코드의 물리적 순서가 인덱스의 엔트리 순서와 일치하게 유지되도록 구성되는 인덱스를 클러스터드(Clustered) 인덱스라고 한다.
- 인덱스는 인덱스를 구성하는 구조나 특징에 따라 트리 기반 인덱스, 비트맵 인덱스, 함수 기반 인덱스, 비트맵 조인 인덱스, 도메인 인덱스 등으로 분류된다.
클러스터드 인덱스 / 넌클러스터드 인덱스
- 클러스터드 인덱스(Clustered Index)
- 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식입니다.
- 실제 데이터가 순서대로 저장되어 있어 인덱스를 검색하지 않아도 원하는 데이터를 빠르게 찾을 수 있습니다
- 데이터 삽입, 삭제 발생 시 순서를 유지하기 위해 데이터를 재정렬해야 합니다.
- 한 개의 릴레이션에 하나의 인덱스만 생성할 수 있습니다.
- 넌클러스터드 인덱스 (Non-Clustered Index)
- 인덱스의 키 값만 정렬되어 있을 뿐 실제 데이터는 정렬되지 않는 방식입니다.
- 데이터를 검색하기 위해서는 먼저 인덱스를 검색하여 실제 데이터의 위치를 확인해야 하므로 클러스터드 인덱스에 비해 검색 속도가 떨어집니다.
- 한 개의 릴레이션에 여러 개의 인덱스를 만들 수 있습니다.
트리 기반 인덱스
- 트리 기반 인덱스는 인덱스를 저장하는 블록들이 트리 구조를 이루고 있는 것으로, 상용 DBMS에서는 트리구조 기반의 B+ 트리 인덱스를 주로 활용한다.
- B 트리 인덱스
- 일반적으로 사용되는 인덱스 방식으로, 루트 노드에서 하위 노드로 키 값의 크기를 비교해 나가면서 단말 노드에서 찾고자 하는 데이터를 검색한다.
- 키 값과 레코드를 가리키는 포인터들이 트리 노드에 오름차순으로 저장된다.
- 모든 리프 노드는 같은 레벨에 있다.
- 브랜치 블록(Branch Block)과 리프 블록(Leaf Block)으로 구성된다.
- 브랜치 블록: 분기를 위한 목적으로 사용되고, 다음 단계를 가리키는 포인터를 가지고 있음
- 리프 블록: 인덱스를 구성하는 컬럼 데이터와 해당 데이터의 행 위치를 가리키는 레코드 식별자로 구성됨
- B+ 트리 인덱스
- B+ 트리는 B 트리의 변형으로 단말 노드가 아닌 노드로 구성된 인덱스 세트(Index Set)와 단말 노드로만 구성된 순차 세트(Sequence Set)로 구분된다.
- 인덱스 세트에 있는 노드들은 단말 노드에 있는 키 값을 찾아갈 수 있는 경로로만 제공되며, 순차 세트에 있는 단말 노드가 해당 데이터 레코드의 주소를 가리킨다.
- 인덱스 세트에 있는 모든 키 값이 단말 노드에 다시 나타나므로 단말 노드만을 이용한 순차처리가 가능하다.
비트맵 인덱스
- 비트맵 인덱스는 인덱스 컬럼의 데이터를 Bit 값인 0 또는 1로 변환하여 인덱스 키로 사용하는 방법이다.
- 비트맵 인덱스의 목적은 키 값을 포함하는 로우(Row)의 주소를 제공하는 것 이다.
- 비트맵 인덱스는 분포도가 좋은 컬럼에 적합하며, 성능 향상 효과를 얻을 수 있다.
- 데이터가 Bit로 구성되어 있기 때문에 효율적인 논리 연산이 가능하고 저장 공간이 작다.
- 비트맵 인덱스는 다중조건을 만족하는 튜플의 개수 계산에 적합하다.
- 비트맵 인덱스는 동일한 값이 반복되는 경우가 많아 압축 효율이 좋다.
함수 기반 인덱스
- 함수 기반 인덱스는 컬럼의 값 대신 컬럼에 특정 함수(Function)나 수식(Expression)을 적용하여 산출된 값을 사용하는 것으로, B+ 트리 인덱스 또는 비트맵 인덱스를 생성하여 사용한다.
- 함수 기반 인덱스는 데이터를 입력하거나 수정할 때 함수를 적용해야 하므로 부하가 발생할 수 있다.
- 사용된 함수가 사용자 정의 함수일 경우 시스템 함수보다 부하가 더 크다.
- 함수 기반 인덱스는 대소문자, 띄어쓰기 등에 상관없이 조회할 때 유용하게 사용된다.
- 적용 가능한 함수의 종류 : 산술식(Arithmetic Expression), 사용자 정의 함수, PL/SQL Function, SQL Function, Package, C callout 등
비트맵 조인 인덱스
- 비트맵 조인 인덱스는 다수의 조인된 객체로 구성된 인덱스로, 단일 객체로 구성된 일반적인 인덱스와 액세스 방법이 다르다.
- 비트맵 조인 인덱스는 비트맵 인덱스와 물리적 구조가 동일하다.
도메인 인덱스
- 도메인 인덱스는 개발자가 필요한 인덱스를 직접 만들어 사용하는 것으로, 확장형 인덱스(Extensible Index)라고도 한다.
- 개발자가 필요에 의해 만들었지만 프로그램에서 제공하는 인덱스처럼 사용할 수도 있다.
인덱스 설계
- 인덱스를 설계할 때는 분명하게 드러난 컬럼에 대해 기본적인 인덱스를 먼저 지정한 후 개발 단계에서 필요한 인덱스의 설계를 반복적으로 진행한다.
- 인덱스 설계 순서
- 인덱스의 대상 테이블이나 컬럼 등을 선정한다.
- 인덱스의 효율성을 검토하여 인덱스 최적화를 수행한다.
- 인덱스 정의서를 작성한다.
- 인덱스 정의 예시
엔티티명 테이블명 인덱스명 컬럼명 타입 테이블 스페이스 인덱스 유형 정렬 구분 사원 EMP PK_EMP EMPNO NUMBER(5) ISTEXTO1 UNIQUE ASC PK INDEX 사원 EMP 1_EMP2 DEPTNO NUMBER(5) ISTEXTO1 NOT UNIQUE ASC UD INDEX 고객 CUST PK_CUST CUSTNO NUMBER(5) ISTEXT01 UNIQUE ASC PK INDEX 부서 DEPT PK_DEPT DEPTNO NUMBER(3) ISTEXT01 UNIQUE ASC PK INDEX 주문 ORDER PK_ORDER ORDERNO NUMBER(6) ISTEXT01 UNIQUE ASC PK INDEX
인덱스 대상 테이블 선정 기준
- MULTI BLOCK READ 수에 따라 판단(MULTI BLOCK READ가 16이면 테이블의 크기가 16블록 이상일 경우 인덱스 필요)
- 랜덤 액세스가 빈번한 테이블
- 특정 범위나 특정 순서로 데이터 조회가 필요한 테이블
- 다른 테이블과 순차적 조인이 발생되는 테이블
인덱스 대상 컬럼 선정 기준
- 인덱스 컬럼의 분포도가 10~15% 이내인 컬럼(분포도 = (컬럼값의 평균 Row 수 / 테이블의 총 Row 수) × 100)
- 분포도가 10~15% 이상이어도 부분 처리를 목적으로 하는 컬럼
- 입·출력 장표 등에서 조회 및 출력 조건으로 사용되는 컬럼
- 인덱스가 자동 생성되는 기본키와 Unique키 제약 조건을 사용한 컬럼
- 가능한 한 수정이 빈번하지 않은 컬럼
- ORDER BY, GROUP BY, UNION이 빈번한 컬럼
- 분포도가 좁은 컬럼은 단독 인덱스로 생성
- 인덱스들이 자주 조합되어 사용되는 경우 하나의 결합 인덱스(Concatenate Index)로 생성
인덱스 설계 시 고려사항
- 새로 추가되는 인덱스는 기존 액세스 경로에 영향을 미칠 수 있다.
- 인덱스를 지나치게 많이 만들면 오버헤드가 발생한다.
- 넓은 범위를 인덱스로 처리하면 많은 오버헤드가 발생한다.
- 인덱스를 만들면 추가적인 저장 공간이 필요하다.
- 인덱스와 테이블 데이터의 저장 공간이 분리되도록 설계한다.
alpaka의 자격증 공부장