Clustering index

클러스터링의 사전적 의미는 유사한 속성을 갖는 요소를 군집으로 묶어주는 작업이다. 인덱스에서 클러스터링이란 단어도 의미를 크게 벗어나지 않는다.

MySQL에서 클러스터링은 테이블의 레코드를 PK 기준으로 묶어서 저장하는 형태로 구현되는데, InnoDB 엔진에서만 지원하며, 나머지 스토리지 엔진에서는 지원하지 않는다.

클러스터링 인덱스

앞에서도 언급했듯이 클러스터링 인덱스는 테이블의 PK에 대해서만 적용된다.

여기서 중요한 것은 PK 값에 의해 레코드의 저장 위치가 결정된다. 또, PK가 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야 한다는 것을 의미한다.

PK로 클러스터링된 테이블은 PK 자체에 의존도가 상당하기 때문에 신중히 결정해야 한다.

클러스터링 인덱스 구조를 보면 B-Tree와 유사하지만 세컨더리 인덱스를 위한 B-Tree 리프 노드와 달리 리프 노드에 레코드의 모든 컬럼(데이터)가 저장되어 있다. 즉, 클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조이다.

클러스터링 테이블에 update가 수행되면 어떤 일이 일어날까?

UPDATE tb_test SET emp_no=100002 WHERE emp_no=100007;

처음 그림에서는 emp_no=100007인 레코드는 3번 페이지에 저장되어 있었다. 하지만 아래 그림에서는 emp_no가 100002로 변경되면서 2번 페이지로 이동했다. 실제로 PK가 변경되는 일은 없지만 이런식으로 작동한다.

MyISAM, 기타 InnoDB를 제외한 테이블의 레코드는 PK나 인덱스 키 값이 변경된다고 해서 실제 레코드 위치가 변경되지 않는다. INSERT 될 때 데이터 파일의 끝 또는 빈 공간에 저장된다.

한번 결정된 위치는 절대 바뀌지 않고, 레코드가 저장된 주소는 MySQL 내부적으로 레코드를 식별하는 ID로 인식하게 된다. 레코드가 저장된 주소를 ROW-ID라고 표현하며, MySQL에서는 사용자에게 노출되지 않는다.

🤔 그럼 PK가 없는 InnoDB 테이블은 어떻게 클러스터링 테이블로 구성하는가?

PK가 없는 경우 InnoDB 스토리지 엔진이 다음 우선순위대로 PK를 대체할 컬럼을 선택한다.

1. PK가 존재하면 그 컬럼을 클러스터링 키로 선택

2. NOT NULL 옵션의 UNIQUE INDEX 중에서 첫 번째 인덱스를 클러스터링 키로 선택

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

4. 이래도 키 후보를 못 찾으면 InnoDB 스토리지 엔진이 내부적으로 레코드의 일련번호 컬럼을 생성

   • 자동으로 생성된 일련번호는 사용자에게 노출되지 않으며 쿼리 문장에 명시해서 사용할 수 없다.

   • 즉, 인덱스가 전혀 없는 InnoDB 테이블에서는 아무 의미 없는 숫자가 클러스터링 된다.

클러스터링 인덱스는 테이블당 하나만 가질 수 있기 때문에 가능하면 PK를 생성하자.

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

MyISAM, MEMORY 테이블 같은 클러스터링 되지 않은 테이블은 INSERT 될 때 처음 저장된 공간에서 절대 이동하지 않는다. 그래서 MyISAM, MEMORY 테이블에서는 PK와 세컨더리 인덱스는 구조적으로 차이가 없다.

🤔 InnoDB 테이블에서 세컨더리 인덱스가 실제 레코드가 저장된 주소를 갖고 있다면?

클러스터링 키 값이 변경될 때마다 실제 레코드 주소가 변경되고, 해당 테이블의 모든 인덱스에 저장된 주소값을 변경해야 한다.

이러한 오버헤드를 제거하기 위해 InnoDB의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 PK 값을 저장하도록 구현돼 있다.

아래 예시에서, first_name을 인덱스로 설정하고 PK로 클러스터링 된 InnoDB와 그렇지 않은 MyISAM의 차이를 보자.

select * from employees where first_name='Aamer';

• MyISAM: idx_firstname 인덱스를 검색해서 레코드의 주소를 확인하고 레코드 주소를 이용해서 최종 레코드를 가져온다.

• InnoDB: 인덱스를 검색해 레코드의 PK를 확인하고 PK를 검색해서 최종 레코드를 가져온다.

