서론

개발을 진행하면 할 수록 필터링 조건이 많아지게 되어 원본 데이터를 효율적으로 조회하기 어려워져, 원본 데이터에서 필터링에 필요한 컬럼들만 가져와 반정규화 하여 별도의 검색 전용 테이블을 두는 방법으로 쿼리 성능을 높이려고 했음. 추가 인프라나 데이터베이스 교체 없이 지금 사용중인 MySQL 환경에서만 최적화하는 것이 목표였다.

그런데, 1:N 관계 대표적으로 Product 와 Category 관계처럼 하나의 엔티티가 N개의 값들을 가질때 반정규화 구조를 설계하기가 쉽지 않았다. 그래서 MySQL 공식문서들로 LLM들을 괴롭힌 결과 FTS(FTI 적용) 방법과 JSON 조회(MVI 적용)로 최적화할 수 있다는 이야기를 듣고 실제로 도입이 가능한지 성능 테스트를 진행함.

Multi Value Index (MVI)

Multi Value Index 동작 그림 - 출처 : GPT o4-mini-high 가 만듬

INSERT 동작

JSON 배열 저장
- 테이블에 JSON 컬럼에 ["A", "B", "C"] 같은 배열 값을 저장
Virtual Column Expansion
- MySQL은 내부적으로 (CAST(tags AS CHAR(...) ARRAY)) 형태의 가상 컬럼을 확장
- 각 배열 요소 A, B, C를 별도 행으로 분리한 후, 가상 컬럼 값과 Clustered Key(Row ID) 를 묶음
B+Tree 인덱스 생성
- 분리된 가상 컬럼 값을 키로, Row ID를 값으로 하는 다중 B+Tree 엔트리를 생성
- ex) A → 1, B → 1, C → 1 의 Key-Value 쌍이 인덱스 내에 삽입

SELECT 동작

쿼리 파싱
- SELECT * FROM table WHERE MEMBER OF(tags, 'B') 같은 함수 호출
인덱스 조회 (B+Tree Search)
- B+Tree 루트에서 키 'B'를 찾고, 리프 노드로 내려가면 B→1 엔트리를 발견
Row ID 추출 후 데이터 로드
- 엔트리의 값인 Row ID 를 통해 실제 테이블의 해당 행을 조회
- 최종적으로 Row ID 의 값에 해당하는 레코드를 반환

MVI 지원 함수와 예시

MEMBER OF()

배열에 특정 값이 한개라도 포함 여부 검사

JSON 문서 파싱 없이 배열 내 스칼라 멤버만 검사만 진행며, 3가지 함수중 가장 빠름
- 예시 쿼리와 결과
JSON_CONTAINS()

JSON 컬럼안의 모든 요소가 포함되어 있는지 검사

즉, 후보(candidate) JSON 문서를 인자로 받아, 전체 JSON 구조를 재귀적으로 비교함
- 예시 쿼리와 결과
JSON_OVERLAPS()

두 JSON 배열 간에 한개라도 공통 요소가 있는지 검사

즉, 두 개의 JSON 배열을 인자로 받아, OR(교집합이 하나 이상 있는지) 연산 수행

하지만, 양쪽 배열을 모두 파싱하여 비교해야 하므로, 3가지 함수중 가장 느림
- 예시 쿼리와 결과