TIL

8.8 클러스터링 인덱스

MySQL 서버에서 클러스터링은 테이블 레코드를 비슷한 것(프라이머리 키를 기준)끼리 묶어 저장하는 형태로 구현된다. InnoDB 스토리지 엔진에서만 지원하며 나머지 엔진에서는 지원하지 않는다.

8.8.1 클러스터링 인덱스

• 클러스터링 인덱스란 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 말한다.
• 프라이머리 키 값에 의해 레코드의 물리적 저장 위치가 결정된다.
  • 때문에 프라이머리 키가 변경된다면 레코드 저장 위치도 바껴야 한다.
• InnoDB와 같이 항상 클러스터링 인덱스로 저장되는 테이블은 프라이머리 키 기반의 검색이 매우 빠르다.
  • 대신 레코드 저장이나 프라이머리 키 변경이 상대적으로 느리다.
• 클러스터링 인덱스 구조는 일반 B-Tree와 비슷하지만 세컨더리 인덱스의 B-Tree와 달리 리프 노드에 레코드의 모든 칼럼이 저장되어 있다.

클러스터링 키 선정 기준

클러스터링의 기준이 되는 칼럼(주로 프라이머리 키)을 클러스터링 키라고 한다. 프라이머리 키가 없는 경우에 아래의 순서대로 대체 칼럼을 선택한다.

1. 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
2. NOT NULL 옵션의 유니크 인덱스 중 첫 번째 인덱스를 클러스터링 키로 선택
3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후, 클러스터링 키로 선택
   1. 이렇게 자동으로 추가된 클러스터링 키는 사용자에게 노출되지 않으며, 쿼리에 사용할 수도 없다.

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

• InnoDB 테이블의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아닌 프라이머리 키 값을 저장하도록 구현되어 있다.
• 인덱스 키 값으로 조회할 때 MyISAM과 InnoDB의 차이를 살펴보자.
  • MyISAM: 인덱스로 검색해서 레코드 주소를 확인한 후 레코드 주소를 이용해 최종 레코드를 가져옴
  • InnoDB: 인덱스를 검색해 레코드의 프라이머리 키 값을 확인한 후, 프라이머리 키 인덱스를 검색해 최종 레코드를 가져옴
• InnoDB 테이블에선 프라이머리 키(클러스터링 인덱스)가 더 큰 장점을 제공하기에 성능 저하에 대해 신경 쓰지 않아도 된다.

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

• 장점
  • 프라이머리 키(클러스터링 키)로 검색할 때 처리 성능이 매우 빠름 (특히 프라이머리 키로 범위 검색하는 경우 매우 빠름)
  • 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음 (커버링 인덱스)
• 단점
  • 모든 세컨더리 인덱스가 클러스터링 키를 갖기에 클러스터링 키 크기가 클 경우 전체적으로 인덱스 크기가 커짐
  • 세컨더리 인덱스로 검색하면 프라이머리 키로 다시 검색해야 하므로 처리 성능이 느림
  • INSERT할 때 프라이머리 키에 의해 레코드 저장 위치가 결정되어 처리 성능이 느림
  • 프라이머리 키를 변경할 때 레코드를 삭제하고 삽입하는 작업이 필요해 처리 성능이 느림
• 클러스터링 인덱스의 장점은 빠른 읽기이며 단점은 느린 쓰기라는 것을 알 수 있다.
• 대부분의 웹 서비스는 쓰기/읽기 비율이 2:8 또는 1:9이기 때문에 느린 쓰기를 감수하고 빠른 읽기를 유지하는 것은 매우 중요하다.

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

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

• 모든 세컨더리 인덱스가 클러스터링 키 값을 포함하기에 프라이머리 키 크기가 커지면 세컨더리 인덱스 크기도 자동으로 커진다.
• 레코드 건수가 많아질수록 메모리도 더 크게 차지하기 때문에 프라이머리 키는 신중하게 선택해야 한다.

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

• 프라이머리 키로 검색하는 경우(특히 범위로 많은 레코드를 검색하는 경우) 매우 빠른 처리 성능을 낼 수 있다.
• 때문에 칼럼 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 칼럼을 프라이머리 키로 설정하는 것이 좋다.

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

• 프라이머리 키를 정의하지 않으면 InnoDB 스토리지 엔진이 내부적으로 일련번호 칼럼을 추가한다.
• 이 칼럼은 사용자에게 보이지 않기 때문에 사용할 수가 없다.

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

• 여러 칼럼을 복합으로 프라이머리 키가 만들어지는 경우 프라이머리 키 크기가 길어질 때가 있다.
• 프라이머리 키 크기가 길어도 세컨더리 인덱스가 없다면 그대로 사용해도 좋다.
• 프라이머리 키 크기가 길고, 세컨더리 인덱스도 필요해진다면 AUTO-INCREMENT 칼럼을 추가하고 이를 프라이머리 키로 설정하면 된다.
• 이처럼 프라이머리 키를 대체하기 위해 인위적으로 추가된 프라이머리 키를 인조 식별자라고 한다.
• 조회 보다는 INSERT 위주의 테이블들은 AUTO-INCREMENT를 이용한 인조 식별자를 프라이머리 키로 설정하는 것이 성능 향상에 도움이 된다.