MySQL Online DDL - InnoDB Online DDL - Alter Online

Last Updated on 6월 8, 2021 by 태랑(정현호)

Fast Index Creation

예전 버전의(5.1~5.5) 에서는 Secondary Index에 대한 Fast Index Creation 외에는 테이블 변경 작업시(DDL) Online 되지 않아 다른 세션의 read , write 작업 모두가 blocking 되어었습니다.

이 버전(예전 버전)에서는 테이블 스키마 구조를 변경 하게 되면

임시 빈 테이블 생성 -> 새 테이블에 하나씩 one-by-one 복사 -> 행이 삽입 될 때 인덱스를 업데이트 수행

그리고 원본 테이블의 모든 행이 새로운 테이블로 복사 -> 원본 테이블이 삭제되고 새로운 복사본 테이블의 이름이 원본 테이블 이름으로 rename 되는 형식으로 처리되었습니다.

MySQL 5.5 이상 또는 InnoDB 플러그인이 있는 MySQL 5.1을 사용하면 InnoDB 테이블에 대한 Secondary Index 생성 및 삭제가 이전보다 훨씬 빠르게 수행 할 수 있게 기능이 개선되었습니다

이전 포스팅 참조 : Fast Index Creation In InnoDB 기능

Mysql Clustered Index 와 Fast Index Creation In InnoDB

InnoDB - PK - Clustered Index Clustered Index(클러스터형 인덱스)기본 키(PK) 인덱스에 대한 InnoDB 용어입니다 InnoDB의 테이블은 Primary Key 값을 기반으로 구성되며 Cluster 라는 용어 의미대로 PK 값이 비슷한 레코드끼리

* Mysql 5.6 의 내용으로 5.7버전은 아래에 이어지게 됩니다.

MySQL Online DDL

Fast Index Creation 기능이 추가된 5.5 버전의 DDL 처리에 대한 부분이 개선되어 MySQL 5.6 부터 DDL 작업에 대해서 Online DDL 이 추가 되었습니다.

Online DDL 기능은 테이블 복사 피하기 위해 CREATE INDEX 및 DROP INDEX를 최적화 한 MySQL 5.5에서 사용할 수있는 Fast Index Creation In InnoDB 기능을 기반으로합니다

Online DDL 기능은 In-Place 방식의 테이블 변경 과 concurrent DML을 지원하며 이 기능의 장점은 다음과 같습니다.

- 비지니스적인 다운타임이 불가능한 중요 업무의 운영 환경에서 응답성과 가용성이 향상
- LOCK 절을 사용하여 DDL 작업 중에 성능과 동시성 간의 균형을 조정하는 기능을 제공(Lock 방식 선택)
- 테이블 복사 방법보다 디스크 공간 사용량과 I/O 오버 헤드가 적으며 Undo 를 생성하지 않아 더 효율적인 처리

*5.6, 5.7 ,8.0 버전 마다 Online DDL의 지원범위가 조금씩은 상이 함으로 버전별로 확인은 필요 합니다.

https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html

Online - Algorithm

우선 Online DDL 수행시 ALTER TABLE 작업은 다음 알고리즘 중 하나를 선택하여 처리 됩니다.

ALGORITHM 구문와 아래와 같이 지정할 수 있습니다.

- INPLACE : 인프레이스로 방식으로 수행되며 원본 테이블에 직접 변경 작업이 발생 합니다.
작업의 준비(during preparation) 및 실행 단계에서(execution phases of the operation) 테이블에 대한 배타적 메타 데이터 잠금(exclusive metadata lock)이 잠깐 동안 수행 될 수 있습니다.

일반적으로 Concurrent DML이 지원됩니다.

InnoDB 테이블의 Online DDL 작업 중에 발생되는 concurrent DML에 대한 임시 로그 파일 크기로 innodb_online_alter_log_max_size 지정된 사이즈 만큼 저장이 가능하고 크기를 초과하면 온라인 DDL 작업이 실패하고 Concurrent DML 작업이 롤백됩니다

innodb_online_alter_log_max_size default 값은 128MB 입니다.

이 임시 파일은 DDL 작업 중에 테이블에 Concurrent DML 데이터를 저장합니다.
임시 파일은 innodb_sort_buffer_size 값 단위로 사용되며 최대 innodb_online_alter_log_max_size에 값까지 사용 가능 합니다.

