MySQL 5.1 ~ 5.5 버전까지는 스키마 변경 작업 시 (DDL) Online 으로 작업이 되지 않아서,
DBA 작업 시 다른 세션에서의 write 작업이 모두 블럭킹 되었다. 또한 과거의 MySQL 에서는 모든 DDL 작업 시 전체테이블이 재생되도록 동작하였고,
테이블이 변경되는 동안에는 ( altering ) 테이블에 write 가 불가하였다.
-- 1. 빈 테이블 생성 -> row 한 개씩 새로운 테이블로 copy
-- 2. 인덱스는 row 가 insert 될 때 마다 update 됨
-- 3. 모든 row 가 copy 되고 나면 과거의 테이블은 drop
-- 4. 새로운 테이블의 이름이 원래의 테이블 이름으로 rename 된다.
그러나 MySQL 5.6 부터는 DDL 의 일부가 Online 으로 수행할 수 있도록 변경 되었다.
테이블이 alter 되는 동안에도 SELECT 쿼리와 INSERT, UPDATE, DELETE (DML) 구문이 수행될 수 있게 허용하는 것이다.
CREATE INDEX, DROP INDEX 명령의 문법은 변경된 것이 없지만, 몇몇 요소는 성능, 공간 사용과 관련하여 영향을 줄 수 있다.
다음은 mysql 래퍼런스에 Online DDL 을 설명하는 표를 해석하였다.
- INPLACE 여부
: ALGORITHM=INPLACE 구문을 허용하는지 여부. 값이 O 여야 ONLINE 으로 수행할 수 있는 작업이라는 뜻.
- Copies Table 여부
: 해당 작업이 고비용의 table copy 작업을 요구하는지 여부. INPLACE 여부 컬럼과 함께 확인한다.
몇몇 작업은 ALGORITHM=INPLACE 가 허용되지만, 여전히 table copy 작업이 포함되어있는 경우가 있다.
[참고] Online 으로 수행하는 것도 두 가지 경우로 나뉜다.
1. 내부적으로 임시 테이블을 생성하면서 Online 으로 동작하는 방식
SELECT, DML 은 허용하지만 내부적으로 copy-table 되므로, 시스템 리소스를 사용한다.
2. 테이블 구조 메타정보만 변경하는 방식. 시스템 리소스 사용 이슈가 없음.
1번의 경우 Online 동작으로 다른 세션에서 write 는 가능하지만, 내부적으로 임시테이블을 만드는 동작으로
2번과 비교하여 디스크 read, write 로 인한 I/O 리소스 사용량이 증가한다.
- 동시 DML 허용 여부
완전히 online 으로 동작할 수 있는지 여부를 나타낸다. 사용자는 LOCK=NONE 을 명시할 수 있지만, MySQL 이 가능한 경우 자동으로 동시 레벨을 허용한다.
동시 DML 이 허용될 때, 동시 쿼리도 마찬가지로 허용된다.
- 동시 쿼리 허용 여부
: 특정 테이블에 작업이 진행되는 동안에 DDL 작업이 동시 쿼리를 허용하는지 여부를 나타낸다.
동시 쿼리는 모든 online DDL 작업 동안에 허용된다. 기본적으로 값이 O 이다.
DDL 을 수행하는 동안에 동시 쿼리를 허용하려고 LOCK=SHARED 옵션을 명시 할 수 있지만, MySQL 이 가능한 경우 자동으로 동시 레벨을 허용한다.
작업 |
INPLACE 여부 |
Copies Table 여부 |
동시 DML 허용 여부 |
동시 쿼리 허용 여부 |
비고 |
CREATE INDEX ADD INDEX |
O |
X |
O |
O |
단, FULLTEXT 인덱스에 대해서는 제한이 있음. |
ADD FULLTEXT INDEX |
O |
X |
X |
O |
사용자가 지정한 FTS_DOC_ID 컬럼이 없다면, table-copy 를 포함하여 테이블에 FULLTEXT 인덱스를 생성한다. 이후 FULLTEXT 인덱스는 동일한 테이블에 INPLACE 로 생성 가능하다. |
DROP INDEX |
O |
X |
O |
O |
.frm 파일만 수정하며, 데이터파일은 수정하지 않는다. |
OPTIMIZE TABLE |
O |
O |
O |
O |
MySQL 5.6.17 버전에서 ALGORITHM=INPLACE 로 사용한다. 만일 old_alter_table=1 또는 mysqld 가 --skip-new 옵션이 활성화 된 상태라면, ALGORITHM=COPY 가 사용된다. OPTIMIZE TABLE 은 FULLTEXT 인덱스를 가진 테이블에는 Online DDL (ALGORITHM=INPLACE) 을 지원하지 않는다. |
컬럼에 default 값 설정 |
O |
X |
O |
O |
.frm 파일만 수정하며, 데이터파일은 수정하지 않는다. |
컬럼 auto-increment 값 변경 |
O |
X |
O |
O |
메모리에 저장된 값을 수정하고, 데이터파일은 수정하지 않는다. |
외래키 제약조건 추가 |
O |
X |
O |
O |
table-copy 를 하지 않으려면, 제약조건을 생성할 때 foreign_key_checks 옵션을 비활성화 해야한다. |
외래키 제약조건 삭제 |
O |
X |
O |
O |
foreign_key_checks 옵션은 활성화/비활성화 될 수 있다. |
컬럼 RENAME | O | X | O | O | 동시 DML 을 허용하기 위해서 동일한 데이터를 유지하고 컬럼명만 변경한다. |
컬럼 ADD | O | O | O | O | auto-increment 컬럼을 추가할 때는 동시 DML 이 허용되지 않는다. ALGORITHM=INPLACE 가 허용되지만, 대체적으로 데이터가 재구성되므로 여전히 고비용 작업이다. |
컬럼 DROP | O | O | O | O | ALGORITHM=INPLACE 가 허용되지만, 대체적으로 데이터가 재구성되므로 여전히 고비용 작업이다. |
컬럼 순서 변경 | O | O | O | O | ALGORITHM=INPLACE 가 허용되지만, 대체적으로 데이터가 재구성되므로 여전히 고비용 작업이다. |
ROW_FORMAT 설정 변경 | O | O | O | O | ALGORITHM=INPLACE 가 허용되지만, 대체적으로 데이터가 재구성되므로 여전히 고비용 작업이다. |
| KEY_BLOCK_SIZE 변경 | O | O | O | O | ALGORITHM=INPLACE 가 허용되지만, 대체적으로 데이터가 재구성되므로 여전히 고비용 작업이다. |
컬럼 NULL 변경 | O | O | O | O | ALGORITHM=INPLACE 가 허용되지만, 대체적으로 데이터가 재구성되므로 여전히 고비용 작업이다. |
| 컬럼 NOT NULL 변경 | O | O | O | O | SQL_MODE 가 STRICT_ALL_TABLES 또는 STRICT_TRANS_TABLES 를 포함할때, 컬럼에 null 이 포함되어있다면 작업은 실패한다. mysql 5.6.7 서버에서는 참조 무결성의 손실이 발생할 수 있는 외래키 컬럼은 변경할 수 없다. ALGORITHM=INPLACE 가 허용되지만, 대체적으로 데이터가 재구성되므로 여전히 고비용 작업이다. |
컬럼 데이터타입 변경 | X | O | X | O | |
PRIMARY KEY 추가 | O | O | O | O | ALGORITHM=INPLACE 가 허용되지만, 대체적으로 데이터가 재구성되므로 여전히 고비용 작업이다. |
PRIMARY KEY 삭제와 다른 컬럼 추가 | O | O | O | O | 동일한 ALTER TABLE 구문 내에 새로운 PRIMARY KEY 를 추가할 때만 ALGORITHM=INPLACE 가 허용된다. 데이터는 대체로 재구성되므로, 여전히 고비용 작업이다. |
PRIMARY KEY DROP | X | O | X | O | 동일한 ALTER TABLE 구문에 새로운 PK 추가 없이 PK 를 삭제할 때에는 제한사항이 적용된다. |
캐릭터셋 변경 | X | O | X | O | 기존과 다른 새로운 캐릭터 인코딩일 경우 테이블이 재구성(Rebuild) 된다. |
| 캐릭터셋 지정 | X | O | X | O | 기존과 다른 새로운 캐릭터 인코딩일 경우 테이블이 재구성(Rebuild) 된다. |
FORCE 옵션으로 Rebuild | O | O | O | O | MySQL 5.6.17 버전에서 ALGORITHM=INPLACE 로 사용한다. 만일 old_alter_table=1 또는 mysqld 가 --skip-new 옵션이 활성화 된 상태라면, ALGORITHM=COPY 가 사용된다. OPTIMIZE TABLE 은 FULLTEXT 인덱스를 가진 테이블에는 Online DDL (ALGORITHM=INPLACE) 을 지원하지 않는다. |
ALTER TABLE ... ENGINE=INNODB null로 Rebuild | O | O | O | O | MySQL 5.6.17 버전에서 ALGORITHM=INPLACE 로 사용한다. 만일 old_alter_table=1 또는 mysqld 가 --skip-new 옵션이 활성화 된 상태라면, ALGORITHM=COPY 가 사용된다. OPTIMIZE TABLE 은 FULLTEXT 인덱스를 가진 테이블에는 Online DDL (ALGORITHM=INPLACE) 을 지원하지 않는다. |
테이블 레벨로 persistent statistics 옵션 설정 (STATS_PERSISTENT, STATS_AUTO_RECALC STATS_SAMPLE_PAGES ) | O | X | O | O | .frm 파일만 수정되며, 데이터파일은 수정되지 않는다. |
Secondary Indexes
- InnoDB 테이블에 세컨더리 인덱스를 생성하고 삭제하는 것은, table-copying 작업을 건너뛰는 것은 InnoDB 플러그인을 사용하는 MySQL 5.1 과 5.5 가 동일하다.
MySQL 5.6 이상 버전에서는 인덱스가 생성 또는 삭제되는 동안, 테이블 읽기 및 쓰기 작업이 모두 가능하다.
CREATE INDEX 또는 DROP INDEX 구문은 해당 테이블에 접근하는 모든 트랜잭션이 완료된 이후에 완료되기 때문에,
인덱스의 초기 상태는 테이블의 가장 최근의 내용을 반영한다.
이전에는, 인덱스가 생성되고 삭제되는 동안에 테이블을 수정하는 것이 일반적으로 데드락으로 인해
테이블에 INSERT, UPDATE, DELETE 구문이 취소되었다.
동시 DML 은 여전히 table-copy 작업을 필요로 한다.
몇몇 다른 ALTER TABLE 작업은 동시 DML 을 허용하지만, 여전히 table-copy 를 필요로 한다.
그러나 MySQL 5.5 이전버전보다는 table-copy 작업이 빠르다.
다음은 Online DDL 로 실행되어 동시 DML 쿼리가 가능하지만, 내부적으로 table-copy 가 발생하는 DDL 의 종류이다.
(위 표에 모두 표기되어있음.)
- 컬럼 add, drop, reorder
- primary key add drop
- 테이블의 ROW_FORMAT 또는 KEY_BLOCK_SIZE 설정 변경
- 컬럼을 nullable 로 변경
- OPTIMIZE TABLE
- FORCE 옵션으로 테이블 재구성 (Rebuild)
- null 을 사용하여 ALTER TABLE ... ENGINE=INNODB 구문으로 테이블 재구성
참고링크
https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html