beomil ideas

MySQL 5.1 미만의 버전에서는 InnoDB 테이블의 변경 작업

(컬럼 또는 인덱스의 추가 삭제 수정)은 InnoDB 테이블 전체의 데이터를 다른 테이블로 복사를 하면서 처리된다.
이로 인해서 간단한 추가나 인덱스 작업마저도 상당한 시간이 소요되며,
이러한 작업들은 일반적으로 해당 테이블 전체에 대해서 Lock을 점유하기 때문에
실시간 사용자들의 접근을 모두 대기 상태로 만들어 버리게 된다.

MySQL 5.1 이상의 버전에서는 InnoDB가 Plugin버전으로 제공되며,
InnoDB Plugin을 이용한 테이블에서는 인덱스 작업은 테이블의 데이터 복사를 피할 수 있다.
(하지만 여전히 Primary key 변경이나 컬럼 변경 작업은 기존과 동일한 방식으로 처리된다.)

그래서, 이러한 작업의 처리 시간을 최소화하기 위해서는
여러 가지 변경 작업들을 한꺼번에 모아서 처리하는 것이 가장 좋다.
사실 간단한 방법이지만, 이 방법을 모르는 사람들이 의외로 많은 듯 하여 글을 작성한다.

MySQL의 ALTER TABLE 명령문에는 여러 개의 변경을 동시에 명시할 수 있다.

ALTER TABLE tb_test ADD INDEX ix_fd1_fd2 (fd1, fd2);
ALTER TABLE tb_test ADD COLUMN fd3 INT NOT NULL DEFAULT 0;

위와 같은 두 개의 작업을 별도로 처리하면 tb_test 테이블의 레코드 건수만큼 2배의 작업 소요시간이 걸리고,
그 시간 동안 테이블이 잠기게 된다. 하지만, 이 처리를 아래와 같이 하나의 문장으로 묶어서 처리할 수 있다.

ALTER TABLE tb_test ADD COLUMN fd3 INT NOT NULL DEFAULT 0 ,
ADD INDEX ix_fd1_fd2(fd1, fd2);

이렇게 한 문장으로 묶어서 실행하면, MySQL은 이 두 가지 작업을 묶어서 한번의 테이블 데이터 복사 작업으로 완료하게 된다.
(각 ALTER 문장 단위로 구분자 “,”를 꼭 붙혀줘야 한다.)

Posted in MySQL