따라서 이 옵션의 값이 크면 Online DDL 작업 중에 더 많은 DML 에 대해서 처리가 가능하지만 ,다시 임시 로그의 데이터를 적용하기 위해서 DDL 작업이 끝나는 때의 기간도 연장됩니다.

# INPLACE 에 가능 여부 확인
INPLACE 에 대해서 명시적으로 지원여부를 확인 하려면 ALGORITHM=INPLACE 구문을 사용 할 수 있습니다.

만약 지원하지 않는 DDL 구문이라면 아래와 같이 에러가 발생 됩니다.

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY

- COPY : DDL 수행시 원본 테이블의 복사본 테이블을(복제 하는 방식) 생성 하여 row 단위로 새로운 테이블에 one-by-one 복사를 하는 처리 방식을 사용 합니다.

복사가 완료 된 후 복사본 테이블이 원본 테이블명으로 rename 처리 되는 방식 입니다.
Concurrent DML은 허용되지 않습니다(select 는 가능, LOCK=SHARED 가능)

COPY 알고리즘의 단점

ALGORITHM=COPY 사용시
- DDL 작업을 일시 중지하거나 작업 중에 I/O 또는 CPU 사용을 제한하는 메커니즘은 없음
- 작업 중 문제 발생시 롤백이 발생될 수 있으며 롤백은 많은 비용이 소모 되는 작업임
- ALTER 테이블 동안 Concurrent DML은 허용되지 않습니다.
- 상황에 따라서 복제 지연이 발생 소지가 있음

ALGORITHM 구문을 생략한 경우나 ALGORITHM=DEFAULT 라고 명시적으로 지정한 경우

ALGORITHM=INPLACE 이 사용 가능한 구문에 대해서는 우선 사용하며 그렇지 않을 경우 ALGORITHM=COPY 로 사용 되게 됩니다.

즉 ALGORITHM 구문 미사용시 INPLACE -> COPY 순으로 처리됩니다.

The LOCK clause

LOCK 절은 테이블에 대한 동시 액세스 수준을 미세 조정하는 데 유용합니다. ALGORITHM 절은 수행되는 방식에 따라서 주로 성능적인 차이를 보이는데 반해 LOCK 절은 동시 Access 에 대한 차이를 보입니다.

기본적으로 MySQL은 DDL 작업 중에 가능한 한 작은 Level의 Lock을 사용합니다. 필요한 경우 더 제한적인 잠금을 적용하기 위해 LOCK 절을 지정할 수 있습니다.

LOCK 절이 특정 DDL 조작에 허용되는 것보다 더 적은 제한적인 Lock 레벨을 지정하면 SQL Statement는 오류와 함께 실패합니다.
LOCK 절은 가장 제한적인 순서로 아래에 설명되어 있습니다.

LOCK=NONE
Concurrent query 와 Concurrent DML 을 허용 합니다.

LOCK=SHARED
Concurrent query 허용 되지만 Concurrent DML 불가합니다.

LOCK=DEFAULT
수행 가능한 동시성 작업에 대해서 허용 합니다(concurrent queries, concurrent DML, or both)
LOCK clause 를 생략할 경우 LOCK=DEFAULT 과 동일 합니다.

LOCK=EXCLUSIVE
Concurrent query 와 Concurrent DML 둘다 불가 합니다.
빠른 시간 내에 DDL이 완료 되거나 , 동시 쿼리나 DML 의 엑세스가 필요하지 않을 경우 사용 합니다.

Online DDL 및 Metadata Lock

Online DDL 작업은 세 단계로(3 phases) 구분 됩니다.

Phase 1: Initialization

초기화 단계에서 Storage Engine 기능, 명령문으 지정된 작업, 사용자 지정의 algorithm 및 LOCK 옵션을 고려하여 작업에 허용가능한 옵션을 결정 합니다
이 단계에서 테이블의 정의를 보호하기 위해 가능한 shared upgradeable metadata lock 을 수행 합니다.

Phase 2: Execution

이 단계에서는 명령문이 준비되고 실행됩니다.
Metadata lock 의 exclusive 여부는 Phase 1 평가된 내역에 따라 다르며, exclusive Metadata lock 이 필요한 경우 alter 문을 준비하는 동안 잠깐 수행됩니다

