Generated Column은 Mysql 5.7.6 부터 지원되는 DataType으로 2가지 방식을 지원한다.
1. VIRTUAL 방식
A. 데이터를 저장하지 않고 정의만 하여 딕셔너리만 추가하는 방식
B. 해당 컬럼을 읽으려고 할 때 Trigger 로 열이 계산된다.
C. 저장 용량은 늘어나지 않지만 조회 시 마다 계산 작업이 추가적으로 발생됨
2. STORED 방식
A. 딕셔너리 및 데이터를 저장하는 방식
B. 데이터가 입력되거나 수정될 때 연산되어 열에 저장된다.
C. 저장 용량이 늘어나고 CU 작업 시 계산 작업이 추가적으로 발생됨
생성 시 아무 설정을 하지 않으면 기본값으로는 VIRTUAL 방식으로 생성된다.
아래는 Online DDL 작업 시 지원 내역이다.
Stored Column 추가
COPY 알고리즘으로만 가능하기 때문에 고 비용이 발생되고 임시테이블에서 작업 후 교체가 진행된다.
COPY 만 지원되는 이유는 컬럼 생성 시 바로 열을 계산하여 데이터까지 저장하기 때문이다.
auto-increment 열과 연산하여서는 생성할 수 없다.
desc alter_test_tbl;
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| a | int | NO | PRI | NULL | auto_increment |
| b | varchar(100) | YES | MUL | NULL | |
| c | varchar(100) | YES | | NULL | |
| d | varchar(1000) | YES | | NULL | |
+-------+---------------+------+-----+---------+----------------+
ALTER TABLE alter_test_tbl
ADD (e varchar(200) GENERATED ALWAYS AS (concat(b,'_test')) STORED),
ALGORITHM=COPY, LOCK=SHARED;
desc alter_test_tbl;
+-------+---------------+------+-----+---------+------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+------------------+
| a | int | NO | PRI | NULL | auto_increment |
| b | varchar(100) | YES | MUL | NULL | |
| c | varchar(100) | YES | | NULL | |
| d | varchar(1000) | YES | | NULL | |
| e | varchar(200) | YES | | NULL | STORED GENERATED |
+-------+---------------+------+-----+---------+------------------+
SELECT b,e FROM alter_test_tbl LIMIT 2;
+---------------------------+--------------------------------+
| b | e |
+---------------------------+--------------------------------+
| test2 | test2_test |
| d876c93a-...-000d3ad76afd | d876c93a-...-000d3ad76afd_test |
+---------------------------+--------------------------------+
Stored Column 수정
생성과 마찬가지로 COPY 알고리즘만 사용이 가능하다. 그 사유도 같다.
생성과 다른 점은 Temporary Table 에서 작업 후 교환하는 것이 아닌 해당 Table의 저장공간에서 일어난다는 것이다.
ALTER TABLE alter_test_tbl MODIFY e VARCHAR(200)
GENERATED ALWAYS AS (concat(C,'_test')) STORED,
ALGORITHM=COPY, LOCK=SHARED;
SELECT b,c,e FROM alter_test_tbl LIMIT 2;
+------------+---------------------------+--------------------------------+
| b | c | e |
+------------+---------------------------+--------------------------------+
| test2 | d876c45d-...-000d3ad76afd | d876c45d-...-000d3ad76afd_test |
| d876c93a...| d876c93f-...-000d3ad76afd | d876c93f-...-000d3ad76afd_test |
+------------+---------------------------+--------------------------------+
Stored Column 삭제
InPlace 알고리즘을 사용 가능하나 Table Rebuild는 발생된다. 하지만 InPlace가 가능함으로 Lock이 None이 가능하여 CURD가 동시 작업이 가능하다.
ALTER TABLE alter_test_tbl DROP e, ALGORITHM=INPLACE, LOCK=NONE;
Virtual Column 생성
딕셔너리만 수정하는 하는 작업으로 Instant 알고리즘 사용이 가능하다
desc alter_test_tbl;
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| a | int | NO | PRI | NULL | auto_increment |
| b | varchar(100) | YES | MUL | NULL | |
| c | varchar(100) | YES | | NULL | |
| d | varchar(1000) | YES | | NULL | |
+-------+---------------+------+-----+---------+----------------+
ALTER TABLE alter_test_tbl
ADD (e varchar(200) GENERATED ALWAYS AS (concat(b,'_test')) VIRTUAL),
ALGORITHM=INSTANT;
desc alter_test_tbl;
+-------+---------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------------------+
| a | int | NO | PRI | NULL | auto_increment |
| b | varchar(100) | YES | MUL | NULL | |
| c | varchar(100) | YES | | NULL | |
| d | varchar(1000) | YES | | NULL | |
| e | varchar(200) | YES | | NULL | VIRTUAL GENERATED |
+-------+---------------+------+-----+---------+-------------------+
Virtual Column 수정
수정 시에는 COPY 알고리즘만 사용이 가능한데 열의 순서 등이나 연산되기 위한 다른 컬럼의 자료가 변경되기 안되기 때문에 Shared Lock이상의 잠금이 필요하기 때문이다.
ALTER TABLE alter_test_tbl
MODIFY e VARCHAR(200) GENERATED ALWAYS AS (concat(C,'_test')) VIRTUAL FIRST,
ALGORITHM=COPY, LOCK=SHARED;
desc alter_test_tbl;
+-------+---------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------------------+
| e | varchar(200) | YES | | NULL | VIRTUAL GENERATED |
| a | int | NO | PRI | NULL | auto_increment |
| b | varchar(100) | YES | MUL | NULL | |
| c | varchar(100) | YES | | NULL | |
| d | varchar(1000) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------------------+
SELECT * FROM alter_test_tbl LIMIT 1;
+-----------------------+---+-------+------------------+------+
| e | a | b | c | d |
+-----------------------+---+-------+------------- ----+------+
| d876c45d-...6afd_test | 1 | test2 | d876c45d-...6afd | 0 |
+-----------------------+---+-------+------------------+------+
Virtual Column 삭제
생성과 마찬가지로 Instant 알고리즘으로 딕셔너리에서 바로 삭제 가능하다.
ALTER TABLE alter_test_tbl DROP e, ALGORITHM=INSTANT;
desc alter_test_tbl;
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| a | int | NO | PRI | NULL | auto_increment |
| b | varchar(100) | YES | MUL | NULL | |
| c | varchar(100) | YES | | NULL | |
| d | varchar(1000) | YES | | NULL | |
+-------+---------------+------+-----+---------+----------------+
참조
https://mysqldba.tistory.com/277
'Database > Mysql & Mariadb' 카테고리의 다른 글
| innodb 행 저장 방식(1) 저장 파일 형식 (0) | 2021.11.08 |
|---|---|
| MySQL Online DDL(7) [Foreign key Operations] (0) | 2021.11.01 |
| MySQL Online DDL(5) [Column Operations] (0) | 2021.11.01 |
| MySQL Online DDL(4) [Primary key Operations] (0) | 2021.10.28 |
| MySQL Online DDL(3) [Index Operations] (0) | 2021.10.27 |
최근댓글