[RealMySQL 8.0] 8. 인덱스 - 기타 인덱스 (멀티 밸류 인덱스, 클러스터링 인덱스, 유니크 인덱스, 외래키)

8.7 멀티 밸류 인덱스

전문 검색 인덱스를 제외한 모든 인덱스는 레코드 1건이 1개의 인덱스 키 값을 가진다. 즉, 인덱스 키와 데이터 레코드는 1:1의 관계를 가진다. 하지만 멀티 밸류 인덱스는 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스다. 최근 RDBMS들이 JSON 데이터 타입을 지원하기 시작하면서 JSON의 배열 타입의 필드에 저장된 원소들에 대한 인덱스 요건이 발생한 것이다.

 

 

다음과 같이 신용 정보 점수를 JSON 타입 칼럼에 저장하는 테이블을 가정해보자.

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

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

 

멀티 밸류 인덱스를 활용하기 위해서는 다음 함수들을 이용해서 검색해야 옵티마이저가 인덱스를 활용한 실행 계획을 수립한다.

• MEMBER OF()
• JSON_CONTAINS()
• JSON_OVERLAPS()

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


+----------+------------+----------+--------------------------------------+
| user_id  | first_name |last_name | credit_info                          |
+----------+------------+----------+--------------------------------------+
|       1  | Matt       | Lee      | {"credit_scores": [360, 353, 351]}   |
+----------+------------+----------+--------------------------------------+

 

 

 

8.8 클러스터링 인덱스

MySQL 서버에서 클러스터링은 테이블의 레코드를 비슷한 것(PK를 기준으로)들끼리 묶어서 저장하는 형태로 구현되는데, 이는 주로 비슷한 값들을 동시에 조회하는 경우가 많다는 점에 착안한 것이다.

 

 

8.8.1 클러스터링 인덱스

클러스터링 인덱스는 테이블의 PK에 대해서만 적용되는 내용이다. 즉 PK 값이 비슷한 레코드끼리 묶어서 저장되는 것을 클러스터링 인덱스라 표현한다. 여기서 중요한 것은 PK 값에 의해 레코드의 저장 위치가 결정되는 것이다. 즉, PK 값이 변경되면 그 레코드의 물리적인 저장 위치가 바뀌어야 하므로 PK 값으로 클러스터링된 테이블은 신중히 프라이머리 키를 결정해야 한다.

 

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

 

 

그림 8.25 클러스터링 테이블(인덱스) 구조

 

그림을 보면 세컨더리 인덱스를 위한 B-Tree의 리프 노드와는 달리 그림 8.25의 클러스터링 인덱스의 리프 노드에는 레코드의 모든 칼럼이 같이 저장되어 있다. 즉, 클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조로 관리되는 것이다.

 

 

그러면 PK가 없는 InnoDB 테이블은 어떻게 클러스터링 테이블로 구성될까? PK가 없는 경우에는 InnoDB 스토리지 엔진이 다음 우선순위대로 PK를 대체할 칼럼을 선택한다.

 

1. PK가 없으면 기본적으로 PK를 클러스터링 키로 선택

2. NOT NULL 옵션의 유니크 인덱스 중에서 첫 번째 인덱스를 클러스터링 키로 선택

3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후, 클러스터링 키로 선택

 

InnoDB 엔진이 적절한 클러스터링 키 후보를 찾지 못한다면, 내부적으로 레코드의 일련번호 칼럼을 생성한다. 이렇게 추가된 PK는 사용자에게 노출되지 않으며, 쿼리 문장에 명시적으로 사용할 수 없다. 결국 우리에게 아무런 혜택을 주지 않으므로 가능하다면 PK를 명시적으로 생성하자.

 

 

8.8.2 클러스터링 인덱스에 미치는 영향

PK가 데이터 레코드의 저장에 미치는 영향을 알아봤다면, 이제 PK가 세컨더리 인덱스에 어떤 영향을 미치는지 살펴보자.

 