Phase 3: Commit Table Definition

테이블의 구조가 완료 되는 phase 입니다.
metadata lock은 테이블 정의를 커밋하기 위해 exclusive로 업그레이드됩니다 배타적 메타 데이터 lock은 기간은 매우 짧습니다.

위에 설명 된 exclusive metadata lock은 요구 사항으로 인해 온라인 DDL 작업은 커밋 또는 롤백을 위해 테이블에 metadata lock을 보유하는 동시 트랜잭션을 기다려야 할 수 있습니다.

Online DDL 작업에서 exclusive metadata lock은 테이블의 후속 변경 트랜잭션을 차단합니다.

Online DDL - Support Detail 5.6

Category	Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Index Operations	Creating or adding a secondary index	Yes	No	Yes	No
	Dropping an index	Yes	No	Yes	Yes
	Adding a FULLTEXT index	Yes*	No*	No	No
	Changing the index type	Yes	No	Yes	Yes

Primary Key Operations	dding a primary key	Yes*	Yes*	Yes	No
	Dropping a primary key	No	Yes	No	No
	Dropping a primary key and adding another	Yes	Yes	Yes	No

Column Operations	Adding a column	Yes	Yes	Yes*	No
	Dropping a column	Yes	Yes	Yes	No
	Renaming a column	Yes	No	Yes*	Yes
	Reordering columns	Yes	Yes	Yes	No
	Setting a column default value	Yes	No	Yes	Yes
	Changing the column data type	No	Yes	No	No
	Dropping the column default value	Yes	No	Yes	Yes
	Changing the auto-increment value	Yes	No	Yes	No*
	Making a column NULL	Yes	Yes*	Yes	No
	Making a column NOT NULL	Yes*	Yes*	Yes	No
	Modifying the definition of an ENUM or SET column	Yes	No	Yes	Yes

Foreign Key Operations	ng a foreign key constraint	Yes*	No	Yes	Yes
	Dropping a foreign key constraint	Yes	No	Yes	Yes

Table Operations	Changing the ROW_FORMAT	Yes	Yes	Yes	No
	Changing the KEY_BLOCK_SIZE	Yes	Yes	Yes	No
	Setting persistent table statistics	Yes	No	Yes	Yes
	Specifying a character set	Yes	Yes*	No	No
	Converting a character set	No	Yes	No	No
	Optimizing a table	Yes*	Yes	Yes	No
	Rebuilding with the FORCEoption	Yes*	Yes	Yes	No
	Performing a null rebuild	Yes*	Yes	Yes	No
	Renaming a table	Yes	No	Yes	Yes

Partitioning Operations

ALTER TABLE Partitioning 절을 제외하고 partitioned InnoDB 테이블에 대한 Online DDL 작업은 일반 InnoDB 테이블에 적용되는 동일한 규칙을 따라서 수행됩니다.

ALTER TABLE partitioning 절은 일반 InnoDB테이블과 달리 Online DDL API를 거치지 않으며 대부분은 ALGORITHM 및 LOCK 절을 지원하지 않습니다

ALTER TABLE 구문에서 Partitioning 절을 사용한다면, Partitioned table은 ALTER TABLE COPY algorithm 을 이용하여 repartitioned 되게 됩니다.

즉, 새로운 파티션 테이블 사용하여 새로운 파티션을 생성하게 됩니다.

새로 생성 된 테이블에는 모든 변경 사항이 포함 되며 테이블 데이터는 새 테이블 구조로 복사됩니다

# Online DDL - Partitioning
MySQL 5.7 에서는 테이블로 더 상세한 내용이 기술되어 있습니다.

ALTER TABLE Partitioning 절이 일반 InnoDB테이블 의 Alter 와 다르게 Internal Online DDL API를 거치지 않더라도 MySQL은 가능한 경우 데이터 복사 및 잠금을 최소화하려고합니다.

- partitioned by RANGE or LIST 에 대해서 ADD PARTITION and DROP PARTITION 기존 데이터를 copy 하지 않습니다.

- TRUNCATE PARTITION 는 기존 데이터를 copy 하지 않습니다.

- tables partitioned by HASH or LIST 에 대해서 ADD PARTITION and COALESCE PARTITION 작업 진행 간 Concurrent queries는 허용 됩니다
MySQL은 copies the data 하는 동안 LOCK을 shared mode 로 획득 하게 됩니다.

