MySQL5.6 온라인 DDL 개요

14.11.1 온라인 DDL 개요

이전부터 InnoDB 테이블에서 많은 DDL 작업은 높은 비용을 필요로했습니다. 많은 ALTER TABLE 작업은 요청한 테이블 옵션과 인덱스를 사용하여 정의 된 빈 테이블을 생성하고 기존 행을 새 테이블에 하나씩 복사하여 행이 삽입 될 때마다 인덱스를 업데이트하는 방식으로 작동했습니다. 원래 테이블의 모든 행이 복사 된 후 오래된 테이블이 삭제 된 복사본의 이름을 원래 테이블의 이름으로 변경되었습니다.

MySQL 5.5 및 InnoDB Plugin을 포함 MySQL 5.1은 테이블 복사 동작을하지 않도록 CREATE INDEX 와 DROP INDEX 를 최적화했습니다. 그 기능은 빠른 인덱싱 라고했습니다. MySQL 5.6은 다른 유형의 많은 ALTER TABLE 작업을 테이블을 복사하지 않도록 확장하고 있습니다. 다른 확장은 테이블이 변경되는 동안 SELECT 쿼리와 INSERT , UPDATE 및 DELETE ( DML ) 문을 계속 처리 할 수 있습니다. 이 기능의 조합은 현재 온라인 DDL 이라고합니다.

이 새로운 메커니즘은 또한 일반적으로 보조 인덱스없이 테이블을 만들고 데이터가로드 된 후 보조 인덱스를 추가함으로써 테이블과 연관된 인덱스를 만들고로드하는 프로세스를 가속화 할 수 도 보여줍니다.

CREATE INDEX 또는 DROP INDEX 명령 구문의 변경은 필요하지 않지만,이 작업의 성능, 공간 사용 및 의미에 영향을 미치는 요인이 몇 가지 있습니다 ( 섹션 14.11.9 "온라인 DDL 제한" 을 참조 하십시오).

MySQL 5.6의 온라인 DDL 확장하여 이전에 테이블 복사 테이블에 DML 작업 블록 또는 둘 모두를 필요로하는 많은 DDL 작업이 개선됩니다. 표 14.5 "DDL 작업의 온라인 상태의 요약" 은 ALTER TABLE 문의 각 종류와 온라인 DDL 기능이 각각 어떻게 적용되는지를 보여줍니다.

ALTER TABLE 파티션 흘리는를 제외하고 분할 된 InnoDB 테이블에 대한 온라인 DDL 작업은 일반적으로 InnoDB 테이블에 적용되는 것과 같은 규칙을 따릅니다. 자세한 내용은 섹션 14.11.8 "분할 된 InnoDB 테이블에 대한 온라인 DDL" 를 참조하십시오.

"in-place?"컬럼은 오퍼레이션의 경우 ALGORITHM=INPLACE 절을 허용되는지를 보여줍니다. 권장 값은 "예"입니다.
"테이블을 복사?"컬럼은 오퍼레이션의 경우 고가의 테이블 복사 작업을 취소 할 수 있는지를 보여줍니다. 권장 값은 "아니오"입니다. ALGORITHM=INPLACE 은 허용되지만, 어느 정도의 양의 테이블 복사가 계속 필요한 작업이 몇 가지 점을 제외하고이 컬럼은 거의 "내부?"컬럼의 반대입니다.
"병렬 DML을 허용?"컬럼은 어떤 작업을 완전히 온라인으로 수행 할 수 있는지를 보여줍니다. 권장 값은 "예"입니다. DDL 동안 완전한 병렬 처리가 허용되는 것을 표현하기 위해 LOCK=NONE 을 지정할 수 있지만, MySQL은 가능하면 자동으로이 수준의 병렬성을 허용합니다. 병렬 DML이 허용되는 경우는 병렬 쿼리도 항상 허용됩니다.
"병렬 쿼리를 허용?"컬럼은 어떤 DDL 작업의 경우 해당 작업이 진행되는 동안 테이블에 대한 쿼리가 허용되는지를 보여줍니다. 권장 값은 "예"입니다. 병렬 쿼리는 모든 온라인 DDL 작업 중에 허용됩니다. 이것은 참고로 모든 셀에 표시되어있는 "예"로 표시되어 있습니다. DDL 동안 병렬 쿼리가 허용되는 것을 표현하기 위해 LOCK=SHARED 을 지정할 수 있지만, MySQL은 가능하면 자동으로이 수준의 병렬성을 허용합니다.
"주"열은 구성 옵션 설정 또는 DDL 문의 다른 조항에 따라 답이 다를 등 다른 컬럼의 "예 / 아니오"의 값에 예외가 있다면 그것에 대해 설명합니다. "예 *"와 "아니오 *"값은 이러한 추가 참고해서 반응이 다른 것을 나타냅니다.

