Real MySQL 8.0 Ch.8 — 클러스터링 인덱스, 유니크 인덱스, 외래키

클러스터링은 여러 개를 하나로 묶는다는 의미다. MySQL의 클러스터링은 테이블의 레코드를 PK 값이 비슷한 것끼리 묶어서 저장하는 형태로 구현되어 있다. 비슷한 값을 함께 조회하는 경우가 많다는 점에 착안한 것이다. InnoDB 스토리지 엔진에서만 지원한다.

클러스터링 인덱스

PK에 대해서만 적용된다.
PK 값에 의해 레코드의 저장 위치가 결정된다. 따라서 PK 값이 바뀌면 레코드의 저장 위치도 바뀌어야 한다.
리프 노드에는 레코드의 모든 컬럼이 함께 저장되어 있다. 클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조다.
인덱스 알고리즘이라기보다 테이블 레코드의 저장 방식에 가깝다.

그 결과 PK 기반 검색은 매우 빠르고, 대신 레코드의 저장이나 PK 변경은 상대적으로 느리다.

PK가 없는 테이블의 클러스터링 키

InnoDB는 다음 우선순위로 클러스터링 키를 선택한다.

PK가 있으면 PK
NOT NULL 옵션의 유니크 인덱스 중 첫 번째 인덱스
자동으로 유니크한 값을 가지도록 증가하는 컬럼을 내부적으로 추가해 사용

3번에서 내부적으로 생성된 일련번호 컬럼은 사용자에게 노출되지 않으며 쿼리에서 사용할 수 없다.

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

MyISAM이나 MEMORY처럼 클러스터링되지 않은 테이블은 레코드가 INSERT될 때 저장된 공간에서 이동하지 않는다. 그래서 레코드가 저장된 주소가 ROWID 역할을 하고, 인덱스는 이 주소를 가리킨다.

InnoDB의 세컨더리 인덱스가 실제 레코드 주소를 가지고 있다면 어떻게 될까? 클러스터링 키 값이 바뀔 때마다 레코드의 주소가 바뀌고, 그때마다 테이블의 모든 인덱스에 저장된 주솟값을 변경해야 한다.

이 오버헤드를 없애기 위해 InnoDB의 모든 세컨더리 인덱스는 레코드의 주소가 아니라 PK 값을 저장한다.

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

SELECT * FROM employees WHERE first_name = 'Aamer';

MyISAM: ix_firstname 인덱스에서 레코드의 주소를 확인한 뒤, 그 주소로 최종 레코드를 가져온다.
InnoDB: ix_firstname 인덱스에서 PK 값을 확인한 뒤, PK 인덱스를 다시 검색해 최종 레코드를 가져온다.

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

장점

PK로 검색할 때 매우 빠르다. 특히 PK 범위 검색이 빠르다.
모든 세컨더리 인덱스가 PK를 포함하므로 인덱스만으로 처리되는 경우(커버링 인덱스)가 많다.

단점

모든 세컨더리 인덱스가 PK를 포함하므로, PK가 크면 전체 인덱스 크기가 커진다.
세컨더리 인덱스로 검색하면 PK로 한 번 더 검색해야 한다.
INSERT할 때 PK에 의해 저장 위치가 결정되므로 느리다.
PK를 변경하면 레코드를 DELETE하고 INSERT해야 하므로 느리다.

정리하면 클러스터링 인덱스는 빠른 읽기와 느린 쓰기의 교환이다. 웹 서비스 같은 온라인 트랜잭션 환경에서는 쓰기와 읽기의 비율이 2:8 또는 1:9 정도이므로, 쓰기가 조금 느려지더라도 읽기를 빠르게 유지하는 쪽이 유리하다.

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

PK의 크기

모든 세컨더리 인덱스가 PK 값을 포함하므로, PK가 커지면 세컨더리 인덱스도 함께 커진다. 테이블 하나에 세컨더리 인덱스가 보통 4~5개 생성된다는 점을 고려하면 인덱스 크기는 급격히 증가한다. 세컨더리 인덱스가 5개인 테이블을 예로 들면 다음과 같다.

PK 크기	레코드당 증가하는 인덱스 크기	100만 건 저장 시 증가하는 인덱스 크기
10바이트	10 * 5 = 50바이트	50 * 1,000,000 = 47MB
50바이트	50 * 5 = 250바이트	250 * 1,000,000 = 238MB

PK는 AUTO_INCREMENT보다 업무적인 컬럼으로

InnoDB의 PK는 클러스터링 키여서 PK를 이용한 검색이 매우 빠르게 처리된다. PK는 그 의미만큼이나 대부분의 검색에서 빈번하게 사용된다.

그래서 컬럼의 크기가 다소 크더라도, 업무적으로 해당 레코드를 대표할 수 있는 컬럼이 있다면 그 컬럼을 PK로 설정하는 것이 좋다.

유니크 인덱스

유니크는 인덱스라기보다 제약 조건에 가깝다. 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없다는 의미다. 다만 MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다.

PK와 달리 유니크 인덱스는 NULL을 저장할 수 있다. 또 InnoDB의 PK는 클러스터링 키 역할도 하므로 유니크 인덱스와는 근본적으로 다르다.

일반 세컨더리 인덱스와의 비교

유니크 인덱스와 일반 세컨더리 인덱스는 인덱스 구조상 아무런 차이가 없다. 읽기와 쓰기 성능의 차이만 살펴보면 된다.

읽기

유니크하지 않은 세컨더리 인덱스는 일치하는 값을 찾은 뒤 다음 레코드를 한 번 더 확인해야 한다. 하지만 이는 디스크 읽기가 아니라 CPU에서 컬럼 값을 비교하는 작업이어서 성능에 거의 영향이 없다.
유니크하지 않은 인덱스가 느리다면 중복된 값이 많아 읽어야 할 레코드가 많기 때문이지, 인덱스 자체의 특성 때문이 아니다.

쓰기

유니크 인덱스에 키 값을 쓸 때는 중복된 값이 있는지 확인하는 과정이 한 단계 더 필요하다. 그래서 일반 세컨더리 인덱스의 쓰기보다 느리다.
중복 값을 확인할 때는 읽기 잠금을, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 아주 빈번하게 발생한다.
InnoDB는 인덱스 키의 저장을 체인지 버퍼로 버퍼링해 빠르게 처리한다. 하지만 유니크 인덱스는 반드시 중복 확인을 해야 하므로 작업을 버퍼링하지 못한다.

주의사항

유일성이 꼭 보장돼야 하는 컬럼에는 유니크 인덱스를 생성하되, 꼭 필요하지 않다면 유니크하지 않은 세컨더리 인덱스를 생성하는 방법을 고려한다.

외래키

MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있다. 외래키 제약을 설정하면 연관되는 테이블의 컬럼에 인덱스까지 자동으로 생성된다.

InnoDB의 외래키 관리에는 중요한 특징이 두 가지 있다.

테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.
외래키와 연관되지 않은 컬럼의 변경은 최대한 잠금 경합을 발생시키지 않는다.

참고

Real MySQL 8.0