Index(인덱스)?
인덱스
정의: 테이블의 검색 속도를 향상시키기 위한 자료구조
특징
테이블의 데이터 값에 빠르게 액세스하도록 하는 데이터베이스 객체
데이터를 빠르게 찾을 수 있기 때문에 디스크 액세스 횟수를 줄일 수 있음
언제든지 생성/삭제 가능하며, 테이블이나 다른 인덱스에 영향을 주지 않음
데이터를 조회하는 원리
사용자가 DB 시스템에 SELECT 질의를 날린다.
DB 시스템은 DB 메모리 안의 버퍼 캐시를 확인한다.
만약 버퍼 캐시안에 찾는 데이터가 있다면 바로 데이터 조회를 수행한다.
버퍼 캐시 안에 데이터가 없다면,
디스크에서 데이터를 찾아서
버퍼 캐시로 복사한다.
데이터 결과값을 사용자에게 반환한다.
인덱스가 없다면 전체를 탐색하는 Full Scan을 수행해야하므로 검색 속도가 떨어진다. 인덱스는 위 과정 중 디스크에서 데이터를 찾을 때, 검색 속도를 높이기 위해 쓰인다.
인덱스의 관리
DBMS는 index를 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다. 때문에 DML시 다음과 같은 연산을 추가적으로 해주어야 한다.
INSERT: 새로운 데이터에 대한 인덱스를 추가해야함.
DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행해야함.
UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가해야함.
때문에 인덱스를 사용하지 않을 때와 비교하여 추가작업으로 인한 오버헤드가 발생한다. 그리고 DELETE의 경우 index에 존재하는 값은 삭제하지 않고 사용 안한다는 표시로 남게 되므로, row의 수는 줄어들진 않고 늘어만 난다. 이런 작업이 반복되면 실제 데이터가 10만 건인데 데이터는 100만건 쌓이는 결과를 낳을 수도 있고, 이렇게 되면 index가 제 역할을 하지 못할 수 있다.
인덱스의 종류
인덱스를 구현하는데는 여러 가지 자료구조를 사용할 수 있다.
Hash 인덱스
빠른 데이터 검색이 필요할 때 유용하다. Key 값이 유효하면 시간복잡도 O(1)로 데이터를 조회할 수 있다.
하지만 값이 다르면 해시 키 값이 완전히 달라지므로, 값의 일부만 가지고 검색할 때는 사용이 불가능하다.
주로 메모리 기반의 데이터베이스에서 많이 사용한다.
B-Tree 인덱스
B-Tree는 Balanced Tree의 약자로 균형을 유지하는 트리를 말한다. 기존에 자식을 두개만 가질 수 있던 Binary Tree를 확장하여 더 많은 자식을 가질 수 있게 고안한 것이다.
인덱스는 데이터의 종류가 많고 동일한 데이터가 적은 경우에 주로 사용한다.
종류 | 설명 | 사용 예 |
unique index | 중복 데이터가 없는 경우(unique)에 사용한다. | 기본 키, 유일 키 데이터 |
non-unique index | 중복 데이터가 있는 경우에 빠른 검색 결과를 보장한다. | 인덱스가 필요한 일반적인 데이터 |
descending index | 내림차순 데이터 값으로 인덱스를 생성한다. | 매출, 최근 일자 등 |
composite index | 여러 열을 합쳐서 하나의 인덱스를 생성한다. | 여러 조건이 필요한 경우 ex) 고객 번호 and 성별 |
B+-Tree 인덱스
B-Tree와 다른 특성을 가지고 있다.
리프노드(데이터 노드)만 인덱스와 함께 데이터(Value)를 가지고 있다.
나머지 노드(인덱스 노드)들은 데이터를 위한 인덱스(Key)만을 갖는다.
리프노드들은 LinkedList로 연결되어 있다.
리프 노드(데이터 노드)의 크기는 인덱스 노드의 크기와 같지 않아도 된다.
데이터베이스의 인덱스 컬럼은 부등호를 이용하여 순차 검색 연산이 자주 발생될 수 있다. B+Tree는 BTree의 리프 노드들을 LinkedList로 연결하여 순차검색을 최적화한 사례이다. 물론 BTree와 달리 무조건 리프노드까지 가야 데이터 조회가 가능하다는 단점도 있다.
비트맵 인덱스
정의
컴퓨터에서 사용하는 최소 단위인 비트를 이용하여 컬럼값을 저장하고, row_id를 자동으로 생성하는 인덱스 방법
구조
비트맵 인덱스도 B-tree와 같이 구성되지만, 최하위(leaf) 노드는 row_id 리스트 대신 각 키 값에 대한 비트맵을 저장한다. 비트맵 내의 비트는 가능한 row_id와 일치하며 비트가 설정되어 있으면 해당 row_id가 있는 행이 키 값을 포함하고 있음을 의미한다.
장점
비트를 직접 관리하므로 저장공간이 크게 감소한다.
비트를 직접 관리하므로 비트연산을 수행할 수 있다.
적절한 상황
테이블에 수백만 개의 행이 있고, 키 열에 low cardinality가 있는 경우. 즉 해당 열의 구분 값이 매우 적은 경우. 참고로 중복도가 '높으면' 카디널리티가 '낮다'고 표현한다.
ex) 여권 레코드를 포함하는 테이블의 성별 및 결혼 여부 열에는 B-Tree 인덱스보다 비트맵 인덱스가 더 적절할 수 있다.
query가 OR 연산자를 포함하는 여러 WHERE 조건을 조합하여 사용할 경우
읽기 전용 또는 키 열에 대한 갱신 작업이 저조할 경우
인덱스의 장점 / 단점 / 고려해야할 점
인덱스의 장점
테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있다.
전반적인 시스템의 부하를 줄일 수 있다.
인덱스의 단점
인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요하다.
인덱스를 관리하기 위해 추가 작업이 필요하다.
인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있다.
아래 사항을 고려해서 적절히 적용해야한다.
분석 시스템(OLAP)과 운영 시스템(OLTP)에 따라 인덱스 유형이 달라진다.
인덱스가 지나치게 많으면 과부하가 발생한다.
조인할 때 옵티마이저가 인덱스를 사용하도록 유도해야 한다.
데이터베이스 시스템 운영 상황에 따라 별도의 저장 공간으로 지정이 필요하거나 재생성이 필요할 수 있다.
DML 문을 자주 사용하는 경우에는 데이터베이스 시스템 성능에 악영향을 끼칠 수 있다.
인덱스를 쓰기 적절한 상황
열이 WHERE 절의 조인 조건으로 자주 사용된다.
열이 다양한 값을 포함한다. 또한 많은 수의 null 값을 포함한다.
테이블 크기가 대형이고 대부분의 질의가 행의 2~4% 이하보다 적게 읽어 들일 것으로 예상된다.
인덱스를 쓰기 부적절한 상황
열이 WHERE 절의 조인 조건으로 자주 사용되지 않는다.
테이블 크기가 소형이고 열의 데이터 분포가 고르지 않다.
질의의 대부분이 행의 2~4% 이상을 읽어 들일 것으로 예상된다.
테이블이 자주 갱신된다. DML 문을 자주 사용하면 인덱스의 유지 작업을 위해 상대적으로 더 많은 시간이 걸린다.
참고
Last updated