클러스터링 인덱스란?
🫧 클러스터링이란?
서로 연관이 있는 데이터들을 하나로 묶는 것을 의미한다.
따라서 클러스터링 인덱스란, 테이블의 레코드를 프라이머리키가 비슷한 것 끼리 묶는 것을 의미한다. 비슷한 시점에 생성된 데이터들을 동시에 조회하는 경우가 많다는 점에서 착안되었다.
중요한 것은 InnoDB 에서는 프라이머리 키에 의해, 삽입 시의 레코드 위치가 결정된다는 것이다. 반면, MyISAM 이나 메모리 스토리지에서는 클러스터링 테이블이 아니기 때문에 처음 삽입될 때 저장된 공간에 고정된다.
아래 그림에서 볼 수 있듯이, PK 값으로 인덱스가 걸려있으며 리프 노드에 실제 레코드의 모든 칼럼(테이블 자체)이 저장되어 있는 것을 볼 수 있다.
☁️ 세컨더리 인덱스에 미치는 영향
SELECT * FROM employees WHERE first_name='Aamer';MyISAM:ix_firstname인덱스를 검색해서 레코드의 주소를 확인한 후, 해당 주소를 이용해 최종 레코드를 가져온다.InnoDB:ix_firstname인덱스를 검색해 레코드의 PK를 확인한 후, 프라이머리 키 인덱스를 검색해서 최종 레코드를 가져온다.
☁️ 클러스터링 인덱스 장점
1. 빠른 조회 성능
이러한 구조 덕분에, PK 기반 다량의 데이터 범위 검색에 매우 뛰어난 조회 성능을 가진다.
만약 클러스터링 테이블이 아니였다면, 조건문에 인덱스 설정이 되어 있지 않은 필드 값이 필요할 때 세컨더리 인덱스로 PK를 설정해서 인덱스를 탄 이후, Random I/O 로 테이블에 한번 더 접근하는 과정이 일어난다. 하지만, 클러스터링 인덱스를 타면 데이터 파일 한번만 접근해서 모든 데이터를 가져올 수 있다.
2. 커버링 인덱스
모든 세컨더리 인덱스가 PK를 가지고 있기 때문에, 커버링 인덱스와 같이 테이블에 접근하지 않고 인덱스로만 처리되는 경우가 많다.
☁️ 클러스터링 인덱스 단점
1. 레코드 위치 변경 시 오버헤드
PK 값이 변경되는 경우, 바뀐 값에 알맞는 위치에 새롭게 추가하고 기존 레코드를 삭제하는 작업이 필요하여 처리 성능이 느리다. 물론, 추가 자체도 위치 결정을 위해 클러스터 인덱스를 한번 더 검색하는 과정이 포함되므로 더 느려진다.
InnoDB 를 제외한 테이블의 데이터 레코드는, PK 값이나 인덱스 키 값이 변경될지라도 실제 레코드의 위치까지 변경되지는 않는다. 참고로 이렇게 레코드가 저장된 주소를 ROW_ID 라고 한다. 그리고 PK나 인덱스의 각 키는, 그 주소를 이용해 실제 데이터의 레코드를 찾아온다.
2. 클러스터링 키 크기에 비례하여 세컨더리 인덱스 크기도 커진다.
모든 세컨더리 인덱스가 PK를 가지고 있으므로 비례하여 커진다.
☁️ InnoDB에서의 주의사항
1. PK 는 꼭 명시하자!
🫧 만약
PK가 없는 테이블은 어떻게 클러스터링 테이블로 구성되는가?
PK 가 없다면, InnoDB 스토리지 엔진이 다음의 우선순위대로 PK를 대체할 칼럼을 선택한다.
PK가 있으면 클러스터링 키로 택NOT NULL옵션의 유니크 인덱스 중 첫번째 인덱스를 택- 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후 택
하지만 3번에서 자동으로 생성된 일련번호 칼럼은 사용자에게 노출되지 않기 때문에, 실질적으로 아무런 소용이 없으므로 반드시 PK 를 명시해 InnoDB 만의 수혜를 얻도록 하는 것이 좋다.
2. AUTO-INCREMENT 칼럼을 식별자로 사용할 경우
만약 PK로 지정하려는 칼럼의 크기가 매우 큰데, 세컨더리 인덱스가 필요한 상황이라면 AUTO_INCREMENT 칼럼을 추가하고 이를 PK로 설정하는 것이 좋다.
INSERT 위주의 테이블들은, 해당 인조 식별자를 프라이머리 키로 설정하는 것이 성능 향상에 도움이 되기 때문이다.
유니크 인덱스란?
유니크 인덱스는, 테이블이나 인덱스에 같은 값이 중복으로 저장될 수 없음을 의미하는 일종의 제약 조건을 가진 일반 인덱스이다. MySQL에서는 인덱스 없이, 유니크 제약만 설정할 방법이 없다.
🫧 Q. 프라이머리 키와 유니크 인덱스가 같은거 아닌가요?
MyISAM 이나 MEMORY 테이블에서 PK 는 NULL이 허용되지 않은 유니크 인덱스와 같지만, InnoDB 기준으로는 맞는 설명이 아니다.
Primary Key: 클러스터링 키의 역할을 하며, MySQL에서는 PK가 생성될 때 자동으로NULL를 허용하지 않는 유니크 속성이 부여된다.유니크 인덱스:NULL이 중복으로 저장될 수 있다.
☁️ 세컨더리 인덱스와의 성능 비교
인덱스 조회
많은 사람들이 유니크 인덱스는 중복이 0 이므로 한번만 읽으면 되지만, 세컨더리 인덱스는 여러 칼럼을 읽어야 하는 경우가 많으므로 조회 성능이 더 느리다고 한다.
하지만, 단순히 읽어야 하는 칼럼이 많은 것인지 인덱스 자체의 특성 때문에 조회 성능이 느려지는 것은 아니므로 유니크와 세컨더리 인덱스 모두 큰 차이가 없다.
인덱스 쓰기
중요하게 봐야 할 것은, 조회가 아닌 인덱스 쓰기 성능이다.
인덱스 쓰기는 유니크 인덱스가 세컨더리 인덱스보다 다음과 같은 이유로 월등히 느리다.
- 삽입 시 중복된 값이 있는지 없는지 확인하는 과정에서 읽기 잠금을 사용한다.
- 중복이 없다면, 데이터를 삽입하는데 이 때 쓰기 잠금을 사용한다.
- 기본적으로 인덱스 쓰기 작업은 체인지 버퍼에서 버퍼링이 되어 모아놓았다가 한꺼번에 처리되도록 하는데, 중복 체크 때문에 버퍼링되지 못한다.
같은 칼럼에 대해 유니크 인덱스와 세컨더리 인덱스를 동일하게 생성하거나, 유니크 인덱스와 PK를 동일하게 생성하는 것은 불필요한 중복이므로 주의하자!
외래키
MySQL에서 외래키는, InnoDB 스토리지 엔진만 생성할 수 있으며 한번 외래키 제약이 설정되면, 자동으로 테이블의 칼럼에 인덱스까지 생성된다.
하지만 외래키는 다음과 같은 특성 때문에 잠금 경합을 발생시켜, 쿼리 처리의 성능 저하를 일으킬 수 있다는 문제점이 있다.
- 테이블 변경(쓰기 잠금)이 발생하는 경우에만, 잠금 대기가 발생한다.
- 외래키와 연관되지 않은 칼럼의 변경은, 최대한 잠금 대기를 발생시키지 않는다.
그렇다면 실제 테스트를 해보자.
☁️ 자식 테이블의 변경이 대기하는 경우
세션을 두개를 만들고, 한쪽은 부모 테이블에서 id가 2 인 레코드에 업데이트를 실행한다. 그러면 해당 레코드에 쓰기 잠금이 걸리고, 다른 세션의 자식 테이블에서 FK로 설정해놓은 부모 id가 2 인 레코드에 접근하려 하는 상황이다.
-
부모 테이블 레코드 변경
-
커밋하지 않은 상태에서 자식 테이블에서 FK 값을 변경
이렇게 되면 자식 테이블의 외래 키 컬럼의 변경은 부모 테이블 확인이 필요한데, 해당 레코드에 쓰기 잠금이 걸려있는 상태이므로 해제될 때까지 기다려야 하는 상황이 발생한다.
🫧 부모 테이블 확인이 필요한 이유?
물리적으로 외래키를 생성하면, 자식 테이블에 레코드가 추가되는 경우 해당 참조키가 실제 부모 테이블에 존재하는지 확인해야 한다. 하지만 이러한 체크 작업에는 읽기 잠금을 걸어야 한다.
이후 부모 테이블에서 Rollback을 하거나 커밋을 하는 경우, 대기가 풀리면서 2번째 세션에서 업데이트에 성공한다.
☁️ 부모 테이블이 대기하는 경우
이 역시 반대로 만약 자식 테이블의 레코드를 변경하고자 해서 쓰기 잠금을 걸었을 때, 해당 참조키를 PK로 가지는 부모 레코드를 삭제하려는 경우 쓰기 잠금이 해제될 때까지 기다려야 하는 상황이 발생한다.
ON DELETE CASCADE 특성 때문에, 부모 레코드가 삭제되면 자동으로 자식 레코드도 삭제되는 방식으로 동작하기 때문에 변경 작업이 끝나기를 기다려야 하는 것이다.
중요한 것은, 참조키 존재 여부 확인을 위해 읽기 잠금을 걸게 되고, 이러한 잠금이 여러 테이블로 확장되면 쿼리의 동시 처리 성능에 영향을 미치게 된다.