표 14.5 DDL 작업의 온라인 상태 요약

조작	현재 위치?	테이블을 복사?	병렬 DML을 허용?	병렬 쿼리를 허용?	메모
`CREATE INDEX` , `ADD INDEX`	예 *	아니오 *	예	예	`FULLTEXT` 인덱스에는 몇 가지 제한이 있습니다. 다음 행을 참조하십시오. 현재는 작성 대상의 동일한 인덱스도 같은 `ALTER TABLE` 문 앞에 절에 의해 삭제 된 경우이 작업은 적절한하지 않습니다 (즉, 테이블을 복사합니다).
`ADD FULLTEXT INDEX`	예	아니오 *	아니오	예	사용자가 지정한 `FTS_DOC_ID` 열이없는 한 테이블의 첫 번째 `FULLTEXT` 인덱스를 만들 때 테이블 복사본이 필요합니다. 같은 테이블에서 이후의 `FULLTEXT` 인덱스는 내부에서 만들 수 있습니다.
`DROP INDEX`	예	아니오	예	예	데이터 파일이 아닌 `.frm` 파일 만 변경합니다.
`OPTIMIZE TABLE`	예	예	예	예	MySQL 5.6.17 시점에서는 `ALGORITHM=INPLACE` 을 사용합니다. `old_alter_table=1` 또는 mysqld `--skip-new` 옵션이 활성화되어있는 경우 `ALGORITHM=COPY` 가 사용됩니다. FULLTEXT 인덱스가있는 테이블에서는 온라인 DDL ( `ALGORITHM=INPLACE` )를 사용하여 `OPTIMIZE TABLE` 은 지원되지 않습니다.
컬럼의 기본값을 설정	예	아니오	예	예	데이터 파일이 아닌 `.frm` 파일 만 변경합니다.
컬럼의 자동 증가 값을 변경하려면	예	아니오	예	예	데이터 파일이 아닌 메모리에 저장된 값을 변경합니다.
외래 키 제약 조건 을 추가	예 *	아니오 *	예	예	테이블을 복사하지 않도록하려면 제약을 만드는 동안 `foreign_key_checks` 을 해제합니다.
외래 키 제약 조건 을 삭제	예	아니오	예	예	`foreign_key_checks` 옵션을 활성화하거나 비활성화 할 수 있습니다.
열 이름 바꾸기	예 *	아니오 *	예 *	예	병렬 DML을 허용하려면 동일한 데이터 형식을 유지하고 열 이름 만 변경합니다.
컬럼을 추가	예	예	예 *	예	자동 증가 컬럼을 추가 할 경우 병렬 DML이 허용되지 않습니다. `ALGORITHM=INPLACE` 은 허용되지만 데이터가 크게 개편되기 때문에 여전히 고가의 작업입니다.
열을 제거하려면	예	예	예	예	`ALGORITHM=INPLACE` 은 허용되지만 데이터가 크게 개편되기 때문에 여전히 고가의 작업입니다.
컬럼을 정렬	예	예	예	예	`ALGORITHM=INPLACE` 은 허용되지만 데이터가 크게 개편되기 때문에 여전히 고가의 작업입니다.
`ROW_FORMAT` 등록 정보 수정	예	예	예	예	`ALGORITHM=INPLACE` 은 허용되지만 데이터가 크게 개편되기 때문에 여전히 고가의 작업입니다.
`KEY_BLOCK_SIZE` 등록 정보 수정	예	예	예	예	`ALGORITHM=INPLACE` 은 허용되지만 데이터가 크게 개편되기 때문에 여전히 고가의 작업입니다.
컬럼 `NULL` 을 만들	예	예	예	예	`ALGORITHM=INPLACE` 은 허용되지만 데이터가 크게 개편되기 때문에 여전히 고가의 작업입니다.
열 `NOT NULL` 을 만들	예 *	예	예	예	`SQL_MODE` 에 `strict_all_tables` 또는 `strict_all_tables` 가 포함되어 있다면, 컬럼에 Null이 포함되어 있다고 작업이 실패합니다. `ALGORITHM=INPLACE` 은 허용되지만 데이터가 크게 개편되기 때문에 여전히 고가의 작업입니다.
열 데이터 형식을 변경	아니오	예	아니오	예
기본 키 추가	예 *	예	예	예	`ALGORITHM=INPLACE` 은 허용되지만 데이터가 크게 개편되기 때문에 여전히 고가의 작업입니다. 컬럼을 `NOT NULL` 로 변환 할 필요가있는 경우, 특정 상황에서는 `ALGORITHM=INPLACE` 이 허용되지 않습니다. 예 14.9 "기본 키의 생성 및 삭제" 를 참조하십시오.
기본 키 를 삭제하고 다른 기본 키를 추가	예	예	예	예	`ALGORITHM=INPLACE` 같은 `ALTER TABLE` 에서 새로운 기본 키를 추가하는 경우에만 허용됩니다. 데이터가 크게 개편되기 때문에 이것은 여전히 고가의 작업입니다.
기본 키 를 제거	아니오	예	아니오	예	같은 `ALTER TABLE` 문에서 새로운 기본 키를 추가하지 않고 기본 키를 삭제하는 경우 제한이 적용됩니다.
문자 집합 변환	아니오	예	아니오	예	새로운 문자 인코딩이 다른 것 인 경우에는 테이블을 다시 작성합니다.
문자 집합을 지정하는	아니오	예	아니오	예	새로운 문자 인코딩이 다른 것 인 경우에는 테이블을 다시 작성합니다.
`FORCE` 옵션을 사용하여 다시 작성	예	예	예	예	MySQL 5.6.17 시점에서는 `ALGORITHM=INPLACE` 을 사용합니다. `old_alter_table=1` 또는 mysqld `--skip-new` 옵션이 활성화되어있는 경우 `ALGORITHM=COPY` 가 사용됩니다. FULLTEXT 인덱스가있는 테이블에서는 온라인 DDL ( `ALGORITHM=INPLACE` )를 사용한 테이블 재구성은 지원되지 않습니다.
"null" `ALTER TABLE ... ENGINE=INNODB` 을 사용하여 다시 작성	예	예	예	예	MySQL 5.6.17 시점에서는 `ALGORITHM=INPLACE` 을 사용합니다. `old_alter_table=1` 또는 mysqld `--skip-new` 옵션이 활성화되어있는 경우 `ALGORITHM=COPY` 가 사용됩니다. FULLTEXT 인덱스가있는 테이블에서는 온라인 DDL ( `ALGORITHM=INPLACE` )를 사용한 테이블 재구성은 지원되지 않습니다.
테이블 수준의 지속적인 통계 옵션 ( `STATS_PERSISTENT` , `STATS_AUTO_RECALC` `STATS_SAMPLE_PAGES` )을 설정	예	아니오	예	예	데이터 파일이 아닌 `.frm` 파일 만 변경합니다.

