etc_index

클러스터링 인덱스(Clustering Index)

클러스터형 인덱스는 프라이머리 키(PK) 값의 순서에 따라 테이블의 데이터가 물리적으로 정렬되어 저장되는 방식을 의미한다.

• 변경되는 경우엔 해당 레코드의 물리적인 위치도 변경

• 테이블 구조 자체는 일반 B-Tree와 비슷하지만, 클러스터링 인덱스의 리프노드에 레코드의 모든 컬럼이 저장

InnoDB 테이블은 반드시 클러스터형 인덱스를 가지며, 아래의 우선순위에 따라 클러스터링 키를 결정한다.

1. 명시적으로 지정된 프라이머리 키(Primary Key)

2. 첫 번째 NOT NULL + 유니크 인덱스(Unique Index).

3. 내부적으로 6바이트 크기의 숨겨진 ROW_ID 컬럼 생성

보조 인덱스가 PK를 참조하는 구조

InnoDB의 모든 보조 인덱스는 리프 노드에 데이터의 물리적 주소 대신 프라이머리 키 값을 저장하게 되는데, 이는 다음과 같은 이유 때문이다.

1. 보조 인덱스가 실제 레코드 주소를 가짐

2. 실제 레코드 주소의 PK 변경 시 레코드의 주소가 변경 필요(클러스터링 된 상태여야 하기 때문)

3. 그때마다 해당 테이블의 모든 인덱스에 대해 변경 작업 수행 필요

4. 오버헤드 발생

PK를 클러스터링 인덱스로 사용하는 이유

PK를 클러스터링 인덱스로 사용하는 이유는 읽기 성능을 향상시키기 위함이고, 그 장단점은 다음과 같다.

• 장점

  • 프라이머리 키 기반의 범위 검색(Range Scan) 시, 데이터가 물리적으로 인접해 있어 빠른 I/O 성능 제공

  • 보조 인덱스가 프라이머리 키 값을 포함하므로, 쿼리에 필요한 모든 컬럼이 보조 인덱스와 프라이머리 키에 있다면 커버링 인덱스 사용 가능

• 단점

  • 모든 보조 인덱스가 PK 값을 포함하므로, PK의 크기가 크면 보조 인덱스의 전체 크기도 상승

  • 데이터 삽입 시 PK 값에 따라 물리적 저장 위치가 결정되므로, 순차적이지 않은 PK 값(예: UUID)을 사용하면 페이지 분할(Page Split)이 빈번하게 발생하여 쓰기 성능이 저하 발생

  • 보조 인덱스를 통한 조회는 PK를 이용한 2차 조회로 추가적인 오버헤드 발생

유니크 인덱스(Unique Index)

유니크 인덱스는 특정 컬럼 또는 컬럼 조합에 대해 중복된 값이 저장될 수 없도록 보장하는 제약 조건이자 인덱스로, PK와는 다음과 같은 차이가 있다.

• 하나의 테이블에 여러 개 생성 가능

• NULL 값 허용

• 유니크 인덱스는 클러스터형 인덱스가 아닌, 비클러스터형(보조) 인덱스로 생성

유니크 인덱스는 일반 인덱스와 동일한 역할을 수행하지만, 읽기와 쓰기에 있어서는 일반 인덱스와 다음과 같은 차이점이 존재한다.

• 읽기: 조회하려는 데이터 건수가 명확할 경우, 일반적인 보조 인덱스와 성능상 큰 차이 없음

• 쓰기: 데이터를 추가하거나 수정할 때마다 유일성(Uniqueness)을 검증 과정 필요

  • 이 중복 체크 과정은 추가적인 디스크 읽기를 유발하여 일반 보조 인덱스보다 쓰기 성능 저하

  • 유니크 인덱스는 중복 여부를 즉시 확인해야 하므로, 쓰기 성능을 최적화하는 체인지 버퍼(Change Buffer) 사용 불가능

  • 중복 체크를 위한 읽기 잠금과 실제 데이터 삽입을 위한 쓰기 잠금이 경합하여 데드락이 발생할 가능성 존재

외래키(Foreign Key)

