MySQL에서 큰 테이블에 열을 추가하는 방법 큰 테이블 ~ 5.5GB

저는 PHP 개발자이므로 엄격하지는 않습니다. 큰 테이블 ~ 5.5GB 덤프가 있습니다. PM은 새로운 기능을 수행하기 위해 새로운 칼럼을 만들기로 결정했습니다. 테이블은 InnoDB이므로 내가 시도한 것 :

  1. 테이블 잠금 화면에서 테이블을 변경하십시오. ~ 30 시간도 걸리지 않았습니다. 그래서 나는 그것을 막았다. 모든 거래를 끝내지는 않았지만 두 번째 시간은 멀티 락이 아니기 때문에 실수했습니다. 상태는 copy to tmp table입니다.

  2. 이 테이블에 대해서도 파티셔닝을 적용해야하므로 동일한 이름과 새 구조로 덤프, 이름 바꾸기 및 테이블을 작성하기로 결정했습니다. 그러나 덤프는 엄격한 사본을 만들고 있습니다 (적어도 다른 것을 찾지 못했습니다). 그래서 새 열을 덤프 sed하고 쿼리하기 위해 추가 했습니다. 그러나 몇 가지 이상한 오류가 시작되었습니다. 나는 이것이 문자셋에 의한 것이라고 생각합니다. utf-8의 테이블과 파일은 이후 us-ascii가되었습니다 sed. 그래서 30 %의 데이터에 오류 (알 수없는 명령 ‘\’ ‘)가 있습니다. 따라서 이것은 또한 나쁜 방법입니다.

이것을 달성하고 성능을 향상시키는 다른 옵션은 무엇입니까 (php 스크립트로 할 수는 있지만 시간이 오래 걸립니다). INSERT SELECT이 경우 성능은 무엇입니까?

미리 감사드립니다.

답변

MySQL Workbench를 사용하십시오 . 테이블을 마우스 오른쪽 단추로 클릭하고 “Send to SQL Editor”-> “Create Statement”를 선택할 수 있습니다. 이렇게하면 “속성”테이블을 추가 CHARSET하거나 잊어 버리지 않습니다 ( 또는 포함 COLLATE).
이 방대한 양의 데이터를 사용하면 사용중인 테이블 또는 데이터 구조를 정리하는 것이 좋습니다 (좋은 DBA가 편리함). 가능하지 않은 경우 :

  • 테이블 이름을 바꾸고 ( ALTER) CREATEWorkbench에서 가져온 스크립트를 사용하여 새 테이블을 작성하십시오 . 필요한 새 필드를 사용하여 해당 쿼리를 확장 할 수도 있습니다
  • 이전 테이블의 데이터를 새 테이블로 대량로드 : 
    SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
INSERT INTO new_table (fieldA, fieldB, fieldC, ..., fieldN)
   SELECT fieldA, fieldB, fieldC, ..., fieldN
   FROM old_table
SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

    이렇게하면 레코드별로 레코드를 실행하기 위해 색인 작성 등을 피할 수 있습니다. 테이블의 “업데이트”는 여전히 느리지 만 (데이터 양이 많기 때문에) 이것이 내가 생각할 수있는 가장 빠른 방법입니다.

    편집 : 위의 샘플 쿼리에 사용 된 명령에 대한 자세한 내용을 보려면 이 기사를 읽으십시오 .)

답변

sed 아이디어는 괜찮은 방법이지만 오류나 실행 한 명령이 없으면 도움을 줄 수 없습니다.

그러나 큰 테이블을 온라인으로 변경하는 잘 알려진 방법은 pt-online-schema-change 입니다. 이 도구의 기능에 대한 간단한 간과는 설명서에서 복사됩니다.

pt-online-schema-change는 테이블의 빈 복사본을 만들어 원하는대로 수정 한 다음 원래 테이블의 행을 새 테이블로 복사하여 작동합니다. 복사가 완료되면 원래 테이블이 사라지고 새 테이블로 바뀝니다. 기본적으로 원본 테이블도 삭제됩니다.

이 방법은 완료하는 데 다소 시간이 걸리지 만 프로세스 중에 원본 테이블을 완전히 사용할 수 있습니다.

답변

alter table add column, algorithm=inplace, lock=none 테이블을 복사하지 않고 잠금 영향을 미치지 않고 MySQL 5.6 테이블을 변경합니다.

방금 어제 테스트 한 결과, 280K 행 7 파티션 테이블에 70K 행을, 각 파티션에 10K 행을 삽입했으며, 다른 처리량을 허용하기 위해 5 초 동안 대기했습니다.

대량 삽입을 시작한 다음 별도의 세션에서 alter위의 MySQL Workbench에서 온라인 명령문을 시작하고 alter삽입 전에 완료하고 두 개의 새로운 열이 추가되었으며 변경으로 인해 행이 생성되지 않았으며 MySQL은 행을 복사하지 않았습니다.

답변

현재 거대한 테이블을 변경하는 가장 좋은 옵션은 아마도 https://github.com/github/gh-ost입니다.

gh-ost는 MySQL을위한 트리거없는 온라인 스키마 마이그레이션 솔루션입니다. 테스트 가능하며 일시 정지성, 동적 제어 / 재구성, 감사 및 많은 운영상의 이점을 제공합니다.

gh-ost는 마이그레이션 동안 테이블에서 기존 작업 부하와 분리 된 채로 마이그레이션 전체에서 마스터에 대한 가벼운 작업 부하를 생성합니다.

기존 솔루션에 대한 수년간의 경험을 바탕으로 설계되었으며 테이블 마이그레이션의 패러다임을 변경합니다.

답변

Mydumper / Myloader 는 다음과 같은 작업에 유용한 도구 라고 생각 합니다. 매일 향상되고 있습니다. CPU를 활용하고 병렬로 데이터를로드 할 수 있습니다 : http://www.percona.com/blog/2014/03/10/new-mydumper-0-6-1-release-offers-several-performance-and- 유용성 특징 /

몇 시간에 수백 기가 바이트 MySQL 테이블을로드했습니다.

이 새로운 열을 추가에 관해서 이제, 전체 테이블은 메모리에 걸쳐 MySQL의 사본으로 까다 롭습니다 TMP와 지역 ALTER TABLE...없는 잠금 대규모 테이블에 대한 온라인으로 할 MySQL을 5.6는 온라인 스키마 변경을 할 수 있다고하지만, 내가 관리하지 않은 아직 경합.

답변

방금 같은 문제가있었습니다. 약간의 해결 방법 :

CREATE TABLE new_table SELECT * FROM oldtable;

new_table에서 삭제

ALTER TABLE new_table ADD COLUMN new_column int (11);

new_table에 삽입 *, old_table에서 0을 선택하십시오.

테이블 삭제 old_table; 테이블 이름을 new_table에서 old_table로 바꾸십시오.

답변