개요
많은 DBMS에서 가상 컬럼이라는 기능을 제공하고 있습니다. 가상 컬럼은 기존의 컬럼을 기반으로 특정 조건식에 의해 생성된 결과를 저장한 컬럼을 의미하는데요,
이번 포스팅에서는 MySQL에서의 가상 컬럼(Generated Column)에 대해 알아보겠습니다.
MySQL Generated Column
MySQL에서 Generated Column은 저장 방식에 따라 VIRTUAL과 STORED 타입으로 나눌 수 있습니다.
- VIRTUAL (기본값): VIRTUAL 타입은 값을 실제 저장 공간에 저장하지 않고, 행을 읽을 때 계산됩니다.
- STORED: STORED 타입은 행이 추가되거나 변경될 때 계산된 값이 실제로 저장됩니다.
col_name data_type [GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']이러한 가상 컬럼은 다음과 같은 상황에서 유용합니다.
- 기존 컬럼을 바탕으로 계산된 결과를 자주 보여줘야 할 때
- 함수 기반 인덱스를 대체하고 싶을 때
- JSON 데이터의 조회 편의성을 높이고 싶을 때
사용 예시
물리적 저장 공간을 할당하지 않으면서 이름 컬럼에 마스킹을 적용하고 싶다고 가정해보겠습니다.
이때 다음과 같이 설정할 수 있습니다.
CREATE TABLE users (
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '유저 ID',
name VARCHAR(20) NOT NULL COMMENT '이름',
masked_name VARCHAR(20) GENERATED ALWAYS AS (
CASE
WHEN CHAR_LENGTH(name) > 2 THEN
CONCAT(SUBSTRING(name, 1, 1), LPAD('*', CHAR_LENGTH(name) - 2, '*'), SUBSTRING(name, CHAR_LENGTH(name), CHAR_LENGTH(name)))
WHEN CHAR_LENGTH(name) <= 3 THEN
CONCAT(SUBSTRING(name, 1, 1), LPAD('*', CHAR_LENGTH(name) - 1, '*'))
END
) COMMENT '마스킹 이름',
PRIMARY KEY (id)
);
위와 같이 설정하면 데이터를 조회하면 기존 컬럼을 기반으로 수식을 계산하여 새로운 가상의 컬럼을 만들 수 있습니다.
INSERT INTO users(id, name) VALUES(1, '홍');
INSERT INTO users(id, name) VALUES(2, '홍길');
INSERT INTO users(id, name) VALUES(3, '홍길동');
INSERT INTO users(id, name) VALUES(4, '홍길동동');
SELECT * FROM users;
일부 상황에서는 JSON 데이터 타입을 지정하는 경우도 존재하는데요, 하지만 MySQL의 JSON 컬럼에는 직접 인덱스를 사용할 수 없습니다.
(실제로 저희 회사에서도 RDB에 JSON을 사용하는 곳이 있답니다 !)
이러한 상황에 인덱스를 적용하려는 JSON 컬럼 정보를 추출하여 가상 컬럼으로 만들면 JSON 데이터를 기반으로 한 조회 성능을 높일 수 있습니다.
ALTER TABLE request_log ADD COLUMN url VARCHAR(200) GENERATED ALWAYS AS (
JSON_EXTRACT(log_info, '$.url')
);
CREATE INDEX idx_request_log_url ON request_log(url);
EXPLAIN SELECT * FROM request_log WHERE url = '/health';