MySQL 버전이 업그레이드 되면서 Online 으로 수행할 수 있는 DDL (ALTER TABLE) 이 더 많아지고 있다.
Online DDL 은 DDL 구문을 수행할 때, 변경되는 Object 에 동시 DML (insert, update, delete) 쿼리 수행 가능하다는 것이 기본적인 기준이 된다.
모든 DDL 이 Online 으로 처리될 수 있는 것은 아니며,
Online 으로 수행하더라도 temp file 을 생성하여 DB 서버의 Disk 공간을 사용하는 동작이 있기 때문에, 사용상 주의해야 할 부분들은 숙지해야 한다.
MySQL 8.0.12 버전 부터는 테이블의 meta data 구조만 변경하는 INSTANT DDL 기능이 추가되었다.
이 post 는 다음 link 페이지를 번역한 내용이다.
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
MySQL 5.7 버전 기준의 Online DDL 은 아래 post 를 참고
https://dolphhong.tistory.com/220?category=500475
MySQL 8.0.12 부터는 ALTER TABLE 작업 시 ALGORITHM=INSTANT 가 지원된다.
ALGORITHM=INSTANT 는 data dictionary 의 metadata 만 변경한다.
Table 에 metadata lock 을 걸지 않고, table data 도 영향 받지 않으므로 작업을 즉시 수행한다.
( INSTANT DDL 이 모든 DDL 을 지원하는 것이 아니므로 반드시 "ALTER TABLE {table_name} , ALGORITHM=INSTANT" 로 가능 여부를 확인 하자. )
DDL 구문 마지막에 ALGORITHM=INSTANT 를 명시하지 않으면, 기본적으로 지원되는 작업의 ALGORITHM (INPLACE, COPY) 으로 수행된다.
- 컬럼 추가 ( instant ADD COLUMN ) --> 제한적으로 적용됨. 아래 표에서 확인
- 가상 컬럼 추가 / 삭제 ( Generated column )
- 컬럼의 default 값 추가 삭제
- ENUM 또는 SET 컬럼의 정의 수정
- 인덱스 타입 변경
- 테이블 Rename
ALGORITHM=INSTANT 를 명시했으나 해당 DDL 작업이 지원되지 않는 작업이면, 에러와 함께 구문이 실패한다.
Online DDL 작업은 크게 다음과 같은 작업으로 구분된다.
- Index Operations
- Primary Key Operations
- Column Operations
- Generated Column Operations ( virtual column )
- Foreign Key Operations
- Table Operations
- Tablespace Operations
- Partitioning Operations
메뉴얼에는 각각의 작업에 대해서 상세한 내용이 작성되어있는데
먼저, 빠른 확인을 위해 Operation 들에 대한 표 들을 모아서 정리한다.
(메뉴얼에 있는 상세한 내용은 표 정리된 내용 아래에 작성한다.)
Index Operations
| Operation | INSTANT | INPLACE | Rebuild Table (COPY) 재생성 | 동시 수행 DML 허용여부 | Metadata 만 수정 여부 |
| secondary index 생성 및 추가 | X | O | X | O | X |
| index drop (삭제) | X | O | X | O | O |
| index rename (이름변경) | X | O | X | O | O |
| FULLTEXT index 추가 | X | O | X | X | X |
| SPATIAL index 추가 | X | O | X | X | X |
| index type 변경 | O | O | X | O | O |
Primary Key Operations
| Operation | INSTANT | INPLACE | Rebuild Table | 동시 수행 DML 허용여부 | Metadata 만 수정 여부 |
| Primary key 추가 | X | O | O | O | X |
| Primary key 삭제 | X | X | O | X | X |
| Primary key 변경 (삭제 후 다른 컬럼으로 재생성) |
X | O | O | O | X |
Column Operation
| Operation | INSTANT | INPLACE | Rebuild Table | 동시 수행 DML 허용여부 | Metadata 만 수정 여부 |
| 컬럼 추가 (Add) | O (테이블의 마지막 컬럼으로 추가 가능) |
O | X | O | X |
| 컬럼 삭제 (Drop) | O (8.0.29) | O | O | O | X |
| 컬럼명 변경 (Rename) | X | O | X | O | O |
| 컬럼 순서 변경 | X | O | O | O | X |
| 컬럼 default 값 설정 | O | O | X | O | O |
| 컬럼 data type 변경 | X | X | O | X | X |
| VARCHAR 컬럼 확장 | X | O | X | O | O |
| 컬럼 default 값 삭제 | O | O | X | O | O |
| auto-increment 값 변경 | X | O | X | O | X |
| 컬럼 NULL 로 변경 | X | O | O | O | X |
| 컬럼 NOT NULL 로 변경 | X | O | O | O | X |
| ENUM, SET 컬럼 정의 수정 | O | O | X | O | O |
Generated Column Operation
| Operation | INSTANT | INPLACE | Rebuild Table | 동시 수행 DML 허용여부 | Metadata 만 수정 여부 |
| STORED 컬럼 추가 | X | X | O | X | X |
| STORED 컬럼 순서 변경 | X | X | O | X | X |
| STORED 컬럼 삭제 | X | O | O | O | X |
| VIRTUAL 컬럼 추가 | O | O | X | O | O |
| VIRTUAL 컬럼 순서 변경 | X | X | O | X | X |
| VIRTUAL 컬럼 삭제 | O | O | X | O | O |
Foreign Key Operation
| Operation | INSTANT | INPLACE | Rebuild Table | 동시 수행 DML 허용여부 | Metadata 만 수정 여부 |
| FK 제약조건 추가 | X | O | X | O | O |
| FK 제약조건 삭제 | X | X | X | O | O |
Table Operation
| Operation | INSTANT | INPLACE | Rebuild Table | 동시 수행 DML 허용여부 | Metadata 만 수정 여부 |
| ROW_FORMAT 변경 | X | O | O | O | X |
| KEY_BLOCK_SIZE 변경 | X | O | O | O | X |
| persistent table 상태 설정 | X | O | X | O | O |
| 캐릭터셋 설정 | X | O | O | X | X |
| 캐릭터셋 변경 | X | X | O | X | X |
| Optimize 테이블 (최적화) | X | O | O | O | X |
| FORCE 옵션으로 rebuild | X | O | O | O | X |
| null rebuild | X | O | O | O | X |
| 테이블명 변경 | O | O | X | O | O |
Tablespace Operation
| Operation | INSTANT | INPLACE | Rebuild Table | 동시 수행 DML 허용여부 | Metadata 만 수정 여부 |
| General Tablespace 이름변경 | X | O | X | O | O |
| General tablespace 암호화 활성/비활성화 |
X | O | X | O | X |
| file-per-table tablespace 암호화 활성화/비활성화 |
X | X | O | X | X |
Partitioning Operation
파티션 테이블 작업은 아직 instant algorithm 이 적용되지 않는다.
| Partitioning 구문 | INSTANT | INPLACE | DML 허용 |
| PARTITION BY | X | X | X |
| ADD PARTITION | O | O | |
| DROP PARTITION | O | O | |
| DISCARD PARTITIOIN | X | X | |
| IMPORT PARTITION | X | X | |
| TRUNCATE PARTITION | O | O | |
| COALESE PARTITION | O | X | |
| REORGANIZE PARTITION | O | X | |
| EXCHANGE PARTITION | O | O | |
| ANALYZE PARTITION | O | O | |
| CHECK PARTITION | O | O | |
| OPTIMIZE PARTITION | X | X | |
| REBUILD PARTITION | O | O | |
| REPAIR PARTITION | O | O | |
| REMOVE PARTITIONING | X | X |
다음은 메뉴얼에 작성되어있는 각 Operation 에 대한 상세한 설명을 포함한 내용이다.
Index Operations
INPLACE 와 INSTANT 를 헷갈리지 않도록 하자. ( INSTANT 가 MySQL 8.0 New feature 이다. )
| Operation | INSTANT | INPLACE | Rebuild Table (COPY) 재생성 | 동시 수행 DML 허용여부 | Metadata 만 수정 여부 |
| secondary index 생성 및 추가 | X | O | X | O | X |
| index drop (삭제) | X | O | X | O | O |
| index rename (이름변경) | X | O | X | O | O |
| FULLTEXT index 추가 | X | O | X | X | X |
| SPATIAL index 추가 | X | O | X | X | X |
| index type 변경 | O | O | X | O | O |
인덱스 생성 및 추가
CREATE INDEX name ON table (col_list);
ALTER TABLE table_name ADD INDEX name (col_list);
인덱스 삭제
DROP INDEX name ON table ;
ALTER TABLE table_name DROP INDEX name;
인덱스 이름 변경 (rename)
ALTER TABLE table_name RENAME INDEX old_index_name TO new_index_name, ALGORITHM=INPLACE, LOCK=NONE;
인덱스 타입 변경 (INSTANT)
ALTER TABLE tbl_name DROP INDEX i1, ADD INDEX i1 (key_part , .. ) USING BETREE, ALGORITHM=INSTANT;
Primary Key Operations
| Operation | INSTANT | INPLACE | Rebuild Table | 동시 수행 DML 허용여부 | Metadata 만 수정 여부 |
| Primary key 추가 | X | O | O | O | X |
| Primary key 삭제 | X | X | O | X | X |
| Primary key 변경 (삭제 후 다른 컬럼으로 재생성) |
X | O | O | O | X |
Primary key 추가
ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
Primary key 변경 (삭제 후 다른 것으로 추가)
ALTER TABLE tbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
Column Operation
| Operation | INSTANT | INPLACE | Rebuild Table | 동시 수행 DML 허용여부 | Metadata 만 수정 여부 |
| 컬럼 추가 (Add) | O | O | X | O | X |
| 컬럼 삭제 (Drop) | X | O | O | O | X |
| 컬럼명 변경 (Rename) | X | O | X | O | O |
| 컬럼 순서 변경 | X | O | O | O | X |
| 컬럼 default 값 설정 | O | O | X | O | O |
| 컬럼 data type 변경 | X | X | O | X | X |
| VARCHAR 컬럼 확장 | X | O | X | O | O |
| 컬럼 default 값 삭제 | O | O | X | O | O |
| auto-increment 값 변경 | X | O | X | O | X |
| 컬럼 NULL 로 변경 | X | O | O | O | X |
| 컬럼 NOT NULL 로 변경 | X | O | O | O | X |
| ENUM, SET 컬럼 정의 수정 | O | O | X | O | O |
컬럼추가 ( Adding a column )
INSTANT 알고리즘을 적용할 수 없는 컬럼 추가의 경우는 다음과 같다.
- ALGORITHM=INSTANT 를 지원하지 않는 구문을 동일한 하나의 ALTER TABLE 로 합칠 수 없다.
- 테이블의 마지막 컬럼으로 추가할 경우에만 가능하다.
- ROW_FORMAT=COMPRESSED 를 사용하는 테이블에는 INSTANT 로 추가할 수 없다.
- FULLTEXT 인덱스를 포함한 테이블에는 INSTANT 로 추가할 수 없다.
- Temporary table 에는 INSTANT 로 추가할 수 없다. ( only COPY 방식 사용 가능 )
- dictionary tablespace 의 테이블에는 추가할 수 없다.
- 컬럼 추가 시 row size 제한은 고려되지 않는다. 그러나 해당 테이블에 DML (insert, update) 이 수행되는 동안에는 row size 제한이 확인된다.
auto-increment 컬럼을 추가할 때는 동시 DML 이 허용되지 않는다.
data is reorganized substantially ( 실질적으로 데이터가 재구성 ) 되므로 고비용 작업이며, 최소 ALGORITHM=INPLACE, LOCK=SHARED 가 필요하다.
INPLACE 방식으로 컬럼 추가 시 테이블은 재생성된다.
컬럼 삭제
COPY 또는 INPLACE 로 수행해야 한다.
mysql> alter table test_tbl drop column var3, algorithm=INSTANT ;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.2023.08 Updates
INSTANT is the default algorithm as of MySQL 8.0.29, and INPLACE before that.
There are some conditions for dropping columns. https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
컬럼 Rename
mysql> alter table test_tbl change var3 var3_cp varchar(200) DEFAULT NULL, algorithm=INSTANT ;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.INPLACE 로 수행해야 한다.
mysql> alter table test_tbl change var3 var3_cp varchar(200) DEFAULT NULL, algorithm=inplace ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
데이터 타입과 NULL 여부가 동일하면, 컬럼명 변경은 online 으로 변경 가능하다. (바로 완료됨)
FK constraint 의 일부 컬럼의 rename 도 가능하다. FK 정의가 자동적으로 새로운 컬럼명으로 업데이트 된다.
Generated column 에서는 ALGORITHM=INPLACE 를 지원하지 않는다.
컬럼 재 정렬 ( FIRST )
COPY 또는 INPLACE 로 작업해야 한다.
실질적으로 데이터가 재생성되므로 고비용 작업이다.
mysql> alter table test_tbl modify column var3_cp varchar(200) DEFAULT NULL FIRST, algorithm=instant ;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
컬럼 데이터 타입 변경
데이터 타입 변경은 COPY 방식 만 가능하다.
mysql> alter table test_tbl change var3_cp var3_cp text DEFAULT NULL , algorithm=inplace ;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
VARCHAR 컬럼 사이즈 확장
varchar 타입의 길이 변경은 각각 varchar 0 ~ 255 와 256 ~ 의 범위 내에서 INPLACE 로 가능하다.
| (AS-IS) var3_cp varchar(200) DEFAULT NULL, (TO-BE) var3_cp varchar(250) DEFAULT NULL |
INPLACE 로 수행하면 정상적으로 수행된다.
mysql> alter table test_tbl change column var3_cp var3_cp varchar(250) DEFAULT NULL , algorithm=inplace ;
Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0
축소는 무조건 COPY 방식만 가능하다.
mysql> alter table test_tbl change column var2 var2 varchar(50) DEFAULT NULL, ALGORITHM=INPLACE ;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
컬럼의 DEFAULT 값 설정
mysql> alter table test_tbl alter column var3 SET DEFAULT 'xxx-xxx-xxx-xxx-xxx', ALGORITHM=INSTANT ;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
CREATE TABLE `test_tbl` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`var` varchar(200) DEFAULT NULL,
`var2` varchar(257) DEFAULT NULL,
`var3` varchar(261) DEFAULT 'xxx-xxx-xxx-xxx-xxx',
metadata 만 수정하고, 컬럼 값은 data dictionary 에 저장된다.
기존의 null 값은 update 되지 않는다.
default 값 삭제도 가능
mysql> alter table test_tbl alter column var3 DROP DEFAULT , ALGORITHM=INSTANT ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
AUTO INCREMENT 값 변경
mysql> alter table test_tbl AUTO_INCREMENT=1300000 , ALGORITHM=INPLACE ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test_tbl AUTO_INCREMENT=1300000 , ALGORITHM=INPLACE ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
메모리에 있는 값을 변경하는 것임 not the data file !
컬럼 NULL 로 만들기
mysql> alter table test_tbl modify column var3 varchar(261) NULL , ALGORITHM=INPLACE , LOCK=NONE ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
NULL 로 변경하면 inplace 로 rebuild 한다. 기존의 값이 NULL 이 되지는 않는다.
컬럼 NOT NULL 로 만들기
INPLACE 로 테이블을 재생성한다.
SQL mode 가 STRICT_ALL_TABLES 또는 STRICT_TRANS_TABLES 로 되어있어야 작업이 성공한다.
컬럼에 NULL 이 포함되어있으면 작업은 실패한다.
서버는 잠재적으로 참조 무결성이 손실될 가능성이 있는 FK 컬럼을 변경하는 것은 금지한다.
ENUM 또는 SET 컬럼 정의 변경
mysql> ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd'), ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
멤버 추가는 INSTANT 로 가능한데, 제거는 INSTANT 로 불가하다
-- 멤버추가
mysql> ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd', 'e','f','g','h','i','j','k','l','m','n') , ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 멤버 삭제
mysql> ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd'), ALGORITHM=INSTANT;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported.
Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY/INPLACE.
CREATE TABLE `t1` (
`c1` enum('a','b','c','d','e','f','g','h','i','j','k','l','m','n') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ENUM 값에서 맨 마지막 멤버로 추가하는 것은 INSTANT 로 처리가 가능하지만,
다음과 같은 경우에서는 COPY (not online) 로 처리해야 한다.
- SET 의 경우 멤버가 8 개인 컬럼에 멤버를 추가하면, 값당 필요한 공간이 1 바이트에서 2 바이트로 변경된다. 그러면 COPY 로 처리해야 한다.
- 리스트의 중간에 멤버를 추가하면 순번을 다시 정해야 하기 때문에 (renumbering) COPY 로 처리해야 한다.
SET 컬럼 정의
CREATE TABLE `myset` (
`col` set('a','b','c','d') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
mysql> alter table myset MODIFY COLUMN col set('a','b','c','d','e','f','g') , ALGORITHM=INSTANT ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table myset MODIFY COLUMN col set('a','b','c','d','e','f','g','h') , ALGORITHM=INSTANT ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table myset MODIFY COLUMN col set('a','b','c','d','e','f','g','h','i') , ALGORITHM=INSTANT ;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported.
Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY/INPLACE.
다음은 Generated Column 의 Online DDL 이다.
물리적으로 저장시키는 STORED 컬럼과 저장하지 않는 VIRTUAL 컬럼이 있다.
| Operation | INSTANT | INPLACE | Rebuild Table | 동시 수행 DML 허용여부 | Metadata 만 수정 여부 |
| STORED 컬럼 추가 | X | X | O | X | X |
| STORED 컬럼 순서 변경 | X | X | O | X | X |
| STORED 컬럼 삭제 | X | O | O | O | X |
| VIRTUAL 컬럼 추가 | O | O | X | O | O |
| VIRTUAL 컬럼 순서 변경 | X | X | O | X | X |
| VIRTUAL 컬럼 삭제 | O | O | X | O | O |
Foreign Key 의 Online DDL
| Operation | INSTANT | INPLACE | Rebuild Table | 동시 수행 DML 허용여부 | Metadata 만 수정 여부 |
| FK 제약조건 추가 | X | O | X | O | O |
| FK 제약조건 삭제 | X | X | X | O | O |
Table 작업의 Online DDL
| Operation | INSTANT | INPLACE | Rebuild Table | 동시 수행 DML 허용여부 | Metadata 만 수정 여부 |
| ROW_FORMAT 변경 | X | O | O | O | X |
| KEY_BLOCK_SIZE 변경 | X | O | O | O | X |
| persistent table 상태 설정 | X | O | X | O | O |
| 캐릭터셋 설정 | X | O | O | X | X |
| 캐릭터셋 변경 | X | X | O | X | X |
| Optimize 테이블 (최적화) | X | O | O | O | X |
| FORCE 옵션으로 rebuild | X | O | O | O | X |
| null rebuild | X | O | O | O | X |
| 테이블명 변경 | O | O | X | O | O |
persistent table 상태 설정은 다음과 같이 수행할 수 있다.
ALTER TABLE tbl_name STATS_PERSISTENT=0, STATS_SAMPLE_PAGES=20, STATS_AUTO_RECALC=1, ALGORITHM=INPLACE, LOCK=NONE;
이는 테이블의 metadata 만 수정한다.
STATS_PERSISTENT
STATS_AUTO_RECALC
STATS_SAMPLE_PAGES
위와 같은 상태를 포함한다. 자세한 내용은 https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html 를 참고한다.
테이블명 변경 ( Renaming a table )
ALTER TABLE old_tbl_name RENAME TO new_tbl_name, ALGORITHM=INSTANT;
테이블명 변경은 INPLACE, INSTANT 로 모두 수행할 수 있다. 복사 하지 않고 table_name 에 해당하는 파일이름을 변경한다.
RENAME TABLE 문법으로도 사용할 수 있다.
변경한 테이블명으로 별도의 권한이 생성되어있었다면, 수동으로 지정해줘야 한다 !
새로운 테이블명으로 반영하지 않는다.
테이블스페이스 작업 Online DDL
| Operation | INSTANT | INPLACE | Rebuild Table | 동시 수행 DML 허용여부 | Metadata 만 수정 여부 |
| General Tablespace 이름변경 | X | O | X | O | O |
| General tablespace 암호화 활성/비활성화 |
X | O | X | O | X |
| file-per-table tablespace 암호화 활성화/비활성화 |
X | X | O | X | X |
파티션 테이블 Online DDL ( Partitioning )
파티션 테이블 작업은 아직 instant algorithm 이 적용되지 않는다.
| Partitioning 구문 | INSTANT | INPLACE | DML 허용 |
| PARTITION BY | X | X | X |
| ADD PARTITION | O | O | |
| DROP PARTITION | O | O | |
| DISCARD PARTITIOIN | X | X | |
| IMPORT PARTITION | X | X | |
| TRUNCATE PARTITION | O | O | |
| COALESE PARTITION | O | X | |
| REORGANIZE PARTITION | O | X | |
| EXCHANGE PARTITION | O | O | |
| ANALYZE PARTITION | O | O | |
| CHECK PARTITION | O | O | |
| OPTIMIZE PARTITION | X | X | |
| REBUILD PARTITION | O | O | |
| REPAIR PARTITION | O | O | |
| REMOVE PARTITIONING | X | X |
