인덱스: 클러스터링, 유니크 등

☁️ 클러스터링 인덱스

데이터베이스를 조회할 때, 주로 비슷한 값을 통시에 조회하는 경우가 많다고 한다. 이를 착안하여 비슷한 값(보통 프라이머리 키 기준)으로 묶어서 저장하는 것을 클러스터링 인덱스라고 한다.

 

클러스터링 인덱스는 다른 세컨더리 인덱스와는 다르게 인덱스의 값으로 레코드의 물리적인 주소를 가지고 있다. 레코드는 클러스터링 키 값에 의해 저장위치가 결정되며, 특정 레코드의 클러스터링 키의 데이터가 변경된다면 해당 레코드의 물리적인 위치까지 변경되는 코스트 높은 작업이 수행될 것이다. 이처럼 InnoDB에서 레코드는 클러스터링 인덱스에 상당히 의존적이기에 프라이머리 키를 신중하게 결정해야 한다.

 

https://medium.com/@h369kr/clustered-index-vs-non-clustered-index-in-easy-words-2e8a674aafdf

 

InnoDB에서는 각 테이블에 클러스터링 인덱스가 무조건 생성된다. 클러스터링 인덱스는 키 값으로 테이블의 프라이머리 키 없다면 NOT NULL 유니크 키 없다면 InnoDB가 내부적으로 생성한 키를 가지고 값으로 레코드 주소를 가진다. 클러스터링 인덱스가 아닌 세컨더리 인덱스는 인덱스의 값으로 레코드의 주소가 아닌 클러스터링 키 값을 가진다.

 

만약에 InnoDB에서 인덱스가 클러스터링 키값이 아닌 실제 레코드 주소값을 가지고 있다면 어떨까?

만약 인덱스가 이런 클러스터링 키가 아닌 레코드 주소값을 가지는 경우 클러스터링 키 값 변경에 대해 모든 인덱스가 인덱스의 값을 변경하는 작업을 수행해야 할 것이고, 성능 오버헤드가 발생할 것이다. 따라서 InnoDB에서 인덱스의 값은 실제 레코드의 주소가 아닌 클러스터링 키 값을 가지는 것이다.

 

대부분 클러스터링 인덱스의 장점은 빠른 읽기이며, 단점은 느린 쓰기이다. 일반적으로 웹 서비스와 같은 온라인 트랜잭션 환경에서는 쓰기와 읽기 비율이 2:8 혹은 1:9 정도이기에 조금 느린 쓰기를 감수하고 읽기를 빠르게 유지하는 것은 매우 중요하다.
- Real MySQL 8.0 1

 

InnoDB의 프라이머리 키는 클러스터링 키로 사용되며, 이 값에 의해 레코드의 물리적인 위치가 결정된다고 했다. 즉, 프라이머리 키로 검색하는 경우(특히 범위로 많은 레코드를 검색하는 경우) 클러스터링되지 않은 테이블에 비해 매우 빠르게 처리될 수 있음을 의미한다. 따라서 프라이머리 키에 의한 검색이 빈번한 경우 설령 클러스터링 키의 크기가 커지는 한이 있더라도 프라이머리 키를 의미있는 키로 설정하는 것이 좋다.

로그 테이블과 같이 INSERT 위주의 테이블은 AUTO_INCREMENT를 이용한 인조 식별자를 프라이머리 키로 설정하는 것이 성능 향상에 도움이 된다.

 

☁️ 유니크 인덱스

유니크 인덱스는 일반 세컨더리 인덱스와 구조상 아무런 차이점이 없다. 데이터 그저 Insert 작업 시 중복 검사를 수행하는 체크하는 과정이 한 단계 더 필요하다는 차이점이 있다.

유니크 인덱스의 Insert 작업은 중복된 값을 체크할 때 읽기 잠금을 사용하고, 쓰기 작업 시 쓰기 잠금을 획득하여 사용한다. 또한 세컨더리 인덱스의 저장과 변경은 체인지 버퍼를 통해 버퍼링되지만 유니크 인덱스는 중복 체크를 위해 바로 수행되어야 한다는 차이점이 있다.

 

☁️ 가상 컬럼 인덱스

CREATE TABLE user (
    user_id BIGINT,
    first_name VARCHAR(10),
    last_name VARCHAR(10),
    PRIMARY KEY (user_id)
);

위와 같은 테이블이 존재한다고 가정했을 때

ALTER TABLE user
    ADD full_name VARCHAR(30) AS (CONCAT(first_name,' ',last_name)) VIRTUAL,
    ADD INDEX ix_fullname (full_name);

이처럼 가상 컬럼을 생성하고, 가상 컬럼을 인덱스로 설정할 수 있다. 가상 컬럼은 테이블에 새로운 컬럼을 추가하는 것과 동일한 효과를 내기 때문에 실제 테이블 구조가 변경된다는 단점이 있다.

 

☁️ 함수 인덱스

CREATE TABLE user ( 
    user_id BIGINT, 
    first_name VARCHAR(10), 
    last_name VARCHAR(10), 
    PRIMARY KEY (user_id), 

    INDEX ix_fullname ((CONCAT(first_name,' ',last_name))) 
);

위의 코드 처럼 테이블 구조를 변경하지 않고 함수를 통해 계산된 결괏값으로 인덱스를 생성한다. 함수 인덱스를 활용하려면 Where 조건 절에 인덱스를 생성한 함수 표현식 그대로 사용되어야 한다. 표현식이 다르다면 함수 기반 인덱스를 사용하지 못한다.

 

☁️ 외래키와 잠금 전파

외래 키는 자동으로 인덱스로 설정된다. 따라서 외래 키를 변경한다면 외래 키의 컬럼을 가지고 있는 부모 테이블의 레코드에도 쓰기 락이 걸리게 된다. 반대로 자식 테이블에서 외래 키로 선언되어 있는 부모 테이블의 컬럼을 변경하더라도 자식 테이블에서 해당 외래 키를 포함하는 레코드에 쓰기락이 걸리게 된다. 이는 부모 레코드가 삭제되면 해당 외래 키를 가지는 자식 레코드도 삭제되는 특성을 가지고 있기 때문이다.

이처럼 외래 키를 선언하려는 경우에는 잠금이 전파된다는 점을 잘 고려하여야 한다.