- partitioned by LINEAR HASH or LIST 로 구성된 테이블에 대해서 REORGANIZE PARTITION, REBUILD PARTITION, or ADD PARTITION or COALESCE PARTITION 수행은 concurrent queries 수행이 허용 됩니다.
MySQL은 copies the data 하는 동안 LOCK을 shared mode 로 획득 하게 됩니다.

[참조] partition by range 에 대해서 REORGANIZE PARTITION 수행시 5.7 버전에서는 ALGORITHM=INPLACE 이 불가능하지만 8.0 에서는 ALGORITHM=INPLACE(Online) 이 가능합니다.

- PARTITION BY 구문절과 REMOVE PARTITIONING 에서 concurrent queries 는 제한됩니다.

- ALTER TABLE의 PARTITION BY 변경 관련된 구문에서는 ALGORITHM=DEFAULT, ALGORITHM=INPLACE, and ALGORITHM=COPY 구문은 허용되지 않습니다

[SUB]PARTITION BY [LINEAR] KEY에서 사용되는 ALGORITHM 은 ALGORITHM=1 or 2 를 지정할 수 있으며 ALGORITHM=1 은 5.1 과 동일한 Key-hasing function 을 사용하게 되고 ALGORITHM=2 는 Mysql 5.5 and later 에서 사용하는 Key-hasing function 을 사용 하게 됩니다.

Default 는 ALGORITHM=2 이며 ALGORITHM=2로 생성된 Key 파티션테이블은 5.1 버전에서 사용할수 없습니다.

버전간 다운그레이드 할 때 사용하거나 Mysql 5.5 이후 버전에서 Mysql 5.1의 Key-Hasing Function을 사용한 Key or Linear Key 파티션을 생성하기 위해 사용 됩니다.
또한 Lock 절도 지원되지 않습니다.

ex) ALTER TABLE p PARTITION BY LINEAR KEY ALGORITHM=2 (id) PARTITIONS 32;

Online DDL - Support Detail 5.7

Mysql 5.7 에서는 Online DDL이 몇가지가 추가 되었습니다.
아래 표에서 5.7 New 컬럼에 체크 되어 있는 항목 입니다.

Category	Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata	5.7 New
Index Operations	Creating or adding a secondary index	Yes	No	Yes	No
	Dropping an index	Yes	No	Yes	Yes
	Renaming an index	Yes	No	Yes	Yes	V
	Adding a FULLTEXT index	Yes*	No*	No	No
	Adding a SPATIAL index	Yes	No	No	No	V
	Changing the index type	Yes	No	Yes	Yes

Primary Key Operations	Adding a primary key	Yes*	Yes*	Yes	No
	Dropping a primary key	No	Yes	No	No
	Dropping a primary key and adding another	Yes	Yes	Yes	No

Column Operations	Adding a column	Yes	Yes	Yes*	No
	Dropping a column	Yes	Yes	Yes	No
	Renaming a column	Yes	No	Yes*	Yes
	Reordering columns	Yes	Yes	Yes	No
	Setting a column default value	Yes	No	Yes	Yes
	Changing the column data type	No	Yes	No	No
	Extending VARCHAR column size	Yes	No	Yes	Yes	V
	Dropping the column default value	Yes	No	Yes	Yes
	Changing the auto-increment value	Yes	No	Yes	No*
	Making a column NULL	Yes	Yes*	Yes	No
	Making a column NOT NULL	Yes*	Yes*	Yes	No
	Modifying the definition of an ENUM or SET column	Yes	No	Yes	Yes

Generated Column Operations	Adding a STORED column	No	Yes	No	No	V
	Modifying STORED column order	No	Yes	No	No	V
	Dropping a STORED column	Yes	Yes	Yes	No	V
	Adding a VIRTUAL column	Yes	No	Yes	Yes	V
	Modifying VIRTUAL column order	No	Yes	No	No	V
	Dropping a VIRTUAL column	Yes	No	Yes	Yes	V

Foreign Key Operations	Adding a foreign key constraint	Yes*	No	Yes	Yes
	Dropping a foreign key constraint	Yes	No	Yes	Yes

