[MySQL][InnoDB] Online DDL
- Databases/MySQL
- 2020. 6. 28.
온라인 DDL 기능은 전체 테이블 변경 및 동시 DML을 지원합니다. 이 기능의 장점은 다음과 같습니다.
+ 몇 분 또는 몇 시간 동안 테이블을 사용할 수 없는 바쁜 생산 환경에서 응답 성과 가용성이 향상되었습니다.
+ LOCK 절을 사용하여 DDL 작업 중 성능과 동시성간의 균형을 조정하는 기능입니다.
+ 테이블 복사 방법보다 적은 디스크 공간 사용 및 I/O 오버 헤드를 가집니다.
일반적으로 온라인 DDL을 활성화하기 위해 특별한 작업을 수행 할 필요는 없습니다. 기본적으로 MySQL은 허용되는 한 적은 잠금으로 작업을 제 위치(in-place)에서 수행합니다.
ALTER TABLE 문의 ALGORITHM 및 LOCK 절을 사용하여 DDL 조작의 측면을 제어 할 수 있습니다. 이 절은 명령문 끝에 테이블 및 컬럼 스펙과 쉼표로 구분되어 있습니다. 예를 들면 다음과 같습니다.
mysql> ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
LOCK 절은 테이블에 대한 동시 액세스 수준을 미세 조정하는데 유용합니다. ALGORITHM절은 기본적으로 성능 비교 및 문제 발생시 이전 테이블 복사 동작의 대체를 위한 것입니다. 예를 들면 다음과 같습니다.
+ 실수로 테이블을 읽기, 쓰기 또는 둘 다에 사용할 수 없게하려면 ALTER TABLE 문에 LOCK = NONE (읽기 및 쓰기 허용) 또는 LOCK = SHARED (읽기 허용)와 같은 절을 지정합니다. 요청된 동시성 수준을 사용할 수 없으면 작업이 즉시 중지됩니다.
+ 알고리즘간의 성능을 비교하려면 ALGORITHM = INPLACE 및 ALGORITHM = COPY로 명령문을 실행합니다. 또는 old_alter_table 구성 옵션을 비활성화하고 활성화 한 명령문을 실행합니다.
+ 테이블을 복사하는 ALTER TABLE 조작으로 서버를 연결하지 않으려면 ALGORITHM = INPLACE를 포함합니다. 내부 메커니즘을 사용할 수 없으면 명령문이 즉시 정지됩니다.
■ Online DDL Operations
이 섹션의 다음 주제에서 DDL 조작에 대한 온라인 지원 세부 사항, 구문 예제 및 사용법 정보가 제공됩니다.
+ 인덱스 작업
+ 기본 키 작업
+ 열 연산
+ 생성 된 열 연산
+ 외래 키 운영
+ 테이블 작업
+ 테이블 스페이스 작업
+ 파티셔닝 작업
▶ 인덱스 작업
다음 표는 인덱스 작업에 대한 온라인 DDL 지원 개요를 제공합니다. 별표는 추가 정보, 예외 또는 종속성을 나타냅니다.
Table 14.10 인덱스 작업을 위한 온라인 DDL 지원
| 작업 | 제자리 (In Place) |
리빌드 테이블 |
동시 DML 허용 | 메타 데이터만 수정 |
| 보조 인덱스 생성 또는 추가 | Yes | No | Yes | No |
| 인덱스 드롭(삭제) | Yes | No | Yes | Yes |
| 인덱스 이름 바꾸기 | Yes | No | Yes | Yes |
| FULLTEXT 인덱스 추가 | Yes* | No* | No | No |
| SPATIAL 인덱스 추가 | Yes | No | No | No |
| 색인 유형 변경 | Yes | No | Yes | Yes |
+ 보조인덱스 생성 혹은 추가
mysql> CREATE INDEX name ON table (col_list);
mysql> ALTER TABLE tbl_name ADD INDEX name (col_list);
인덱스가 작성되는 동안 테이블은 읽기 및 쓰기 조작에 사용 가능합니다. CREATE INDEX 문은 테이블에 액세스하는 모든 트랜잭션이 완료된 후에만 완료되므로 인덱스의 초기 상태가 테이블의 최신 내용을 반영합니다.
보조 인덱스 추가를 위한 온라인 DDL지원은 일반적으로 보조 인덱스없이 테이블을 생성한 다음 데이터가로드된 후 보조 인덱스를 추가하여 테이블 및 관련 인덱스를 생성하고 로드하는 전체 프로세스의 속도를 높일 수 있음을 의미합니다.
새로 작성된 2차 인덱스에는 CREATE INDEX 또는 ALTER TABLE 문 실행이 완료될 때 테이블에 커미트 된 데이터만 포함됩니다. 커밋되지 않은 값, 이전 버전의 값 또는 삭제 표시되었지만 아직 이전 인덱스에서 제거되지 않은 값은 포함되지 않습니다.
보조 인덱스를 생성하는 동안 서버가 종료되면 복구시 MySQL은 부분적으로 생성 된 인덱스를 삭제합니다. ALTER TABLE 또는 CREATE INDEX 문을 다시 실행해야합니다.
일부 요인은이 작업의 성능, 공간 사용량 및 의미에 영향을줍니다. 자세한 내용은 아래에서 설명합니다
+ Dropping an index
mysql> DROP INDEX name ON table;
mysql> ALTER TABLE tbl_name DROP INDEX name;인덱스가 삭제되는 동안 테이블은 읽기 및 쓰기 작업에 사용할 수 있습니다. DROP INDEX문은 테이블에 액세스하는 모든 트랜잭션이 완료된 후에만 완료되므로 인덱스의 초기 상태가 테이블의 최신 내용을 반영합니다.
+ 인덱스 이름변경
mysql> ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name, ALGORITHM=INPLACE, LOCK=NONE;FULLTEXT인덱스 추가
mysql> CREATE FULLTEXT INDEX name ON table(column);첫 번째 FULLTEXT 인덱스를 추가하면 사용자 정의 FTS_DOC_ID 열이없는 경우 테이블이 다시 작성됩니다. 테이블을 다시 작성하지 않고 추가 FULLTEXT 인덱스를 추가 할 수 있습니다.
+ SPATIAL 인덱스 추가
mysql> CREATE TABLE geom (g GEOMETRY NOT NULL);
mysql> ALTER TABLE geom ADD SPATIAL INDEX(g), ALGORITHM=INPLACE, LOCK=SHARED;
+ 인덱스 타입 변경 (USING {BTREE | HASH})
mysql> ALTER TABLE tbl_name DROP INDEX i1, ADD INDEX i1(key_part,...) USING BTREE, ALGORITHM=INPLACE;
▶ 프리머리키 작업
다음 표는 기본키 작업에 대한 온라인 DDL 지원 개요를 제공합니다. 별표는 추가 정보, 예외 또는 종속성을 나타냅니다. 구문 및 사용법 참고 사항을 참조하세요.
Table 14.11 기본 키 작업을위한 온라인 DDL 지원
| 작업 | 제자리 (In Place) |
리빌드 테이블 |
동시 DML 허용 | 메타 데이터만 수정 |
| 기본키(프리머리) 추가 | Yes* | Yes* | Yes | No |
| 기본키 삭제(드롭) | No | Yes | No | No |
| 기본 키를 삭제하고 다른 키 추가 | Yes | Yes | Yes | No |
▷ 문법과 사용방법
• 프리머리키 추가
테이블을 제자리(In-place)에서 재구성합니다. 데이터는 실질적으로 재구성되어 비용이 많이 드는 작업입니다. 열을 NOT NULL로 변환해야하는 경우 특정 조건에서 ALGORITHM = INPLACE가 허용되지 않습니다.(예를 들면 열의 값들중 2개이상 중복되는 경우)
클러스터형 인덱스를 재구성하려면 항상 테이블 데이터를 복사해야합니다. 따라서 나중에 ALTER TABLE ... ADD PRIMARY KEY를 발행하는 대신 테이블을 작성할때 기본 키를 정의하는 것이 가장 좋습니다.
UNIQUE 또는 PRIMARY KEY인덱스를 생성 할 때 MySQL은 추가 작업을 수행해야합니다. 고유 인덱스의 경우 MySQL은 테이블에 키에 대한 중복 값이 없는지 확인합니다. PRIMARY KEY 인덱스의 경우 MySQL은 PRIMARY KEY열에 NULL이 포함되어 있지 않은지도 확인합니다.
ALGORITHM=COPY 절을 사용하여 기본 키를 추가하면 MySQL은 연결된 열의 NULL값을 컬럼에서 정의된 기본값과 관련되어 변경됩니다. 즉 숫자는 0, 문자열 기반 컬럼과 BLOB은 빈 문자열, DATETIME은0000-00-00 00:00:00으로 변환합니다. 이것은 오라클이 권장하지 않는 비표준 동작입니다. ALGORITHM = INPLACE를 사용하여 기본 키를 추가하는 것은 SQL_MODE 설정에 strict_trans_tables 또는 strict_all_tables 플래그가 포함 된 경우에만 허용됩니다. SQL_MODE 설정이 엄격한 경우 ALGORITHM = INPLACE가 허용되지만 요청된 기본 키 열에 NULL 값이 포함되어 있으면 명령문이 계속 실패 할 수 있습니다. ALGORITHM = INPLACE 동작은 강력한 표준을 준수합니다.
기본 키가 없는 테이블을 생성하면 InnoDB가 NOT NULL열에 정의된 첫 번째 고유키 또는 시스템 생성 키가 될 수있는 테이블을 선택합니다. 여분의 숨겨진 열에 대한 불확실성과 잠재적인 공간 요구를 피하려면 CREATE TABLE 문의 일부로 PRIMARY KEY절을 지정합니다.
MySQL은 기존 테이블의 기존 데이터를 원하는 인덱스 구조의 임시 테이블로 복사하여 새로운 클러스터형 인덱스를 만듭니다. 데이터가 임시 테이블에 완전히 복사되면 원래 테이블의 이름이 다른 임시 테이블 이름으로 바뀝니다. 새 클러스터형 인덱스를 구성하는 임시 테이블의 이름이 원본 테이블의 이름으로 바뀌고 원본 테이블이 데이터베이스에서 삭제됩니다.
보조 인덱스 작업에 적용되는 온라인 성능 향상 기능은 기본 키 인덱스에는 적용되지 않습니다. InnoDB 테이블의 행은 기본 키를 기반으로 구성된 클러스터형 인덱스에 저장되어 일부 데이터베이스 시스템은 "인덱스 구성 테이블"이라고합니다. 테이블 구조는 기본 키와 밀접하게 연결되어 있으므로 기본키를 재정의하려면 여전히 데이터를 복사해야합니다.
기본 키의 조작이 ALGORITHM = INPLACE를 사용하는 경우, 데이터가 여전히 복사 되더라도 다음과 같은 이유로 ALGORITHM = COPY를 사용하는 것보다 더 효율적입니다.
- ALGORITHM = INPLACE에는 실행 취소(언두) 로깅 또는 관련 다시 실행(리두) 로깅이 필요하지 않습니다. 이러한 조작은 ALGORITHM = COPY를 사용하는 DDL 문에 오버 헤드를 추가합니다.
- 보조 인덱스 항목이 미리 정렬되어 있으므로 순서대로 로드 할 수 있습니다.
- 보조 인덱스에 임의 액세스 삽입이 없으므로 변경 버퍼가 사용되지 않습니다.
새 클러스터형 인덱스를 만드는 동안 서버가 종료되면 데이터가 손실되지 않지만 프로세스중에 존재하는 임시 테이블을 사용하여 복구 프로세스를 완료해야 합니다. 클러스터된 인덱스를 다시 만들거나 큰 테이블에서 기본키를 다시 정의하거나 이 작업 중에 시스템 충돌이 발생하는 경우는 드물기 때문에 이 매뉴얼에서는 이 시나리오에서 복구하는 데 대한 정보를 제공하지 않습니다.
• 프리머리키 삭제
mysql> ALTER TABLE tbl_name DROP PRIMARY KEY, ALGORITHM=COPY;
ALGORITHM = COPY만이 동일한 ALTER TABLE문에 새 키를 추가하지 않고 기본키 삭제를 지원합니다.
• 프리머리 키 삭제와 프리머리 키 재 추가
mysql> ALTER TABLE tbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
데이터는 실질적으로 재구성되어 비용이 많이 드는 작업입니다.
▶ 컬럼 작업
다음 표는 열 작업에 대한 온라인 DDL 지원 개요를 제공합니다. 별표는 추가 정보, 예외 또는 종속성을 나타냅니다.
Table 14.12 열 작업을위한 온라인 DDL 지원
| 작업 | 제자리 (In Place) |
리빌드 테이블 |
동시 DML 허용 | 메타 데이터만 수정 |
| 컬럼 추가 | Yes | Yes | Yes* | No |
| 컬럼 삭제 | Yes | Yes | Yes | No |
| 컬럼 이름 변경 | Yes | No | Yes* | Yes |
| 컬럼 재정렬 | Yes | Yes | Yes | No |
| 컬럼 기본값 설정 | Yes | No | Yes | Yes |
| 컬럼 데이터 유형 변경 | No | Yes | No | No |
| VARCHAR열 크기 확장 | Yes | No | Yes | Yes |
| 컬럼 기본값 삭제 | Yes | No | Yes | Yes |
| 자동 증분(Auto Inrement) 값 변경 | Yes | No | Yes | No* |
| Null 컬럼 만들기 | Yes | Yes* | Yes | No |
| NOT NULL 컬럼 만들기 | Yes* | Yes* | Yes | No |
| ENUM 혹은 SET 컬럼 정의 변경 | Yes | No | Yes | Yes |
▷ 문법과 사용방법
• 컬럼 추가
mysql> ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INPLACE, LOCK=NONE;auto-increment 열을 추가 할 때 동시 DML은 허용되지 않습니다. 데이터는 실질적으로 재구성되어 비용이 많이 드는 작업입니다. 최소한 ALGORITHM = INPLACE, LOCK = SHARED가 필요합니다.
• 컬럼 삭제
mysql> ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INPLACE, LOCK=NONE;데이터는 실질적으로 재구성되어 비용이 많이 드는 작업입니다.
• 컬럼이름 변경
mysql> ALTER TABLE tbl CHANGE old_col_name new_col_name data_type,ALGORITHM=INPLACE, LOCK=NONE; 동시 DML을 허용하려면 동일한 데이터 유형을 유지하고 열 이름 만 변경합니다.
동일한 데이터 유형과 [NOT] NULL 속성을 유지하고 열 이름만 변경하면 항상 온라인으로 작업을 수행 할 수 있습니다.
외래 키 제약 조건의 일부인 열의 이름을 바꿀 수도 있습니다. 외래 키 정의는 새 열 이름을 사용하도록 자동으로 업데이트됩니다. 외래 키에 참여하는 열의 이름을 바꾸면 ALGORITHM = INPLACE에서만 작동합니다. ALGORITHM = COPY 절을 사용하거나 일부 다른 조건으로 인해 명령이 백그라운드에서 ALGORITHM = COPY를 사용하면 ALTER TABLE 문이 실패합니다.
ALGORITHM = INPLACE는 생성된 열의 이름을 변경하는것은 지원되지 않습니다.
• 컬럼 재정렬
열을 재정렬하려면 CHANGE 또는 MODIFY 조작에서 FIRST 또는 AFTER를 사용합니다.
mysql> ALTER TABLE tbl_name MODIFY COLUMN col_name column_definition FIRST, ALGORITHM=INPLACE, LOCK=NONE;데이터는 실질적으로 재구성되어 비용이 많이 드는 작업입니다.
• 컬럼 데이터 타입 변경
mysql> ALTER TABLE tbl_name CHANGE c1 c1 BIGINT, ALGORITHM=COPY;열 데이터 유형 변경은 ALGORITHM = COPY에서만 지원됩니다.
• VARCHAR 컬럼 크기 확장
mysql> ALTER TABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;VARCHAR 열에 필요한 길이 바이트 수는 동일하게 유지되어야 합니다. 크기가 0-255 바이트인 VARCHAR 열의 경우 값을 인코딩하려면 하나의 길이 바이트가 필요합니다. 크기가 256 바이트 이상인 VARCHAR 컬럼의 경우 두개의 길이 바이트가 필요합니다. 결과적으로, 내부 ALTER TABLE은 VARCHAR 열 크기를 0에서 255 바이트로 늘리거나 256 바이트에서 더 큰 크기로만 지원합니다. 전체 ALTER TABLE은 VARCHAR 열의 크기를 256 바이트 미만에서 256 바이트 이상으로 늘리는 것을 지원하지 않습니다. 이 경우 필요한 길이 바이트 수는 1에서 2로 변경되며 테이블 사본에서만 지원됩니다 (ALGORITHM = COPY). 예를 들어, 내부 ALTER TABLE을 사용하여 단일 바이트 문자 세트의 VARCHAR열 크기를 VARCHAR (255)에서 VARCHAR(256)로 변경하려고 하면 이 오류가 리턴됩니다.
mysql> ALTER TABLE tbl_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
노트
VARCHAR 컬럼의 바이트 길이는 문자 세트의 바이트 길이에 따라 다릅니다.
내부 ALTER TABLE을 사용하여 VARCHAR 크기를 줄이는 것은 지원되지 않습니다. VARCHAR 크기를 줄이려면 테이블 사본이 필요합니다 (ALGORITHM = COPY).
• 컬럼 기본값 설정
mysql> ALTER TABLE tbl_name ALTER COLUMN col SET DEFAULT literal, ALGORITHM=INPLACE, LOCK=NONE;테이블 메타 데이터 만 수정합니다. 기본 열 값은 InnoDB 데이터 사전이 아니라 테이블의 .frm 파일에 저장됩니다.
• 컬럼 기본값 삭제
mysql> ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT, ALGORITHM=INPLACE, LOCK=NONE;
• auto-increment 값 변경
mysql> ALTER TABLE table AUTO_INCREMENT=next_value, ALGORITHM=INPLACE, LOCK=NONE;데이터 파일이 아닌 메모리에 저장된 값을 수정합니다.
복제 또는 샤딩을 사용하는 분산 시스템에서 때때로 테이블의 자동 증분 카운터를 특정 값으로 재설정합니다. 테이블에 삽입된 다음 행은 자동 증가 열에 지정된 값을 사용합니다. 주기적으로 모든 테이블을 비우고 다시로드한 후 자동 증가 순서를 1에서 다시 시작하는 데이터웨어 하우징 환경에서 이 기술을 사용할 수도 있습니다.
• Null 컬럼만들기
mysql> ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NULL, ALGORITHM=INPLACE, LOCK=NONE;테이블을 제자리(in-place)에 재구성합니다. 데이터는 실질적으로 재구성되어 비용이 많이 드는 작업입니다.
• NOT NULL 컬럼 만들기
mysql> ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;테이블을 제자리(in-place)에 재구성합니다. 조작이 성공하려면 STRICT_ALL_TABLES 또는 STRICT_TRANS_TABLES SQL_MODE가 필요합니다. 열에 NULL 값이 포함되어 있으면 작업이 실패합니다. 서버는 참조 무결성이 손실 될 가능성이있는 외래 키 열에 대한 변경을 금지합니다. 데이터는 실질적으로 재구성되어 비용이 많이 드는 작업입니다.
• ENUM, SET 컬럼의 정의 변경
mysql> CREATE TABLE t1 (c1 ENUM('a', 'b', 'c'));
mysql> ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd'), ALGORITHM=INPLACE, LOCK=NONE;데이터 유형의 스토리지 크기가 변경되지 않는 한 유효한 멤버값 목록 끝에 새 열거 또는 세트 멤버를 추가하여 ENUM 또는 SET 컬럼의 정의를 수정할 수 있습니다. 예를 들어 멤버가 8개인 SET 열에 멤버를 추가하면 값당 필요한 스토리지가 1 바이트에서 2 바이트로 변경됩니다. 이를 위해서는 테이블 사본이 필요합니다. 목록 중간에 멤버를 추가하면 기존 멤버의 번호가 다시 매겨 지므로 테이블 복사가 필요합니다.
▶ 생성된 컬럼 조작
다음 표는 생성 된 컬럼 조작에 대한 온라인 DDL 지원 개요를 제공합니다.
Table 14.13 생성 된 열 작업을위한 온라인 DDL 지원
| 작업 | 제자리 (In Place) |
리빌드 테이블 |
동시 DML 허용 | 메타 데이터만 수정 |
| STORED 컬럼 추가 | No | Yes | No | No |
| STORED 컬럼 순서 변경 | No | Yes | No | No |
| STORED 컬럼 삭제 | Yes | Yes | Yes | No |
| VIRTUAL 컬럼 추가 | Yes | No | Yes | Yes |
| VIRTUAL 컬럼 순서 변경 | No | Yes | No | No |
| VIRTUAL 컬럼 삭제 | Yes | No | Yes | Yes |
▷ 문법과 사용법
• STORED 컬럼 추가
mysql> ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) STORED), ALGORITHM=COPY;ADD COLUMN은 서버에서 표현식을 평가해야 하므로 저장된 컬럼(임시 테이블을 사용하지 않고 수행)에 대한 내부 조작이 아닙니다.
• STORED 컬럼 순서 변경
mysql> ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED FIRST, ALGORITHM=COPY;테이블을 제자리(in-place)에 재구성합니다.
• STORED 컬럼 삭제
mysql> ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;테이블을 제자리에 재구성합니다.
• Adding a VIRTUAL column
가상 컬럼 추가는 파티션되지 않은 테이블에는 제자리(in-place) 작업입니다. 그러나 가상 컬럼 추가는 다른 ALTER TABLE 조치와 결합 할 수 없습니다.
VIRTUAL 컬럼 추가는 파티션된 테이블에서는 제자리(in-place) 작업이 아닙니다.
• Virtual 컬럼 순서 변경
mysql> ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL FIRST, ALGORITHM=COPY;
• VIRTUAL 컬럼 삭제
mysql> ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;VIRTUAL 열 삭제는 파티션되지 않은 테이블에는 제자리(in-place) 작업입니다. 그러나 가상 컬럼 삭제는 다른 ALTER TABLE 조치와 결합 할 수 없습니다.
VIRTUAL을 삭제하는 것은 파티션 된 테이블서는 제자리(in-place) 작업이 아닙니다.
▶ 외래키(Foreign Key)작업
다음 표는 외래키 작업에 대한 온라인 DDL지원 개요를 나타냅니다. 별표는 추가 정보, 예외 또는 종속성을 나타냅니다.
Table 14.14 외래 키 작업에 대한 온라인 DDL 지원
| 작업 | 제자리 (In Place) |
리빌드 테이블 |
동시 DML 허용 | 메타 데이터만 수정 |
| 외래키(foreign key) 제약 추가 | Yes* | No | Yes | Yes |
| 외래키(foreign key) 제약 삭제 | Yes | No | Yes | Yes |
▷ 문법과 사용법
• 외래키 제약 추가
foreign_key_checks가 비활성화되면 INPLACE 알고리즘이 지원됩니다. 그렇지 않으면 COPY 알고리즘 만 지원됩니다.
mysql> ALTER TABLE tbl1 ADD CONSTRAINT fk_name FOREIGN KEY index (col1) REFERENCES tbl2(col2) referential_actions;
• 외래키 제약 제거
mysql> ALTER TABLE tbl DROP FOREIGN KEY fk_name;foreign_key_checks 옵션을 활성화 또는 비활성화하여 외래 키 삭제를 온라인으로 수행 할 수 있습니다.
특정 테이블에서 외래 키 제한 조건의 이름을 모르는 경우 다음 명령문을 실행하고 각 외래 키에 대한 CONSTRAINT 절에서 제한 조건 이름을 찾아야 합니다.
mysql> SHOW CREATE TABLE table\G
또는 INFORMATION_SCHEMA.TABLE_CONSTRAINTS 테이블을 쿼리하고 CONSTRAINT_NAME 및 CONSTRAINT_TYPE 열을 사용하여 외래 키 이름을 식별합니다.
외래 키와 관련 인덱스를 단일 명령문으로 삭제할수도 있습니다.
mysql> ALTER TABLE table DROP FOREIGN KEY constraint, DROP INDEX index;
노트
외래 키가 이미 변경중인 테이블에 존재하는 경우 (즉, FOREIGN KEY ... REFERENCE 절을 포함하는 자식 테이블) 외래키 열을 직접 포함하지 않는 경우에도 추가 제한 사항이 온라인 DDL 작업에 적용됩니다.
• CASCADE 또는 SET NULL 매개 변수를 사용하여 ON UPDATE 또는 ON DELETE 절을 통해 상위 테이블을 변경하면 하위 테이블에서 연관된 변경이 발생하여 하위 테이블의 ALTER TABLE이 다른 트랜잭션이 커미트 될 때까지 기다릴 수 있습니다.
• 같은 방식으로 테이블이 외래 키 관계에서 상위 테이블인 경우 FOREIGN KEY 절이 포함되어 있지 않더라도 INSERT, UPDATE 또는 DELETE 문으로 인해 자식 테이블이 ON UPDATE 또는 ON DELETE 조치가 발생하면 ALTER TABLE이 완료 될 때까지 기다릴 수 있습니다.
▶ 테이블 작업
다음 표는 테이블 조작에 대한 온라인 DDL 지원 개요를 제공합니다. 별표는 추가 정보, 예외 또는 종속성을 나타냅니다.
Table 14.15 테이블 작업을위한 온라인 DDL 지원
| 작업 | 제자리 (In Place) |
리빌드 테이블 |
동시 DML 허용 | 메타 데이터만 수정 |
| ROW_FORMAT 변경 | Yes | Yes | Yes | No |
| KEY_BLOCK_SIZE 변경 | Yes | Yes | Yes | No |
| 지속적 테이블 통계 설정 | Yes | No | Yes | Yes |
| 문자 세트 지정 | Yes | Yes* | No | No |
| 문자 세트 변경 | No | Yes* | No | No |
| 테이블 최적화 | Yes* | Yes | Yes | No |
| FORCE옵션을 사용하여 재 구축 | Yes* | Yes | Yes | No |
| Null 재구축 수행 | Yes* | Yes | Yes | No |
| 테이블 이름 변경 | Yes | No | Yes | Yes |
▷ 문법과 사용방법
• ROW_FORMAT 변경
mysql> ALTER TABLE tbl_name ROW_FORMAT = row_format, ALGORITHM=INPLACE, LOCK=NONE;데이터는 실질적으로 재구성되어 비용이 많이 드는 작업입니다.
• KEY_BLOCK_SIZE 변경
mysql> ALTER TABLE tbl_name KEY_BLOCK_SIZE = value, ALGORITHM=INPLACE, LOCK=NONE;데이터는 실질적으로 재구성되어 비용이 많이 드는 작업입니다.
• 지속적 테이블 통계옵션 설정
mysql> ALTER TABLE tbl_name STATS_PERSISTENT=0, STATS_SAMPLE_PAGES=20, STATS_AUTO_RECALC=1, ALGORITHM=INPLACE, LOCK=NONE;테이블 메타 데이터만 수정합니다.
영구 통계에는 STATS_PERSISTENT, STATS_AUTO_RECALC 및 STATS_SAMPLE_PAGES가 포함됩니다.
• 특정 character set 설정
mysql> ALTER TABLE tbl_name CHARACTER SET = charset_name, ALGORITHM=INPLACE, LOCK=NONE;새 문자 인코딩이 다른 경우 테이블을 다시 작성합니다.
• character set 변경
mysql> ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name, ALGORITHM=COPY;새 문자 인코딩이 다른 경우 테이블을 다시 작성합니다.
• 테이블 최적화
mysql> OPTIMIZE TABLE tbl_name;FULLTEXT 인덱스가있는 테이블에 대해서는 내부 조작(in-place)이 지원되지 않습니다. 조작은 INPLACE 알고리즘을 사용하지만 ALGORITHM 및 LOCK 구문은 허용되지 않습니다.
• FORCE 옵션을 이용하여 테이블 리빌드
mysql> ALTER TABLE tbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;MySQL 5.6.17부터 ALGORITHM = INPLACE를 사용합니다. FULLTEXT 인덱스가있는 테이블에는 ALGORITHM = INPLACE가 지원되지 않습니다.
• null 값 리빌드 수행.
mysql> ALTER TABLE tbl_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;MySQL 5.6.17부터 ALGORITHM = INPLACE를 사용합니다. FULLTEXT 인덱스가있는 테이블에는 ALGORITHM = INPLACE가 지원되지 않습니다.
• 테이블 이름 변경
mysql> ALTER TABLE old_tbl_name RENAME TO new_tbl_name, ALGORITHM=INPLACE, LOCK=NONE;MySQL은 테이블 tbl_name에 해당하는 파일을 복사하지 않고 이름을 바꿉니다. (RENAME TABLE 문을 사용하여 테이블 이름을 바꿀 수도 있습니다.) 이름이 바뀐 테이블에 대해 특별히 부여 된 권한은 새 이름으로 마이그레이션되지 않습니다. 수동으로 변경해야합니다.
▶ 테이블스페이스 작업
다음 표는 테이블 스페이스 조작에 대한 온라인 DDL 지원 개요를 제공합니다.
Table 14.16 Online DDL Support for Tablespace Operations
| 작업 | 제자리 (In Place) |
리빌드 테이블 |
동시 DML 허용 | 메타 데이터만 수정 |
| 테이블당 파일 테이블스페이스 암호화 사용 또는 사용 안함 |
No | Yes | No | No |
▷ 문법과 사용방법
• 테이블당 파일 테이블스페이스 암호화 사용 또는 사용 안함
mysql> ALTER TABLE tbl_name ENCRYPTION='Y', ALGORITHM=COPY;암호화는 테이블 당 파일 테이블 스페이스에 대해서만 지원됩니다. 관련 정보는 14.14 절.“InnoDB 미사용 데이터 암호화”를 참조하십시오.
▶ 파티셔닝 작업
대부분의 ALTER TABLE 파티셔닝 절을 제외하고 파티션된 InnoDB 테이블에 대한 온라인 DDL 작업은 일반 InnoDB 테이블에 적용되는 것과 동일한 규칙을 따릅니다.
대부분의 ALTER TABLE 파티셔닝 절은 파티션되지 않은 일반 InnoDB 테이블과 동일한 내부 온라인 DDL API를 거치지 않습니다. 결과적으로 ALTER TABLE 파티셔닝 절에 대한 온라인 지원은 다양합니다.
다음 표는 각 ALTER TABLE 파티셔닝 명령문의 온라인 상태를 보여줍니다. 사용되는 온라인 DDL API에 관계없이 MySQL은 가능한 경우 데이터 복사 및 잠금을 최소화하려고 시도합니다.
ALGORITHM = COPY를 사용하거나 "ALGORITHM = DEFAULT, LOCK = DEFAULT"만 허용하는 ALTER TABLE 파티셔닝 옵션은 COPY 알고리즘을 사용하여 테이블을 다시 분할합니다. 다시 말해, 새로운 파티션 구성표를 사용하여 새로운 파티션된 테이블이 작성됩니다. 새로 작성된 테이블에는 ALTER TABLE 문에 의해 적용된 모든 변경 사항이 포함되며 테이블 데이터는 새 테이블 구조로 복사됩니다.
Table 14.17 파티셔닝 운영을 위한 온라인 DDL 지원
| 파티셔닝 술어 | In Place | DML 허용 | 노트 |
| PARTITION BY | No | No | ALGORITHM = COPY, LOCK = {DEFAULT | SHARED | EXCLUSIVE} 허용 |
| ADD PARTITION | No | No | ALGORITHM = DEFAULT, LOCK = DEFAULT 만 허용합니다. RANGE 또는 LIST로 파티션 된 테이블의 기존 데이터를 복사하지 않습니다. HASH 또는 LIST로 파티션 된 테이블에 대해 동시 쿼리가 허용됩니다. MySQL은 공유 잠금을 유지하면서 데이터를 복사합니다. |
| DROP PARTITION | No | No | ALGORITHM = DEFAULT, LOCK = DEFAULT 만 허용합니다. RANGE 또는 LIST로 파티션 된 테이블의 기존 데이터를 복사하지 않습니다. |
| DISCARD PARTITION | No | No | ALGORITHM=DEFAULT, LOCK=DEFAULT 허용 |
| IMPORT PARTITION | No | No | ALGORITHM=DEFAULT, LOCK=DEFAULT 호용 |
| TRUNCATE PARTITION | Yes | Yes | 기존 데이터를 복사하지 않습니다. 단순히 행을 삭제합니다. 테이블 자체 또는 파티션 정의를 변경하지 않습니다. |
| COALESCE PARTITION | No | No | ALGORITHM = DEFAULT, LOCK = DEFAULT 만 허용합니다. MySQL이 공유 잠금을 유지하면서 데이터를 복사하므로 asHASH 또는 LIST로 파티션 된 테이블에 대해 동시 쿼리가 허용됩니다. |
| REORGANIZE PARTITION | No | No | ALGORITHM = DEFAULT, LOCK = DEFAULT 만 허용합니다. LINEAR HASH 또는 LIST로 파티션 된 테이블에 대해 동시 쿼리가 허용됩니다. MySQL은 공유 메타 데이터 잠금을 유지하면서 영향을받는 파티션에서 데이터를 복사합니다. |
| EXCHANGE PARTITION | Yes | Yes | |
| ANALYZE PARTITION | Yes | Yes | |
| CHECK PARTITION | Yes | Yes | |
| OPTIMIZE PARTITION | No | No | ALGORITHM 및 LOCK 절은 무시됩니다. 전체 테이블을 재 빌드합니다. |
| REBUILD PARTITION | No | No | ALGORITHM = DEFAULT, LOCK = DEFAULT 만 허용합니다. LINEAR HASH 또는 LIST로 파티션 된 테이블에 대해 동시 쿼리가 허용됩니다. MySQL은 공유 메타 데이터 잠금을 유지하면서 영향을받는 파티션에서 데이터를 복사합니다. |
| REPAIR PARTITION | Yes | Yes | |
| REMOVE PARTITIONING | No | No | ALGORITHM=COPY, LOCK={DEFAULT|SHARED|EXCLUSIVE} 허용 |
파티션 된 테이블에서 비 파티션 온라인 ALTER TABLE 조작은 일반 테이블에 적용되는 동일한 규칙을 따릅니다. 그러나 ALTER TABLE은 각 테이블 파티션에서 온라인 조작을 수행하므로 여러 파티션에서 수행되는 조작으로 인해 시스템 자원에 대한 수요가 증가합니다.
■ Online DDL 성능과 동시성
온라인 DDL은 MySQL 운영의 여러 측면을 개선합니다.
DDL 작업이 진행되는 동안 테이블에 대한 쿼리 및 DML 작업을 진행할 수 있으므로 테이블에 액세스하는 응용 프로그램의 응답성이 향상됩니다. MySQL 서버 리소스에 대한 잠금 및 대기 감소는 DDL 작업과 관련되지 않은 작업에 대해서도 확장성을 향상시킵니다.
전체 작업은 데이터베이스의 전체로드를 최소화하는 테이블 복사 방법과 관련된 디스크 I/O 및 CPU주기를 피합니다. 로드를 최소화하면 DDL 작업 중에 우수한 성능과 높은 처리량을 유지할 수 있습니다.
전체 작업은 테이블 복사 작업보다 버퍼 풀로 적은 데이터를 읽어 메모리에서 자주 액세스하는 데이터의 제거를 줄입니다. 자주 액세스하는 데이터를 제거하면 DDL 작업 후 일시적인 성능 저하가 발생할 수 있습니다.
▶ LOCK 문법
기본적으로 MySQL은 DDL 작업 중에 가능한 한 적은 잠금을 사용합니다. 필요한 경우 더 제한적인 잠금을 적용하기 위해 LOCK절을 지정할 수 있습니다. LOCK절이 특정 DDL 조작에 허용되는 것보다 덜 제한적인 잠금 레벨을 지정하면 명령문이 오류와 함께 실패합니다. LOCK 절은 가장 제한적인 순서로 아래에 설명되어 있습니다.
+ LOCK=NONE:
동시 쿼리 및 DML을 허용합니다.
예를 들어, 고객 가입 또는 구매와 관련된 테이블에 이 절을 사용하면 긴 DDL 작업 중에 테이블을 사용할 수 없게됩니다.
+ LOCK=SHARED:
동시 쿼리는 허용하지만 DML은 차단합니다.
예를 들어, DDL 조작이 완료 될 때까지 데이터로드 조작을 지연시킬 수 있지만 쿼리를 장기간 지연시킬 수없는 데이터웨어 하우스 테이블에서 이 절을 사용합니다.
+ LOCK=DEFAULT:
가능한 한 많은 동시성 (동시 쿼리, DML 또는 둘 다)을 허용합니다. LOCK 절을 생략하는 것은 LOCK = DEFAULT를 지정하는 것과 같습니다.
DDL 문의 기본 잠금 레벨이 테이블의 가용성 문제점을 유발하지 않음을 알고있는 경우 이 절을 사용합니다.
+ LOCK=EXCLUSIVE:
동시 쿼리 및 DML을 차단합니다.
주요 관심사가 가능한 한 짧은 시간에 DDL 작업을 완료하고 동시 쿼리 및 DML 액세스가 필요하지 않은 경우 이 절을 사용합니다. 서버가 유휴 상태 인 경우 예기치 않은 테이블 액세스를 피하기 위해이 절을 사용할 수도 있습니다.
▶ 온라인 DDL과 메타데이터 잠금(Lock)
온라인 DDL 작업은 3 단계로 볼 수 있습니다.
+ 1 단계 : 초기화
초기화 단계에서 서버는 스토리지 엔진 기능, 명령문에 지정된 조작 및 사용자 지정 ALGORITHM 및 LOCK 옵션을 고려하여 조작 중에 허용되는 동시성 정도를 판별합니다. 이 단계에서는 현재 테이블 정의를 보호하기 위해 업그레이드 가능한 공유 메타 데이터 잠금이 수행됩니다.
+ 2 단계 : 실행
이 단계에서 명령문은 준비되어 실행됩니다. 메타 데이터 잠금이 배타적으로 업그레이드 되는지 여부는 초기화 단계에서 평가된 요소에 따라 다릅니다. 배타적 메타 데이터 잠금이 필요한 경우 명령문 준비중 잠깐 동안 만 수행됩니다.
+ 3 단계 : 커밋 테이블 정의
커밋 테이블 정의 단계에서 메타 데이터 잠금은 독점으로 업그레이드되어 기존 테이블 정의를 제거하고 새 테이블 정의를 커밋합니다. 일단 부여되면 독점 메타 데이터 잠금 기간은 짧습니다.
위에서 설명한 독점적인 메타 데이터 잠금 요구 사항으로 인해 온라인 DDL 작업은 테이블에서 메타 데이터 잠금을 유지하는 동시 트랜잭션이 커밋 또는 롤백 될 때까지 기다려야 할 수 있습니다. DDL조작 이전 또는 도중에 시작된 트랜잭션은 변경중인 테이블에서 메타 데이터 잠금을 보유 할 수 있습니다. 장기 실행 또는 비활성 트랜잭션의 경우 온라인 DDL조작이 독점 메타 데이터 잠금 대기 시간을 초과 할 수 있습니다. 또한 온라인 DDL 작업에서 요청한 보류중인 독점 메타 데이터 잠금은 테이블에서 후속 트랜잭션을 차단합니다.
다음 예는 배타적 메타 데이터 잠금을 기다리는 온라인 DDL 작업과 보류중인 메타 데이터 잠금이 테이블에서 후속 트랜잭션을 차단하는 방법을 보여줍니다.
Session 1:
mysql> CREATE TABLE t1 (c1 INT) ENGINE=InnoDB;
mysql> START TRANSACTION;
mysql> SELECT * FROM t1;세션 1 SELECT 문은 테이블 t1에서 공유 메타 데이터 잠금을 사용합니다.
Session 2:
mysql> ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE;테이블 정의 변경을 커밋하기 위해 테이블 t1에 독점 메타 데이터 잠금이 필요한 세션 2의 온라인 DDL 작업은 세션 1 트랜잭션이 커밋 또는 롤백 될 때까지 기다려야합니다.
Session 3:
mysql> SELECT * FROM t1;세션 3에서 발행 된 SELECT 문은 세션 2의 ALTER TABLE 조작이 요청한 독점 메타 데이터 잠금이 부여되기를 기다리면서 차단됩니다.
SHOW FULL PROCESSLIST를 사용하여 트랜잭션이 메타 데이터 잠금을 기다리고 있는지 확인할 수 있습니다.
mysql> SHOW FULL PROCESSLIST\G
...
*************************** 2. row ***************************
Id: 5
User: root
Host: localhost
db: test
Command: Query
Time: 44
State: Waiting for table metadata lock
Info: ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE
...
*************************** 4. row ***************************
Id: 7
User: root
Host: localhost
db: test
Command: Query
Time: 5
State: Waiting for table metadata lock
Info: SELECT * FROM t1
4 rows in set (0.00 sec)
메타 데이터 잠금 정보는 세션 간의 메타 데이터 잠금 종속성, 세션이 대기중인 메타 데이터 잠금 및 현재 메타 데이터 잠금을 보유한 세션에 대한 정보를 제공하는 성능 스키마 metadata_locks 테이블을 통해 제공도됩니다.
▶ 온라인 DDL성능
DDL 작업의 성능은 작업 수행 여부와 테이블을 다시 작성하는지 여부에 따라 크게 결정됩니다.
DDL 조작의 상대 성능을 평가하기 위해 ALGORITHM = INPLACE를 사용한 결과와 ALGORITHM = COPY를 사용한 결과를 비교할 수 있습니다. 또는 old_alter_table을 비활성화 및 활성화 한 결과를 비교할 수 있습니다.
테이블 데이터를 수정하는 DDL 조작의 경우 명령이 완료된 후 표시된 "영향을받는 행"값을보고 DDL 조작이 변경을 수행하는지 또는 테이블 복사를 수행하는지 판별 할 수 있습니다. 예를 들면 다음과 같습니다.
+ 열의 기본값 변경 (빠름, 테이블 데이터에 영향을 미치지 않음) :
Query OK, 0 rows affected (0.07 sec)
+ 인덱스 추가 (시간이 걸리지 만 영향을받는 행이 0이면 테이블이 복사되지 않음을 나타냄) :
Query OK, 0 rows affected (21.42 sec)
+ 열의 데이터 유형 변경 (많은 시간이 걸리고 테이블의 모든 행을 다시 작성해야 함) :
Query OK, 1671168 rows affected (1 min 35.54 sec)
큰 테이블에서 DDL 작업을 실행하기 전에 다음과 같이 작업이 빠르거나 느린 지 확인합니다.
1. 테이블 구조를 복제합니다.
2. 소량의 데이터로 복제된 테이블을 채웁니다.
3. 복제 된 테이블에서 DDL 조작을 실행합니다.
4. "행 영향을받는 행"값이 0인지 여부를 확인합니다. 0이 아닌 값은 작업이 테이블 데이터를 복사하므로 특별한 계획이 필요할 수 있습니다. 예를 들어, 예약 된 가동 중지 시간 동안 또는 각 복제 슬레이브 서버에서 한 번에 하나씩 DDL 작업을 수행 할 수 있습니다.
노트
DDL 작업과 관련된 MySQL 처리에 대한 이해를 높이려면 DDL 작업 전후에 InnoDB와 관련된 성능 스키마 및 INFORMATION_SCHEMA 테이블을 검사하여 물리적 읽기, 쓰기, 메모리 할당 등의 수를 확인해야 합니다.
ALTER TABLE 진행 상황을 모니터링하는데 성능 스키마 스테이지 이벤트를 사용할 수 있습니다.
동시 DML 작업의 변경 사항을 기록한 다음 마지막에 해당 변경 사항을 적용하는 것과 관련된 일부 처리 작업이 있기 때문에 온라인 DDL 작업은 다른 세션의 테이블 액세스를 차단하는 테이블 복사 메커니즘보다 전체 시간이 오래 걸릴 수 있습니다. 원시 성능 감소는 테이블을 사용하는 응용 프로그램의 응답성 향상과 균형을 이룹니다. 테이블 구조를 변경하는 기술을 평가할 때는 웹 페이지로드 시간과 같은 요소를 기반으로 최종 사용자의 성능 인식을 고려합니다.
■ 온라인 DDL 공간(용량) 요구사항
온라인 DDL 작업에는 다음과 같은 공간 요구 사항이 있습니다.
+ 임시 로그 파일을 위한 공간
온라인 DDL 조작이 인덱스를 작성하거나 테이블을 변경할 때 임시 로그 파일은 동시 DML을 기록합니다. 임시 로그 파일은 innodb_sort_buffer_size의 값에 따라 innodb_online_alter_log_max_size에 지정된 최대 값까지 확장됩니다. 임시 로그 파일이 크기 제한을 초과하면 온라인 DDL 작업이 실패하고 커밋되지 않은 동시 DML 작업이 롤백됩니다. 큰 innodb_online_alter_log_max_size 설정은 온라인 DDL 조작 중에 더 많은 DML을 허용하지만, 로그가 기록 된 DML을 적용하기 위해 테이블이 잠길때 DDL 조작 종료시 시간이 연장됩니다.
작업 시간이 오래 걸리고 동시 DML이 임시 로그 파일의 크기가 innodb_online_alter_log_max_size 값을 초과하도록 테이블을 너무 많이 수정하면 온라인 DDL 작업이 DB_ONLINE_LOG_TOO_BIG 오류와 함께 실패합니다.
+ 임시 정렬 파일을 위한 공간
테이블을 다시 작성하는 온라인 DDL 작업은 인덱스 생성 중에 임시 정렬 파일을 MySQL 임시 디렉토리 (Unix의 경우 $ TMPDIR, Windows의 경우 % TEMP % 또는 --tmpdir로 지정된 디렉토리)에 씁니다. 원래 테이블이 포함된 디렉토리에 임시 정렬 파일이 작성되지 않습니다. 각 임시 정렬 파일은 하나의 데이터 열을 보유 할만큼 충분히 크며 데이터가 최종 테이블 또는 인덱스에 병합 될 때 각 정렬 파일이 제거됩니다. 임시 정렬 파일과 관련된 작업에는 테이블의 데이터 양에 인덱스를 더한 임시 공간이 필요할 수 있습니다. 온라인 DDL 조작이 데이터 디렉토리가있는 파일 시스템에서 사용 가능한 모든 디스크 공간을 사용하는 경우 오류가 보고됩니다.
MySQL 임시 디렉토리가 정렬 파일을 보유하기에 충분히 크지 않은 경우 tmpdir을 다른 디렉토리로 설정합니다. 또는 innodb_tmpdir을 사용하여 온라인 DDL 조작을 위한 별도의 임시 디렉토리를 정의해야 합니다. 이 옵션은 대용량 임시 정렬 파일의 결과로 발생할 수있는 임시 디렉토리 오버플로를 방지하기 위해 MySQL 5.7.11에서 도입되었습니다.
+ 중간 테이블 파일을 위한 공간
테이블을 재 빌드하는 일부 온라인 DDL 조작은 원래 테이블과 동일한 디렉토리에 임시 중간 테이블 파일을 작성합니다. 중간 테이블 파일에는 원래 테이블의 크기와 동일한 공간이 필요할 수 있습니다. 중간 테이블 파일 이름은 #sql-ib 접두사로 시작하며 온라인 DDL 작업 중 잠시만 나타납니다.
innodb_tmpdir 옵션은 중간 테이블 파일에는 적용되지 않습니다.
■ 온라인 DDL로 DDL 문 단순화
온라인 DDL을 도입하기 전에 많은 DDL 작업을 단일 ALTER TABLE 문으로 결합하는 것이 일반적이었습니다. 각 ALTER TABLE 문은 테이블 복사 및 재 빌드를 포함하므로 테이블에 대한 단일 재빌드 조작으로 변경 사항을 모두 수행할 수 있으므로 동일한 테이블을 한번에 여러번 변경하는 것이 더 효율적이었습니다. 단점은 DDL 작업과 관련된 SQL 코드가 다른 스크립트에서 유지 관리 및 재사용하기가 더 어렵다는 것입니다. 매번 특정 변경사항이 다른 경우 약간 다른 시나리오마다 새로운 복잡한 ALTER TABLE을 구성해야 할 수도 있습니다.
제자리(in-place)에서 수행 할 수 있는 DDL 작업의 경우 효율성을 저하시키지 않으면서 스크립팅 및 유지 관리가 용이하도록 개별 ALTER TABLE 문으로 분리 할 수 있습니다. 예를 들어, 다음과 같은 복잡한 진술을 할 수 있습니다.
ALTER TABLE t1 ADD INDEX i1(c1), ADD UNIQUE INDEX i2(c2),
CHANGE c4_old_name c4_new_name INTEGER UNSIGNED;
다음과 같이 독립적으로 테스트하고 수행 할 수있는 더 간단한 파트로 분류하십시오.
ALTER TABLE t1 ADD INDEX i1(c1);
ALTER TABLE t1 ADD UNIQUE INDEX i2(c2);
ALTER TABLE t1 CHANGE c4_old_name c4_new_name INTEGER UNSIGNED NOT NULL;
다음과 같이 여러 부분으로 된 ALTER TABLE 문을 계속 사용할 수 있습니다.
+ 인덱스 생성 및 해당 인덱스를 사용하는 외래 키 제약 조건과 같이 특정 순서로 수행해야하는 작업.
+ 그룹으로 성공 또는 실패하려는 동일한 특정 LOCK 절을 사용하는 모든 조작.
+ 제자리(in-place)에서 수행 할 수 없는, 즉 여전히 테이블 복사 방법을 사용하는 작업.
+ ALGORITHM = COPY 또는 old_alter_table = 1을 지정하는 조작은 특수 시나리오에서 정확한 역 호환성을 위해 필요한 경우 테이블 복사 동작을 강제 실행합니다.
■ 온라인 DDL 실패 조건
온라인 DDL 작업 실패는 일반적으로 다음 조건 중 하나에 기인합니다.
+ ALGORITHM 절은 특정 유형의 DDL작업 또는 스토리지 엔진과 호환되지 않는 알고리즘을 지정합니다.
+ LOCK절은 특정 유형의 DDL 작업과 호환되지 않는 낮은 수준의 잠금 (SHARED 또는 NONE)을 지정합니다.
+ 테이블에서 배타적 잠금을 기다리는 동안 시간 종료가 발생하며, 이는 DDL 조작의 초기 및 최종 단계에서 잠시 필요할 수 있습니다.
+ tmpdir 또는 innodb_tmpdir 파일 시스템의 디스크 공간이 부족한 반면, MySQL은 인덱스 작성 중에 디스크에 임시 정렬 파일을 씁니다.
+ 작업 시간이 오래 걸리고 동시 DML이 테이블을 수정하여 임시 온라인 로그의 크기가 innodb_online_alter_log_max_size 구성 옵션의 값을 초과합니다. 이 조건으로 인해 DB_ONLINE_LOG_TOO_BIG 오류가 발생합니다.
+ 동시 DML은 원래 테이블 정의에는 허용되지만 새 테이블 정의에는 허용되지 않는 테이블을 변경합니다. MySQL이 동시 DML 문의 모든 변경 사항을 적용하려고 할 때 작업이 끝날 때만 실패합니다. 예를 들어 고유 인덱스를 만드는 동안 열에 중복 값을 삽입하거나 해당 열에 기본 키 인덱스를 만드는 동안 NULL 값을 열에 삽입 할 수 있습니다. 동시 DML에 의한 변경 사항이 우선하며 ALTER TABLE 작업이 효과적으로 롤백됩니다.
■ 온라인 DDL 제한
온라인 DDL 작업에는 다음 제한이 적용됩니다.
+ TEMPORARY TABLE에서 인덱스를 만들 때 테이블이 복사됩니다.
+ 테이블에 ON ... CASCADE 또는 ON ... SET NULL 제한 조건이 있으면 ALTER TABLE 절 LOCK = NONE이 허용되지 않습니다.
+ 온라인 DDL작업을 완료하려면 테이블에서 메타 데이터 잠금을 보유한 트랜잭션이 커밋 또는 롤백 될 때까지 기다려야합니다. 온라인 DDL 작업의 경우 실행 단계에서 테이블에 독점 메타 데이터 잠금이 잠깐 필요할 수 있으며 테이블 정의를 업데이트 할 때는 항상 작업의 마지막 단계에서 하나가 필요합니다. 따라서 테이블에서 메타 데이터 잠금을 보유한 트랜잭션으로 인해 온라인 DDL 작업이 차단될 수 있습니다. 테이블에서 메타 데이터 잠금을 보유하는 트랜잭션이 온라인 DDL작업전 또는 도중에 시작되었을 수 있습니다. 테이블에서 메타 데이터 잠금을 보유하는 장기 실행 또는 비활성 트랜잭션으로 인해 온라인 DDL 작업이 시간 초과 될 수 있습니다.
+ 외래키 관계의 테이블에 대한 온라인 DDL작업은 외래키 관계의 다른 테이블에서 실행되는 트랜잭션이 커밋 또는 롤백될 때까지 기다리지 않습니다. 트랜잭션은 업데이트중인 테이블에 독점 메타 데이터 잠금을 보유하고 외래키 관련 테이블에 대한 공유 메타 데이터 잠금을 보유합니다(외래 키 검사에 필요). 공유 메타 데이터 잠금은 테이블 정의를 업데이트하기 위해 독점 메타 데이터 잠금이 필요한 경우 온라인 DDL 작업이 진행되도록하지만 최종 단계에서 작업을 차단합니다. 이 시나리오에서는 다른 트랜잭션이 온라인 DDL 작업이 완료되기를 기다리는 동안 교착 상태가 발생할 수 있습니다.
+ 온라인 DDL작업을 실행할 때 ALTER TABLE문을 실행하는 스레드는 다른 연결 스레드의 동일한 테이블에서 동시에 실행된 DML 작업의 온라인 로그를 적용합니다. DML작업이 적용되면 중복 항목이 일시적이고 온라인 로그의 이후 항목으로 되돌아가더라도 중복키 항목 오류 (오류 1062 (23000) : 중복 항목)가 발생할 수 있습니다. 이는 트랜잭션 중에 제약 조건을 유지해야하는 InnoDB의 외래 키 제약 조건 검사와 유사합니다.
+ InnoDB 테이블의 OPTIMIZE TABLE은 ALTER TABLE작업에 매핑되어 테이블을 재구성하고 인덱스 통계를 업데이트하고 클러스터형 인덱스에서 사용되지 않은 공간을 비웁니다. 키가 기본키에 나타난 순서대로 삽입되므로 보조 인덱스가 효율적으로 생성되지 않습니다. OPTIMIZE TABLE은 일반 및 파티션 된 InnoDB 테이블을 재구축하기위한 온라인 DDL지원을 추가하여 지원됩니다.
+ 임시 열 (DATE, DATETIME 또는 TIMESTAMP)을 포함하고 ALGORITHM = COPY를 사용하여 재구축되지 않은 MySQL 5.6 이전에 생성된 테이블은 ALGORITHM=INPLACE를 지원하지 않습니다. 이 경우 ALTER TABLE ... ALGORITHM=INPLACE 조작은 다음 오류를 리턴합니다.
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.
Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
+ 다음 제한 사항은 일반적으로 테이블을 재구성하는 대형 테이블에서 온라인 DDL작업에 적용됩니다.
- 온라인 DDL 작업을 일시 중지하거나 온라인 DDL 작업에 대한 I/O 또는 CPU 사용량을 조절하는 메커니즘이 없습니다.
- 온라인 DDL작업의 롤백은 작업이 실패하는 경우 비용이 많이들 수 있습니다.
- 온라인 DDL작업을 오래 실행하면 복제 지연이 발생할 수 있습니다. 온라인 DDL 작업은 슬레이브에서 실행되기 전에 마스터에서 실행을 완료해야합니다. 또한 마스터에서 동시에 처리된 DML은 슬레이브의 DDL 작업이 완료된 후에 만 슬레이브에서 처리됩니다.
※도움이 되셨다면 광고클릭 한번 부탁드립니다.※
'Databases > MySQL' 카테고리의 다른 글
| [MySQL][InnoDB] InnoDB에서 다른 SQL 문으로 설정된 잠금 (0) | 2020.07.02 |
|---|---|
| [MySQL][InnoDB] InnoDB 스키마 정보 테이블 (0) | 2020.06.28 |
| [MySQL][InnoDB] 행(Row)형식 (0) | 2020.06.22 |
| [MySQL][InnoDB] 페이지 압축 (2) | 2020.06.17 |
| [MySQL][InnoDB] 테이블 압축 (0) | 2020.06.13 |