Ch08-7 멀티 밸류 인덱스

한 줄 정의

멀티 밸류 인덱스는 하나의 레코드가 여러 개의 키 값 을 가질 수 있는 인덱스로, JSON 배열 필드처럼 1:N 관계의 값 을 가진 칼럼을 효율적으로 검색하기 위해 MySQL 8.0.17부터 도입된 기능입니다.

쉽게 말하면

일반 인덱스는 “한 레코드당 하나의 키 값” 을 전제로 합니다. 예를 들어 name 칼럼의 인덱스는 레코드마다 name이 하나씩이죠.

그런데 “이 사용자가 보유한 자격증 목록”처럼 한 레코드에 여러 값 이 들어가는 경우가 있습니다. 관계형 DB의 정석은 별도 테이블로 분리하는 것이지만, JSON 배열 필드 로 담고 있다면 멀티 밸류 인덱스가 해답이 됩니다.

왜 필요한가?

관계형 DB에서 “한 레코드의 여러 값”을 다루는 두 가지 방법이 있습니다.

방법	예시	단점
별도 테이블 (정규화)	`user_certifications` 테이블 분리	JOIN 비용, 스키마 복잡
JSON 배열	`certs: ["AWS", "GCP", "K8s"]`	검색 시 풀 스캔 발생

JSON 배열은 편리하지만 인덱스를 탈 수 없다 는 치명적 약점이 있었습니다. 멀티 밸류 인덱스는 이 약점을 해결합니다.

핵심 내용

멀티 밸류 인덱스 생성

CAST(... AS ... ARRAY) 표현식을 인덱스에 사용합니다.

CREATE TABLE users (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    credentials JSON,
    INDEX mv_ix_creds ((CAST(credentials->'$.certs' AS CHAR(20) ARRAY)))
);

credentials->'$.certs' : JSON 경로 추출
CAST(... AS CHAR(20) ARRAY) : 배열의 각 요소를 CHAR(20)으로 변환해 인덱스에 저장
ARRAY 키워드 가 핵심입니다. 이 키워드가 있어야 멀티 밸류 인덱스로 인식됩니다

검색 시 사용 함수

일반 WHERE col = ? 이 아니라 JSON 검색 함수 와 함께 써야 옵티마이저가 멀티 밸류 인덱스를 활용합니다.

함수	용도
`MEMBER OF()`	특정 값이 배열에 포함되는가
`JSON_CONTAINS()`	배열이 특정 값/값들을 포함하는가
`JSON_OVERLAPS()`	두 배열이 교집합이 있는가

예시

-- 'AWS' 자격증을 가진 사용자
SELECT * FROM users
WHERE 'AWS' MEMBER OF (credentials->'$.certs');
 
-- 'AWS' 또는 'GCP' 중 하나라도 가진 사용자
SELECT * FROM users
WHERE JSON_OVERLAPS(credentials->'$.certs', '["AWS","GCP"]');
 
-- 'AWS'와 'GCP'를 모두 가진 사용자
SELECT * FROM users
WHERE JSON_CONTAINS(credentials->'$.certs', '["AWS","GCP"]');

제약 사항

멀티 밸류 인덱스는 일반 B-Tree 인덱스에 비해 여러 제약 이 있습니다.

테이블당 1개 만 생성 가능
커버링 인덱스로 사용 불가
정렬(ORDER BY)에 활용 불가
PK나 유니크 키로 사용 불가
한 레코드가 생성하는 인덱스 엔트리 수에 제한 있음 (배열 크기 제한)

즉, 멀티 밸류 인덱스는 “JSON 배열에 특정 값이 있는지 검색”이라는 좁은 용도 에만 사용됩니다.

내 생각

멀티 밸류 인덱스는 “태그 검색” 같은 기능을 JSON으로 구현할 때 빛을 발합니다. 전에는 태그 전용 정규화 테이블을 별도로 만들어야 했지만, 이제는 JSON 배열 하나로 해결 가능합니다.
하지만 “기능이 있다고 무조건 써야 한다” 는 아닙니다. 태그의 개수가 많고, 태그별 통계/집계 같은 요구사항이 있다면 정규화가 여전히 정답 입니다. 멀티 밸류 인덱스는 “배열 원소의 포함 검색” 외에는 도움이 되지 않습니다.
JSON 경로 지정 시 키 이름이 바뀌면 인덱스가 무효화 됩니다. 스키마 변경에 취약한 점을 감안해야 합니다. JSON 구조를 고정해 쓴다면 차라리 별도 칼럼으로 추출 하는 것이 장기적으로 유지보수에 유리할 수 있습니다.

출처

Real MySQL 8.0 (1권), 8.7 멀티 밸류 인덱스

Dev Wiki

탐색기