[MySQL] 대용량 테이블 스키마 변경하기

목차

 

 

 

1. ALTER TABLE

아래와 같은 단순 DDL 구문으로 테이블의 스키마를 변경할 수 있습니다.

ALTER TABLE [테이블명] MODIFY COLUMN [변경할 컬럼명][변경할 컬럼 타입]

• 예시 코드

ALTER TABLE user MODIFY COLUMN name varchar(10);

 

 

2. 대용량 데이터 테이블 스키마 변경하기

데이터가 100만, 1000만 정도일때 테이블 스키마 변경은 alter table로 가능했습니다.
천만건이라도 alter table은 5분안에 끝났기 때문입니다.
하지만 1억건이 넘어가면 이야기가 달라집니다.
3~4시간동안 진행될수도 있기 때문에 다른 방법으로 진행해야합니다.

 

전체적인 작업 과정은 다음과 같습니다.

• 복사테이블 생성 -> 복사테이블에 원본 테이블 데이터 복사 -> 테이블 이름 변경

 

*주의) 외부의 DB 사용이 중지되었을 경우 해당 작업을 진행합니다.

 

2.1 FK 삭제하기

기존 테이블에 FK 제약조건이 있을 경우엔 바로 카피 테이블을 생성할수 없습니다.
FK는 이름이 유니크해야하므로, 기존 테이블의 FK를 삭제해야만 진행할 수 있습니다.

 

ALTER TABLE 테이블명 DROP FOREIGN KEY FK이름

 

2.2 copy table 생성

기존 테이블에서 스키마가 변경된 테이블을 새로 만듭니다.
(예를 들어 컬럼이 하나 추가되어야 하거나, 삭제되어야 하는 등)
이때 인덱스는 모두 추가하여 생성합니다.

 

 

2.3 SELECT INSERT 실행

새로 생성된 카피 테이블에 기존 테이블 데이터를 select insert 합니다.

INSERT INTO 카피테이블 (카피테이블의 컬럼명들)
SELECT 원본테이블 컬럼명들 FROM 원본테이블;

 

 

2.4 테이블 명 변경

 

먼저 기존 테이블 명을 임의의 다른 이름으로 변경합니다.

RENAME TABLE `item` TO `item_20240310`;

 

 

이후 복사 테이블을 원본테이블 명으로 변경합니다.

RENAME TABLE `item_copy` TO `item`;

 

이렇게하면 1억개 이상의 row를 가진 테이블이어도 40~50분 이내에 작업이 완료된다고합니다.

 

---

참고 문서:

https://jojoldu.tistory.com/244

https://nexthops.tistory.com/2