외래 키는 두 테이블 간의 관계를 정의하여 데이터의 참조 무결성을 보장하는 제약 조건으로, 참조하는 컬럼에 자동으로 인덱스가 생성된다.

외래키와 잠금

InnoDB에서 외래 키는 잠금(Lock)과 밀접한 관련이 있으며 두 가지 중요한 특징이 있다.

• 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 대기 발생

• 외래키와 연관되지 않은 컬럼의 변경은 최대한 잠금 대기를 발생시키지 않음

-- 샘플 테이블 및 데이터
CREATE TABLE tb_parent
(
    id INT          NOT NULL,
    fd VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE tb_child
(
    id  INT NOT NULL,
    pid INT          DEFAULT NULL, # parent id
    fd  VARCHAR(100) DEFAULT NULL,
    PRIMARY KEY (id),
    KEY ix_parentid (pid),
    CONSTRAINT child_ibfk_1 FOREIGN KEY (pid) REFERENCES tb_parent (id) ON DELETE CASCADE
);

INSERT INTO tb_parent
VALUES (1, 'parent-1'),
       (2, 'parent-2');
INSERT INTO tb_child
VALUES (100, 1, 'child-1');

자식 테이블의 변경 대기

자식 테이블의 외래 키 컬럼 값을 변경할 때, 변경하려는 값이 부모 테이블에 실제로 존재하는지 확인해야 한다.

• 부모 테이블 확인 과정에서 부모 테이블의 해당 레코드에 공유 잠금(S-Lock) 필요

• 다른 트랜잭션이 부모 레코드를 변경 중(X-Lock 보유)이라면 대기 발생

커넥션 1	커넥션 2
BEGIN;
UPDATE tb_parent SET fd='changed-2' WHERE id=2;
	BEGIN;
	UPDATE tb_child SET pid=2 WHERE id=100;
ROLLBACK;	커넥션 1 대기
	COMMIT;

1. 1번 커넥션에서 부모 테이블 id 2인 레코드에 대해 쓰기 잠금 획득

2. 2번 커넥션에서 자식 테이블의 외래키 컬럼(pid)을 pid 2로 변경하는 쿼리 실행

3. 부모 테이블의 변경 작업이 완료될 때까지 2번 커넥션은 대기

부모 테이블의 변경 대기

부모 테이블의 키를 변경하거나 레코드를 삭제할 때, 해당 키를 참조하는 자식 테이블의 레코드가 있는지 확인해야 한다.

• 부모 테이블 변경 시 자식 테이블의 연관 레코드에 잠금을 설정

• 다른 트랜잭션이 자식 레코드를 변경 중이라면 대기 발생

커넥션 1	커넥션 2
BEGIN;
UPDATE tb_child SET fd='changed-100' WHERE id=100; 부모 id 1을 참조하는 자식 테이블 레코드에 접근
	BEGIN;
	DELETE FROM tb_parent WHERE id=1;
ROLLBACK;	커넥션 1 대기
	COMMIT;

1. 1번 커넥션에서 부모키 1을 참조하는 자식 테이블 레코드에 대해 쓰기 잠금 획득

2. 2번 커넥션에서 부모 테이블의 id 1에 대해 삭제하는 쿼리 실행

3. 자식 테이블의 변경 작업이 완료될 때까지 2번 커넥션은 대기

그 외

• 전문 검색 인덱스(Full-Text Index): 일반적인 B-Tree 인덱스로는 검색이 어려운 장문 텍스트(기사 본문 등) 내의 단어나 구문을 효율적으로 검색하기 위한 인덱스

• 공간 인덱스(Spatial Index): 위치 정보나 지도 데이터와 같은 2차원 공간 데이터를 처리하기 위한 인덱스(R-Tree 기반)

• 다중값 인덱스(Multi-Valued Index): 하나의 데이터 레코드에 여러 개의 값이 포함될 수 있는 JSON 배열과 같은 데이터 타입을 위해 설계된 인덱스

• 함수 기반 인덱스(Function-Based Index): 컬럼의 값을 그대로 인덱싱하는 것이 아니라, 특정 함수나 표현식을 적용한 결과를 인덱싱하는 방식

참고자료

• Real MySQL 8.0 (1권)