Index(인덱스)
목적
추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조테이블의 칼럼을 색인화한다.
마치, 두꺼운 책의 목차와 같다고 생각하면 편하다.
데이터베이스 안의 레코드를 처음부터 풀스캔하지 않고, B+ Tree로 구성된 구조에서 Index 파일 검색으로 속도를 향상시키는 기술이다.
파일 구성
테이블 생성 시, 3가지 파일이 생성된다.
- FRM : 테이블 구조 저장 파일
- MYD : 실제 데이터 파일
- MYI : Index 정보 파일 (Index 사용 시 생성)
사용자가 쿼리를 통해 Index를 사용하는 칼럼을 검색하게 되면, 이때 MYI 파일의 내용을 활용한다.
장점
인덱스의 장점으로는 앞에서 말했듯이 테이블을 검색하는 속도와 성능이 향상된다. 또 그에 따라 시스템의 전반적인 부하를 줄일 수 있다.
핵심은 인덱스에 의해 데이터들이 정렬된 형태를 갖는다는 것이다. 기존엔 Where문으로 특정 조건의 데이터를 찾기 위해서 테이블의 전체를 조건과 비교해야 하는 '풀 테이블 스캔(Full Table Scan)' 작업이 필요했는데, 인덱스를 이용하면 데이터들이 정렬되어 있기 때문에 조건에 맞는 데이터를 빠르게 찾을 수 있다.
또 ORDER BY 문이나 MIN/MAX 같은 경우도 이미 정렬이 되어 있기 때문에 빠르게 수행할 수 있다.
단점
인덱스가 항상 정렬된 상태로 유지되어 오는 장점도 있지만, 그에 따른 여러 단점도 존재한다.
-
인덱스를 관리하기 위한 추가 작업이 필요
-
추가 저장 공간 필요
-
잘못 사용하는 경우 오히려 검색 성능 저하
인덱스를 항상 정렬된 상태로 유지해야 하기 때문에 인덱스가 적용된 컬럼에 삽입(INSERT), 삭제(DELETE), 수정(UPDATE) 작업을 수행하면 다음과 같은 추가 작업이 필요하다.
-
INSERT : 새로운 데이터에 대한 인덱스를 추가
-
DELETE : 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업 수행
-
UPDATE : 기존의 인덱스를 사용하지 않음 처리, 갱신된 데이터에 대한 인덱스 추가
이처럼 인덱스의 수정도 추가적으로 필요하기 때문에 데이터의 수정이 잦은 경우 성능이 낮아진다. 또 데이터의 인덱스를 제거하는 것이 아니라 '사용하지 않음'으로 처리하고 남겨두기 때문에 수정 작업이 많은 경우 실제 데이터에 비해 인덱스가 과도하게 커지는 문제점이 발생할 수 있다. 별도의 메모리 공간에 저장되기 때문에 추가 저장 공간이 많이 필요하게 된다.
또한 인덱스는 전체 데이터의 10 ~ 15% 이상의 데이터를 처리하거나, 데이터의 형식에 따라 오히려 성능이 낮아질 수 있다. 예를 들어 나이나 성별과 같이 값의 range가 적은 컬럼인 경우, 인덱스를 읽고 나서 다시 많은 데이터를 조회해야 하기 때문에 비효율적이다.
상황 분석
-
사용하면 좋은 경우
(1) Where 절에서 자주 사용되는 Column
(2) 외래키가 사용되는 Column
(3) Join에 자주 사용되는 Column
-
Index 사용을 피해야 하는 경우
(1) Data 중복도가 높은 Column
(2) DML이 자주 일어나는 Column
DML이 일어났을 때의 상황
-
INSERT
기존 Block에 여유가 없을 때, 새로운 Data가 입력된다.
→ 새로운 Block을 할당 받은 후, Key를 옮기는 작업을 수행한다.
→ Index split 작업 동안, 해당 Block의 Key 값에 대해서 DML이 블로킹 된다. (대기 이벤트 발생)
→ 이때 Block의 논리적인 순서와 물리적인 순서가 달라질 수 있다. (인덱스 조각화)
-
DELETE
<Table과 Index 상황 비교>
Table에서 data가 delete 되는 경우 : Data가 지워지고, 다른 Data가 그 공간을 사용 가능하다.
Index에서 Data가 delete 되는 경우 : Data가 지워지지 않고, 사용 안 됨 표시만 해둔다.
→ Table의 Data 수와 Index의 Data 수가 다를 수 있음
Table에서 update가 발생하면 → Index는 Update 할 수 없다.
Index에서는 Delete가 발생한 후, 새로운 작업의 Insert 작업 / 2배의 작업이 소요되어 힘들다.
인덱스 관리 방식
-
B-Tree 자료구조
이진 탐색트리와 유사한 자료구조
-
자식 노드를 둘이상 가질 수 있고 Balanced Tree 라는 특징이 있다 → 즉 탐색 연산에 있어 O(log N)의 시간복잡도를 갖는다.
모든 노드들에 대해 값을 저장하고 있으며 포인터 역할을 동반한다.
-
B+Tree 자료구조
B-Tree를 개선한 형태의 자료구조
UPDATE
값을 리프노드에만 저장하며 리프노드들 끼리는 링크드 리스트로 연결되어 있다 → 때문에 부등호문 연산에 대해 효과적이다.
리프 노드를 제외한 노드들은 포인터의 역할만을 수행한다.
-
HashTable 자료구조
해시 함수를 이용해서 값을 인덱스로 변경 하여 관리하는 자료구조
일반적인 경우 탐색, 삽입, 삭제 연산에 대해 O(1)의 시간 복잡도를 갖는다.
다른 관리 방식에 비해 빠른 성능을 갖는다.
최악의 경우 해시 충돌이 발생하는 것으로 탐색, 삽입, 삭제 연산에 대해 O(N)의 시간복잡도를 갖는다.
값 자체를 변경하기 때문에 부등호문, 포함문등의 연산에 사용할 수 없다.
[참고사항]
