MySQL Online DDL(5) [Column Operations]

RDBMS에서 가장 서비스하게 될 시 가장 많이 사용하는 마이그레이션은 컬럼 변경일 것이다.

Mysql 8 이 되면서 좀 더 다양한 online ddl을 제공하게 되었는데 컬럼 추가의 경우

Instant 알고리즘 사용이 가능하기 때문에 보다 서비스에 영향이 적게 반영이 가능해졌다.

 

Column 추가

컬럼 추가 시 Instant 알고리즘을 사용이 가능하지만 몇 가지 제약 사항이 따른다.

1.      ALTER TABLE 한 구문 내에 컬럼 추가 외에 다른 작업을 같이 진행해서는 안 된다.

2.      추가되는 컬럼은 반드시 그 위치가 테이블의 맨 끝에 위치한다.
(AFTER 사용 안됨)

3.      ROW_FOTMAT=COMPRESSED 에서는 사용 불가

4.      테이블에 FULLTEXT Index가 포함되어 있으면 사용 불가

5.      임시 테이블에 사용 불가

6.      데이터 사전 데이블스페이스에 있는 테이블에는 사용 불가

7.      컬럼 추가 행 크기가 고려되지 않으나 이 후 진행된 DML(배포 중 발생된 Insert나 Update) 을 반영 시 고려 될 수 있음
(Mysql은 행의 크기가 65,535 bytes로 제한 되어 있음)

기본적인 Syntax는 다음과 같다.

ALTER TABLE alter_test_tbl ADD d VARCHAR(100) NULL, ALGORITHM=INSTANT;

 

2개의 컬럼을 동시에 추가도 다음과 같이 가능하다

ALTER TABLE alter_test_tbl ADD e VARCHAR(100) NULL DEFAULT '',

ADD f VARCHAR(100)  NULL, ALGORITHM=INSTANT;

 

변경된 스키마는 바로 INFORMATION_SCHEMA 테이블 들에 반영된다.

1.      INFORMATION_SCHEMA.INNODB_TABLES

2.      INFORMATION_SCHEMA.INNODB_COLUMNS

3.      INFORMATION_SCHEMA.INNODB_TABLES.INSTANT_COLS

4.      INFORMATION_SCHEMA.INNODB_COLUMNS.HAS_DEFAULT and DEFAULT_VALUE

기본값의 경우는 Instant 알고리즘이 허용되기 때문에 동시에 작업할 수 있다.

만약 자동 증가 값을 추가하게 되면 동시 DML이 되지 않는데 데이터를 재구성하는 비용이 매우 크기 때문이고 최소 InPlace 알고리즘의 Shared Lock 수준이 필요하다.

 

Column 삭제

데이터가 재구성되는 비용이 큰 작업으로 syntax는 다음과 같다.

ALTER TABLE alter_test_tbl DROP COLUMN e, ALGORITHM=INPLACE, LOCK=NONE;

-- 또는

ALTER TABLE alter_test_tbl DROP f, ALGORITHM=INPLACE, LOCK=NONE;

 

Column 명 변경

동시 DML을 진행하려면 데이터 형식과 NULL 정보는 유지하고 컬럼명만 변경해야 한다.

외래 키 제약조건의 일부인 컬럼명도 바꿀 수 있다.

새 컬럼명으로 외래 키의 정의가 자동 갱신된다.

외래 키 제약조건의 일부인 컬럼을 변경할 시 에는 InPlace 알고리즘만 사용해야 한다.

Generated column명을 변경할 시 에는 InPlace 알고리즘을 사용할 수 없다.

ALTER TABLE alter_test_tbl CHANGE d e VARCHAR(100),

ALGORITHM=INPLACE, LOCK=NONE;

 

Column 위치 변경

위치 변경 Syntax는 다음과 같다.

ALTER TABLE alter_test_tbl MODIFY e VARCHAR(100) FIRST,

ALGORITHM=INPLACE, LOCK=NONE;

-- 또는

ALTER TABLE alter_test_tbl CHANGE e e VARCHAR(100) AFTER c,

ALGORITHM=INPLACE, LOCK=NONE;

 

Column Defualt value 추가 및 변경

기본값은 추가 및 변경은 Instant 알고리즘 사용이 가능하다

ALTER TABLE alter_test_tbl MODIFY e VARCHAR(100) DEFAULT '',

ALGORITHM=INSTANT;

-- 또는

ALTER TABLE alter_test_tbl CHANGE e e VARCHAR(100) default 'dt',

ALGORITHM=INSTANT;

 

기본값을 추가하는 것 역시 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(255) | YES  |     | NULL    |                |

+-------+--------------+------+-----+---------+----------------+

 

ALTER TABLE alter_test_tbl ALTER d SET DEFAULT '', 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(100) | YES  |     |         |                |

+-------+--------------+------+-----+---------+----------------+

 

Column Data Type 변경

Data Type 변경은 무조건 Copy 알고리즘만 사용이 가능하다. 또한 Shared Lock 이하만 지원되기 때문에 CUD는 불가하다.

UPDATE alter_test_tbl SET e=0;

 

ALTER TABLE alter_test_tbl MODIFY e INT DEFAULT 0,

ALGORITHM=COPY, LOCK=SHARED;

-- 또는

ALTER TABLE alter_test_tbl CHANGE e d VARCHAR(100) DEFAULT '',

ALGORITHM=COPY, LOCK=SHARED;

 