다음 섹션에서는 병렬 DML 현재 위치 또는 둘 모두에서 실행 가능한 주 작업 각각에 대해 온라인 DDL에 관련한 기본적인 문법과 사용상의주의 사항을 나타냅니다.

보조 인덱스

보조 인덱스 를 만들 : CREATE INDEX name ON table ( col_list ) 또는 ALTER TABLE table ADD INDEX name ( col_list ) . ( FULLTEXT 인덱스를 만들 때 테이블 잠금이 계속 필요합니다.)
보조 인덱스 삭제 : DROP INDEX name ON table ; 또는 ALTER TABLE table DROP INDEX name

InnoDB 테이블의 보조 인덱스의 생성 및 삭제는 MySQL 5.5과 InnoDB Plugin을 포함 MySQL 5.1과 마찬가지로 테이블 복사 작업이 생략됩니다.

MySQL 5.6 이상에서 인덱스를 만들거나 삭제되는 동안에도 그 테이블의 읽기 및 쓰기 작업은 가능한 상태입니다. CREATE INDEX 또는 DROP INDEX 문은 인덱스의 초기 상태로 테이블의 최신 내용이 반영되도록 해당 테이블에 액세스하는 모든 트랜잭션이 완료된 후에 만 완료합니다. 이전에는 인덱스를 만들거나 삭제되는 동안 테이블을 변경하면 일반적으로 교착 상태 가 발생하고 그로 인하여 테이블에서 INSERT , UPDATE 또는 DELETE 문이 취소되었습니다.

