Search

[MySQL] 7. 인덱스 (2) - 클러스터링 인덱스 / 유니크 인덱스 / 외래키

Created
2024/09/09 05:33
Tags
Database
MySQL
Study
Last edited time
2024/09/09 06:48
Status
Done
Search
[MySQL] 7. 인덱스 (2) - 클러스터링 인덱스 / 유니크 인덱스 / 외래키
Database
MySQL
Study
2024/09/09 5:33
[MySQL] 7. 인덱스 (2) - 클러스터링 인덱스 / 유니크 인덱스 / 외래키
Database
MySQL
Study
2024/09/09 5:33

1. 클러스터링 인덱스

1.1. 정의

클러스터링 인덱스는 프라이머리 키를 기준으로 레코드를 묶어 저장하는 방식으로 비슷한 값들이 함께 저장됩니다
InnoDB 스토리지 엔진에서만 사용 가능
프라이머리 키 값이 비슷한 레코드를 물리적으로 가까이 배치해 빠른 조회를 가능

1.2. 특징

프라이머리 키를 기반으로 레코드의 저장 위치가 결정됨
인덱스 알고리즘이라기 보단 테이블 레코드의 저장방식이라고 볼 수 있음
따라서, 클러스터링 인덱스 = 클러스터링 테이블
프라이머리 키가 변경되면 저장 위치도 변경됨
클러스터링 인덱스는 하나의 테이블에만 적용될 수 있음

1.3. 장/단점

장점
프라이머리 키를 통한 검색 속도가 빠르며, 범위 검색에서 매우 유리
또한, 커버링 인덱스(프라이머리 키로 모든 세컨더리 인덱스를 해결할 수 있는 경우)로 작동할 수 있
단점
세컨더리 인덱스를 통한 검색에서 처리 성능이 느림
프라이머리 키를 변경할 때 레코드를 다시 저장해야 하므로 성능 저하가 발생할 수 있음

1.4. 클러스터링 인덱스와 B-Tree

InnoDB의 클러스터링 인덱스도 사실상 B-Tree 구조로 관리됨
하지만 클러스터링 인덱스는 테이블의 데이터 자체를 포함하고 있으며, 다음과 같은 특징이 있음
데이터와 인덱스의 결합
클러스터링 인덱스는 B-Tree 구조로 정렬되어 있지만, 인덱스의 각 노드(리프 노드)는 테이블의 실제 레코드 자체를 포함
클러스터링 인덱스를 통해 데이터를 찾으면, 바로 해당 레코드의 모든 데이터를 가져올 수 있음.
이를 인덱스-오거나이즈드 테이블(Index-Organized Table) 이라고도 부름
B-Tree 구조
클러스터링 인덱스도 실제로는 B-Tree 구조에 기반하고 있음
기본 키를 기준으로 데이터를 정렬하고, B-Tree 구조의 각 노드가 테이블의 레코드를 가리키는 것이 아니라 레코드를 포함하고 있는 형태임
이 때문에 클러스터링 인덱스도 B-Tree 인덱스의 일종으로 간주할 수 있음.

2. 유니크 인덱스

2.1. 정의

동일한 값이 두 번 이상 저장되지 않도록 하는 제약 조건입
InnoDB 테이블에서 프라이머리 키는 기본적으로 유니크한 속성을 가짐
유니크 인덱스에 NULL도 저장될 수 있음. NULL은 특정 값이 아니므로 여러 번 허용될 수 있음

2.2. 유니크 인덱스와 세컨더리 인덱스

유니크 인덱스와 일반 인덱스는 구조적으로 큰 차이가 없으나, 성능 관점에서는 다름
읽기 성능에서는 큰 차이가 없음
쓰기 시 유니크 인덱스는 중복 확인 과정이 추가되므로 느리게 처리될 수 있음
InnoDB는 인덱스 키 저장을 버퍼링 하기 위해 체인지 버퍼 사용함
그러나 유니크 인덱스는 반드시 중복 체크를 해야하므로 버퍼링 불가 → 즉시 디스크에 저장
따라서 체인지 버퍼는 비 유니크 인덱스에만 사용 가능

2.3. 사용시 유의사항

유니크 인덱스는 꼭 필요할 때만 생성할 것
중복해서 유니크 인덱스와 일반 인덱스를 설정하는 것은 불필요
테이블의 유일성을 보장해야 할 경우에만 유니크 인덱스를 생성

3. 외래키

3.1. 정의

자식 테이블의 칼럼이 부모 테이블의 특정 칼럼을 참조하도록 강제하는 제약 조건
InnoDB 스토리지 엔진에서만 사용 가능
외래키가 설정되면 자동으로 인덱스가 생성됨
외래키는 참조 무결성을 유지
Cascade 설정 가능
부모 테이블에 관련된 데이터의 삭제 및 업데이트 시 자식 테이블의 데이터도 함께 변경되도록 설정

3.2. 잠금 메커니즘

