클러스터드 인덱스

MySQL의 모든 테이블은 어떤 컬럼을 기준으로 클러스터링 되어 저장됩니다. 클러스터링 된다는 것은 물리적 데이터가 해당 컬럼과 같은 순서로 저장된다는 것입니다.
기준이 되는 컬럼은 디폴트로 Primary 키입니다.
예를 들어 다음과 같은 User 테이블이 있을 때 :

CREATE TABLE user (
	id INT PRIMARY KEY,
    name VARCHAR(100),
    EMAIL VARCHAR(100)
)

디스크에 데이터가 아래와 같이 저장됩니다.

• 물리적 데이터도 primary key인 id의 순서대로 저장됩니다.

Prmimary 키가 없다면 임의로 컬럼을 정하는데, 이 컬럼이 어떤 것이 되느냐에 따라 데이터 파편화가 발생할 수 있어 MySQL에서는 Primary 키를 무조건 만드는 것이 좋습니다.
이 Primary key에 대한 인덱스를 클러스터드 인덱스라고 합니다.

클러스터드 인덱스의 장점

프라이머리 키에 따라 데이터의 위치가 결정되어 범위 데이터 조회가 빠릅니다. 프라이머리 키와 실제 물리적 데이터의 위치가 매핑되어 탐색이 쉽기 때문입니다.
예를 들어 User id가 40 ~ 45인 유저를 찾는다면, User 테이블의 프라이머리 키에 대한 인덱스를 검색해 id가 40인 유저를 찾습니다. 디스크의 저장 위치가 id의 순서와 매핑되므로 나머지 데이터 위치도 유추할 수 있습니다. 디스크로의 I/O를 최소화하게 됩니다.

클러스터링 인덱스의 단점

단점은 인덱스와 디스크의 위치가 매핑되는 만큼 삽입 / 삭제시에 상대적으로 느리다는 것입니다. 그럴 일은 거의 없지만 primary key의 값이 변경된다면 디스크상에서의 위치 또한 변해야 합니다.

세컨더리 인덱스

클러스터링은 프라이머리 키에만 적용됩니다. 그렇다면 보조키에 인덱스를 걸 경우에는 어떻게 될까요?
MySQL에서 보조키에 적용된 인덱스, 즉 세컨더리 인덱스는 디스크 위치가 아닌 프라이머리 키를 가리킵니다.

세컨더리 인덱스의 장점

이 구조의 장점은 데이터 위치 변경 시 인덱스 업데이트로 인한 부하를 줄여준다는 것입니다.
예를 들어 user id = 1인 행의 디스크 주소가 88인 상태에서 세컨더리 인덱스 A, 세컨더리 인덱스 B를 만들었다고 가정해봅시다.
id = 1인 행을 업데이트하는 과정에서 디스크의 주소가 바뀌어 99가 되었다면 어떻게 해야 할까요?
프라이머리 인덱스의 주소만 업데이트 하면 됩니다. 세컨더리 인덱스 A와 B는 실제 디스크의 물리적 주소가 아니라 프라이머리 인덱스를 가리키고 있기 때문입니다.
PostgreSQL은 실제로 모든 인덱스가 디스크의 주소인 tid를 가리키고 있어, 이런 경우 모든 인덱스를 업데이트 해야 합니다. 이 지점에서는 MySQL이 효율적인 구조를 갖고 있다고 할 수 있을 것 같습니다.

세컨더리 인덱스의 단점

다만 이 경우 데이터를 가져오는 과정이 보조키 인덱스 -> 프라이머리 키 -> 디스크 접근의 순서가 되어 단계가 하나 추가됩니다.
또한 클러스터링의 장점을 활용할 수 없습니다. 보조키를 기준으로 범위 조회를 해도 Primary 키로 정렬했을 때와 순서가 다르기 때문에 한번에 데이터를 가져오는 것을 보장할 수 없습니다.

reference: Real MySQl 1권