본 포스트는 Real MySQL 8.0 1권을 읽은 뒤 정리하는 글입니다.
8.8 클러스터링 인덱스
8.8.1 클러스터링 인덱스
클러스터링 인덱스는 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 의미한다. 프라이머리 키 값에 의해 레코드의 저장 위치가 결정 및 변경되기 때문에 프라이머리 키의 의존도가 상당히 크며, 인덱스 알고리즘이라기보다 테이블 레코드 저장 방식이라고 볼 수 있어 클러스터링 테이블이라고도 한다.
클러스터링의 기준이 되는 프라이머리 키는 클러스터링 키라고도 표현한다. InnoDB와 같이 항상 클러스터링 인덱스로 저장되는 테이블은 프라이머리 키 기반의 검색이 매우 빠르며, 대신 레코드의 저장이나 프라이머리 키 변경이 상대적으로 느리다.
위 이미지는 클러스터링 테이블의 구조로 일반 B-Tree와 비슷하지만, 세컨더리 인덱스를 위한 B-Tree의 리프 노드와는 달리 클러스터링 인덱스의 리프 노드에는 레코드의 모든 칼럼이 같이 저장되어 있다. 즉, 클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조로 관리되는 것이다.
기존 클러스터링 테이블에서 다음 쿼리와 같이 프라이머리 키를 변경하는 문장이 실행될 때 클러스터링 테이블의 데이터 레코드의 변화를 살펴보자.
mysql> UPDATE tb_test SET emp_no=100002 WHERE emp_no=100007;
3번 페이지에 저장되어 있던 emp_no가 100007인 레코드의 emp_no가 100002로 변경되며 2번 페이지로 이동한 것을 알 수 있다. 실제로 프라이머리 키의 값이 변경되는 경우는 거의 없지만, 클러스터링 테이블에서의 프라이머리 키 값의 중요성을 강조하고 작동 방식을 설명하기 위해 프라이머리 키 값의 변경 과정을 살펴보았다.
프라이머리 키가 없는 InnoDB 테이블의 경우 다음 우선순위대로 프라이머리 키를 대체할 칼럼을 선택한다.
- 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
NOT NULL옵션의 유니크 인덱스 중에서 첫 번째 인덱스를 클러스터링 키로 선택- 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 추가한 후, 클러스터링 키로 선택
8.8.2 세컨더리 인덱스에 미치는 영향
세컨더리 인덱스가 실제 레코드가 저장된 주소를 가지고 있다면 클러스터링 키 값이 변경될 때마다 데이터 레코드의 주소가 변경되고 그때마다 해당 테이블의 모든 인덱스에 저장된 주솟값을 변경해야 할 것이다. 이런 오버헤드를 제거하기 위해 InnoDB 클러스터링 테이블의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 프라이머리 키 값을 저장하도록 구현돼 있다.
8.8.3 클러스터링 인덱스의 장점과 단점
장점
-
프라이머리 키(클러스터링 키)로 검색할 때 처리 성능이 매우 빠름(특히, 프라이머리 키를 범위 검색하는 경우 매우 빠름)
-
테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음
단점
-
테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐
-
세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 처리 성능이 느림
-
INSERT할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느림 -
프라이머리 키를 변경할 때 레코드를
DELETE하고INSERT하는 작업이 필요하기 때문에 성능이 느림
8.8.4 클러스터링 테이블 사용 시 주의사항
클러스터링 인덱스 키의 크기
클러스터링 테이블의 경우 모든 세컨더리 인덱스가 프라이머리 키(클러스터링 키) 값을 포함하므로, 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 커진다. 또한 일반적으로 테이블에 세컨더리 인덱스가 4~5개 정도 생성된다는 것을 고려하면 세컨더리 인덱스 크기는 급격히 증가한다. 추가로 인덱스가 커질수록 같은 성능을 내기 위해 그만큼의 메모리가 더 필요해지므로 InnoDB 테이블의 프라이머리 키는 신중하게 선택해야 한다.
프라이머리 키는 AUTO-INCREMENT보다는 업무적인 칼럼으로 생성(가능한 경우)
프라이머리 키로 검색하는 경우 클러스터링되지 않은 테이블에 비해 매우 빠르게 처리될 수 있다. 프라이머리 키는 중요한 역할을 하기 때문에 대부분 검색에서 빈번하게 사용된다. 설령 그 칼럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 칼럼을 프라이머리 키로 설정하는 것이 좋다.
프라이머리 키는 반드시 명시할 것
InnoDB 테이블에서 프라이머리 키를 정의하지 않으면 InnoDB 스토리지 엔진이 내부적으로 일련번호 칼럼을 추가한다. 이렇게 추가된 칼럼은 사용자에게 보이지 않기 때문에 사용자가 접근할 수 없다. 이는 AUTO_INCREMET 칼럼을 생성하고 프라이머리 키로 설정하는 것과 같으므로 사용자가 사용할 수 있는 값(AUTO_INCREMENT 값)을 프라이머리 키로 설정하는 것이 좋다.
AUTO-INCREMENT 칼럼을 인조 식별자로 사용할 경우
프라이머리 키의 크기가 길어도 세컨더리 인덱스가 필요치 않다면 그대로 프라이머리 키를 사용하는 것이 좋다. 세컨더리 인덱스도 필요하고 키의 크기도 길다면 AUTO_INCREMENT 칼럼을 추가하고, 이를 프라이머리 키로 설정하면 된다. 또한 로그 테이블과 같이 조회보다는 INSERT 위주의 테이블을 AUTO_INCREMENT를 이용한 인조 식별자를 프라이머리 키로 설정하는 것이 성능 향상에 도움이 된다.
프라이머리 키를 대체하기 위해 인위적으로 추가된 프라이머리 키를 인조 식별자(Surrogate Key)라고 한다.
8.9 유니크 인덱스
8.9.1 유니크 인덱스와 일반 세컨더리 인덱스의 비교
인덱스 읽기
많은 사람들이 유니크하지 않은 세컨더리 인덱스가 더 느리다고 생각한다. 하지만 유니크하지 않은 세컨더리 인덱스는 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 느린 것이지, 인덱스 자체의 특성 때문에 느린 것이 아니다. 즉, 읽어야 할 레코드의 건수가 같다면 성능상의 차이는 미미하다.
인덱스 쓰기
새로운 레코드드가 INSERT되거나 인덱스 칼럼의 값이 변경되는 경우 인덱스 쓰기 작업이 필요하다. 유니크 인덱스의 키 값을 쓸 때는 중복을 체크하는 과정이 더 필요해 유니크하지 않은 세컨더리 인덱스보다 느리다.
MySQL에서는 유니크 인덱스에서 중복된 값을 체크할 때는 읽기 잠금을, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 아주 빈번히 발생한다. 또한 InnoDB 스토리지 엔진에는 인덱스 키의 저장을 버퍼링 하기 위해 체인지 버퍼를 사용하여 인덱스의 저장이나 변경 작업이 상당히 빨리 처리되지만, 유니크 인덱스는 반드시 중복 체크를 해야 하므로 작업 자체를 버퍼링하지 못한다.
8.9.2 유니크 인덱스 사용 시 주의사항
꼭 필요한 경우 유니크 인덱스를 생성하는 것은 당연하지만, 성능이 더 좋아질 것으로 생각하고 불필요하게 유니크 인덱스를 생성하지 않는 것이 좋다. 또한 하나의 테이블에서 같은 칼럼에 유니크 인덱스와 일반 인덱스를 중복해서 생성하는데, 유니크 인덱스는 일반 다른 인덱스와 같은 역할을 하므로 중복해서 생성할 필요는 없다. 프라이머리 키의 경우도 불필요한 중복이므로 주의하자.
8.10 외래키
MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있으며, 외래키 제약이 설정되면 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성된다.
InnoDB의 외래키 관리 특징
-
테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.
-
외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않는다.
다음과 같은 테이블에서 자식 테이블과 부모 테이블의 변경이 언제 잠금 대기를 하는지 살펴보자.
mysql> CREATE TABLE tb_parent (
id INT NOT NULL,
fd VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
)ENGINE=InnoDB;
mysql> 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 chuld_ibfk_1 FOREIGHN KEY (pid) REFERENCES tb_parent (id) ON DELETE CASCADE
)ENGINE=InnoDB;
mysql> INSERT INTO tb_parent VALUES (1, 'parent-1'), (2, 'parent-2');
mysql> INSERT INTO tb_child VALUDE (100, 1, 'child-100');8.10.1 자식 테이블의 변경이 대기하는 경우
이 작업에서는 1번 커넥션에서 먼저 트랜잭션을 시작하고 부모(tb_parent) 테이블에서 UPDATE를 실행하여 id가 2인 레코드에 대해 쓰기 잠금을 획득한다. 그리고 2번 커넥션에서 자식 테이블(tb_child)의 외래키 칼럼인 pid를 2로 변경하는 쿼리를 실행하는 경우, 부모 테이블의 변경 작업이 완료될 때까지 대기하고 1번 커넥션의 트랜잭션이 종료되면 2번 커넥션의 대기 중이던 작업이 즉시 처리되는 것을 확인할 수 있다.
즉, 자식 테이블의 외래 키 칼럼의 변경(INSERT, UPDATE)은 부모 테이블의 확인이 필요한데, 이 상태에서 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면 해당 쓰기 잠금이 해제될 때까지 기다리게 되는 것이다.
8.10.2 부모 테이블의 변경 작업이 대기하는 경우
첫 번째 커넥션에서 부모 키 "1"을 참조하는 자식 테이블의 레코드를 변경하면 tb_child 테이블의 레코드에 대해 쓰기 잠금을 획득한다. 이 상태에서 2번 커넥션이 tb_parent 테이블에서 id가 1인 레코드를 삭제하는 경우 tb_child 테이블의 레코드에 대한 쓰기 잠금이 해제될 때까지 기다려야 한다. 이는 자식 테이블이 생성될 때 정의된 외래키의 특성때문에 부모 레코드가 삭제되면 자식 레코드도 동시에 삭제되는 식으로 작동하기 때문이다.