Table Operations	Changing the ROW_FORMAT	Yes	Yes	Yes	No
	Changing the KEY_BLOCK_SIZE	Yes	Yes	Yes	No
	Setting persistent table statistics	Yes	No	Yes	Yes
	Specifying a character set	Yes	Yes*	No	No
	Converting a character set	No	Yes*	No	No
	Optimizing a table	Yes*	Yes	Yes	No
	Rebuilding with the FORCE option	Yes*	Yes	Yes	No
	Performing a null rebuild	Yes*	Yes	Yes	No
	Renaming a table	Yes	No	Yes	Yes

Tablespace Operations	Enabling or disabling file-per-table tablespace encryption	No	Yes	No	No	V

Partitioning Clause	In Place	Permits DML	Notes
PARTITION BY	No	No	Permits ALGORITHM=COPY, LOCK={DEFAULT\|SHARED\|EXCLUSIVE}
ADD PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Does not copy existing data for tables partitioned by RANGE or LIST. Concurrent queries are permitted for tables partitioned by HASH or LIST. MySQL copies the data while holding a shared lock.
DROP PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Does not copy existing data for tables partitioned by RANGE or LIST.
DISCARD PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT
IMPORT PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT
TRUNCATE PARTITION	Yes	Yes	Does not copy existing data. It merely deletes rows; it does not alter the definition of the table itself, or of any of its partitions.
COALESCE PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Concurrent queries are permitted for tables partitioned by HASH or LIST, as MySQL copies the data while holding a shared lock.
REORGANIZE PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Concurrent queries are permitted for tables partitioned by LINEAR HASH or LIST. MySQL copies data from affected partitions while holding a shared metadata lock.
EXCHANGE PARTITION	Yes	Yes
ANALYZE PARTITION	Yes	Yes
CHECK PARTITION	Yes	Yes
OPTIMIZE PARTITION	No	No	ALGORITHM and LOCK clauses are ignored. Rebuilds the entire table. SeeSection 22.3.4, “Maintenance of Partitions”.
REBUILD PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Concurrent queries are permitted for tables partitioned by LINEAR HASH or LIST. MySQL copies data from affected partitions while holding a shared metadata lock.
REPAIR PARTITION	Yes	Yes
REMOVE PARTITIONING	No	No	Permits ALGORITHM=COPY, LOCK={DEFAULT\|SHARED\|EXCLUSIVE}

5.7에서 새로 추가된 Extending VARCHAR column size 부분에 대해서 살펴 보겠습니다.

먼저 varchar의 바이트는 사용되는 character set 에 의존하여 계산됩니다.
utf8 은 3바이트 이며, utf8mb4 는 4바이트 입니다

utf8 인 varchar(20) 은 20*3 = 60byte 이고
utf8mb4 인 varchar(20) 은 20*4 = 80 byte 입니다.

varchar 의 크기(byte)가 0~ 255 byte 일때 inplace 가능하며
256byte ~ 이상으로 일때 inplace 가능합니다.
즉 위의 2가지 경우에서 inlpace 로 변경 할 수 있습니다.

그러므로 255byte -> 256byte 이상은 COPY 방식이 사용 됩니다.

크기가 0 ~ 255 바이트 인 VARCHAR 열의 경우 값을 인코딩하는 데 1 바이트 길이가 필요하고
256 바이트 이상의 VARCHAR 컬럼의 경우 2 바이트 길이가 필요합니다.

값의 길이정보를 담는 바이트에 의해서 0~255 / 256~ 이상 2부분으로 나뉘게 됩니다.

결과적으로 Inplace는 ALTER TABLE은 VARCHAR 열 크기를 0에서 255 바이트 내에서
또는 256 바이트에서 ~ 더 큰 크기로만 지원합니다

그외 5.7에서 추가 된 Online DDL은 Index 에서는 Renaming Index와 Adding Spatial Index이고 STORED/VIRTUAL Column 에 관한 부분이 추가 되었습니다

또한 Tablespace Operation이 5.7 버전에서 추가 되었습니다

Online DDL - Support Detail 8.0

MySQL 8.0.12 버전부터 ADD COLUMN 및 그외 DDL 작업 시 ALGORITHM=INSTANT 이 지원되기 시작하였습니다.

ALGORITHM=INSTANT support is available for ADD COLUMN and other operations in MySQL 8.0.12.

