ALTER TABLE Add Column에 시간이 오래 걸립니다.

ALTER TABLE Add Column에 시간이 오래 걸립니다.

데이터베이스의 테이블(main_table)에 "location"이라는 열을 추가하려고 했습니다.제가 실행한 명령어는

ALTER TABLE main_table ADD COLUMN location varchar (256);

main_table에는 2,000,000 이상의 행이 포함되어 있습니다.2시간 이상 계속 작동하지만 아직 완료되지 않았습니다.

사용하려고 했습니다.mytop이 데이터베이스의 액티비티를 감시하여 쿼리가 다른 쿼리 프로세스에 의해 잠겨 있지 않은지 확인합니다.그렇게 오래 걸릴까요?사실 이 명령어를 실행하기 전에 컴퓨터를 재부팅했습니다.이 명령어는 아직 실행 중입니다.어떻게 해야 할지 모르겠어요.

당신의.ALTER TABLEstatement는 mysql이 새 열을 포함하여 테이블의 모든 행을 다시 써야 함을 의미합니다.200만 개 이상의 행이 있기 때문에 상당한 시간이 걸릴 것으로 예상되며, 이 기간 동안 서버는 대부분 IO바인드 상태가 될 것입니다.일반적으로 다음과 같은 작업을 수행하는 것이 더 좋습니다.

CREATE TABLE main_table_new LIKE main_table;
ALTER TABLE main_table_new ADD COLUMN location VARCHAR(256);
INSERT INTO main_table_new SELECT *, NULL FROM main_table;
RENAME TABLE main_table TO main_table_old, main_table_new TO main_table;
DROP TABLE main_table_old;

이렇게 빈 테이블에 열을 추가하고 기본적으로 리소스를 잠그지 않으면 다른 사용자가 볼 수 없는 데이터를 새 테이블에 기록합니다.

이에 대한 적절한 답변은 pt-online-schema-change 또는 gh-ost 등의 기능을 사용하는 것이라고 생각합니다.

이를 통해 40억 행 이상의 마이그레이션을 수행했지만 다운타임은 1분도 채 걸리지 않고 최대 10일이 소요될 수 있습니다.

Percona는 위와 같은 방식으로 작업합니다.

• 임시 테이블 생성
• 첫 번째 테이블(삽입, 업데이트, 삭제용)에 트리거를 생성하여 임시 테이블로 복제합니다.
• 소규모 배치로 데이터 마이그레이션
• 완료되면 테이블 이름을 새 테이블로 변경하고 다른 테이블을 삭제합니다.

고유 검사 및 외부 키 검사를 일시적으로 해제하여 프로세스 속도를 높일 수 있습니다.사용되는 알고리즘을 변경할 수도 있습니다.

새 열을 테이블 끝에 배치하려면algorithm=instant:

SET unique_checks = 0;
SET foreign_key_checks = 0;
ALTER TABLE main_table ADD location varchar(256), algorithm=instant;
SET unique_checks = 1;
SET foreign_key_checks = 1;

그렇지 않으면 컬럼을 특정 위치에 배치해야 할 경우algorithm=inplace:

SET unique_checks = 0;
SET foreign_key_checks = 0;
ALTER TABLE main_table ADD location varchar(256) AFTER othercolumn, algorithm=inplace;
SET unique_checks = 1;
SET foreign_key_checks = 1;

참고로 inplace 알고리즘을 사용하여 2000만 행의 테이블을 변경하는 데 2분 정도 걸렸습니다.Workbench와 같은 프로그램을 사용하는 경우 작업을 시작하기 전에 설정에서 기본 시간 초과 기간을 늘릴 수 있습니다.

조작이 무기한으로 행하고 있는 것을 발견했을 경우는, 프로세스의 리스트를 참조해, 테이블에 잠겨 있는 프로세스를 모두 종료할 필요가 있습니다.이를 수행하려면 다음 명령을 사용합니다.

SHOW FULL PROCESSLIST;
KILL PROCESS_NUMBER_GOES_HERE;

변경 테이블은 사용자의 경우와 같이 빅데이터에 시간이 오래 걸리므로 이러한 상황에서는 사용하지 말고 다음과 같은 코드를 사용하십시오.

select main_table.*, 
  cast(null as varchar(256)) as null_location, -- any column you want accepts null
  cast('' as varchar(256)) as not_null_location, --any column doesn't accept null
  cast(0 as int) as not_null_int, -- int column doesn't accept null
into new_table 
from main_table;

drop table main_table;
rename table new_table TO main_table;

DB2 z/OS는 열을 가상으로 즉시 추가합니다.테이블을 Advisory-Reg 상태로 만듭니다.reorg 전에 실행되는 모든 것.기본값이 없는 경우 null이 됩니다.업데이트가 완료되면 업데이트된 행을 확장합니다.삽입이 확장됩니다.다음 reorg는 전개되지 않은 모든 행을 전개하고 전개되는 모든 행에 기본값을 할당합니다.

이것은 실제 데이터베이스만이 잘 처리합니다.DB2 z/OS.

언급URL : https://stackoverflow.com/questions/7599519/alter-table-add-column-takes-a-long-time