CLUSTERED INDEX와 SECONDARY INDEX는 모두 내부적으로 균형 트리(B-TREE)로 만들어진다.
INDEX 내부 작동 원리
INDEX의 정확한 사용을 위해서는 내부 작동 원리를 이해하는 것이 좋다.
B-TREE 개념
균형 트리에서 데이터가 저장되는 공간을 NODE라고 한다.
NODE는 자료구조에서 주로 사용하는 용어이며, MYSQL에서는 이를 PAGE라고 부른다.
PAGE는 최소한의 저장 단위로, 16Kbyte의 크기를 가진다.
균형 트리는 데이터를 검색할 때 아주 뛰어난 성능을 발휘하는데, 균형 트리에서 자료를 탐색하는 경우 루트 페이지부터 검색을 시작한다.
루트 페이지에는 각 노드의 첫 내용이 들어가며 모든 데이터는 정렬되어 있다.
루트 페이지의 데이터를 읽으면 데이터를 가진 다음 리프 페이지로 이동하여 다시 탐색을 시작한다.
원하는 데이터가 있는 페이지까지 최소한의 탐색이 가능하기 때문에, 배열과 같은 다른 자료구조보다 탐색을 빠르게 할 수 있다.
페이지가 많아지고 리프 페이지의 갯수가 늘어날수록 이 효과는 크게 체감이 된다.
균형 트리의 페이지 분할
데이터의 탐색에 균형 트리가 더 효율적인 것은 확인할 수 있으나, 인덱스를 구성하는 과정에서 데이터 변경 작업을 진행하면 성능이 나빠진다.
특히 INSERT 작업 시 더 느리게 입력될 수 있는데 이유는 PAGE PARTIONING이라는 작업이 발생하기 때문이다.
새로 데이터가 입력되는 경우 리프 페이지에 빈 공간이 없으면 페이지를 새로 만든 뒤 바로 위 루트 페이지에 등록하도록 한다.
루트 페이지가 모두 채워진 경우에는 새로운 루트 페이지를 만듦과 동시에 기존 루트 페이지는 또 다른 새로 만든 페이지가 되어 데이터를 적당히 나눈 뒤 중간 페이지가 된다.
결국 데이터를 하나 입력하기 위해 최악의 경우 3개의 새로운 페이지가 할당되고 2번의 페이지 분할이 이루어 지는 것이다.
INDEX 사용
인덱스 생성
CREATE [UNIQUE] INDEX 인덱스_이름
ON 테이블_이름 (열_이름) [ASC | DESC]- UNIQUE : 고유 인덱스를 만들지에 대한 여부, 생략하는 경우 중복이 허용된다.
이름과 같은 열의 경우 UNIQUE를 지정하면 안되지만, EMAIL 등에는 지정해도 별 문제 없다.
인덱스 제거
DROP INDEX 인덱스_이름 ON 테이블_이름기본키, 고유키 등으로 자동 생성된 인덱스는 DROP INDEX로 지울 수 없다.
ALTER TABLE을 이용해서 기본키나 고유키를 제거하면 자동으로 생성된 인덱스도 제거할 수는 있다.