ALGORITHM=INSTANT 는 Data Dictonary의 메타 데이터 만 수정을 하고, 테이블에 대한 메타 데이터 잠금이 수행되지 않으며 테이블 데이터가 영향을받지 않으므로 작업이 즉시 수행됩니다

또한 동시 DML 수행도 영향 받지 않습니다

ALGORITHM 절은 선택 사항이며 ALGORITHM 절이 생략되면 MySQL은 ALGORITHM = INSTANT를 사용됩니다.

사용이 불가능할 경우 ALGORITHM = INPLACE가 사용됩니다.
ALGORITHM = INPLACE가 지원되지 않으면 ALGORITHM = COPY가 사용됩니다

즉 ALGORITHM 절 생략시 INSTANT -> INPLACE -> COPY 순으로 적용됩니다.

# INSTANT 에 가능 여부 확인
INSTANT 가 지원되지 않은 구분에서 사용하면 이전의 INPLACE의 경우처럼 에러가 발생하게 됩니다.

INSTANT 에 대해서 사용 가능여부를 명시적으로 확인 하려면 ALGORITHM=INSTANT 구문을 사용 할 수 있습니다.

만약 지원하지 않는 DDL 구문이라면 아래와 같이 에러가 발생 됩니다.

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY

8.0 에서 지원되는 목록은 아래와 같고 INSTANT 지원은 별도의 색으로 표기 해두었습니다.

Category	Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Index Operations	Creating or adding a secondary index	No	Yes	No	Yes	No
	Dropping an index	No	Yes	No	Yes	Yes
	Renaming an index	No	Yes	No	Yes	Yes
	Adding a FULLTEXT index	No	Yes*	No*	No	No
	Adding a SPATIAL index	No	Yes	No	No	No
	Changing the index type	Yes	Yes	No	Yes	Yes

Primary Key Operations	Adding a primary key	No	Yes*	Yes*	Yes	No
	Dropping a primary key	No	No	Yes	No	No
	Dropping a primary key and adding another	No	Yes	Yes	Yes	No

Column Operations	Adding a column	Yes*	Yes	No*	Yes*	No
	Dropping a column	No	Yes	Yes	Yes	No
	Renaming a column	No	Yes	No	Yes*	Yes
	Reordering columns	No	Yes	Yes	Yes	No
	Setting a column default value	Yes	Yes	No	Yes	Yes
	Changing the column data type	No	No	Yes	No	No
	Extending VARCHARcolumn size	No	Yes	No	Yes	Yes
	Dropping the column default value	Yes	Yes	No	Yes	Yes
	Changing the auto-increment value	No	Yes	No	Yes	No*
	Making a column NULL	No	Yes	Yes*	Yes	No
	Making a column NOT NULL	No	Yes*	Yes*	Yes	No
	Modifying the definition of an ENUM or SET column	Yes	Yes	No	Yes	Yes

Generated Column Operations	Adding a STORED column	No	No	Yes	No	No
	Modifying STORED column order	No	No	Yes	No	No
	Dropping a STOREDcolumn	No	Yes	Yes	Yes	No
	Adding a VIRTUAL column	Yes	Yes	No	Yes	Yes
	Modifying VIRTUALcolumn order	No	No	Yes	No	No
	Dropping a VIRTUALcolumn	Yes	Yes	No	Yes	Yes

Foreign Key Operations	Adding a foreign key constraint	No	Yes*	No	Yes	Yes
	Dropping a foreign key constraint	No	Yes	No	Yes	Yes

Table Operations	Changing the ROW_FORMAT	No	Yes	Yes	Yes	No
	Changing the KEY_BLOCK_SIZE	No	Yes	Yes	Yes	No
	Setting persistent table statistics	No	Yes	No	Yes	Yes
	Specifying a character set	No	Yes	Yes*	No	No
	Converting a character set	No	No	Yes*	No	No
	Optimizing a table	No	Yes*	Yes	Yes	No
	Rebuilding with the FORCE option	No	Yes*	Yes	Yes	No
	Performing a null rebuild	No	Yes*	Yes	Yes	No
	Renaming a table	Yes	Yes	No	Yes	Yes

Tablespace Operations	Renaming a general tablespace	No	Yes	No	Yes	Yes
	Enabling or disabling general tablespace encryption	No	Yes	No	Yes	No
	Enabling or disabling file-per-table tablespace encryption	No	No	Yes	No	No

