MySQL 8 JSON 기능 활용 — Generated Column과 인덱싱

MySQL JSON 타입의 장점

MySQL 8은 네이티브 JSON 타입을 지원하여 유연한 스키마가 필요한 데이터를 효과적으로 저장할 수 있습니다. JSON 데이터를 바이너리 형태로 저장하여 빠른 접근이 가능하고, 풍부한 함수와 Generated Column을 통한 인덱싱으로 높은 쿼리 성능을 달성할 수 있습니다.

JSON 컬럼 기본 사용

-- 테이블 생성
CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    attributes JSON NOT NULL,
    metadata JSON DEFAULT '{}',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name)
);

-- JSON 데이터 삽입
INSERT INTO products (name, attributes) VALUES
('MacBook Pro 16', JSON_OBJECT(
    'brand', 'Apple',
    'specs', JSON_OBJECT(
        'cpu', 'M3 Max',
        'ram', 36,
        'storage', '1TB SSD'
    ),
    'colors', JSON_ARRAY('Space Black', 'Silver'),
    'price', 3499000,
    'in_stock', true
)),
('Galaxy S24 Ultra', JSON_OBJECT(
    'brand', 'Samsung',
    'specs', JSON_OBJECT(
        'cpu', 'Snapdragon 8 Gen 3',
        'ram', 12,
        'storage', '256GB'
    ),
    'colors', JSON_ARRAY('Titanium Gray', 'Titanium Violet'),
    'price', 1698400,
    'in_stock', true
));

JSON 경로 표현식과 쿼리

-- 값 추출 (->> 는 텍스트로 반환, -> 는 JSON으로 반환)
SELECT
    name,
    attributes->>'$.brand' AS brand,
    attributes->>'$.specs.cpu' AS cpu,
    attributes->>'$.price' AS price
FROM products;

-- 배열 요소 접근
SELECT name, attributes->>'$.colors[0]' AS primary_color
FROM products;

-- JSON 검색 (WHERE 절)
SELECT name FROM products
WHERE attributes->>'$.brand' = 'Apple'
  AND CAST(attributes->>'$.price' AS UNSIGNED) > 1000000;

-- JSON_CONTAINS: 배열에 값이 포함되는지
SELECT name FROM products
WHERE JSON_CONTAINS(attributes->'$.colors', '"Silver"');

-- JSON_SEARCH: 값으로 경로 찾기
SELECT JSON_SEARCH(attributes, 'one', 'Apple') FROM products;

JSON 수정 함수

-- 값 설정/변경
UPDATE products
SET attributes = JSON_SET(attributes,
    '$.price', 3299000,
    '$.specs.ram', 48,
    '$.discount', true
)
WHERE name = 'MacBook Pro 16';

-- 값 추가 (존재하면 무시)
UPDATE products
SET attributes = JSON_INSERT(attributes,
    '$.warranty', '1 year'
)
WHERE id = 1;

-- 값 삭제
UPDATE products
SET attributes = JSON_REMOVE(attributes, '$.discount')
WHERE id = 1;

-- 배열에 추가
UPDATE products
SET attributes = JSON_ARRAY_APPEND(attributes,
    '$.colors', 'Midnight'
)
WHERE name = 'MacBook Pro 16';

Generated Column과 인덱싱

JSON 경로에 직접 인덱스를 걸 수 없으므로, Generated Column(가상 컬럼)을 생성하고 그 위에 인덱스를 만듭니다.

-- Virtual Generated Column 추가
ALTER TABLE products
ADD COLUMN brand VARCHAR(100)
    GENERATED ALWAYS AS (attributes->>'$.brand') VIRTUAL,
ADD COLUMN price INT UNSIGNED
    GENERATED ALWAYS AS (CAST(attributes->>'$.price' AS UNSIGNED)) VIRTUAL,
ADD COLUMN in_stock BOOLEAN
    GENERATED ALWAYS AS (CAST(attributes->>'$.in_stock' AS UNSIGNED)) VIRTUAL;

-- Generated Column에 인덱스 생성
CREATE INDEX idx_brand ON products (brand);
CREATE INDEX idx_price ON products (price);
CREATE INDEX idx_brand_price ON products (brand, price);

-- 이제 인덱스를 활용한 빠른 쿼리
EXPLAIN SELECT name, brand, price FROM products
WHERE brand = 'Apple' AND price > 1000000;
-- type: range, key: idx_brand_price

MySQL 8.0.17+ Multi-Valued Index

-- JSON 배열에 대한 멀티밸류 인덱스
ALTER TABLE products
ADD INDEX idx_colors ((CAST(attributes->'$.colors' AS CHAR(50) ARRAY)));

-- MEMBER OF 연산자로 검색
SELECT name FROM products
WHERE 'Silver' MEMBER OF (attributes->'$.colors');

-- JSON_OVERLAPS로 교집합 검색
SELECT name FROM products
WHERE JSON_OVERLAPS(
    attributes->'$.colors',
    CAST('["Silver", "Gold"]' AS JSON)
);

성능 비교와 모범 사례

• 자주 검색하는 JSON 필드는 반드시 Generated Column + 인덱스를 만드세요
• VIRTUAL 컬럼은 디스크 공간을 차지하지 않고, STORED는 실제 저장되어 읽기가 빠릅니다
• JSON 문서가 64KB를 초과하면 성능이 저하되므로 적절한 크기를 유지하세요
• 정형 데이터는 일반 컬럼, 반정형 부가 데이터에 JSON을 사용하는 하이브리드 설계를 권장합니다