[MySQL] 클러스터링 인덱스

개요

MySQL의 InnoDB 스토리지 엔진은 PK에 대해 클러스터링 인덱스를 자동으로 생성합니다. 그렇다면 클러스터링 인덱스 무엇이길래 테이블마다 존재하는 것일까요? 이 글에서 알아봅시다.

클러스터링 인덱스

클러스터링이란 여러 개를 하나로 묶는다는 의미로 사용됩니다. 클러스터링 인덱스도 이러한 의미에서 크게 벗어나지 않습니다. InnoDB에서 클러스터링은 테이블의 레코드를 비슷한 것들끼리(PK 기준) 묶어서 저장하는 형태로 구현됩니다. 이는 주로 비슷한 값들을 동시에 조회하는 경우가 많다는 점에서 착안한 것입니다.

 

클러스터링 인덱스는 PK에 대해서만 적용됩니다. PK값이 비슷한 레코드끼리 묶어서 저장됩니다. 즉, PK 값에 의해 레코드의 저장 위치가 결정된다는 것입니다. 또한 PK 값이 변경된다면 레코드의 물리적인 저장 위치가 바뀌어야 한다는 것을 의미합니다. PK 클러스터링된 테이블은 PK에 대한 의존도가 높기 때문에 신중하게 PK를 결정해야 합니다.

 

클러스터링 인덱스는 테이블 레코드의 저장 방식이라 볼 수 있습니다. 그래서 PK 클러스터링 인덱스는 PK 기반 검색이 매우 빠르며, 대신 레코드 저장이나 PK 변경이 상대적으로 느립니다.

PK 클러스터링 인덱스

위 그림은 PK 클러스터링 인덱스를 간단하게 표현한 그림입니다. B-Tree와의 다른 점은 리프 노드에 레코드의 모든 칼럼이 같이 저장돼 있다는 것 입니다. 만약 PK가 변경되면 저장 위치도 변경된다.

 

만약 PK가 없는 InnoDB 테이블은 대체할 칼럼을 선택하여 클러스터링 테이블로 구성됩니다. 

 

1. PK가 있으면 PK를 클러스터링 키로 선택

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

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

 

자동으로 추가된 칼럼은 쿼리 문장에 명시적으로 사용할 수 없다. 즉, PK 클러스터링의 효과를 보지 못한다. 따라서 PK는 항상 지정해 주자.

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

PK가 데이터 레코드의 저장에 미치는 영향을 알아보았습니다. 이제 PK가 세컨더리 인덱스에 미치는 영향을 알아봅시다.

 

MyISAM이나 MEMORY 테이블 같은 클러스터링되지 않은 테이블은 INSERT될 때 처음 저장된 공간에서 절대 이동하지 않습니다. 데이터 레코드가 저장된 주소는 내부적인 레코드 아이디(ROWID) 역할을 합니다. 그리고 PK나 세컨더리 인덱스는 ROWID를 이용해 실제 데이터 레코드를 찾아옵니다. 이 경우 PK 인덱스와 세컨더리 인덱스는 구조적으로 아무런 차이가 없습니다.

 

만약 InnoDB 테이블에서 세컨더리 인덱스가 실제 레코드가 저장된 주소를 가지고 있다면 어떻게 될까요?

 

1) 클러스터링 키 값이 변경될 때마다 데이터 레코드의 주소가 변경

2) 해당 테이블의 모든 인덱스에 저장된 주솟값 변경

 

이러한 오버헤드를 제거하기 위해 InnoDB 테이블의 모든 세컨더리 인덱스는 해당 ROWID가 아닌 PK를 저장하도록 구현되어 있습니다.

 

스토리지 엔진에 따라 인덱스 검색 과정을 비교하면

 

• MyISAM: 인덱스를 검색해 레코드 주소를 확인한 후, 레코드 주소를 이용해  최종 레코드를 가져옴
• InnoDB: 인덱스를 검색해 레코드의 PK값을 확인한 후, PK 인덱스를 검색해 최종 레코드를 가져옴

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

장점

• PK로 검색할 때 처리 성능이 빠름(특히, PK 범위를 검색하는 경우)
• 테이블의 모든 세컨더리 인덱스가 PK를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음(커버링 인덱스)

단점

• 테이블의 모든 세컨더리 인덱스가 PK를 가지기 때문에 PK 값이 클 경우 전체적인 인덱스의 크기가 커짐
• 세컨더리 인덱스를 통한 검색은 PK를 거쳐서 한 번 더 검색하기 때문에 처리 성능이 느림
• INSERT할 때 PK에 의해 레코드 저장 위치가 결정되기 때문에 처리 성능이 느림
• PK를 변경할 때 레코드를 DELETE하고 INSERT하기 때문에 처리 성능이 느림

빠른 읽기와, 느린 쓰기로 정리할 수 있습니다. OLTP 환경에서는 읽기의 비율이 높기 때문에 이러한 단점은 감수하고 빠른 읽기를 유지하는 것이 좋습니다.

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

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

클러스터링 키는 모든 세컨더리 인덱스에 포함됩니다. 그래서 PK의 값이 커지면 세컨더리 인덱스도 커집니다. 

프라이머리 키는 AUTO-INCREMENT보다는 업무적인 칼럼으로 생성(가능한 경우)

PK로 검색하는 경우 매우 빠르게 검색 가능합니다. 따라서 그 칼럼이 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 칼럼을 PK로 설정하는 것이 좋습니다.

프라이머리 키는 반드시 명시할 것

앞서 설명했듯이 PK를 지정하지 않으면 AUTO-INCREMENT 칼럼을 생성하고 이를 PK로 지정합니다. 이는 PK 클러스터링의 장점을 활용하지 못하는 것과 같습니다. 또한 ROW 기반 복제나 InnoDB Cluster에서는 모든 테이블이 PK를 가져야 정상적인 복제 성능을 보장합니다.

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

여러 개의 칼럼이 복합으로 PK를 만드는 경우가 있습니다. PK의 크기가 크더라도 세컨더리 인덱스가 필요하지 않다면 그대로 사용하는 것이 좋습니다. 만약 세컨더리 인덱스도 필요하고 프라이머리 키의 크기도 길다면 AUTO_INCREMENT 칼럼을 추가하고, 이를 PK로 설정하면 됩니다. 이를 인조 식별자라고 합니다. 로그 테이블같은 조회보다 INSERT 위주의 테이블은 AUTO_INCREMENT를 이용한 인조 식별자를 PK로 지정하는 것이 성능 향상에 도움이 됩니다.