MySQL JSON 데이터 타입 활용

Introduction

MySQL 5.7부터 도입된 JSON 데이터 타입은 비정형 데이터를 효과적으로 처리할 수 있게 해줍니다. 관계형 데이터베이스의 고정된 스키마 제약을 넘어서 동적인 데이터 구조를 저장하고 조회할 수 있으며, JSON 함수들을 통해 효율적인 데이터 추출이 가능합니다. 이 글에서는 JSON 데이터 타입의 활용 방법과 최적화 기법을 다루겠습니다.

Environment

-- MySQL 버전 확인 (5.7 이상 필요)
SELECT VERSION();

-- JSON 데이터 타입 지원 확인
SELECT JSON_TYPE('"test"');
-- STRING

# MySQL JSON 함수 테스트
mysql -u root -p -e "SELECT JSON_EXTRACT('{\"name\": \"joel\"}', '$.name');"

Problem

JSON 데이터 처리 시 발생하는 문제들:

-- JSON 데이터 삽입
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    attributes JSON
);

INSERT INTO products (name, attributes) VALUES
('스마트폰', '{"brand": "Samsung", "color": "black", "specs": {"ram": "8GB", "storage": "256GB"}}');

-- JSON 경로 조회 시 성능 문제
SELECT * FROM products 
WHERE JSON_EXTRACT(attributes, '$.brand') = 'Samsung';
-- 인덱스 미사용으로 전체 테이블 스캔

-- JSON 배열 처리의 복잡성
SELECT * FROM products 
WHERE JSON_CONTAINS(attributes->'$.tags', '"premium"');

# JSON 함수 실행 시간 측정
mysql -u root -p -e "
EXPLAIN SELECT * FROM products 
WHERE JSON_EXTRACT(attributes, '$.brand') = 'Samsung';
"
-- type: ALL (전체 스캔)
-- key: NULL (인덱스 미사용)

Analysis

JSON 데이터 처리 성능 문제를 분석했습니다:

-- JSON 데이터 분포 확인
SELECT 
    JSON_EXTRACT(attributes, '$.brand') as brand,
    COUNT(*) as count
FROM products
GROUP BY brand
ORDER BY count DESC
LIMIT 10;

-- JSON 데이터 크기 분석
SELECT 
    AVG(LENGTH(attributes)) as avg_json_size,
    MAX(LENGTH(attributes)) as max_json_size,
    COUNT(*) as total_rows
FROM products;
-- avg_json_size: 156
-- max_json_size: 1024
-- total_rows: 1000000

-- JSON 쿼리 실행 계획 분석
EXPLAIN FORMAT=JSON
SELECT * FROM products 
WHERE JSON_EXTRACT(attributes, '$.brand') = 'Samsung';

Solution

1단계: Generated Column과 인덱스

-- Generated Column을 사용한 JSON 인덱싱
ALTER TABLE products 
ADD COLUMN brand VARCHAR(100) 
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.brand'))) STORED;

-- 인덱스 생성
CREATE INDEX idx_products_brand ON products(brand);

-- 이제 인덱스가 사용됨
EXPLAIN SELECT * FROM products WHERE brand = 'Samsung';
-- type: ref
-- key: idx_products_brand
-- rows: 150000

-- 복합 Generated Column
ALTER TABLE products 
ADD COLUMN ram VARCHAR(20) 
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.specs.ram'))) STORED;

ALTER TABLE products 
ADD COLUMN storage VARCHAR(20) 
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.specs.storage'))) STORED;

CREATE INDEX idx_products_specs ON products(ram, storage);

2단계: JSON 함수 최적화

-- JSONPath 표현식 최적화
SELECT 
    id,
    name,
    attributes->>'$.brand' as brand,
    attributes->>'$.color' as color,
    attributes->'$.specs'->>'$.ram' as ram
FROM products
WHERE attributes->>'$.brand' = 'Samsung'
  AND attributes->'$.specs'->>'$.ram' = '8GB';

-- JSON 배열 함수 활용
SELECT 
    id,
    name,
    JSON_LENGTH(attributes->'$.tags') as tag_count
FROM products
WHERE JSON_CONTAINS(attributes->'$.tags', '["premium", "new"]');

-- JSON 테이블 함수 (MySQL 8.0+)
SELECT 
    p.id,
    p.name,
    jt.*
FROM products p,
JSON_TABLE(
    p.attributes->'$.tags',
    '$[*]' COLUMNS (
        tag VARCHAR(50) PATH '$'
    )
) jt
WHERE jt.tag = 'premium';

3단계: JSON 데이터 구조 설계

-- JSON 데이터 구조 최적화
CREATE TABLE optimized_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    brand VARCHAR(100),
    category VARCHAR(50),
    base_price DECIMAL(10,2),
    -- 변형 속성만 JSON으로 저장
    variant_attributes JSON,
    -- 정적 인덱스 컬럼
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_brand (brand),
    INDEX idx_category (category)
);

-- 데이터 삽입
INSERT INTO optimized_products (name, brand, category, base_price, variant_attributes) VALUES
('갤럭시 S24', 'Samsung', '스마트폰', 1200000, '{"color": "black", "storage": "256GB", "ram": "8GB"}');

-- JSON 데이터와 정적 컬럼 결합 조회
SELECT 
    id,
    name,
    brand,
    base_price,
    variant_attributes->>'$.color' as color,
    variant_attributes->>'$.storage' as storage
FROM optimized_products
WHERE brand = 'Samsung'
  AND base_price > 1000000;

Lessons Learned

Generated Column 활용: 빈번하게 조회되는 JSON 필드는 Generated Column으로 변환하여 인덱싱해야 합니다
스키마 설계: 모든 데이터를 JSON에 넣지 말고, 정적 필드는 별도 컬럼으로 분리하는 것이 성능에 유리합니다
JSON 함수 선택: JSON_EXTRACT보다 ->> 연산자가 더 효율적입니다
배치 처리: 대량의 JSON 데이터 삽입 시 배치 처리를 사용하면 성능이 향상됩니다
MySQL 8.0 기능 활용: JSON_TABLE, JSON_ARRAYAGG 등의 함수를 활용하면 복잡한 JSON 처리가 쉬워집니다

이 블로그는 외부 스폰서십, 제휴 마케팅 또는 광고 수익을 받지 않습니다.

MySQL JSON 데이터 타입 활용: 비정형 데이터 처리