Column Varchar Data Type의 크기 확장

Varchar Type 일 시에 크기를 확장만 가능하고 축소는 당연히 불가하다

가이드에 보면 1byte로 표현 가능 값이 varchar(0~255) 에서 2Byte 값이 필요한 varchar(256 ~)

변경 시 Copy 알고리즘을 사용해야 하는 오류가 발생된다고 되어 있지만

최신 버전인 8.0.27 에서는 상관 없이 InPlace 알고리즘으로 처리가 가능한 것으로 보인다.

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(100) | YES  |     |         |                |

+-------+--------------+------+-----+---------+----------------+

 

ALTER TABLE alter_test_tbl CHANGE d d VARCHAR(1000),

ALGORITHM=INPLACE, LOCK=NONE;

 

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

+-------+--------------+------+-----+---------+----------------+

 

Column Defualt value 삭제

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(100) | YES  |     |         |                |

+-------+--------------+------+-----+---------+----------------+

 

ALTER TABLE alter_test_tbl ALTER d DROP DEFAULT, 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(255) | YES  |     | NULL    |                |

+-------+--------------+------+-----+---------+----------------+

 

자동 증분 변경

데이터 파일이 아닌 메모리에 저장된 값을 변경한다.

복제 또는 샤딩을 이용한 분산 시스템에서 자동 증분값을 특정 값으로 재 설정하는 경우 유용하다.

 

아래와 같이 테이블 정보에는 갱신이 안되었지만 데이터 입력 시 변경된 값으로 입력되는 것을 확인할 수 있다.

 show table status where name='alter_test_tbl'\G;

*************************** 1. row ***************************

           Name: alter_test_tbl

         Engine: InnoDB

…

 Auto_increment: 10026945

…

 

ALTER TABLE alter_test_tbl AUTO_INCREMENT=20000000,

ALGORITHM=INPLACE, LOCK=NONE;

 

INSERT INTO alter_test_tbl (b,c,d) VALUES (uuid(),uuid(),uuid());

 

show table status where name='alter_test_tbl'\G;

*************************** 1. row ***************************

           Name: alter_test_tbl

         Engine: InnoDB

…

 Auto_increment: 10026945

…

 

SELECT * FROM alter_test_tbl ORDER BY a DESC LIMIT 2;

 

+----------+----------+----------+----------+

| a        | b        | c        | d        |

+----------+----------+---------------------+

| 20000000 | 205f4495 | 205f44b0 | 205f44b4 |

| 10026944 | 4e077875 | 4e077895 | 0        |

+----------+----------+----------+----------+

 

NULL 허용 여부 변경

Inplace 알고리즘을 사용할 수 있으나 Table Rebuild가 결국 일어나기 때문에 비용이 많이 드는 작업이다.

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 MODIFY d VARCHAR(1000) NOT NULL,

ALGORITHM=INPLACE, LOCK=NONE;

 

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) | NO   |     | NULL    |                |

+-------+---------------+------+-----+---------+----------------+

 

ALTER TABLE alter_test_tbl MODIFY d VARCHAR(1000) NULL,

ALGORITHM=INPLACE, LOCK=NONE;

 

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

+-------+---------------+------+-----+---------+----------------+

 

ENUM 과 SET Type 변경

기본적으로 Instant 알고리즘을 사용가능 하지만 set의 경우 저장소의 크키가 변경될 시에는 Table 복사본이 필요해지고 Table rebuild가 일어나 고비용이 발생된다.

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 ENUM('a', 'b', 'c', 'd'),

ALGORITHM=INSTANT;

ALTER TABLE alter_test_tbl ADD f SET('a', 'b', 'c', 'd'),

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     | enum('a','b','c','d') | YES  |     | NULL    |                |

| f     | set('a','b','c','d')  | YES  |     | NULL    |                |

+-------+-----------------------+------+-----+---------+----------------+

 

ALTER TABLE alter_test_tbl

MODIFY e ENUM('a', 'b', 'c', 'd', 'e' , 'f', 'g', 'h', 'i', 'l'),

ALGORITHM=INSTANT;

 

desc alter_test_tbl;

+-------+-----------------------------------------------+

| Field | Type                                          |

+-------+-----------------------------------------------+

| a     | int                                           |

| b     | varchar(100)                                  |

| c     | varchar(100)                                  |

| d     | varchar(1000)                                 |

| e     | enum('a','b','c','d','e','f','g','h','i','l') |

| f     | set('a','b','c','d')                          |

+-------+-----------------------------------------------+

 

ALTER TABLE alter_test_tbl

MODIFY f SET('a', 'b', 'c', 'd', 'e' , 'f', 'g', 'h', 'i', 'l'),

ALGORITHM=INSTANT;

 

ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported.

Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY/INPLACE.

 

ALTER TABLE alter_test_tbl

MODIFY f SET('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h'),

ALGORITHM=INSTANT;

 

desc alter_test_tbl;

+-------+-----------------------------------------------+

| Field | Type                                          |

+-------+-----------------------------------------------+

| a     | int                                           |

| b     | varchar(100)                                  |

| c     | varchar(100)                                  |

| d     | varchar(1000)                                 |

| e     | enum('a','b','c','d','e','f','g','h','i','l') |

| f     | set('a','b','c','d','e','f','g','h')          |

+-------+-----------------------------------------------+

 

참조

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-primary-key-operations

https://myinfrabox.tistory.com/61

https://dolphhong.tistory.com/229