MyISAM 테이블 같은 클러스터링되지 않은 테이블은 INSERT될 때 처음 저장된 공간에서 절대 이동하지 않는다. 데이터 레코드가 저장된 주소는 내부적인 레코드 아이디 역할을 하는데 그렇기에 PK나 세컨더리 인덱스의 각 키는 그 주소를 이용해 실제 데이터 레코드를 가져온다.

 

그렇다면 InnoDB 테이블에서 세컨더리 인덱스가 실제 레코드가 저장된 주소를 가지고 있다면 어떻게 될까? 클러스터링 키 값이 변경될 때마다 레코드의 주소가 변경되고 그때마다 해당 테이블의 모든 인덱스에 저장된 주솟값을 변경해야 한다. 이런 오버헤드를 제거하기 위해 InnoDB 테이블(클러스터링 테이블)의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 PK 값을 저장하도록 구현돼 있다.

 

 

8.8.3 클러스터링 인덱스의 장점과 단점

MyISAM과 같은 클러스터링되지 않은 일반 PK와 클러스터링 인덱스를 비교했을 때의 상대적인 장단점을 정리해 보자.

 

장점

• PK(클러스터링 키)로 검색할 때 처리 성능이 매우 빠름
• 테이블의 모든 세컨더리 인덱스가 PK를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음 (커버링 인덱스)

단점

• 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 가지기에 키 값이 크면 전체적으로 인덱스의 크기가 커짐
• 세컨더리 인덱스를 통해 검색할 때 PK로 다시 한번 검색해야 해서 처리 성능이 느림
• INSERT할 때 PK에 의해 레코드의 저장 위치가 결정되어 처리 성능이 느림
• PK를 변경할 때 레코드를 DELETE하고 INSERT하는 작업이 필요해 처리 성능이 느림

 

대부분 클러스터링 인덱스의 장점은 빠른 읽기이며, 단점은 느린 쓰기라는 것을 알 수 있다. 일반적으로 쓰기와 읽기의 비율이 2:8 정도이니 조금 느린 쓰기를 감수하고 읽기를 빠르게 유지하는 것은 매우 중요하다.

 

 

 

8.9 유니크 인덱스

유니크는 인덱스라기보다는 제약 조건에 가깝다고 볼 수 있다. MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다.

 

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

유니크 인덱스와 유니크하지 않은 일반 세컨더리 인덱스는 인덱스의 구조상 아무런 차이점이 없다. 유니크 인덱스와 일반 세컨더리 인덱스의 읽기와 쓰기를 성능 관점에서 한번 살펴보자.

 

 

8.9.1.1 인덱스 읽기

많은 사람이 유니크 인덱스가 빠르다고 생각하지만 이것은 사실이 아니다. 어떤 책에서는 유니크 인덱스는 1건만 읽으면 되지만 유니크하지 않은 세컨더리 인덱스에서는 레코드를 한 건 더 읽어야하므로 느리다고 이야기한다. 하지만 유니크하지 않은 세컨더리 인덱스에서 한 번 더 해야 하는 작업은 디스크 읽기가 아니라 CPU에서 칼럼값을 비교하는 작업이기 때문에 이는 성능상 영향이 거의 없고 볼 수 있다. 유니크하지 않은 세컨더리 인덱스는 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 느린 것이지, 인덱스 자체의 특성 때문에 느린 것이 아니라는 것이다.

 

8.9.1.2 인덱스 쓰기

새 레코드가 INSERT되거나 인덱스 칼럼의 값이 변경되는 경우에는 인덱스 쓰기 작업이 필요하다. 그런데 유니크 인덱스의 키 값을 쓸 때는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요하다. 그래서 유니크하지 않은 세컨더리 인덱스의 쓰기보다 느리다.

 

그런데 MySQL에서는 유니크 인덱스에서 중복된 값을 체크할 때는 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 아주 빈번히 발생한다. 또한 InnoDB 엔진에는 인덱스 키의 저장을 버퍼링하기 위해 체인지 버퍼가 사용된다. 그래서 인덱스의 저장이나 변경 작업이 상당히 빨리 처리되지만, 유니크 인덱스는 반드시 중복 체크를 해야 해서 작업 자체를 버퍼링하지 못한다. 이 때문에 유니크 인덱스는 일반 세컨더리 인덱스보다 변경 작업이 더 느리게 작동한다.

 

