[Real MySQL] 8-8. 인덱스: 클러스터링 인덱스

백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.

 

1. 클러스터링 인덱스

• 프라이머리 키 값을 기준으로 정렬하여 저장하는 방식
• InnoDB 에서는 테이블 자체가 클러스터링 인덱스 구조로 관리됨 (InnoDB 전용 기능)

 

B+Tree 자료구조

레코드 저장 방식

• 클러스터링 인덱스는 인덱스 알고리즘이 아니라 테이블의 저장 방식임
• 테이블 자체가 프라이머리 키를 기준으로 정렬된 하나의 거대한 인덱스 구조로 관리됨
• 프라이머리 키 값 기준 가까운 레코드들이 물리적으로 인접하여 저장됨

 

프라이머리 키를 기준으로 레코드가 묶여서 저장됨

• 범위 검색이나 프라이머리 키 기반 조회가 매우 빠름

 

쓰기 성능 고려

• 프라이머리 키 순서대로 입력하면 성능이 좋음
• 키 값이 변경되면 물리적으로 리프 노드의 위치가 변할 수 있음

 

장점

• 프라이머리 키로 검색할 때 처리 성능이 매우 빠름
• 모든 세컨더리 인덱스가 프라이머리 인덱스를 가지므로 인덱스만으로 처리될 수 있는 경우가 많음

 

단점

용량

• 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스 크기가 커짐

 

성능

• 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 성능이 느림
• INSERT할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느림
• 프라이머리 키를 변경할 때 레코드를 DELETE하고 INSERT하는 작업이 필요하기 때문에 처리 성능이 느림

 

vs 세컨더리 인덱스

• 기준 컬럼은 일반 컬럼이 아닌 프라이머리 키
• 리프 노드의 value값이 ROWID가 아닌 레코드의 모든 컬럼 값이 기록됨

 

프라이머리 키가 없는 InnoDB 테이블에서 클러스터링 테이블을 구성하는 방법

1. 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
2. NOT NULL 옵션의 유니크 인덱스 중에서 첫 번째 인덱스를 클러스터링 키로 선택
3. 자동으로 유니크한 값을 가지도록 증가하는 칼럼(row_id) 을 내부적으로 추가한 후, 클러스터링 키로 선택

 

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

• 클러스터링 키 값이 변경될 때마다 레코드의 주소가 변경되고 그때마다 모든 인덱스에 저장된 주솟값을 변경해야 함
• 이런 오버헤드를 제거하기 위해 InnoDB 테이블(클러스터링 테이블)의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 프라이머리 키 값을 저장하도록 구현되어 있음
• 이로 인해 성능 저하를 우려하지 않아도 됨

 

3. 주의사항

설정

클러스터링 테이블의 경우, 모든 세컨더리 인덱스가 프라이머리 키를 포함함

• 레코드 건수가 커질수록 인덱스 크기가 크게 증가하므로 프라이머리 키를 신중하게 선택해야 함

 

프라이머리 키는 반드시 명시하기

• 프라이머리 키를 명시적으로 정의하지 않으면, InnoDB 스토리지 엔진이 내부적으로 일련번호 칼럼을 추가함
• 즉, AUTO_INCREMENT 컬럼을 생성하고 프라이머리 키로 설정하는 것과 같음
• 또한 ROW 기반의 복제나 InnoDB CLuster에서 프라이머리 키를 가져야만 정상적인 복제 성능을 보장함

 

성능

프라이머리 키는 AUTO-INCREMENT보다는 업무적인 칼럼으로 생성하기

• 프라이머리 키가 클러스터링 키로 사용되며, 레코드의 위치가 결정됨
• 프라이머리 키로 검색하는 경우 매우 빠르게 처리될 수 있음
• 이 성질을 이용하면 성능상 이점을 얻을 수 있음

 

인조 식별자 사용 (AUTO-INCREMENT)

• 세컨더리 인덱스도 필요하고 프라이머리 키의 길이도 길 경우
  
  • 세컨더리 인덱스에 프라이머리 키가 포함되므로, 길이가 길면 성능이 낮아짐
• 조회보다는 INSERT 위주의 테이블
  • ex) 로그 테이블