Generated 컬럼 & 함수 기반 인덱스

 

1. Generated Column

정의

• "표현식"으로 정의된 컬럼
• 정의된 "표현식"에 따라 컬럼의 값이 자동으로 생성
• "표현식" = 고정된 값, 함수 또는 다른 컬럼들에 대한 연산 조합 등이 해당
• 사용자가 직접 값을 입력하거나 변경할 수 없음

두 가지 종류가 존재합니다.

- Virtual Generated Column (가상 컬럼)
- Stored Generated Column (스토어드 컬럼)

 

생성 방법

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

 

기본적으로 위와 같은 문법으로 생성할 수 있습니다. 익숙한 문법이겠지만 []의 경우 필수적인 구문이 아니라는 뜻입니다.

• 기본적으로 VIRTUAL 타입으로 생성 & NULL 값 허용
• PRIMARY KEY로는 STORED 타입만 허용
• 하나의 테이블에서 가상 컬럼과 스토어드 컬럼 혼합해서 사용 가능

 

가상 컬럼

정의:  하나의 테이블에 존재하는 다른 컬럼들을 이용하여 새로운 값을 만들어 내는 것입니다. 실제로는 정의만 정의하여 딕셔너리로 추가하고, 해당 컬럼을 읽으려고 할 때, 계산이 진행됩니다.

CREATE TABLE tb_virtual_column (
    id int NOT NULL AUTO_INCREMENT,
    price int NOT NULL DEFAULT '0',
    quantity int NOT NULL DEFAULT '0',
    total_price int GENERATED ALWAYS AS ((quantity * price)) VIRTUAL,
    PRIMARY KEY (id),
    KEY ix_totalprice (total_price)
);

• column의 값을 disk에 저장하지 않고 그때 그때 계산합니다.
• 인덱스를 생성 가능하고 인덱스 데이터는 디스크에 저장됩니다. 또한 인덱스 참조 시 실제 값이 아닌 인덱스에 있는 값을 읽어갈 수 있게 됩니다.
• 가상 컬럼은 일반 컬럼으로 전환이 불가합니다.

스토어드 컬럼

딕셔너리에만 정보를 저장하지 않고 데이터가 입력되거나 수정될 때 해당 컬럼의 데이터도 직접 저장됩니다.

CREATE TABLE tb_stored_column (
    id int NOT NULL AUTO_INCREMENT,
    price int NOT NULL DEFAULT '0',
    quantity int NOT NULL DEFAULT '0',
    total_price int GENERATED ALWAYS AS ((quantity * price)) STORED,
    PRIMARY KEY (id),
    KEY ix_totalprice (total_price)
);

• 컬럼의 값을 디스크에 저장합니다.
• 컬럼의 값은 레코드가 INSERT 되거나 UPDATE 될 때 계산되어 저장합니다.
• 인덱스를 생성할 수 있습니다.
• 일반 컬럼을 스토어드 컬럼으로, 스토어드 컬럼을 일반 컬럼으로 변경이 가능합니다.
• 스토어드 컬럼과 가상 컬럼 간 변경은 불가능합니다.

Online DDL

실무에서 중요한 Online DDL에 대한 작동 알고리즘입니다.

 

 

실제 자신의 mysql version에서 지원하는지 여부를 확인하는 것을 더 추천드립니다.

• 실행 알고리즘을 직접 명시하는 것을 권장하는데, 잠금 없이 진행 시 알아서 자동으로 선택한 알고리즘으로 수행하지만, 해당 알고리즘이 내가 예상한 것과 다를 수 있으니 명시하는 것을 권장합니다.
• 명시할 경우 지정된 알고리즘으로 수행되지 않을 경우 에러를 만드므로 더 안전합니다.
• 처음엔 INSTANT 알고리즘으로 수행하고 안 될 경우 INPLACE에 락 없이 시도해보는 식으로 진행하는 것이 좋습니다.
• 그것도 안되면 서드 파티 툴을 활용해볼 수 있습니다. 서드 파티 툴을 활용하는 것은 또 큰 주제이므로 추후 따로 서술하겠습니다.

 

인덱스 사용

• 일반 컬럼과 동일하게 쿼리에서 인덱스 사용 가능
• 쿼리에 Generated 컬럼명 대신 표현식을 사용해도 인덱스 사용 가능
• 대신 표현식은 컬럼에 정의된 표현식과 완전히 일치해야 합니다.
• 컬럼에는 (col1 + 1)로 정의됐으나, 쿼리에서 (1 + col1)로 사용 시 인덱스 사용 불가
• 또한 주어진 조건값과 컬럼 타입도 동일해야 합니다.
• =, <, <=, >, >=, BETWEEN, IN 연산자 사용 시 이러한 최적화가 적용됩니다.
• 완전히 일치: 결과값이 같더라도, 표현식 순서까지 똑같아야 한다는 뜻입니다.

다만 비결정적 함수, Stored Program, 변수, 서브 쿼리는 사용이 불가합니다.

 

2. Function Based Index

정의

쿼리를 짜다보면 두개의 컬럼을 조합하는 조건으로 쿼리를 짜야하는 경우가 있습니다.

그렇지만 이렇경우 index를 탈 수 없는 상황이 발생하게 되어 성능에 큰 영향을 미칠수 있습니다. 이럴때는 Function Based Index(함수기반)를 활용할 수 있다

- 일반 인덱스는 컬럼 또는 컬럼의 Prefix만 인덱싱 가능합니다.
    - CREATE INDEX ix_col ON tab (col);
    - CREATE INDEX ix_col20 ON tab (col(20));
- 하지만 함수 기반 인덱스는 "표현식"을 인덱싱 값으로 사용 가능하게 해줍니다.
    - CREATE INDEX f_index ON tab ((col1 + col2), (col1 * col2));
    - CREATE INDEX f_index ON tab (DATE(col1));
- 쿼리의 조건절에서 컬럼을 가공하는 경우에 유용하게 사용 가능합니다.
    - 사용하는 쿼리 → SELECT * FROM tab WHERE (col1 + col2) > 10;
    - 쿼리를 위한 인덱스 → CREATE INDEX f_index ON tab ((col1 + col2));

 

동작 방식

Virtual Generated 컬럼을 자동 생성 후 인덱싱합니다. 
• 자동 생성된 Virtual 컬럼은 일반적인 환경에서는 확인 불가
• 가상 컬럼의 이름은 '!hidden!index_name!key_part!counter' 형태로 지정되며, 타입도 자동 지정됩니다.

mysql> CREATE TABLE tb1 (col1 int, KEY ix_test ( (abs(col1)) ));

mysql> SET SESSION debug="+d,show_hidden_columns";

mysql> SHOW CREATE TABLE tb1;

CREATE TABLE `tb1` (
    `col1` int DEFAULT NULL,
    `!hidden!ix_test!0!0` int GENERATED ALWAYS AS (abs(`col1`)) VIRTUAL,
    KEY `ix_test` ((abs(`col1`)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

 

사용 방법

 

- 각각의 표현식은 반드시 괄호로 묶어서 명시

CREATE INDEX f_index ON tab ((col1 + col2), (col1 * col2));

- 일반 컬럼과 함께 복합 인덱스로도 구성 가능

CREATE INDEX f_index ON tab (col1, (LOWER(col2)), col3(20));

- 표현식 값에 대해 ASC & DESC 지정 가능
- UNIQUE 설정 가능