[MySQL] 클러스터링 인덱스

프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 한다.

중요한 점은 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다. 따라서 프라이머리 키 값의 변경은 레코드의 물리적 저장위치의 변경을 뜻한다.

클러스터링 인덱스는 사실 인덱스라기 보단 레코드의 저장 방식으로 보는게 편하다. (클러스터링 테이블이라고 부름)

일반적으로 우리가 사용하는 InnoDB 테이블은 항상 클러스터링 인덱스로 저장되기 때문에 키 기반의 검색이 매우 빠르지만,
그에따라 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느리다.

 

클러스터링 인덱스 구조 자체는 일반 B-Tree와 비슷하지만, 세컨더리 인덱스를 위한 B-Tree의 리프 노드와는 달리, 클러스터링 인덱스의 리프 노드에는 모든 칼럼이 같이 저장되어있다. 즉 클러스터링 테이블은 그 자체로 거대한 인덱스 구조인 것이다.

 

만약 프라이머리 키가 없는 InnoDB테이블은 어떻게 클러스터링 테이블로 구성될까?

이러한 상황에는 우선순위에 따라 프라이머리 키를 대체할 칼럼을 선택한다.

 

1. 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택

2. Not Null 옵션의 유니크 인덱스 중 첫 번째 인덱스를 클러스터링 키로 선택

3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후, 클러스터링 키로 선택

 

InnoDB 스토리지 엔진은 적절한 클러스터링 키 후보를 찾지 못하는 경우 내부적으로 레코드의 일련번호 칼럼을 생성한다.

이렇게 자동 생성된 키는 사용자에게 노출되지 않으며, 쿼리에 명시적으로 사용할 수 없다.

결국 이 테이블은 아무 의미 없는 일련변호로 클러스터링되기 때문에, 우리는 아무 혜택을 얻을 수 없다.

따라서 되도록이면 프라이머리 키를 명시적으로 생성해주는 것이 좋다.

 

세컨더리 인덱스에 미치는 영향

InnoDB가 아닌 테이블 (클러스터링되지 않은 테이블)은 INSERT시 처음 저장된 공간에서 절대 움직이지 않는다.

InnoDB vs MyISAM에서도 봤지만, 이 경우 실제 레코드의 물리적 저장 주소 = 레코드 아이디 의 역할을 하게 된다.

따라서 MyISAM, MEMORY 테이블에서는, 프라이머리 인덱스와 세컨더리 인덱스의 실질적인 차이가 없다.

(인덱스가 프라이머리, 세컨더리인지에 상관없이 인덱스의 리프노드에는 실제 레코드의 주소가 저장되는 셈)

 

그렇다면 만약 InnoDB 테이블에서도 인덱스가 실제 레코드의 저장위치를 가진다면 어떻게 될까?

당연하겠지만, 클러스터링 키 값의 변경 시 레코드 주소의 변경은 물론이고 모~~든 인덱스에 저장된 주솟값들을 변경해야할 것이다.

따라서 이런 오버헤드를 제거하기 위해 InnoDB 테이블의 모든 세컨더리 인덱스는 해당 레코드의 실제 주소가 아닌, 프라이머리 키 값을 저장하도록 구현되어있다.

 

따라서, 세컨더리 인덱스 검색의 경우 InnoDB가 MyISAM보다 조금 더 복잡하게 처리된다.
만약 세컨더리 인덱스(pk가 아닌 "name"을 인덱스의 칼럼으로 가지는)로 검색을 하는 경우,

- MyISAM : "name" 인덱스를 검색해서 레코드의 주소를 확인하고, 바로 최종 레코드를 가져옴

- InnoDB : "name"인덱스를 검색해도 결국 리프노드에 저장된건 실제 레코드의 주소가 아닌 프라이머리 키값. 따라서 프라이머리 키 값을 검색해서 최종 레코드를 가져옴

 

하지만 InnoDB의 클러스터링 인덱스는 더 큰 장점을 제공하기 때문에 성능 저하는 걱정할 부분이 아니라고 한다.

 

클러스터링 인덱스의 장단점

장점

- 클러스터링 키로 검색 시 성능이 매우 우수 (특히 클러스터링 키를 범위 검색 하는 경우)

- 테이블의 모든 세컨더리 인덱스는 프라이머리 키를 가지기 때문에, 인덱스만으로 처리될 수 있는 경우가 많다.

 

단점

- 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖는다. 따라서 클러스터링 키가 커지면 전체 인덱스의 크기가 커진다.

- 상기했듯이 세컨더리 인덱스로 검색 시 프라이머리 키 검색을 한번 더해야하므로 느리다.

- INSERT 시에도 느리다.

- 프라이머리 키를 변경 시 DELETE > INSERT 과정을 거치므로 느리다.

 

사용 시 주의사항

클러스터링 인덱스의 단점 부분에서 봤듯이, 모든 세컨더리 인덱스가 클러스터링 키를 포함하기 때문에,
프라이머리 키 크기의 증가는 세컨더리 인덱스 크기의 증가를 초래하는데, 일반적으로 테이블에 세컨더리 인덱스가 더 많이 생성되는 것을 감안하면, 전체 인덱스의 크기는 급격히 증가하게 된다.

따라서 InnoDB 테이블의 프라이머리 키는 신중하게 선택해야 한다.

 

또한 InnoDB의 경우 프라이머리 키 = 클러스터링 키 이고, 프라이머리 키 값에 따라 레코드의 실제 위치가 결정된다.

따라서 아무 생각없이 프라이머리키를 AUTO-INCREMENT로 설정하는것 보단, 검색에 자주 사용되는(업무적으로 해당 레코드를 대표할 수 있는) 칼럼으로 설정하는 것이 좋다. (심지어 해당 칼럼의 크기가 크더라도)

 

다음은 InnoDB의 클러스터링 키 생성 기준에서도 말했지만, 되도록이면 AUTO-INCREMENT칼럼을 사용하더라도 명시적인 프라이머리 키를 생성해 주는 것이 좋다. 그 이유는 위에 써진대로, InnoDB 스토리지 엔진이 내부적으로 생성한 일련번호 칼럼은 사용자가 써먹을 수 없으므로 이 경우 사용자는 클러스터링 키의 혜택을 누릴 수 없게 된다.