ALTER로 복사하지 않는 VARCHAR 크기의 변경(ONLINE DDL 기능)

오래된 Mysql에서 ALTER TABLE은 완전한 데이터의 복사가 필요했다. ALTER TABLE 을 실행하면 새로운 정의의, 사용자에게 보이지 않는 임시 테이블을 작성해서 원래의 테이블에서 데이터를 모두 복사한 후, 완료되면
재명명하는 처리를 했다. 이러한 조작이 진행되는 동안 사용자는 테이블의 데이터를 참조는 할 수 있지만 변경하거나 LOCK을 걸 수는 없다. 다시 말해 Non Locking Read만 허용했다.
그 외의 처리는 ALTER TABLE이 종료될 때까지 금지되었으므로 변경이나 LOCK이 필요한 애플리케이션은 정지해 버린다. 또한 마지막에 rename하는 시점만은 배타적 접근이 된다.

 

먼저 Mysql 5.1의 InnoDB Plugin과 Mysql 5.5에는 Fast index Creation이라는 기능이 추가되었다. 이것은 인덱스를 추가할 때 새로운 정의의 테이블을 작성하고 데이터를 완전히 복사하는 대신 인덱스만을 작성하는
아주 자연스러운 기능이다. 이로 인해 인덱스 작성에 걸리던 시간은 비약적으로 짧아졌지만 작성 중에는 마찬가지로 변경이나 Lock이 불가능했다.

 

그래서 Mysql 5.6에서는 마침내 InnoDB 온라인 DDL이 추가되었다. 온라인에서 테이블 정의의 변경이 실행 가능한 경우, 데이터를 복사하는 중에서도 참조뿐 아니라 변경도 가능했다.
단, ALTER TABLE을 시작하는 시점과 종료하는 시점에는 테이블 정의의 정합성을 확보하기 위해 그 순간만 배타 처리를 일으켜 row lock이 필요한 트랜잭션과는 동시에 실행할 수 없다. 이러한 제한이 있기는 하지만 그래도 온라인
상태가 아닌 경우보다는 애플리케이션에 대한 영향이 훨씬 적어졌다.
Mysql 5.6의 온라인 DDL로 충분하다고 생각할지 모르겠지만, 문제가 완전히 사라진 것은 아니다. 가장 큰 문제는 온라인 상태에서 실행할 수 있는 경우와 그렇지 않은 경우가 있다는 것이다. 온라인 상태에서 실행할 수 없는 DDL은
기존처럼 임시 테이블을 작성하고 데이터를 복사하고 마지막에 rename하는 방법으로 실행한다. 온라인 상태에서 실행할 수 없는 DDL은 다음과 같다.

-----------------------------------------------------

컬럼 정의 변경
기본키 삭제
전문 검색 인덱스 추가

-----------------------------------------------------

 

ysql 5.7 에서는 컬럼 정의의 변경에 관한 제한이 일부 완화되어, VARCHAR의 크기를 늘릴 때 데이터를 복사하지 않고 정의를 변경할 수 있게 되었다. 단, 크기를 늘릴 수는 있지만 줄이지는 못한다.
예를 들면 VARCHAR(32)인 컬럼을 VARCHAR(64)로 바꾼다면 리스트 4.8과 같이 명령어를 실행한다.

 

alter table tbl_name ALGORITHM=INPLACE, MODIFY col VARCHAR(64) NOT NULL;

온라인 DDL임을 강제하고자 할 때는 ALGORITHM=INPLACE을 지정한다. 이 지정이 있으면 DDL이 온라인 상태에서 처리될 수 없는 상황에서는 명령어 실행이 실패한다.
이 지정이 없는 경우에는 온라인 수행 가능 여부에 따라 자동적으로 ALTER TABLE의 실행방식을 선택한다. 덧붙여 이전부터 있었던 COPY에 의한 ALTER TABLE은 ALGORITHM=COPY 를 지정하면 된다.
VARCHAR의 크기를 늘릴 수 있지만 어떤 크기로든지 지정할 수는 없으니 주의해야 한다. 왜나하면 VARCHAR 타입의 컬럼에는 실제의 문자열의 길이를 표시하기 위한 메타 데이터가 있어 그것이 255 바이트 이하인지 그보다 큰지에 따라
나뉘기 때문이다. 또한 VARCHAR는 65,535 바이트를 넘으면 내부적으로는 TEXT 타입이 선택되므로 그 경계를 넘어 온라인 DDL로 크기를 늘릴 수는 없다. 예를 들면 온라인 DDL로 최대크기를 10 byte에서 100 byte로 늘릴 수는
있지만, 100 byte에서 1000 byte로 늘릴 수는 없다. 여기서 이러한 제한에 걸려있는 크기의 단위가 byte라는 점에 주의하자.

테이블 정의에서 VARCHAR의 크기는 문자 수를 나타내므로 컬럼의 최대 바이트 수가 얼마가 될지는 사용할 문자 코드와 문자 수로 계산해야 한다.
예를 들면 utf8md4의 경우, 한문자당 최대 4바이트를 소비한다. 따라서 VARCHAR(64)의 최대 바이트 수는 256이므로 VARCHAR(63)을 VARCHAR(64)로 변경하는 것은 온라인 상태에서 할 수 없다. 그러한 제한에 걸리지 않는다면
VARCHAR 크기의 변경은 실질적으로 메타 데이터를 고쳐쓰는 것만으로 완료한다.

 

--> mysql 5.5에서 5.7로 업데이트해서 5.6에서는 지원제한이 있고 5.7에서 업데이트된 내용인지 몰랐다.

컬럼의 사이즈 변경은 5.7.23에서 한번 더 버그패치가 된다. 최신버전으로 업데이트할 이유가 생겼다.