멈재

[MySQL / Hibernate] 가상 컬럼 (Virtual Columns - Generated Column, @Formula) 본문

카테고리 없음

[MySQL / Hibernate] 가상 컬럼 (Virtual Columns - Generated Column, @Formula)

멈재 2024. 1. 21. 22:37
728x90

개요

일부 DBMS에는 가상 컬럼이란 기능을 제공합니다.
가상 컬럼은 이름 그대로 가상의 컬럼을 의미하는데요, 기존의 컬럼을 가지고 어떠한 조건식을 이용해 만들어진 결과를 저장한 컬럼을 의미합니다.
 
이번 포스팅에서는 MySQL에서의 가상 컬럼(Generated Column)과 이와 비슷한 기능을 하는 Hibernate의 @Formula 어노테이션을 알아보려고 합니다.
 
 
 

MySQL Generated Column

MySQL에서 Generated Column은 저장 방식에 따라 VIRTUAL과 STORED 타입으로 나눌 수 있습니다.

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

 

  • VIRTUAL(DEFAULT): VIRTUAL 타입은 값을 실제 저장 공간에 저장하지 않고, 행을 읽을 때 BEFORE 트리거 직후에 계산되는 방식
  • STORED: STORED 타입은 행이 추가되거나 변경될 때 수식에 의해서 계산된 값이 실제로 저장 공간을 차지하여 저장되는 방식

 
Generated Columns (VIRTUAL)을 활용하면 유용한 상황이 몇 가지 존재합니다.

  • 기존 컬럼을 바탕으로 수식 또는 함수가 적용된 결과를 자주 보여줘야 할 때
  • 함수기반 인덱스 대신 사용하고 싶을 때
  • JSON 데이터의 조회 편의성을 높이고 싶을 때

 
 

언제 사용하면 좋을까

Generate Column에는 기본적으로 표현식을 사용할 수 있습니다.
예를 들어, 이름 컬럼에 대하여 마스킹을 하되, 물리적인 저장 공간을 할당하지 않지만 컬럼으로 두고 싶을 때 아래와 같은 형태로 구성할 수 있습니다.

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;

 

 
 
 
Generated Column은 JSON 데이터에 대해서도 활용이 가능합니다.
MySQL의 JSON 컬럼에는 일반 인덱스를 직접 사용할 수 없는데요,

 
 
그에 따라 JSON 컬럼에 특정 값으로 데이터를 조회하면 테이블 풀스캔이 일어나게 됩니다.

 
이러한 경우에 인덱싱하려는 컬럼 정보를 추출하여 Generated Column으로 만들면 해당 컬럼에 인덱스를 생성(적용)하여 조회 성능을 높일 수 있습니다.

ALTER TABLE request_log ADD COLUMN `url` VARCHAR(200) GENERATED ALWAYS AS (
    JSON_EXTRACT(log_info, '$.url')); -- 기본값: VIRTUAL
CREATE INDEX idx_request_log_url ON request_log(url);

EXPLAIN SELECT * FROM request_log WHERE url = '/health';

 
 
 
 

Hibernate @Formula

이와 유사하게 Hibernate에서는 테이블 스키마에 컬럼으로 존재하지 않지만기존 컬럼을 대상으로 계산하여 만들어진 가상의 컬럼을 엔티티의 속성으로 만드는 기능을 제공합니다.
따라서 테이블의 컬럼으로 존재하지 않는 가상의 컬럼을 엔티티 소석과 매핑되어 사용할 수 있게 됩니다.
( 해당 어노테이션으로 만들어진 값은 읽기 전용(read-only)입니다. )
 
 

언제 사용하면 좋을까

@Formula 어노테이션을 사용하기에 적절한 상황은 기존 컬럼을 기준으로 계산하는 상황에 주로 사용됩니다.
예를 들어, 상품(Product)과 좋아요(ProductLike) 엔티티는 1:N 관계를 가지고, 각각의 상품마다 좋아요한 개수를 상품 리스트에 나타내야 하는 상황이라고 가정해 보겠습니다. 

@Entity
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class Product {

    @Id 
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "product_id")
    private Long id;

    @Column
    private String title;

    @OneToMany(mappedBy = "target", fetch = FetchType.LAZY)
    private List<ProductLike> productLikes = new ArrayList<>();

    public int getLikeCounts() {
        return productLikes.size();
    }
}
@Entity
@NoArgsConstructor
@EntityListeners(AuditingEntityListener.class)
public class ProductLike {

    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "product_like_id")
    private Long id;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "product_id")
    private Product target;

    @CreatedDate
    public LocalDateTime createDt;

}

 
두 엔티티(상품, 좋아요)는 ManyToOne, OneToMany로 양방향 관계를 맺고 있습니다.
단순히 상품 엔티티의 좋아요 리스트 사이즈를 조회한다면 기본 설정(fetch = Lazy)으로 인해 관련된 좋아요를 전부 로드해오게 됩니다.
그러나, 좋아요의 수를 구하기 위해 각 좋아요의 모든 컬럼들을 로드하므로 불필요한 메모리 사용이 생기게 됩니다.
 
이처럼 불필요한 데이터로 인한 문제를 방지하고자 할 때 @Formula 어노테이션을 사용할 수 있습니다.

@Entity
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class Product {

    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "product_id")
    private Long id;

    @Column
    private String title;

    @OneToMany(mappedBy = "target", fetch = FetchType.LAZY)
    private List<ProductLike> productLikes = new ArrayList<>();

    @Formula("(SELECT count(1) FROM product_like p WHERE p.product_id = product_id)")
    private int likeCount;

    public int getLikeCount() {
        return likeCount;
    }
}
SELECT p.product_id,
       p.title, 
       (SELECT count(1) FROM product_like pl WHERE p.product_id = pl.product_id)       
  FROM
        product p

 

또한, @Formula 어노테이션으로 만들어진 엔티티 속성으로 추가적인 동작도 가능한데요,

각 상품마다 좋아요 개수를 가상 컬럼으로 가지므로 해당 속성을 이용해서 상품 좋아요 순으로 정렬하는 기능도 가능합니다.

public interface ProductRepository extends JpaRepository<Product, Long> {
    
    @Query("SELECT p FROM Product p ORDER BY p.likeCount DESC")
    List<Product> findAllOrderByLikeCountDesc();
}
SELECT p.product_id,
       p.title 
       (SELECT count(1) FROM product_like pl WHERE pl.product_id = p.product_id)
  FROM product p 
 ORDER BY
       (SELECT count(1) FROM product_like ipl WHERE ipl.product_id = p.product_id) desc

 

 

주의점

 
@Formula 어노테이션은 JPQL이 아닌 Native SQL문을 사용하고, 그에 따라 RDBMS의 벤더를 교체 시에 영향을 줄 수 있으니 적절한 상황에 사용해야 합니다.
또한, @Formula 어노테이션으로 정의된 필드는 지연 로딩을 적용하려면 별도의 바이트 코드 조작(Bytecode Enhancement)을 적용해야 합니다.
 
 
 
참고