클러스터 인덱스 (Clustered Index)
- PK가 비슷한 레코드들끼리 묶은 인덱스를 클러스터 인덱스라고 한다.
- PK 가 데이터 레코드의 물리적인 위치를 결정한다. (PK가 바뀌면 레코드의 저장 위치도 바뀐다.) → 레코드의 저장 방식을 나타내기도 하므로 ‘클러스터 테이블’ 으로 불리기도 한다.
- InnoDB 엔진에서만 제공한다. (MyISAM, MEMORY 엔진은 제공하지 않는다.)
- leaf 노드에는 데이터 레코드의 주소가 아닌 데이터 레코드 자체가 들어있다.
- 수직 탐색만 하면 데이터 레코드를 바로 찾을 수 있으므로 검색이 빠르다.
- 어떤 데이터 레코드의 PK 가 변경되면 해당 레코드의 저장 위치(노드)도 바뀌어야 하므로 PK 변경 처리는 상대적으로 느리다.
→ 빠른 검색을 위해 PK를 지정하되, PK를 변경하는 건 지양하자.
아래 그림에서는
CustId 가 PK 에 해당한다.
PK가 없는 경우
테이블 내에 PK 가 없으면 아래의 순서대로 PK 를 대체할 키를 결정한다.
1) unique key
테이블 내에 PK 가 없으면 테이블 내에 가장 첫번째 (첫번째의 기준이 뭔지는 모르겠음) not null unique 컬럼에 붙은 유니크 인덱스가 클러스터 키가 된다.
2) rowID
테이블 내에 PK도 없고 not null unique key 도 없으면 rowID 를 클러스터링 키로 지정한다.
→ rowID 는 사용자가 접근할 수 없으므로 rowID로 클러스터링 했을 때 이점이 없다.
→ InnoDB 엔진을 쓴다면 PK 또는 not null unique key 컬럼을 반드시 지정하자.
세컨더리 인덱스가 데이터 레코드 주소로 PK 를 가지는 이유
- InnoDB 환경에서 테이블에 PK가 있다면 클러스터 인덱스도 생긴다.
- 만약 어떤 클러스터 인덱스 키가 바뀐다면 (PK가 변경된다면) 해당 데이터 레코드의 rowID (데이터 블록 주소) 도 바뀌어야 한다.
- 세컨더리 인덱스 트리의 leaf 노드에 있는 인덱스 레코드가 데이터 레코드 주소를 rowID 로 가지고 있다면? → 세컨더리 인덱스 레코드도 바뀌어야 한다. → 오버헤드
위와 같은 오버헤드를 줄이기 위해 세컨더리 인덱스의 leaf 노드는 PK 값을 레코드 주소로 저장하도록 되어 있다.
클러스터 인덱스 장/단점
장점
- PK 를 가지고 검색하면 매우 빠르다. (leaf 노드에 데이터 레코드가 있기 때문에 수직 탐색만 하면 되고 비슷한 PK끼리 묶여있기 때문에 범위 검색이 빠르다.)
- 테이블의 모든 세컨더리 인덱스는 데이터 레코드 주소로서 PK를 가지고 있기 때문에 왠만하면 테이블 풀 스캔 없이 인덱스 탐색만으로 검색이 처리될 수 있다.
단점
- PK 가 크면 클러스터 인덱스, 세컨더리 인덱스 용량도 커진다.
- 세컨터리 인덱스로 검색하면 클러스터 인덱스를 탐색하는 과정이 부가적으로 발생하므로 MyISAM, MEMORY 엔진에 비해 검색이 느리다.
- PK 를 변경하면 데이터 레코드의 물리적인 위치도 바뀌어야 하므로 PK 변경 처리 성능이 느리다.
- PK 를 생성/삭제하면 세컨더리 인덱스에도 반영이 되어야 한다.
→ 요약 : PK 를 가지고 select 할 때는 매우 빠르지만, update/delete/insert 성능은 상대적으로 느리다.
유니크 인덱스 (Unique Index)
- 데이터를 조회하는 측면에서 유니크 인덱스와 세컨더리 인덱스 간의 성능 차이는 거의 없다.
- 데이터를 생성/변경하는 측면에서 유니크 인덱스는 인덱스의 중복 여부를 검사하는 절차가 필요하고, 체인지 버퍼링 (change buffering)을 못하기 때문에 세컨더리 인덱스에 비해 상대적으로 느리다.
→ 유니크 인덱스가 세컨더리 인덱스에 비해 성능이 좋다고 할 수 없다.