• 파티션 작업 관련

파티션에서는 INSTANT 작업을 지원하지 않습니다.

Partitioning Clause	Instant	In Place	Permits DML	Notes
PARTITION BY	No	No	No	Permits ALGORITHM=COPY, LOCK={DEFAULT\|SHARED\|EXCLUSIVE}
ADD PARTITION	No	Yes	Yes	ALGORITHM=INPLACE, LOCK={DEFAULT\|NONE\|SHARED\|EXCLUSISVE} is supported for RANGE and LIST partitions, ALGORITHM=INPLACE, LOCK={DEFAULT\|SHARED\|EXCLUSISVE} for HASH and KEY partitions, and ALGORITHM=COPY, LOCK={SHARED\|EXCLUSIVE} for all partition types. Does not copy existing data for tables partitioned by RANGE or LIST. Concurrent queries are permitted with ALGORITHM=COPY for tables partitioned by HASH or LIST, as MySQL copies the data while holding a shared lock.
DROP PARTITION	NO	Yes	Yes	ALGORITHM=INPLACE, LOCK={DEFAULT\|NONE\|SHARED\|EXCLUSIVE} is supported. Does not copy data for tables partitioned by RANGE or LIST. DROP PARTITION with ALGORITHM=INPLACE deletes data stored in the partition and drops the partition. However, DROP PARTITION with ALGORITHM=COPY or old_alter_table=ON rebuilds the partitioned table and attempts to move data from the dropped partition to another partition with a compatible PARTITION ... VALUES definition. Data that cannot be moved to another partition is deleted.
DISCARD PARTITION	No	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT
IMPORT PARTITION	No	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT
TRUNCATE PARTITION	No	Yes	Yes	Does not copy existing data. It merely deletes rows; it does not alter the definition of the table itself, or of any of its partitions.
COALESCE PARTITION	No	Yes	No	ALGORITHM=INPLACE, LOCK={DEFAULT\|SHARED\|EXCLUSIVE} is supported.
REORGANIZE PARTITION	No	Yes	No	ALGORITHM=INPLACE, LOCK={DEFAULT\|SHARED\|EXCLUSIVE} is supported.
EXCHANGE PARTITION	No	Yes	Yes
ANALYZE PARTITION	No	Yes	Yes
CHECK PARTITION	No	Yes	Yes
OPTIMIZE PARTITION	No	No	No	ALGORITHM and LOCK clauses are ignored. Rebuilds the entire table. SeeSection 22.3.4, “Maintenance of Partitions”.
REBUILD PARTITION	No	Yes	No	ALGORITHM=INPLACE, LOCK={DEFAULT\|SHARED\|EXCLUSIVE} is supported.
REPAIR PARTITION	No	Yes	Yes
REMOVE PARTITIONING	No	No	No	Permits ALGORITHM=COPY, LOCK={DEFAULT\|SHARED\|EXCLUSIVE}

Ref Link
https://mydbops.wordpress.com/2020/03/04/an-overview-of-ddl-algorithms-in-mysql-covers-mysql-8/
https://dev.mysql.com/doc/refman/5.6/en/alter-table-partition-operations.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html
https://dev.mysql.com/doc/refman/5.6/en/alter-table.html
https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

관련된 글

Mysql Clustered Index 와 Fast Index Creation In InnoDB

InnoDB - PK - Clustered Index Clustered Index(클러스터형 인덱스)기본 키(PK) 인덱스에 대한 InnoDB 용어입니다 InnoDB의 테이블은 Primary Key 값을 기반으로 구성되며 Cluster 라는 용어 의미대로 PK 값이 비슷한 레코드끼리 묶어서 저장되어 사용하고 그것을 Clustered Index(PK) 라고 합니다. PK 기반의 테이블 구조이기 때문에 최상의 성능을 위해서는 성능에 가장 중요한 쿼리를 기반으로 신중하게 기본 키 컬럼이 선택되어야 합니다.

태랑(정현호)

Senior DBA(MySQL, Oracle) - 현재 위메프에서 많은 새로움을 경험중입니다
At WeMakePrice / Previous - Oracle Korea ACS Support / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io

답글 남기기 답글 취소하기