인덱스
데이터베이스에서 검색 속도를 빠르게 하기 위해 사용하는 기능.
데이터의 인덱스를 참조하면 데이터가 저장된 레코드의 주소를 알 수 있다.
DBMS는 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오기엔 시간이 너무 오래 걸리기 때문에 컬럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 인덱스를 만들어둔다.
DBMS의 인덱스는 항상 정렬된 상태를 유지하기 때문에 원하는 값을 탐색하는 데는 빠르나 새로운 값 추가하거나 삭제, 수정하는 경우 실행 속도 느려진다.
=> 데이터의 저장 성능 희생,,, 데이터의 읽기 속도 높인다.
키 값 : 인덱스가 걸린 컬럼의 값.
포인터 : 해당 컬럼이 저장된 레코드의 물리적인 주소.
키 값이 정렬되어 있기 때문에 인덱스를 통해 레코드를 빠르게 접근할 수 있다.
인덱스 특징
- 인덱스는 데이터가 저장된 물리적 구조와 깊은 관계가 있다.
- 인덱스는 레코드가 저장된 물리적 구조에 접근하는 방법을 제공한다.
- 인덱스를 통해 파일의 레코드에 대한 액세스를 빠르게 수행할 수 있다.
- 레코드의 삽입과 삭제가 수시로 일어나는 경우, 인덱스의 개수를 최소화하는 것이 효율적이다.
- 인덱스가 없으면 특정 값을 찾기 위해 모든 데이터 페이지를 확인하는 TABLE SCAN(테이블에 있는 모든 레코드를 순차적으로 읽는 것)이 발생한다.
- 기본키를 위한 인덱스를 기본 인덱스라 하고, 기본 인덱스가 아닌 인덱스들을 보조 인덱스라 한다. 대부분의 RDBMS에서는 모든 기본키에 대해 자동적으로 기본 인덱스를 생성한다.
- 인덱스는 인덱스를 구성하는 구조나 특징에 따라 트리 기반 인덱스, 비트맵 인덱스, 함수 기반 인덱스, 비트맵 조인 인덱스, 도메인 인덱스 등으로 분류된다.
장점
검색 속도가 빨라진다.(데이터가 백만건 정도 될 때)
시스템의 부하를 줄여 성능을 향상시킨다.
단점
DB에 추가적인 저장공간이 필요한다.
인덱스 생성 시간이 오래걸린다.
변경 작업이 자주 일어나면 오히려 성능 저하를 가져올 수 있다.
인덱스 성능과 고려해야할 사항
인덱스를 잘못 사용하면 오히려 성능이 저하될 수 있다.
인덱스를 생성하게 되면 insert, delete, update 쿼리문을 실행할 때 별도의 과정이 추가된다.
insert의 경우 인덱스에 대한 데이터 추가,
delete의 경우 인덱스에 존재하는 값은 삭제하지 않고 사용하지 않는다는 표시만 남는다.(로우의 수가 그대로 남는다.)
=> 실제 데이터가 10만인데 테이블에는 100만개의 데이터가 있을 수 있다.
update는 더 문제다..
인덱스에 대한 데이터를 추가하는 것 뿐만 아니라 변경 전 데이터도 사용하지 않음 처리해야하므로 insert, delete의 문제가 모두 발생.
인덱스는 데이터의 형식에 따라서도 성능이 달라질 수 있다.
데이터의 형식에 따라 인덱스를 만들었을 때 효율적인 데이터가 존재한다.
이름, 나이, 성별 세가지 필드를 가지고 있는 테이블이 있다면 이름에 대해 인덱스를 생성하는 것이 효율적이다.
인덱스를 사용하면 좋을 상황들
- 규모가 작지 않은 테이블
- INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼
- JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 컬럼
- 데이터의 중복도가 낮은 컬럼
인덱스 자료구조
클러스터드 인덱스
인덱스 키의 순서에 따라 데이터가 정렬되어 저장됨.
실제 데이터가 순서대로 저장되어 있어 인덱스를 검색하지 않아도 원하는 데이터를 빠르게 찾을 수 있다.
데이터 삽입, 삭제 발생 시 순서를 유지하기 위해 데이터를 재정렬해야함.
한 개의 릴레이션에 하나의 인덱스만 생성할 수 있음.
넌클러스터드 인덱스
인덱스의 키 값만 정렬되어 있을 뿐 실제 데이터는 정렬되지 않음.
데이터를 검색하기 위해서 먼저 인덱스를 검색하여 실제 데이터의 위치를 확인해야하므로 클러스터드 인덱스에 비해 검색 속도가 떨어진다.
한 개의 릴레이션에 여러 개의 인덱스 만들 수 있다.
트리 기반 인덱스
인덱스를 저장하는 블록들이 트리 구조를 이루고 있음.
상용 DBMS에서는 트리 구조 기반의 B+트리 인덱스를 주로 활용
B-트리 인덱스
- 일반적으로 사용되는 인덱스 방식. 루트 노드에서 하위 노드로 키 값의 크기 비교해 나가며 단말 노드에서 찾고자 하는 데이터를 검색.
- 모든 노드에 데이터를 저장.
- 키 값과 레코드를 가리키는 포인터들이 트리 노드에 오름차순으로 저장됨.
- 모든 리프 노드는 같은 레벨에 있음
B+트리 인덱스
- 리프노드(데이터 노드)만 인덱스와 함께 데이터를 가지고 있고 나머지 노드들은 데이터를 위한 인덱스 만을 갖는다.
- B-트리의 변형. 단말 노드가 아닌 노드로 구성된 인덱스 세트와 단말 노드로만 구성된 순차 세트로 구분된다.
- 인덱스 세트에 있는 노드들은 단말 노드에 있는 키 값을 찾아갈 수 있는 경로로만 제공되며 순차 세트에 있는 단말 노드가 해당 데이터 레코드의 주소를 가리킨다.
- 인덱스 세트에 있는 모든 키 값이 단말 노드에 다시 나타나므로 단말 노드만을 이용한 순차 처리가 가능하다.
해시테이블 인덱스
컬럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘.
매우 빠른 검색 지원한다.
그러나 값을 변형해서 인덱싱하기 때문에 특정 문자로 시작하는 값으로 검색을 하는 등 값의 일부만으로 검색할때는 해시 인덱스를 사용할 수 없다.
해시 함수는 등호(=)연산에만 특화되어있으므로 부등호 연산(<,>)이 자주 사용되는 데이터베이스 검색을 위해서는 적합하지 않다.
비트맵 인덱스
인덱스 컬럼의 데이터를 비트값인 0 또는 1로 변환하여 인덱스 키로 사용하는 방법.
- 비트맵 인덱스의 목적은 키 값을 포함하는 로우의 주소를 제공하는 것
- 비트맵 인덱스는 분포도가 좋은 컬럼에 적합. 성능 향상 효과를 얻을 수 있다.
- 데이터가 비트로 구성되어 있기 때문에 효율적인 논리 연산 가능. 저장 공간이 작다.
- 비트맵 인덱스는 다중 조건을 만족하는 튜플의 개수 계산에 적합
- 비트맵 인덱스는 동일한 값이 반복되는 경우가 많아 압축 효율이 좋다.
함수 기반 인덱스
컬럼의 값 대신 컬럼에 특정 함수나 수식을 적용하여 산출된 값을 사용.
B+트리 인덱스 또는 비트맵 인덱스를 생성하여 사용.
- 데이터를 입력하거나 수정할 때 함수를 적용해야 해서 부하가 발생할 수 있다.
- 사용된 함수가 사용자 정의 함수일 경우 시스템 함수보다 부하가 더 크다.
- 대소문자, 띄어쓰기 상관없이 조회할 때 유용
비트맵 조인 인덱스
다수의 조인된 객체로 구성된 인덱스. 비트맵 인덱스와 물리적 구조가 동일
도메인 인덱스
개발자가 필요한 인덱스를 직접 만들어 사용.
인덱스 설계
인덱스의 대상 테이블이나 컬럼 등 산정 -> 인덱스 효율성 검토하여 인덱스 최적화 수행 -> 인덱스 정의서 작성
인덱스 정보 조회하기
select index_name, table_name, column_name
from user_ind_columns
where table_name='[테이블이름]'오라클에서는 pk가 자동으로 인덱스로 세팅이 된다.
인덱스 만들기
create index 인덱스이름
on 테이블이름(컬럼명)인덱스 대상 테이블 선정 기준
- MULTI BLOCK READ(테이블 액세스 시 메모리에 한번에 읽어 들일 수 있는 블록의 수)수에 따라 판단. ex)MULTI BLOCK READ가 16이면 테이블의 크기가 16블록 이상일 경우 인덱스가 필요하다.
- 랜덤 액세스가 빈번한 테이블
- 특정 범위나 특정 순서로 데이터 조회가 필요한 테이블
- 다른 테이블과 순차적으로 조인이 발생되는 테이블
인덱스 대상 컬럼 선정 기준
- 인덱스 컬럼의 분포도가 10~15% 이내인 컬럼 또는 이상이어도 부분 처리를 목적으로 하는 컬럼
- 분포도 = (컬럼값의 평균 로우 수 / 테이블의 총 로우 수)*100
- 가능한 한 수정이 빈번하지 않은 컬럼
- ORDER BY, GROUP BY, UNION이 빈번한 컬럼
고려사항
- 새로 추가되는 인덱스는 기존 인덱스 경로에 영향을 미칠 수 있다.
- 인덱스를 지나치게 많이 만들면 오버헤드가 발생
- 넓은 범위를 인덱스로 처리하면 많은 오버헤드 발생
- 인덱스를 만들면 추가적인 저장 공간 필요
- 인덱스와 테이블 데이터의 저장 공간이 분리되도록 설계
