ED's Development: mysql json 인덱스

20250502

mysql json 인덱스

MySQL에서 JSON 인덱스 생성 및 활용 방법

MySQL에서 JSON 타입 컬럼의 특정 값을 빠르게 검색하려면, 해당 값에 대해 인덱스를 생성해야 합니다. JSON 전체에 직접 인덱스를 걸 수는 없고, JSON 내부의 특정 속성을 대상으로 인덱스를 생성하는 것이 일반적입니다.

1. Generated Column(가상/생성 열) + 인덱스

MySQL 5.7 이상에서는 Generated Column(생성 열, 가상 열 또는 저장 열)을 활용하여 JSON 속성을 별도 컬럼으로 추출한 뒤, 이 컬럼에 인덱스를 생성하는 방식이 가장 널리 사용됩니다2 3 613.

예시


sql
-- 1. 가상 컬럼 추가 (예: user라는 키 값 추출)
ALTER TABLE customers
  ADD COLUMN user VARCHAR(100) GENERATED ALWAYS AS (JSON_UNQUOTE(custinfo->'$.user')) STORED;

-- 2. 인덱스 생성
ALTER TABLE customers
  ADD INDEX idx_user (user);

VIRTUAL: 실제 데이터 저장 없이 쿼리 시 계산 (디스크 공간 절약, 성능은 다소 낮을 수 있음)
STORED: 실제로 값을 저장 (조회 성능 우수, 저장 공간 필요)

쿼리 예시


sql
SELECT *
FROM customers
WHERE user = 'Jill';

이렇게 하면 해당 인덱스가 사용되어 빠른 검색이 가능합니다.

2. 함수 기반 인덱스(Functional Index)

MySQL 8.0.13 이상에서는 함수 기반 인덱스(Functional Index)를 직접 생성할 수 있습니다.
즉, JSON 추출 함수 결과에 바로 인덱스를 걸 수 있습니다1 68.

예시


sql
ALTER TABLE customers
  ADD INDEX idx_user ((CAST(custinfo->>'$.user' AS CHAR(100)) COLLATE utf8mb4_bin));

custinfo->>'$.user'는 JSON에서 user 값을 문자열로 추출합니다.
COLLATE utf8mb4_bin을 명시해야 인덱스와 쿼리의 정렬 방식이 일치하여 인덱스가 정상적으로 동작합니다1.

쿼리 예시


sql
SELECT *
FROM customers
WHERE custinfo->>'$.user' = 'Jill';

3. 인덱스 생성 시 주의사항

정렬(COLLATION): 함수 기반 인덱스 생성 시, 인덱스의 collation과 쿼리의 collation이 일치해야 인덱스가 제대로 사용됩니다.
예를 들어, COLLATE utf8mb4_bin을 명시적으로 지정해야 할 수 있습니다1.
인덱스 오버헤드: 인덱스를 추가하면 검색은 빨라지지만, 데이터 추가/수정/삭제 시에는 인덱스 유지 비용이 발생합니다6.
JSON 전체 인덱스 불가: JSON 전체에 인덱스를 걸 수는 없고, 반드시 특정 속성(키)에 대해 인덱스를 생성해야 합니다11.

4. 참고: MySQL 8 이상 JSON 인덱스 예시


sql
ALTER TABLE my_table
  ADD INDEX idx_json_field ((json_column->>'$.key'));

이렇게 하면 json_column의 key 값에 대해 인덱스가 생성되고, WHERE 조건에서 해당 값을 검색할 때 인덱스를 사용할 수 있습니다6 89.

요약

가상(생성) 컬럼을 만들고 인덱스 생성 → 가장 일반적, 호환성 높음
함수 기반 인덱스로 바로 생성(MySQL 8.0.13 이상) → 더 간편
COLLATE 옵션 주의
JSON 전체가 아닌, 특정 키에만 인덱스 가능

이 방식을 적용하면 대용량 JSON 데이터에서도 원하는 값에 대한 빠른 검색이 가능합니다.

Citations:

댓글 없음:

댓글 쓰기