Real MySQL 4

MINJU·2023년 9월 24일

8.7 멀티 벨류 인덱스 ~ 8.10 외래키

1. 멀티 밸류 인덱스

"전문 검색 인덱스"를 제외한 모든 인덱스는 레코드 한 건이 한 개의 인덱스 키 값을 가진다.

하지만 멀티 밸류 인덱스는 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스이다.

최근 RDBMS들이 JSON 데이터 타입을 지원하기 시작하면서 JSON의 배열 타입 필드에 저장된 원소들에 대한 인덱스 요건이 발생하여 등장하게 되었다. 그래서 MySQL은 8.0으로 업데이트되면서 JSON 관리 기능을 강화했다.



CREATE TABLE user (
	user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(10),
    last_name VARCHAR(10),
    credit_info JSON, // 이거!
    INDEX my_creditscores( (CAST(credit_info -> '$.credit_scores' AS UNSIGNED ARRAY)) ));

위와 같은 신용 정보 점수를 "배열"로 "JSON" 타입 칼럼에 저장하는 테이블을 생성했다고 해보자.

INSERT INTO user
VALUES
(1, 'Matt', 'Lee', '{"credit_scores":[360, 353, 351]}');

해당 테이블에 대해 신용 점수를 계산하는 쿼리를 살펴보자.

❗ 멀티 밸류 인덱스를 활용하려면 MEMBER OF() JSON_CONTAINS() JSON_OVERLAPS() 함수를 사용해서 검색해야 옵티마이저가 멀티 밸류 인덱스를 활용한 실행 계획을 수립한다.

SELECT *
FROM user
WHERE 360 MEMBER of(credit_info->'$.credit_scores');

2. 클러스터링 인덱스

MySQL 서버에서 클러스터링은 테이블의 레코드를 비슷한 것(primary key를 기준으로)들끼리 묶어서 저장하는 형태로 구현된다.

왜냐하면 비슷한 것들을 동시에 조회하는 경우가 많기 때문이다.

MySQL 클러스터링 인덱스는 InnoDB 엔진에서만 지원된다.

(1) 클러스터링 인덱스

클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용된다.
여기서 중요한 것은 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다는 것이다. 또한 프라이머리 키 값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야 한다는 것을 의미하기도 한다.

즉, 프라이머리 키 값으로 클러스터링된 테이블은 프라이머리 키 값 자체에 대한 의존도가 상당히 크다! 따라서 신중히 프라이머리 키를 결정해야 한다.

클러스터링 인덱스는 프라이머리 키 값에 의해 "레코드의 저장 위치"가 결정되므로 사실 인덱스 알고리즘이라기보단 테이블 레코드의 저장 방식이라고 볼 수 있다. 그래서 "클러스터링 인덱스=클러스터링 테이블"이라고 보기도 한다.

InnoDB와 같이 항상 클러스터링 인덱스로 저장되는 테이블은 프라이머리 키 기반의 검색이 매우 빠르다. 하지마, 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느리다.

클러스터링 테이블의 구조 자체는 B-Tree와 비슷하다.
하지만, 클러스터링 인덱스의 리프 노드에는 레코드의 모든 칼럼이 같이 저장되어있음을 알 수 있다. 즉, 클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조로 관리되는 것이다.

그렇다면 프라이머리키가 없는 테이블은 어떻게 클러스터링 테이블로 구성될까?

프라이머리키가 없는 경우에는 InnoDB 스토리지 엔진이 아래와 같은 우선순위대로 프라이머리 키를 대체할 칼럼을 선택한다.

프라이머리 키가 있으면 프라이머리키 선택
NOT NULL 옵션의 UNIQUE INDEX 중에서 첫 번째 인덱스
자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후, 클러스터링 키로 선택

3번의 경우 InnoDB 스토리지 엔진이 내부적으로 일련번호 칼럼을 생성하는 경우를 의미한다. 이렇게 생성된 프라이머리키는 사용자에게 노출되지 않고, 쿼리 문장에 명시적으로 사용할 수 없다.

(2) 세컨더리 인덱스에 미치는 영향

프라이머리 키가 세컨더리 인덱스에 어떤 영향을 미치는지 알아보자. (위의 내용은 프라이머리 키가 데이터 레코드의 저장에 미치는 영향을 설명한 내용이다.)

클러스터링되지 않은 테이블은 INSERT될 때 처음 저장된 공간에서 절대 이동하지 않는다.

데이터 레코드가 저장된 "주소"는 내부적인 ROWID 역할을 한다. 그리고 "프라이머리 키"나 "세컨더리 인덱스"의 각 키는 그 ROWID를 이용해 실제 데이터 레코드를 찾아온다.

그래서 클러스터링 되지 않은 테이블에서는 프라이머리 키와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다. (어차피 ROWID 값으로 가져오고.. 이동하지 않기 때문에)

