[DB] 가상 컬럼 (Virtual Columns - Generated Column)

개요

가상 컬럼은 기존의 컬럼을 기반으로 특정 조건식에 의해 생성된 결과를 저장하는 컬럼을 의미합니다.

이번 포스팅에서는 MySQL에서의 가상 컬럼(Generated Column)에 대해 알아보겠습니다.

 

 

MySQL Generated Column

MySQL에서 저장 방식에 따라 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 데이터의 조회 편의성을 높이고 싶을 때

 

 

유스케이스

유스케이스1: 이름 마스킹

물리적 저장 공간을 할당하지 않으면서 조회할때 이름 컬럼에 마스킹된 결과도 적용하고 싶다고 가정해보겠습니다.

이때 아래와 같이 이름 컬럼을 기준으로 마스킹 수식을 적용하여 가상 컬럼을 지정하면 별도 후처리없이 기본 조회 결과에 포함되게 됩니다.

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;

 

 

유스케이스2: JSON 타입에 적용

빈도수가 많지는 않겠지만 컬럼 성격에 따라 특정 컬럼을 JSON 타입으로 지정하여 사용하는 경우도 존재합니다.

단, MySQL 기준 JSON 타입인 컬럼에 직접적으로 인덱스를 추가할 수 없습니다.

 

다시 말해, JSON 타입의 컬럼을 읽어야 하는 경우에 쿼리 실행 시간이 느려지는 상황이 생길수도 있습니다.

JSON 컬럼에 인덱스 지정 불가

 

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';

 

 

다만, 당연하게도 (세컨더리) 인덱스가 적용되기 때문에 데이터 로우가 변경(INSERT, UPDATE)될때마다 인덱스를 변경해야 하므로 추가적인 쓰기 비용은 발생하게 됩니다.

하지만, 쓰기 비용이 조금 더 발생하더라도 가상 컬럼 + 보조 인덱스를 사용하는 방식이 저장 컬럼 방식(계산값을 테이블에 저장)보다 디스크와 메모리를 덜 쓰기 때문에 오히려 더 나을 수도 있다고 합니다.

 

 

가상 컬럼과 보조 인덱스를 사용하는 것에 대한 더 자세한 내용은 다음의 공식 문서를 참고해주세요

https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html

MySQL :: MySQL 8.0 Reference Manual :: 15.1.20.9 Secondary Indexes and Generated Columns