열 특성

컬럼의 기본값을 설정 : ALTER TABLE tbl ALTER COLUMN col SET DEFAULT literal 또는 ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT

컬럼의 기본값은 InnoDB 데이터 사전 이 아니라 그 테이블의 .frm 파일 에 저장됩니다.
컬럼의 자동 증가 값의 변경 : ALTER TABLE table AUTO_INCREMENT= next_value ;

특히 복제 또는 샤드를 사용하여 분산 시스템에서는 테이블의 자동 증가 카운터를 특정 값으로 재설정 할 수 있습니다. 테이블에 삽입 된 다음 행은 자동 증가 컬럼의 지정된 값을 사용합니다. 이 기술은 또한 모든 테이블을 정기적으로 비우고 다시로드하는 데이터웨어 하우스 환경에서도 사용할 수 있습니다. 그러면 자동 증가 순서를 1부터 다시 시작할 수 있습니다.
열 이름 변경 : ALTER TABLE tbl CHANGE old_col_name new_col_name datatype

동일한 데이터 형식 [NOT] NULL 속성을 유지하여 열 이름 만 변경하는 경우이 작업은 항상 온라인으로 수행 할 수 있습니다.

이 확장의 일환으로 외래 키 제약 조건의 일부인 컬럼 이름을 변경 할 수있게되었습니다. 이것은 이전에 허용되지 않았습니다. 외부 키 정의는 새 컬럼 이름을 사용하도록 자동으로 업데이트됩니다. 외부 키에 참여하는 컬럼의 이름 변경은 ALTER TABLE 의 적절한 모드에서만 작동합니다. ALGORITHM=COPY 절을 사용하거나 기타 특정 조건에 의해 명령이 내부적으로 ALGORITHM=COPY 를 사용하는 경우 ALTER TABLE 문은 실패합니다.

외부 키

외래 키 제약 조건 의 추가 또는 삭제 :
```
 ALTER TABLE tbl1 ADD CONSTRAINT fk_name FOREIGN KEY index ( col1 ) REFERENCES tbl2 ( col2 ) referential_actions ;
 ALTER TABLE tbl DROP FOREIGN KEY fk_name ;
```
외부 키의 삭제는 foreign_key_checks 옵션을 활성화하거나 비활성화 된 상태에서 온라인으로 할 수 있습니다. 외부 키를 온라인으로 만들려면, foreign_key_checks 가 비활성화되어 있어야합니다.

특정 테이블의 외래 키 제약 조건의 이름을 모르는 경우 다음 문을 실행하여 각 외부 키에 대한 CONSTRAINT 절에서 제한 조건 이름을 찾습니다.
```
 show create TABLE table \ G
```
또는 information_schema.table_constraints 테이블을 쿼리하고 constraint_name 및 constraint_type 컬럼을 사용하여 외래 키 이름을 식별합니다.

이 확장의 결과로 외부 키와 연관된 인덱스를 하나의 문으로 삭제하는 것도 가능하게되었습니다. 이것은 이전 엄격한 순서로 정렬 된 개별 문장을 필요로했습니다.
```
 ALTER TABLE table DROP FOREIGN KEY constraint , DROP INDEX index ;
```

변경되는 테이블에 외래 키 가 이미 존재하는 (즉, 테이블이 하나의 FOREIGN KEY ... REFERENCE 절을 포함한 자식 테이블 인 경우) foreign 키 컬럼에 직접 관련되지 않은 조작 이어도, 온라인 DDL 작업에는 다음의 추가 제한 사항이 적용됩니다.