과정만 봐도 InnoDB가 더 적게 수행한다.

클러스터링 인덱스 장단점

장점:

• PK로 검색할 때 처리 성능이 빠르다.

• 테이블의 모든 세컨더리 인덱스가 PK를 가지고 있어서 인덱스만으로 처리될 수 있는 경우가 많음 (커버링 인덱스)

단점:

• 테이블의 모든 세컨더리 인덱스가 PK를 갖기 때문에 PK의 크기가 클 경우 인덱스 크기가 커진다.

• 세컨더리 인덱스를 통해 검색할 때 PK로 다시 한번 검색해야한다.

• INSERT하면 PK에 의해 레코드의 저장 위치가 결정되어 처리 성능이 느리다.

• PK를 변경할 때 레코드를 DELETE → INSERT 하는 작업이 필요하여 처리가 느리다.

대부분의 클러스터링 인덱스의 장점은 빠른 읽기, 단점은 느린 쓰기이다. 일반적으로 온라인 트랜잭션 환경(OLTP)에서는 쓰기와 읽기 비율이 2:8, 1:9 정도라고 하기 때문에 조금 느린 쓰기를 감수하고 읽기를 빠르게 유지하는 것은 중요하다.

클러스터링 테이블 사용 주의사항

클러스터링 되지 않은 테이블에 비해 InnoDB와 같은 클러스터링 테이블에서는 주의해야 할 사항이 있다.

클러스터링 인덱스 키의 크기

모든 세컨더리 인덱스는 클러스터링 키를 포함한다. 그래서 PK의 사이즈가 커진다면 세컨더리 인덱스도 같이 커진다. 일반적으로 테이블에 세컨더리 인덱스가 4~5개 생성되는 것을 고려하면 세컨더리 인덱스 크기는 급격히 증가한다. 5개의 세컨더리 인덱스를 가지는 테이블의 PK 크기에 따라 경우를 비교해보자.

• 세컨더리 인덱스의 리프 노드에는 세컨더리 인덱스 키 값과 해당 레코드의 PK 값이 저장된다.

PK 크기	레코드당 증가하는 인덱스 크기	100만 건 레코드 저장 시 증가하는 인덱스 크기
10bytes	10 * 5 = 50 바이트	50 * 1,000,000 = 47MB
50bytes	50 * 5 = 250 바이트	250 * 1,000,000 = 238MB

PK는 AUTO-INCREMENT 보다는 업무적인 컬럼으로 생성(가능하면)

InnoDB는 PK가 클러스터링 키로 사용되고 레코드의 위치를 결정한다. 그래서 PK로 범위 검색은 매우 빠르게 처리된다. MyISAM과 같이 클러스터링 되지 않는 테이블에 비해 엄청난 차이를 만든다.

그러므로 그 컬럼의 크기가 크더라도 해당 레코드를 대표할 수 있다면 그 컬럼을 PK로 설정하는 것이 좋다.

AUTO-INCREMENT 컬럼을 인조 식별자로 사용할 경우

복합 컬럼으로 PK가 구성되는 경우, PK의 데이터 사이즈 크기가 커질 때가 있다. 하지만 PK가 커져도 세컨더리 인덱스가 필요하지 않다면 그대로 PK를 사용하는 것이 좋다.

세컨더리도 필요하고 PK의 크기도 크다면 AUTO_INCREMENT 칼럼을 추가하고 PK로 설정하면 된다. 이렇게 PK를 대체하기 위한 인위적으로 추가된 PK를 인조 식별자(Surrogate key)라고 한다.

로그 테이블과 같이 조회보다 INSERT 위조의 테이블은 AUTO_INCREMENT를 이용한 인조 식별자를 PK로 설정하는 것은 성능 향상에 도움이 된다고 한다.

Q&A

1. 클러스터링 인덱스와 B-Tree는 어떻게 다른가?

데이터 저장

• 클러스터링 인덱스는 리프 노드에 실제 데이터를 저장한다.

• B-Tree는 리프 노드에 키와 데이터 주소만 저장한다.

데이터 변경

• 클러스터링 인덱스는 PK 변경 시 데이터 재배치가 필요하다.

• B-Tree는 상대적으로 변경이 용이하다.

검색 성능

• 클러스터링 인덱스는 PK 기반 검색이 매우 빠르다.

• B-Tree는 모든 키에 대해 균일한 성능을 제공한다.