클러스터링 인덱스

• 클러스터링이란 여러 개를 하나로 묶는다는 의미이다.
• MySQL 서버에서 클러스터링은 테이블의 레코드를 비슷한 것(프라이머리 키를 기준으로)들끼리 묶어서 저장하는 형태로 구현된다.
  • 주로 비슷한 값들을 동시에 조회하는 경우가 많다는 점에서 착안한 것이다.
• MySQL에서 클러스터링 인덱스는 InnoDB 스토리지 엔진에서만 지원하며, 나머지 스토리지 엔진에서는 지원되지 않는다.

클러스터링 인덱스란

• 클러스터링 인덱스는 프라미어리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 말한다.
  • MySQL 서버에서 인덱스와 키는 동의어로 사용하므로, 클러스터링 인덱스는 클러스터링 키라고도 한다.
• 프라이머리의 키 값에 의해 레코드의 저장 위치가 결정된다.
  • 프라이머리 키 값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야 한다.
  • 따라서 클러스터링 인덱스는 인덱스 알고리즘이라기보다 테이블 레코드의 저장 방식이라고 볼 수 있다.
• 프라이머리 키 값으로 클러스터링된 테이블은 프라이머리 키 값 자체에 대한 의존도가 상당히 크기 때문에 신중히 프라이머리 키를 결정해야 한다.
• 클러스터링의 기준이 되는 프라이머리 키는 클러스터링 키라고도 표현한다.
• InnoDB와 같이 항상 클러스터링 인덱스로 저장되는 테이블은 프라이머리 키 기반의 검색이 매우 빠르다.
  • 대신 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느리다.

클러스터링 인덱스 구조

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

프라이머리 키가 없는 경우

• 프라이머리 키가 없는 경우 InnoDB 스토리지 엔진이 다음 우선순위대로 프라이머리 키를 대체할 칼럼을 선택한다.
  1. 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
  2. NOT NULL 옵션의 유니크 인덱스(UNIQUE INDEX) 중에서 첫 번째 인덱스를 클러스터링 키로 선택
  3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후, 클러스터링 키로 선택
• InnoDB 스토리지 엔진이 내부적으로 생성한 레코드의 일련번호 칼럼은 사용자에게 노출되지 않으며 쿼리 문장에 명시적으로 사용할 수 없다.
• InnoDB 테이블에서 클러스터링 인덱스는 테이블당 단 하나만 가질 수 있는 엄청난 혜택이므로 가능하다면 프라이머리 키를 명시적으로 생성하는 것을 권장한다.

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

• MYISAM 테이블이나 MEMORY 테이블에서는 프라이머리 키와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다.
  • 프라이머리 키나 세컨더리 인덱스는 내부적인 레코드 아이디(ROWID)를 이용해 실제 데이터 레코드를 찾아온다.
• InnoDB 테이블(클러스터링 테이블)의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 프라이머리 키 값을 저장하도록 구현되어 있다.
  • 이는 클러스터링 키 값이 변경될 때마다 데이터 레코드의 주소가 변경되고, 해당 테이블의 모든 인덱스에 저장된 주솟값을 변경하는 오버헤드를 제거하기 위함이다.
• InnoDB 테이블에서 프라이머리 키(클러스터링 인덱스)는 더 큰 장점을 제공하기 때문에 성능 저하에 대해 너무 걱정하지 않아도 된다.

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

MyISAM과 같은 클러스터링되지 않은 일반 프라이머리 키와 클러스터링 인덱스를 비교했을 때의 상대적인 장단점

장점

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

단점

• 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐
• 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한 번 검색해야 하므로 처리 성능이 느림
• INSERT할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느림
• 프라이머리 키를 변경할 때 레코드를 DELETE하고 INSERT하는 작업이 필요하기 때문에 처리 성능이 느림

요약

• 장점은 빠른 읽기이며 단점은 느린 쓰기이다.
• 일반적으로 웹 서비스와 같은 온래인 트랜잭션 환경(OLTP, On-Line Transaction Processing)에서는 쓰기와 읽기의 비율이 2:8 또는 1:9 정도이기 때문에 조금 느린 쓰기를 감수하고 읽기를 빠르게 유지하는 것은 매우 중요하다.

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

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

• 클러스터링 테이블의 경우 모든 세컨더리 인덱스가 프라이머리 키(클러스터링 키) 값을 포함하기 때문에, 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다.
  • 일반적으로 테이블에 세컨더리 인덱스가 4~5개 정도 생성된다는 것을 고려하면 세컨더리 인덱스 크기는 급격히 증가한다.
• 인덱스가 커질수록 같은 성능을 내기 위해 그만큼의 메모리가 더 필요해지므로 InnoDB 테이블의 프라이머리 키는 신중하게 선택해야 한다.

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

• InnoDB의 프라이머리 키는 클러스터링 키로 사용되며, 이 값에 의해 레코드의 위치가 결정된다.
  • 프라이머리 키로 검색하는 경우 클러스터링되지 않은 테이블에 비해 매우 빠르게 처리될 수 있다.
• 또한 일반적으로 프라이머리 키는 그 의미만큼이나 중요한 역할을 하기 때문에 대부분 검색에서 상당히 빈번하게 사용된다.
• 설령 그 칼럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 칼럼을 프라이머리 키로 설정하는 것이 좋다.

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

• 가능하면 AUTO_INCREMENT 칼럼을 이용해서라도 프라이머리 키는 생성하는 것을 권장한다.
• InnoDB 테이블에서 프라이머리 키를 정의하지 않으면 InnoDB 스토리지 엔진이 내부적으로 일련번호 칼럼을 추가하는데, 이 칼럼은 사용자가 전혀 접근 및 사용할 수 없다.
• 따라서 사용자가 사용할 수 있는 값을 프라이머리 키로 설정하는 것이 좋다.
• ROW 기반의 복제나 InnoDB Cluster에서는 모든 테이블이 프라이머리 키를 가져야만 하는 정상적인 복제 성능을 보장하기도 하므로 프라이머리 키는 꼭 생성하도록 하자.

AUTO_INCREMENT 칼럼을 인조 식별자로 사용할 경우

• 여러 개의 칼럼이 복합으로 프라이머리 키가 만들어지는 경우 프라이머리 키의 크기가 길어질 때가 가끔있다.
• 하지만 프라이머리 키의 크기가 길어도 세컨더리 인덱스가 필요치 않다면 그대로 프라이머리 키를 사용하는 것이 좋다.
• 프라이머리 키의 크기가 길고 세컨더리 인덱스도 필요하다면 AUTO_INCREMENT 칼럼을 추가하고 이를 프라이머리 키로 설정하면 된다.
  • 이렇게 프라이머리 키를 대체하기 위해 인위적으로 추가된 프라이머리 키를 인조 식별자(Surrogate key)라고 한다.
• 로그 테이블과 같이 조회보다는 INSERT 위주의 테이블은 AUTO_INCREMENT를 이용한 인조 식별자를 프라이머리 키로 설정하는 것이 성능 향상에 도움이 된다.

Reference

참고 서적

📔 Real MySQL 8.0