List
Search
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)이 발생할 수 있음