mysql, 온라인 스키마 변경과 주의사항
DDL 명령 시 알고리즘과 락
- DDL 명령 각각마다 사용 가능한 알고리즘과 리빌드 여부, DML 허용 여부가 다르다. 그러므로 사용하는 서버의 버전에 맞춰 매뉴얼을 확인해야 한다. 해당 매뉴얼은 아래 링크를 참고하자.
-
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
- DDL을 안전하게 처리하는 방법 중 하나는 알고리즘과 락을 명시하는 방식이다. INSTANT 알고리즘과 NONE 락은 가장 낮은 수위로서 해당 옵션을 사용하여 DDL를 수행할 경우, 처리 가능하면 바로 처리되고 그렇지 않은 경우 에러를 출력한다. 이를 통해 속도가 느릴 수 있는 의도치 않은 DDL 작업을 방지한다.
알고리즘
INSTANT
- 테이블의 데이터는 전혀 변경하지 않고, 메타데이터만 변경하고 작업 완료.
- 레코드 건수와 무관하게 작업시간은 매우 짧다.
- 스키마 변경 도중 테이블의 읽고 쓰기는 대기하게 되지만 변경 시간이 매우 짧기 때문에 크게 영향을 미치지 않음.
INPLACE
- 임시 테이블로 데이터를 복사하지 않고 스키마 변경을 실행.
- 스키마 변경 작업 도중에도 테이블의 읽기 쓰기 전부 가능.
- 테이블의 리빌드를 실행할 수도 있음.
- 리빌드를 하더라도 잠금이 필요 없으므로 읽고 쓰기는 가능. 다만 레코드가 많을 경우 많은 시간이 필요.
- 리빌드가 필요 없을 경우 INSTANT와 비견되게 매우 빠름.
- 다른 커넥션의 쿼리 처리에 대한 영향도는 높지 않다.
COPY
- 변경된 스키마를 적용한 임시 테이블을 생성하고, 테이블의 레코드를 모두 임시 테이블로 복사한 후 최종적으로 임시 테이블을 RENAME해서 스키마 변경을 완료한다.
- 테이블 읽기만 가능하고 DML을 실행할 수 없다.
잠금
- NONE : 아무런 잠금을 걸지 않음
- SHARED: 스키마 변경 중 읽기는 가능하지만 쓰기는 불가
- EXCLUSIVE: 읽기 쓰기 불가
권장 방식과 주의 사항
- 알고리즘과 락은 다음과 같은 순서와 같이 우선적으로 사용하기를 권장한다. 1과 2번으로 동작하지 않는 경우 서비스를 멈추고 실행하는 것을 권장한다.
| priority | algorithm | LOCK |
|---|---|---|
| 1 | INSTANT | |
| 2 | INPLACE | NONE |
| 3 | INPLACE | SHARED |
| 4 | COPY | SHARED |
| 5 | COPY | EXCLUSIVE |
- INSTANT 작업은 매우 빠르고 실패 가능성이 거의 없다. 다만 INPLACE의 경우 빠른 처리와 성공을 언제나 보장하지 않는다.
- LOCK=NONE이더라도 테이블의 메타데이터 변경 시점에서는 락이 필요하다. 이때 락을 획득하지 못한 경우 실패한다.
- 다른 커넥션의 온라인 DML은 대기하지는 않지만 바로 적용되는 것은 아니다. 해당 DML은 별도의 로그(Online alter log)에 쌓아 두었다가 스키마 변경이 완료되면 실제 테이블에 일괄 적용한다. 장시간 진행하는 과정에서 해당 메모리 공간(innodb_online_alter_log_max_size)이 부족해진 경우 실패한다.
테이블 변경
- 테이블 생성/변경과 관련하여 다양한 명령이 있지만 그 중 몇 개만 소개한다.
테이블의 생성
CREATE **TEMPORARY** TABLE ...- 해당 커넥션(세션)에서만 사용 가능한 임시 테이블을 생성한다.
my_number int(4) unsigned zerofill- 숫자 왼쪽에 0으로 패딩한다.
insert into tb(my_number) values(1); -- 0001
테이블 조회
- SHOW CREATE TABLE
- 테이블의 메타 정보를 기반으로 CREATE TABLE을 재작성하여 제공한다.
- 신뢰 가능한 데이터이므로 테이블을 파악할 때 자주 사용하자!
- SHOW TABLE STATUS LIKE ‘mytable’ \G
- 테이블의 상태를 조회한다.
테이블 이름 변경
- 배치 프로그램을 작성하면서 테이블을 변경해야 할 때가 있다. 혹은 일정 주기로 테이블을 교체하는 경우가 있다. 이런 다양한 조건에서 보통 다음과 같이 처리한다.
CREATE TABLE batch_new ( ... );
INSERT INTO batch_new SELECT ... ;
RENAME TABLE batch TO batch_old;
RENAME TABLE batch_new TO batch;
- 위의 쿼리는 두 번의 RENAME TABLE이 수행하는 과정에서 일시적인 장애가 발생할 수 있다. 이 경우 한 번에 테이블의 이름을 변경할 수 있다.
RENAME TABLE batch TO batch_old
batch_new TO batch;
테이블 구조 복사
- 아래 DDL을 사용할 경우, 테이블의 칼럼과 인덱스 모두 복사한다.
CREATE TABLE tmp_employees LIKE employees;
INSERT INTO temp_employees SELECT * FROM employees;
테이블의 삭제
- 용량이 큰 경우 테이블 삭제는 큰 부하를 발생한다. 디스크의 파일 조각이 많이 분산되어 많은 디스크 읽기 쓰기 작업이 발생할 수 있기 때문이다.
- 그러므로 테이블이 클 경우 서비스 도중 삭제 작업은 하지 않는다.
칼럼 변경
- 칼럼의 추가
- 마지막에 추가할 경우 INSTANT로 즉시 추가된다.
- 칼럼을 중간에 추가할 경우 리빌드(INPLACE)가 필요하다.
- 그러므로 테이블이 클 경우 칼럼은 마지막에 추가한다.
- 칼럼 삭제: 무조건 리빌드(INPLACE)된다.
- 칼럼 이름 변경: 리빌드를 수행하지 않는다.
- 칼럼 변경
- 길이 축소(varchar(10)->varchar(20)), 데이터 타입 변경은 반드시 리빌드를 한다.
인덱스 변경
락
- B-tree 인덱스의 경우 락이 필요 없다.
- 전문검색과 공간검색의 경우 shared 잠금이 필요하다.
인덱스 이름의 변경
- 특정 인덱스의 이름을 힌트로 사용할 경우, 서비스 도중 해당 인덱스를 삭제하거나 변경하는 것이 어렵다.
- MySQL 5.7 이후부터 인덱스의 이름을 변경하거나 인덱스를 삭제하고 이름을 변경하는 것을 한 번에 처리할 수 있게 되었다.
-- 이름의 변경
ALTER TABLE salaries RENAME INDEX ix_salary TO ix_salary2, ALGORITHM=INPLACE, LOCK =NONE;
-- 기존 인덱스를 제거하고 새로운 인덱스를 해당 이름으로 변경
ALTER TABLE employees
DROP INDEX ix_firstname,
RENAME INDEX index_new TO ix_firstname,
ALGORITHM =INPLACE, LOCK=NONE;
인덱스 가시성 변경
- 어떤 인덱스가 필요 없다고 판단하여 삭제하였는데, 삭제 이후 성능 상 문제가 발생할 수 있다.
- 이런 문제를 방지하기 위하여 가시성 변경을 제공한다.
- 가시성 변경이란 해당 인덱스를 삭제하지 않고 다만 사용하지 못하도록 처리한다. 필요할 때 사용할 수 있도록 즉각 변경 가능하다.
- 가시성을 변경 후 모니터링을 통하여 인덱스 삭제 여부를 결정할 수 있다.
ALTER TABLE employees ALTER INDEX ix_firstname INVISIBLE;
ALTER TABLE employees ALTER INDEX ix_firstname VISIBLE;
인덱스의 삭제
- 인덱스의 삭제는 매우 빠르다.
- 프라이머리 키의 삭제는 COPY 알고리즘을 사용하므로 제약이 크다.
테이블 변경 묶음 실행
- 테이블 변경을 한 번에 묶어서 수행할 수 있다.
- 인덱스의 묶음 변경은 성능 상 좋다. 여러 인덱스를 생성하더라도 1회만 풀스캔을 수행하기 때문이다.
ALTER TABLE employees
ADD INDEX ix_lastname (last_name, first_name),
ADD INDEX ix_birthdate (birth_date),
ALGROITHM=INPLACE, LOCK=NONE;
RealMySQL 8.0, 11장을 참고하여 작성하였습니다.