InnoDB 외래키 관리의 주요 특징
1.
테이블 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기) 발생 (3.3. 참고)
2.
외래키와 연관되지 않은 컬럼의 변경은 최대한 잠금 경합(잠금 대기) 발생 X (3.4. 참고)
외래키가 있는 테이블에서는 부모 테이블과 자식 테이블 간의 잠금 대기가 발생할 수 있음
예시
CREATE TABLE tb_parent ( id INT NOT NULL, fd VARCHAR(100) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB; 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 ) ENGINE=InnoDB; INSERT INTO tb_parent VALUES (1, 'parent-1'), (2, 'parent-2'); INSERT INTO tb_child VALUES (100, 1, 'child-100');
SQL
복사

3.3. 자식 테이블의 외래키 값 변경

자식 테이블의 외래키 값 변경 시, 부모 테이블에서 해당 레코드가 잠겨 있으면 대기해야함
자식 테이블의 외래키가 아닌 컬럼 변경은 외래키로 인한 잠금 확장이 발생하지 않음
예시)
tb_parent 테이블에서 id = 2 인 레코드에 쓰기 잠금 획득
커넥션-2 쿼리는 부모 테이블의 변경 작업이 완료할때까지 대기
자식 테이블의 외래 키 컬럼 변경은 부모 테이블의 확인 필요
부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있다면, 쓰기 잠금 해제 대기 필요

3.4. 부모 테이블 레코드 삭제

부모 테이블 레코드 삭제시 시, 자식 테이블에서 외래키 관련 레코드가 잠겨 있는 경우 대기해야함
예시)
부모 키 1 을 참조하는 자식 테이블의 레코드 변경 → tb_child 테이블의 레코드 쓰기 잠금 획득
2번 커넥센에서 tb_parent 테이블에서 id = 1 삭제하는 경우, 자식 테이블의 해당 테이블 쓰기 잠금 해제 대기 필요
ON DELETE CASCADE 때문에 부모 레코드 삭제 시 자식 레코드 동시 삭제되기 때문

3.5. 외래키와 읽기 잠금

외래 키 제약이 있는 경우 자식 테이블에 새로운 레코드를 삽입하거나, 외래 키 컬럼 값을 변경할 때, MySQL(InnoDB)은 해당 값이 부모 테이블에서 실제로 존재하는지 확인해야 함
이 확인 과정에서, 부모 테이블의 참조되는 행에 대해 읽기 잠금(Read Lock)이 걸림
부모 테이블의 참조 무결성을 보장하기 위해 중복된 값 또는 존재하지 않는 값을 자식 테이블에 삽입하지 않도록 하기 위한 것
읽기 잠금이 걸리면, 다른 트랜잭션은 부모 테이블의 참조된 데이터를 읽는 것은 가능하지만, 수정(UPDATE)이나 삭제(DELETE) 같은 쓰기 작업을 할 수 없음. 쓰기 잠금이 필요할 경우 해당 트랜잭션은 읽기 잠금이 해제될 때까지 대기하게 됨
예시)
자식 테이블 삽입 시 읽기 잠금 발생
자식 테이블에 외래 키를 참조하는 새로운 레코드를 삽입하면, MySQL은 해당 외래 키 값이 부모 테이블에 존재하는지 확인하기 위해 부모 테이블의 해당 레코드에 읽기 잠금을 검
이 잠금은 레코드가 참조 가능함을 확인한 후에 해제되며, 삽입이 완료됨
다른 트랜잭션의 영향
읽기 잠금이 걸려 있는 동안 다른 트랜잭션이 부모 테이블의 해당 레코드를 수정하거나 삭제하려고 하면, 이 트랜잭션은 쓰기 잠금(Write Lock)을 기다려야 함
즉, 해당 레코드를 수정하거나 삭제하려는 다른 트랜잭션은 대기 상태에 들어감
잠금 경합 가능성
동시성 문제
자식 테이블에 새로운 레코드를 추가하는 작업과 부모 테이블의 데이터를 수정하거나 삭제하려는 작업이 동시에 발생하는 경우, 잠금 경합(lock contention)이 발생할 수 있음
자식 테이블의 삽입 작업이 부모 테이블의 레코드에 읽기 잠금을 걸면, 부모 테이블의 레코드를 수정하거나 삭제하려는 트랜잭션은 쓰기 잠금을 요청하지만, 읽기 잠금이 해제될 때까지 대기해야 함
데드락 발생 가능성
여러 트랜잭션이 복잡하게 얽혀서 서로 잠금을 대기하는 상황이 발생할 수 있음
예시)
트랜잭션 A는 자식 테이블에 삽입을 하면서 부모 테이블의 참조된 행에 읽기 잠금을 검
트랜잭션 B는 부모 테이블에서 해당 행을 수정하려고 쓰기 잠금을 요청하는 상황
잠금이 교차되면서 데드락(Deadlock)이 발생할 수 있음