MySQL의 GENERATED 칼럼은 다른 칼럼 값을 기반으로 자동 계산되는 칼럼으로, 가상 컬럼(Virtual) 또는 저장된 컬럼(Stored) 형태로 사용된다.
왜 이걸 찾아보았는가...
1. JSON 내부 필드 인덱스 문제
- 10개 이상의 필드를 가진 JSON 타입을 저장해야 했다.
- 근데 MySQL은 JSON 타입 내부의 특정 필드에 직접 인덱스를 걸 수 없다.
- WHERE JSON_EXTRACT(data, '$.field') = '값' 형태의 조건은 풀 테이블 스캔이 발생했다.
2. 해결 방법 → Generated Column을 써볼까.
- GENERATED COLUMN으로 JSON 내부 필드를 추출한 별도 칼럼을 정의해보자.
- 추출된 칼럼을 STORED 형태로 저장한 뒤 인덱스를 걸어 조회하는 테스트를 했다.
기본 문법
column_name data_type GENERATED ALWAYS AS (expression) [VIRTUAL | STORED]
| 키워드 | 의미 |
| expression | 계산식 (기존 컬럼 기반) |
| VIRTUAL | 계산값을 디스크에 저장하지 않고 필요 시 계산 (기본값) |
| STORED | 계산 결과를 디스크에 저장 (인덱스 가능) |
예시
1. 기본 가상 컬럼
CREATE TABLE orders (
product_price DECIMAL(10,2),
quantity INT,
total_amount DECIMAL(12,2) GENERATED ALWAYS AS (product_price * quantity) VIRTUAL
);
- total_amount는 자동으로 계산되고 저장되지는 않음
- INSERT 시 total_amount를 직접 입력할 수 없음
2. JSON 필드에서 값 추출
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
data JSON,
sku VARCHAR(50) GENERATED ALWAYS AS (
JSON_UNQUOTE(JSON_EXTRACT(data, '$.sku'))
) STORED,
category VARCHAR(50) GENERATED ALWAYS AS (
JSON_UNQUOTE(JSON_EXTRACT(data, '$.category'))
) STORED,
created_at DATE GENERATED ALWAYS AS (
STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(data, '$.created')), '%Y-%m-%d')
) STORED,
INDEX idx_sku (sku),
INDEX idx_category (category),
INDEX idx_created (created_at)
);
- sku, category, created_at 값이 JSON에서 추출되어 인덱스로 활용됨
- STORED 를 사용해 인덱스 설정 가능
3. JSON 인덱스 활용 예시
CREATE TABLE order_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
data JSON,
first_item VARCHAR(100)
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.items[0].name')))
STORED,
INDEX idx_first_item (first_item)
);
INSERT INTO order_logs (data) VALUES
('{"items": [{"name": "Laptop"}, {"name": "Mouse"}]}'),
('{"items": [{"name": "Phone"}]}'),
('{"items": [{"id": 1001}]}'),
('{"note": "empty order"}');
3-1. 데이터 삽입 결과
+----+---------------------------------------------------+------------+
| id | data | first_item |
+----+---------------------------------------------------+------------+
| 1 | {"items": [{"name": "Laptop"}, {"name":"Mouse"}]} | Laptop |
| 2 | {"items": [{"name": "Phone"}]} | Phone |
| 3 | {"items": [{"id": 1001}]} | NULL |
| 4 | {"note": "empty order"} | NULL |
+----+---------------------------------------------------+------------+
- first_item는 items[0].name 경로에서 추출된다.
- 경로에 해당하는 키가 없으면 NULL
3-2. JSON 경로 존재 여부 체크
SELECT id, JSON_CONTAINS_PATH(data, 'one', '$.items[0].name') AS exists_path
FROM order_logs;
+----+-------------+
| id | exists_path |
+----+-------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 0 |
| 4 | 0 |
+----+-------------+
3-3. 인덱스 사용 확인
EXPLAIN SELECT * FROM order_logs WHERE first_item = 'Laptop';
- 인덱스 idx_first_item가 사용되었다.
3-4. 인덱스 대상이 아닌 row 확인
SELECT COUNT(*) FROM order_logs WHERE first_item IS NULL;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
- JSON 경로가 없어 NULL이 된 행이 2건 확인됨
- 경로가 없는 row는 인덱스에 포함되지 않음
'Tips' 카테고리의 다른 글
| 파이썬 환경 구성 (0) | 2025.08.30 |
|---|---|
| python의 변수와 객체 참조 (0) | 2025.08.23 |
| FastAPI 실행 및 문서 접근 가이드 (0) | 2025.06.21 |
| [Rabbitmq] ERROR: could not bind to distribution port (0) | 2025.06.06 |
| pm2로 python 데몬화 (0) | 2025.05.31 |