그렇다면 InnoDB 테이블에서 세컨더리 인덱스가 실제 레코드가 저장된 주소를 가지고 있다면?
💦 클러스터링 키 값이 변경될 때마다 데이터 레코드의 주소가 변경되고, 그때마다 해당 테이블의 모든 인덱스에 저장된 주소 값을 변경해야할 것이다.

이런 오버헤드 가능성을 제거하기 위해 클러스터링 테이블(InnoDB 테이블)의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 프라이머리 키 값을 저장하도록 구현되어 있다.

employees 테이블에서 first_name 칼럼으로 검색하는 경우에 대해 생각해보자. 클러스터링 테이블(InnoDB)과 그렇지 않은 테이블(MyISAM)에서 어떤 차이가 있을까?

CREATE TABLE employees (
	emp_no INT NOT NULL,
    first_name VARCHAR(20) NOT NULL,
    PRIMARY KEY (emp_no),
    INDEX ix_firstname (first_name)
    );

위와 같은 테이블이 있고

SELECT  *
FROM employees
WHERE first_name = 'Aamer';

인 상황을 의미한다.

MyISAM의 경우

ix_firstname 인덱스를 검색해서 레코드의 주소를 확인한 후, 레코드의 주소를 이용해 최종 레코드를 가져올 것이다.

InnoDB의 경우

ix_firstname 인덱스를 검색해서 레코드의 프라이머리 키 값을 확인한 후, 프라이머리 키 인덱스를 검색해서 최종 레코드를 가져올 것이다.

InnoDB 테이블에서 좀 더 복잡한 처리가 이뤄지긴 하지만 InnoDB 테이블에서 프라이머리키(클러스터링 인덱스)의 장점이 더 크기 때문에 너무 걱정하지 않아도 된다고 한다. 😀

(3) 클러스터링 인덱스의 장단점

클러스터링 되지 않은 "일반 프라이머리 키"와 "클러스터링 인덱스"를 비교해보자

장점은

프라이머리 키(클러스터링 키)로 검색할 때 성능이 매우 빠르다.
테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많다.

단점은

테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커진다.
세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한 번 검색해야 한다.
INSERT할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되므로 성능이 느리다.
프라이머리 키를 변경할 때 레코드를 DELETE하고 다시 INSERT 해야한다.

웹서비스와 같이 쓰기와 읽기의 비율이 2:8 또는 1:9인 온라인 트랜잭션 환경(OLTP)에서는 클러스터링 인덱스를 사용하여 조금 느린 쓰기를 감수하고 빠른 읽기을 유지하는 것도 좋은 선택이 될 수 있따.

(4) 클러스터링 테이블 사용 시 주의사항

<1> 클러스터링 인덱스 키의 크기

모든 세컨더리 인덱스가 프라이머리 키 값을 포함하기 때문에, 프라이머리 키의 크기가 커지만 세컨더리 인덱스도 자동으로 크기가 커지기 때문에 고려가 필요하다.

<2> 프라이머리 키는 AUTO-INCREMENT보다는 업무적인 칼럼으로 생성하기

가능한 경우 이렇게 하는 것이 더 낫다는 것을 의미한다.

프라이머 키가 업무적으로 해당 레코드를 대표할 수 있다면, 해당 칼럼을 프라이머리 키로 선택하여 빠른 검색을 수행할 수 있도록 만드는 것이 중요하다.

<3> 프라이머리 키는 반드시 명시할 것

AUTO_INCREMENT 칼럼을 활용해서라도 생성하는 것을 권장한다. 프라이머리 키가 없으면 어차피 InnoDB가 AUTO_INCREMT를 활용한 칼럼을 만드는데 이는 사용자가 전혀 활용할 수 없는 칼럼이 되기 때문이다.

<4> AUTO_INCREMENT 칼럼을 인조 식별자로 사용할 경우

여러 개의 칼럼이 "복합"으로 프라이머리 키가 만들어지는 경우 프라이머리 키의 크기가 길어질 때가 가끔 있다.

하지만, 프라이머리 키의 크기가 길어도 세컨더리 인덱스가 필요치 않다면 그대로 프라이머리 키를 사용하는 것이 좋다.

세컨더리 인덱스도 필요한데 프라이머리 키도 길다면 AUTO_INCREMENT 칼럼을 프라이머리 키로 활용하자. (프라이머리 키를 대체하기 위해 인위적으로 추가된 프라이머리 키를 인조 식별자라고 한다.)

로그 테이블 같이 조회보다는 INSERT 위주로 동작되는 테이블은 인조 식별자를 프라이머리 키로 설정하는 것이 성능에 더 도움이 된다.

3. 유니크 인덱스

테이블이나 인덱스에 같은 값이 두 개 이상 저장될 수 없음을 의미하는데, 사실 제약조건에 더 가깝다.

(1) 유니크 인덱스와 일반 세컨더리 인덱스의 비교

위의 두 가지는 인덱스의 구조상에선 아무런 차이점이 없다.
대신 성능 관점에서 살펴보자.

<1> 인덱스 읽기

