[DB] 클러스터링 인덱스

클러스터링 인덱스

• PK가 비슷한 레코드끼리 묶어서 저장
  • 클러스터링된 테이블은 PK에 의존하므로 PK를 신중하게 결정해야 함
  • PK가 레코드의 저장 위치 결정
  • PK 변경 시 레코드의 물리적인 저장 위치가 변경

• PK 기반 검색 매우 빠름

• 레코드 저장이나 PK 변경이 상대적으로 느림

• B-Tree와 달리 리프 노드에 모든 컬럼이 같이 저장됨

• PK를 명시적으로 생성하는 것이 좋음

• PK가 없는 InnoDB는 스토리지 엔진이 다음 우선순위대로 컬럼 선택

  1. PK
  2. NOT NULL + 유니크 인덱스 중 1번째 인덱스
  3. AUTO-INCREMENT 컬럼을 내부적으로 추가한 후 선택

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

클러스터링되지 않은 테이블

• 데이터 레코드 주소가 레코드 row id 역할

• PK와 세컨더리 인덱스의 키는 row id를 이용해 검색

• PK와 세컨더리 인덱스는 구조 차이x

클러스터링된 InnoDB 테이블

• 세컨더리 인덱스가 실제 레코드 주소를 가지고 있다면
  • 클러스터링 키 값이 변경 > 데이터 레코드 주소 변경 + 해당 테이블의 모든 인덱스에 저장된 주솟값 변경

• 세컨더리 인덱스는 PK 값을 저장함

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

• 빠른 읽기, 느린 쓰기

• 빠른 SELECT, 느린 INSERT, UPDATE, DELETE

• 웹서비스는 읽기 비율이 크므로 느린 쓰기를 감수함

장점

• PK로 검색 시 처리 속도 매우 빠름

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

단점

• 모든 세컨더리 인덱스가 클러스터링 키를 가지므로 키 값의 크기가 커지면 인덱스의 크기가 커짐

• 세컨더리 인덱스로 검색 시 PK로 재검색해야 하므로 느림

• INSERT 시 PK에 의해 레코드 저장 위치가 결정되므로 느림

• PK 변경 시 레코드를 DELETE와 INSERT 작업이 필요하므로 느림

---

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

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

2. PK는 AUTO-INCREMENT보다 업무적인 컬럼으로 생성

3. PK는 반드시 명시하기

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

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

• 세컨더리 인덱스가 PK(클러스터링 키) 값을 포함

• PK 크기가 커지면 세컨더리 인덱스도 커짐

• 테이블에 세컨더리 인덱스가 4~5개 생성 > 세컨더리 인덱스 크기가 급격히 증가

2. PK는 AUTO-INCREMENT보다 업무적인 컬럼으로

• InnoDB의 PK는 클러스터링 키로 사용되고, PK 값에 의해 레코드 위치가 결정됨
• PK로 검색 시 빠르게 처리
• 컬럼의 크기가 크더라도 레코드를 대표한다면 PK로 설정하는 것이 좋음

3. PK는 반드시 명시

• AUTO-INCREMENT 컬럼을 이용해서라도 PK를 생성하기

• InnoDB에서 PK를 정의하지 않으면 스토리지 엔진이 내부적으로 컬럼을 추가함, 추가한 컬럼은 보이지 않아 사용자가 사용할 수 없음

  • 결국 AUTO-INCREMENT 컬럼을 생성하는 것과 동일

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

• PK가 길어도 세컨더리 인덱스가 필요하지 않다면 PK 사용

• PK가 길고 세컨더리 인덱스가 필요하다면 AUTO-INCREMENT 컬럼을 추가하고 PK로 설정하기

• 로그 테이블처럼 INSERT 위주의 테이블은 AUTO-INCREMENT를 이용한 인조 식별자를 PK로 설정하는 게 성능 향상에 도움이 됨

---

출처: Real MySQL 8.0(1권)