이러한 자식 테이블에서 온라인 DDL 작업 중에 병렬 DML이 허용되지 않습니다. (이 제한은 버그로 평가 중이며, 해제 될 수 있습니다.)
부모 테이블이 변경된 위해 CASCADE 또는 SET NULL 매개 변수를 사용하여 ON UPDATE 또는 ON DELETE 절은 자식 테이블에 대한 변경이 발생한 경우 자식 테이블에 대한 ALTER TABLE 이 다른 트랜잭션의 커밋 대기 함 수 있습니다.

마찬가지로 테이블이 외부 키 관계에있는 부모 테이블 인 경우, 거기에는 FOREIGN KEY 절이 포함되어 있지 않아도, INSERT , UPDATE 또는 DELETE 문에 의해 자식 테이블에서 ON UPDATE 또는 ON DELETE 작업이 발생한 경우 해당 테이블이 ALTER TABLE 의 완료를 대기 할 가능성이 있습니다.

ALGORITHM = COPY 관한주의

ALGORITHM=COPY 절에서 실행되는 ALTER TABLE 조작은 모든 병렬 DML 작업을 방해합니다. 병렬 쿼리는 계속 허용됩니다. 즉, 테이블 복사 작업에는 항상 최소한 LOCK=SHARED (쿼리를 허용하지만 DML은 허용하지 않는다)의 병렬성 제한이 포함됩니다. LOCK=EXCLUSIVE (DML 및 쿼리를 막는 것)를 지정하여 이러한 작업의 병렬 성을 더욱 제한 할 수 있습니다.

병렬 DML는 있지만, 테이블 복사가 계속 필요

기타 일부 ALTER TABLE 작업은 병렬 DML은 허용되지만 테이블 복사가 계속 필요합니다. 그러나 이러한 작업 테이블 복사는 MySQL 5.5 이전 작업에 비해 빠릅니다.

열 추가, 삭제 또는 정렬.
기본 키 추가 또는 제거.
테이블 ROW_FORMAT 또는 KEY_BLOCK_SIZE 프로퍼티의 변경.
열의 Null에있는 상태 변경.
OPTIMIZE TABLE
FORCE 옵션을 사용하여 테이블의 재구성
"null" ALTER TABLE ... ENGINE=INNODB 문을 사용하여 테이블의 재구성

참고

새로운 컬럼 데이터 타입, 제약 조건, 인덱스 등을 통해 데이터베이스 스키마가 생기면서 CREATE TABLE 문을 최신의 테이블 정의가 적용되도록 유지하십시오. 온라인 DDL의 성능 향상이 있었다고해도 스키마의 일부를 만들고 그 후에 ALTER TABLE 문을 발행하는 것보다 처음부터 안정된 데이터베이스 구조를 만드는 것이 효율적입니다.

이 지침의 주요 예외는 다수의 행을 포함하는 테이블에서 보조 인덱스 에 관한 것입니다. 일반적으로 보조 인덱스를 제외한 모든 정보가 지정된 상태에서 테이블을 만들고 데이터를로드 한 후 보조 인덱스를 만드는 방법이 가장 효율적입니다. 초기 데이터가 정리 될 것을 알고 있고,로드 과정에서 일관성 검사가 필요없는 경우, 외부 키 에서도 같은 방법을 사용할 수 있습니다 (처음에 데이터를로드 한 후 외부 키를 설정 매스).

CREATE TABLE , CREATE INDEX , ALTER TABLE 및 유사 문 어떤 순서로 테이블을 만든 경우에도 문 SHOW CREATE TABLE table \G (공식적인 형식으로 대문자 \G 가 필요합니다)를 발행하기 하여 현재 형식의 테이블을 재구성하는 데 필요한 SQL을 얻을 수 있습니다. 이 출력은 경우에 따라서는 내부적으로 추가되며 새로운 시스템에서 테이블의 복제와 동일한 형태를 가지는 foreign 키 설정을 할 때 보통이면 생략 할 수있는 수치 정밀도 , NOT NULL , CHARACTER SET 등의 어구가 표시됩니다.