유니크 인덱스는 한 건만 읽으면 되지만, 유니크하지 않은 세컨더리 인덱스에서는 레코드를 한 건 더 읽어야 하기 때문에 느리다고 알려져있다.

하지만 유니크하지 않은 세컨더리 인덱스에서 한 번 더 해야하는 작업은 디스크 읽기가 아니라 CPU에서 칼럼값을 비교하는 작업이기 때문에 이는 성능상 영향이 거의 없다고 볼 수 있다. (유니크한지 판단할 때를 의미하는건가??)

유니크하지 않은 경우엔 읽어야 할 레코드가 많아서 느린 것이지, 인덱스 자체의 특성 때문에 느린 것은 아니라는 말이다. 즉, 읽어야 할 레코드 건수가 같으면 읽기 성능의 차이는 미미하다.

<2> 인덱스 쓰기

새로운 레코드가 INSERT되거나 인덱스 칼럼의 값이 변경되는 경우에는 인덱스 쓰기 작업이 필요하다.

그런데 유니크 인덱스의 키 값을 쓸 때는 중복 값을 체크하는 과정이 한 단계 더 필요하다. 그래서 유니크하지 않은 세컨더리 인덱스의 쓰기 작업보다 느리다.

그런데 MySQL에서는 유니크 인덱스에서 중복된 값을 체크할 때는 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이 과정에서의 데드락이 아주 빈번하다.

또한 InnoDB 스토리지 엔진에는 인덱스 키의 저장을 버퍼링 하기 위해 "체인지 버퍼"가 사용되어 인덱스의 저장이나 변경 작업이 상당히 빨리 처리되지만, 유니크 인덱스는 중복 체크를 해야하므로 작업 자체를 버퍼링하지 못한다.(?)

이때문에 유니크 인덱스는 쓰기 성능이 떨어진다.

(2) 유니크 인덱스 사용 시 주의사항

불필요한 경우엔 유니크 인덱스를 생성하지 않는 것이 더 좋다.

그리고 하나의 테이블에서 같은 컬럼에 유니크 인덱스와 일반 인덱스를 중복해서 설정해 둔 경우가 가끔 있는데, MySQL의 유니크 인덱스는 일반 인덱스와 같은 역할을 하므로 그럴 필요가 없다.

마찬가지로 똑같은 칼럼에 프라이머리 키와 유니크 인덱스를 동일하게 생성하는 것도 불필요한 중복이다.

4. 외래키

MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있다.

InnoDB 외래키 관리에는 아래와 같은 중요한 두 가지 특징이 있다.

테이블의 변경(쓰기 잠금)이 발생하는 경우에만 "잠금 경합"(잠금 대기)이 발생한다.
외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않는다.

아래와 같은 예시를 살펴보자.

CREATE TABLE tb_parent (
	id INT NOT NULL,
    fd VARCHAR(100) NOT NULL, 
    PRIMARY KEY(id)
    )
ENGINE = InnoDB;

CREATE TABEL tb_child (
	id INT NOT NULL,
    pid INT 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');

언제 자식 테이블의 변경이 잠금 대기를 하고,
언제 부모 테이블의 변경이 잠금 대기를 하는지 살펴보자

(1) 자식 테이블의 변경이 대기

1번 커넥션에서 먼저 트랜잭션을 시작하여 부모 테이블에서 id가 2인 레코드에 UPDATE를 실행한다.
이 과정에서 1번 커넥션이 tb_parent 테이블에서id=2인 레코드에 대해 쓰기 잠금을 획득한다.

그리고 2번 커넥션에서 tb_child의 외래키 칼럼인 pid를 2로 변경하는 쿼리를 실행해보자.
이 쿼리의 경우 부모 테이블의 변경 작업이 완료될 때까지 대기한다.

즉, 자식 테이블의 외래키 칼럼의 변경은 부모 테이블의 확인이 필요한데 이 상태에서 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면 해당 쓰기 잠금이 해제될 때까지 기다리게 되는 것이다.

하지만, 자식 테이블의 외래키가 아닌 칼럼의 변경은 외래키로 인한 잠금 확장(위에서 살펴 본 예제와 같은!)이 발생하지 않는다.

(2) 부모 테이블의 변경 작업이 대기하는 경우

1번 커넥션에서 부모 키 1을 참조하는 자식 테이블의 레코드를 변경하면 tb_child 테이블의 레코드에 대해 쓰기 잠금을 획득한다.

이 상태에서 2번 커넥션이 tb_parent 테이블에서 id=1인 레코드를 삭제할 경우 이 쿼리는 tb_child 테이블의 레코드에 대한 쓰기 잠금이 해제될 때까지 기다려야 한다. (부모 레코드가 삭제되면 자식 레코드도 동시에 삭제되는 식으로 동작하기 때문에)

이렇게 잠금이 다른 테이블로 확장되면 전체적으로 쿼리의 동시 처리에 영향을 미치기 때문에 충분한 고려가 필요하다.

MINJU

이전 포스트

Real MySql3

다음 포스트