MySQL JSON 타입과 가상 컬럼 — 반정형 데이터 처리

지난 글에서 이벤트 스케줄러로 자동 배치 작업을 처리하는 방법을 살펴봤습니다. 이번 글에서는 MySQL 5.7에서 도입된 JSON 네이티브 타입과 **가상 컬럼(Generated Column)**을 사용해 반정형 데이터를 효율적으로 관리하는 방법을 다룹니다.

JSON 네이티브 타입

MySQL 5.7.8부터 JSON 타입이 추가됐습니다. VARCHAR에 JSON 문자열을 저장하는 것과 달리, JSON 타입은 이진 최적화 형식으로 저장합니다.

-- JSON 컬럼이 있는 테이블
CREATE TABLE orders (
  id     BIGINT AUTO_INCREMENT PRIMARY KEY,
  meta   JSON,
  amount DECIMAL(10,2)
);

-- 올바른 JSON만 허용
INSERT INTO orders (meta, amount)
VALUES ('{"status":"paid","tags":["VIP","express"]}', 9900);

-- 잘못된 JSON은 에러
INSERT INTO orders (meta) VALUES ('not-a-json');
-- ERROR 3140: Invalid JSON text

JSON 타입은 삽입 시 유효성을 검증하고, 내부적으로 경로 접근이 빠른 이진 형식으로 변환합니다.

경로 접근 연산자

JSON 타입과 가상 컬럼

-> 연산자는 JSON_EXTRACT의 단축형으로 따옴표가 포함된 JSON 값을 반환합니다. ->> 연산자는 JSON_UNQUOTE(JSON_EXTRACT)의 단축형으로 문자열 값에서 따옴표를 제거합니다.

-- -> : JSON 값 그대로 (따옴표 포함)
SELECT meta->'$.status' FROM orders WHERE id = 1;
-- 결과: "paid"

-- ->> : 따옴표 제거된 순수 문자열
SELECT meta->>'$.status' FROM orders WHERE id = 1;
-- 결과: paid

-- 중첩 경로
SELECT meta->>'$.address.city' FROM users WHERE id = 1;

-- 배열 인덱스 접근
SELECT meta->'$.tags[0]' FROM orders WHERE id = 1;
-- 결과: "VIP"

WHERE 조건이나 ORDER BY에서도 동일하게 사용할 수 있습니다. 단, 인덱스 없이 조회하면 Full Table Scan이 발생합니다.

가상 컬럼 — JSON에 인덱스 걸기

JSON 컬럼 자체에는 직접 인덱스를 생성할 수 없습니다. 대신 **Generated Column(가상 컬럼)**을 만들고 그 컬럼에 인덱스를 생성합니다.

JSON 가상 컬럼 코드

-- VIRTUAL: 디스크에 저장 안 함, 조회 시 실시간 계산
-- STORED: 디스크에 저장, INSERT/UPDATE 시 계산

ALTER TABLE orders
  ADD COLUMN status_gc VARCHAR(20)
  GENERATED ALWAYS AS (meta->>'$.status') VIRTUAL;

CREATE INDEX idx_status ON orders(status_gc);

-- 이제 인덱스 활용 조회 가능
EXPLAIN SELECT * FROM orders WHERE status_gc = 'paid';
-- type: ref (인덱스 사용!)

8.0.13+부터는 가상 컬럼을 생략하고 함수 기반 인덱스를 직접 사용할 수 있습니다.

-- 8.0.13+: 함수 인덱스로 직접 생성
CREATE INDEX idx_status_fn
  ON orders ((meta->>'$.status'));

-- 동일하게 활용 가능
SELECT * FROM orders WHERE meta->>'$.status' = 'paid';
-- type: ref

주요 JSON 함수

-- 값 설정 / 삽입 / 대체
UPDATE orders
SET meta = JSON_SET(meta, '$.status', 'shipped')
WHERE id = 1;

-- JSON_SET: 존재하면 덮어씀, 없으면 추가
-- JSON_INSERT: 없을 때만 추가 (존재하면 무시)
-- JSON_REPLACE: 존재할 때만 덮어씀 (없으면 무시)
-- JSON_REMOVE: 해당 경로 삭제
UPDATE orders SET meta = JSON_REMOVE(meta, '$.temp_flag') WHERE id = 1;

-- 여러 키 병합 업데이트 (PATCH 스타일)
UPDATE orders
SET meta = JSON_MERGE_PATCH(meta, '{"status":"refunded","reason":"취소"}')
WHERE id = 1;

-- 배열에 요소 추가
UPDATE orders
SET meta = JSON_ARRAY_APPEND(meta, '$.tags', 'urgent')
WHERE id = 1;

JSON 배열 검색

-- 배열에 특정 값 포함 여부
SELECT * FROM orders
WHERE JSON_CONTAINS(meta, '"VIP"', '$.tags');

-- JSON 경로 존재 여부
SELECT * FROM orders
WHERE JSON_CONTAINS_PATH(meta, 'one', '$.address.city');

-- 배열 펼쳐서 조회 (JSON_TABLE)
SELECT jt.*
FROM orders,
  JSON_TABLE(
    meta,
    '$.tags[*]' COLUMNS (tag VARCHAR(50) PATH '$')
  ) AS jt
WHERE jt.tag = 'VIP';

VIRTUAL vs STORED 선택

VIRTUAL 컬럼은 저장 공간을 사용하지 않지만 조회마다 계산합니다. STORED 컬럼은 디스크에 저장되어 조회가 빠르지만 INSERT/UPDATE 시 계산 비용이 발생합니다.

-- VIRTUAL 선호 상황: 자주 읽지 않는 파생 값
-- STORED 선호 상황: 자주 읽히는 값, 파티셔닝 키로 사용 시
-- (파티셔닝 키는 STORED만 가능)

-- 기존 테이블에 생성 컬럼 추가 (ALTER TABLE)
ALTER TABLE orders
  ADD COLUMN order_year YEAR
  GENERATED ALWAYS AS (YEAR(created_at)) STORED,
  ADD INDEX idx_year (order_year);

JSON 타입 사용 가이드

반정형 데이터를 JSON으로 저장할지, 정규화된 컬럼으로 분리할지는 조회 패턴에 따라 결정합니다.

-- JSON이 적합한 경우
-- - 속성 수가 가변적이고 예측 불가
-- - 소수의 JSON 경로만 조회 기준으로 사용
-- - 전체 문서를 한 번에 읽어야 하는 경우

-- 정규화 컬럼이 적합한 경우
-- - 항상 존재해야 하는 필수 속성
-- - 정렬·집계·조인 조건으로 자주 사용
-- - NOT NULL / UNIQUE / FK 제약이 필요한 경우

-- 혼합 패턴 (추천)
CREATE TABLE products (
  id       BIGINT AUTO_INCREMENT PRIMARY KEY,
  name     VARCHAR(200) NOT NULL,   -- 핵심 컬럼은 정규화
  price    DECIMAL(10,2) NOT NULL,
  extra    JSON,                     -- 선택적 속성은 JSON
  category VARCHAR(50) GENERATED ALWAYS AS (extra->>'$.category') VIRTUAL,
  INDEX idx_category (category)
);

지난 글: MySQL 이벤트 스케줄러 — 자동 배치 작업 스케줄링

다음 글: MySQL 리플리케이션 — 비동기·반동기·그룹 리플리케이션

읽어주셔서 감사합니다. 😊