lock=none, algorithm=inplace 으로 테이블 변경하기

발생일: 2020.07.30

키워드: mysql, aurora, DB 컬럼 추가, DB 인덱스 추가, add index without lock, add column

문제:

 

크기가 큰 MySQL (Aurora) 테이블에 컬럼이나 인덱스를 추가하려고 한다.

테이블 크기가 큰 경우, 락 타임이 길어져서 운영 중일 땐 문제가 발생할 수 있다.

 

어떻게 하면 될까?

 

해결책:

algorithm 절과 lock 절을 이용하면, 락을 걸지 않고 컬럼이나 인덱스를 추가 또는 삭제할 수 있다.

 

컬럼 추가

 

규모가 큰 larget_table 에 new_date 란 이름으로 DATETIME 형식의 컬럼을 추가한다고 가정하면, 아래와 같이 하면 된다.

 

    ALTER TABLE large_table
    ADD new_date DATETIME null, ALGORITH = INPLACE, LOCK = NONE;

 

 

인덱스 추가

 

large_table 의 new_date 컬럼에 new_date_index 를 추가한다고 가정하면, 아래와 같이 하면 된다.

    ALTER TABLE large_table
    ADD INDEX new_date_index (new_date), ALGORITH = INPLACE, LOCK = NONE;

 

 

시간이 조금 걸리더라도 인내를 갖고(?) 기다리면 된다.

운영 중인 5천만 행 정도 되는 테이블을 수정했는데, 3분 정도 후에 특별한 문제 없이 적용됐다.

 

논의:

추가하려는 컬럼이나 인덱스가 여러 개라면, 한 번에 하나씩 하는 게 안전하다.

ALTER TABLE 명령에서 여러 태스크를 실행하면 복잡도가 높아지면서 INPLACE 대신 COPY 로 전환될 수 있나보다.

 

 

참고:

- 자세한 설명: http://www.gurubee.net/lecture/4184

- 여러 컬럼은 나눠서 하는 게 좋음: https://stackoverflow.com/questions/46970401/mysql-add-multiple-column-to-large-table-in-optimised-way