8.9.1.3 유니크 인덱스 사용 시 주의사항

꼭 필요한 경우라면 유니크 인덱스를 생성해야 하지만 더 성능이 좋아질 것으로 생각하고 불필요하게 생성하지 않는 것이 좋다. 하나의 테이블에서 같은 칼럼에 유니크 인덱스와 일반 인덱스를 각각 중복해서 생성해 둔 경우가 가끔 있는데, MySQL의 유니크 인덱스는 일반 다른 인덱스와 같은 역할을 해서 중복해서 인덱스를 생성할 필요는 없다.

 

 

 

8.10 외래키

외래키는 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 child_ibfk_1 FOREIGN 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_parent VALUES (100, 1, 'child-100');

 

위와 같은 테이블에서 언제 자식 테이블의 변경이 잠금 대기를 하고, 언제 부모 테이블의 변경이 잠금 대기를 하는지 예제로 살펴보자.

 

 

8.10.1 자식 테이블의 변경이 대기하는 경우

작업 번호	커넥션-1	커넥션-2
1	BEGIN;
2	UPDATE tb_parent SET fd='changed-2' WHERE id=2;
3		BEGIN;
4		UPDATE tb_child SET pid=2 WHERE id=100;
5	ROLLBACK;
6		Query OK, 1 row affected (3.04 sec)

 

1번 커넥션에서 먼저 트랜잭션을 시작하고 부모 테이블에서 id가 2인 레코드에 UPDATE를 실행한다. 이 과정에서 1번 커넥션이 tb_parent 테이블에서 id가 2인 레코드에 대해 쓰기 잠금을 획득한다. 그리고 2번 커넥션에서 자식 테이블의 외래키 칼럼인 pid를 2로 변경하는 쿼리를 실행해보자. 이 쿼리는 부모 테이블의 변경 작업이 완료될 때까지 대기한다. 다시 1번 커넥션에서 롤백이나 커밋으로 트랜잭션을 종료하면 2번 커넥션의 대기 중이던 작업이 즉시 처리되는 것을 확인할 수 있다.

 

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

 

자식 테이블의 외래키가 아닌 칼럼의 변경은 외래키로 인한 잠금 확장이 발생하지 않는다. 이것은 InnoDB의 외래키 관리의 두 번째 특징이다.

 

 

8.10.2 부모 테이블의 변경이 대기하는 경우

작업 번호	커넥션-1	커넥션-2
1	BEGIN;
2	UPDATE tb_child SET fd='changed-100' WHERE id=100;
3		BEGIN;
4		DELETE FROM tb_parent WHERE id=1;
5	ROLLBACK;
6		Query OK, 1 row affected (6.09 sec)

 

 

첫 번째 커넥션에서 부모 키 "1"을 참조하는 자식 테이블의 레코드를 변경하면 tb_child 테이블의 레코드에 대해 쓰기 잠금을 획득한다. 이 상태에서 2번 커넥션이 tb_parent 테이블에서 id가 1인 레코드를 삭제하는 경우 이 쿼리(작업번호 4번)는 tb_child 테이블의 레코드에 대한 쓰기 잠금이 해제될 때까지 기다려야 한다. 자식 테이블이 생성될 때 정의된 외래키의 특성(ON DELETE CASCADE) 때문에 부모 레코드가 삭제되면 자식 레코드도 삭제되는 식으로 작동하기 때문이다.

 

 

DB에서 외래 키를 물리적으로 생성하려면 이런 현상으로 인한 잠금 경합까지 고려해 모델링을 진행하는 것이 좋다. 물리적으로 외래키를 생성하면 자식 테이블에 레코드가 추가되는 경우 해당 참조키가 부모 테이블에 확인한다는 것은 알고 있을 것이다. 하지만 물리적인 외래키의 고려 사항은 이러한 체크 작업이 아니라 이러한 체크를 위해 연관 테이블에 읽기 잠금을 걸어야 한다는 것이다. 또한 이렇게 잠금이 다른 테이블로 확장되면 그만큼 전체적으로 쿼리의 동시 처리에 영향을 미친다.