etc_index

클러스터링 인덱스

클러스터링 인덱스는 PK에만 적용되는 내용으로, 즉 PK 값이 비슷한 레코드끼리 묶어서 저장하는 것을 의미한다. 여기서 저장되는 위치는 실제 물리적인 저장 위치를 의미하며, 변경되는 경우엔 해당 레코드의 물리적인 위치도 변경되어야 한다.

테이블 구조 자체는 일반 B-Tree와 비슷하지만, 클러스터링 인덱스의 리프노트에는 레코드의 모든 컬럼이 저장된다. 클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조로 관리되며, PK 값이 없는 경우엔 아래의 규칙으로 PK를 생성한다.

1. 지정된 PK를 사용

2. PK가 없다면 NOT NULL + 유니크 인덱스 중 첫 번째 컬럼을 PK로 사용

3. 둘 다 해당하지 않는다면 AUTO_INCREMENT를 사용한 유니크 컬럼을 추가하여 PK로 사용(노출되지 않으며 쿼리에서 사용 불가능)

세컨더리 인덱스가 PK를 참조하는 이유

InnoDB에서는 세컨더리 인덱스에서 실제 레코드가 아닌, PK를 참조하고 있다. 만약 PK가 아닌 실제 레코드 주소를 참조했을 경우 아래의 이유로 성능상 문제가 발생하게 된다.

1. 세컨더리 인덱스에서 실제 레코드 주소를 가지고 있음

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

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

4. 오버헤드 발생

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

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

• 장점

  • PK로 검색할 때 빠른 성능을 보장

  • 테이블의 모든 세컨더리 인덱스가 PK를 갖고 있기 때문에 인덱스만으로 처리할 수 있는 경우가 많음

• 단점

  • 테이블의 모든 세컨더리 인덱스가 PK를 갖고 있기 떄문에 PK 값의 크기가 커질수록 세컨더리 인덱스의 크기도 커짐

  • 세컨더리 인덱스를 통한 조회 시 PK로 다시 한 번 조회해야 하기 때문에 성능 저하가 발생할 수 있음

  • 데이터 추가 및 변경 시 PK에 의해 레코드 저장 위치가 변경되어야 하기 때문에 처리 성능이 느림

유니크 인덱스

테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없게 하는 제약을 의미하며, MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다. 유일하다는 점에서 PK와 유사하지만, 다음과 같은 차이점이 존재한다.

• PK는 NULL을 허용하지 않지만, 유니크 인덱스는 NULL을 허용

• 클러스터링 인덱스를 사용하지 않음(= 논클러스터링 인덱스)

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

• 읽기: 읽는 건수가 같다면 동일한 속도로 처리

• 쓰기: 일반적인 인덱스 쓰기 작업에 추가적으로 중복 체크 과정이 수행되어 성능 저하 발생

  • 중복값 체크 시엔 읽기 잠금 & 쓰기 할 때 쓰기 잠금이 사용되어 데드락이 발생할 수 있음

외래키

외래키 제약이 설정되면 자동으로 연관되는 테이블의 컬럼에 인덱스가 생성되며, InnoDB에서는 두 가지 중요한 특징이 있다.

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

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

테이블과 레코드가 아래와 같이 생성되어 있을 때, 자식/부모 테이블의 변경 대기에 대해 알아보자.

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');

자식 테이블의 변경 대기

커넥션 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번 커넥션은 대기

자식 테이블의 외래 키 컬럼은 부모 테이블의 확인이 필요하므로, 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면 해당 쓰기 잠금이 해제될 때까지 대기하게 된다. 하지만 자식 테이블의 외래키(pid)가 아닌 컬럼은 부모 테이블의 변경 여부와 상관없이 변경이 가능하다.

부모 테이블의 변경 대기

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

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

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

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

자식 테이블이 생성될 때 정의된 외래키 특성(ON DELETE CASCADE) 때문에 대기하는 것으로, 부모 테이블의 레코드가 삭제되면 자식 테이블의 레코드도 함께 삭제 되어야 하기 때문이다.

그 외

전문 검색 인덱스 / 멀티 밸류 인덱스 / 클러스터링 인덱스 / R-Tree 인덱스 / 함수 기반 인덱스 등이 존재한다.

참고자료

• Real MySQL 8.0 (1권)