[ MySQL ] Clustering Index

Manx·2023년 11월 14일

DBMS

목록 보기

7/8

클러스터링이란 여러 개를 하나로 묶는다는 의미로 주로 사용된다.

MySQL의 클러스터링은 테이블의 레코드를 비슷한 것(프라이머리 키를 기준으로)들끼리 묶어서 저장하는 형태로 구현

→ 주로 비슷한 값들을 동시에 조회하는 경우가 많다는 점에 착안한 것이다.

MySQL의 클러스터링 인덱스는 InnoDB 스토리지 엔진에서만 지원하며, 나머지 스토리지 엔진에서는 지원되지 않는다.

1. Clustering Index

클러스터링 인덱스는 PK에 대해서만 적용되는 내용이다.
PK 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현한다.
PK 값에 의해 레코드의 저장 위치가 결정된다.
PK 값이 변경되면, 레코드의 저장 위치도 변경되어야 한다.
클러스터링 인덱스는 인덱스 알고리즘이라기보다 테이블 레코드의 저장 방식이라고 볼 수 있다.
InnoDB와 같이 항상 클러스터링 인덱스로 저장되는 테이블은 PK 기반의 검색이 매우 빠르며, 대신 레코드의 저장이나 PK의 변경이 상대적으로 느리다.
클러스터링 인덱스의 리프 노드에는 레코드의 모든 컬럼이 같이 저장되어 있다.
클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조로 관리된다.

PK가 없는 InnoDB 테이블의 클러스터링 구성 과정

PK가 있으면 기본적으로 PK를 클러스터링 키로 선택
NOT NULL 옵션의 유니크 인덱스 중 첫 번째 인덱스를 클러스터링 키로 선택
자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 후, 클러스터링 키로 선택

내부적으로 생성된 레코드의 일련번호 컬럼은 사용자에게 노출되지 않으며, 쿼리 문장에 사용할 수 없다.

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

MyISAM이나 MEMORY 테이블 같은 클러스터링 되지 않은 테이블은 INSERT될 떄 처음 저장된 공간에서 절대 이동하지 않는다.
레코드가 저장된 주소는 ROWID 역할을 한다.

만약 InnoDB 테이블의 세컨더리 Index가 실제 레코드가 저장된 주소를 가지고 있다면?

클러스터링 키 값이 변경될 때마다 레코드의 주소가 변경되고, 그때마다 해당 테이블의 모든 인덱스에 저장된 주솟값을 변경해야 한다.
이런 오버헤드를 제거하기 위해 클러스터링 테이블의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 PK를 저장하도록 구현되어 있다.

예시

mysql > CREATE TABLE employyes (
					emp_no INT NOT NULL,
					first_name VARCHAR(20) NOT NULL,
					PRIMARY KEY (emp_no),
					INDEX ix_firstname (first_name)
);

mysql > SELECT * FROM employees WHERE first_name='Aamer';

MyISAM : ix_firstname 인덱스를 검색해서 레코드의 주소를 확인 후, 이를 이용해 최종 레코드를 가져옴
InnoDB : ix_firstname 인덱스를 검색해 PK를 확인 후, PK Index를 검색해서 최종 레코드를 가져옴