[SQL] 테이블을 잠그지 않고 ALTER 표?

테이블을 잠그지 않고 ALTER 표?

MySQL의에서 ALTER TABLE 문을 수행 할 때, 전체 테이블은 명령문의 기간 동안 (동시 읽기 수 있지만, 금지 동시 쓰기)를 읽기 잠겨 있습니다. 이 큰 테이블의 경우, INSERT 또는 UPDATE 문은 looooong 시간 동안 차단 될 수 있습니다. 테이블이 여전히 과정 전반에 걸쳐 업데이트 할 것을 같은 방식의 열을 추가하는 등, "핫 ALTER"를 할 수있는 방법이 있나요?

대부분 내가 MySQL을위한 솔루션에 관심이 있지만, MySQL이 그것을 할 수 없다면 나는 다른 RDBMS에 관심이있을 것입니다.

별도의 테이블 컬럼을 필요로하는 새로운 기능 생산에 푸시 할 때 명확히하기 위해, 내 목적은 피하기 중단하는 것입니다. 모든 데이터베이스 스키마는 삶의 단순한 사실이, 시간이 지남에 변경됩니다. 우리는 이러한 변화가 불가피 다운 타임 발생해야한다는 것을 받아 들여야하는 이유는 표시되지 않습니다; 그건 그냥 약한.

해결법

1. ==============================

   1.유일한 다른 옵션은 많은 RDBMS 시스템 어쨌든 일을 직접하는 것입니다 ... - 새 테이블을 만듭니다

   유일한 다른 옵션은 많은 RDBMS 시스템 어쨌든 일을 직접하는 것입니다 ... - 새 테이블을 만듭니다

   그런 다음 한 번에 체크를 통해 이전 테이블의 내용을 복사 할 수 있습니다. 항상 INSERT / UPDATE의 신중 동안 / 소스 테이블에 삭제합니다. (트리거에 의해 관리 될 수 있습니다.이 느린 다운 원인이 있지만,이 잠금 아니다 ...)

   완료되면, 새로운 테이블의 이름을 변경, 소스 테이블의 이름을 변경합니다. 바람직하게 거래한다.

   완성되면, 해당 테이블을 사용하는 저장 프로 시저 등을 컴파일. 실행 계획은 가능성이 더 이상 유효하지 않습니다.

   편집하다:

   일부 의견이 제한 비트 가난에 대해되었습니다. 그래서 나는 그것이 얼마나 이유를 보여주기 위해 그 위에 새로운 관점을 넣어 줄 알았는데 ...

2. ==============================

   2.Percona는이 작업을 수행 할 수 있도록 PT-온라인 스키마 변화라는 도구를 만든다.

   Percona는이 작업을 수행 할 수 있도록 PT-온라인 스키마 변화라는 도구를 만든다.

   그것은 본질적으로 테이블의 복사본을 만들고 새 테이블을 수정합니다. 이 업데이트 트리거를 사용하여 원본과 동기화 새 테이블을 유지합니다. 이 새로운 테이블이 백그라운드에서 준비되는 동안 원래의 테이블에 액세스 할 수 있습니다.

   이것은 위의 방법을 제안 민주당 원과 유사하지만,이 자동화 된 방식으로 그렇게한다.

   자신의 도구 중 일부는 즉 데이터베이스에 연결, 학습 곡선을 가지고 있지만, 그 아래가되면, 그들이 가지고있는 훌륭한 도구입니다.

   전의:

   pt-online-schema-change --alter "ADD COLUMN c1 INT" D=db,t=numbers_are_friends
   

3. ==============================

   3.2009 년 지금의 MySQL에서이 질문은 솔루션을 제공합니다 :

   2009 년 지금의 MySQL에서이 질문은 솔루션을 제공합니다 :

   온라인 DDL (데이터 정의 언어)

   그것은 당신이 쿼리가 아니라 DML (LOCK = SHARED 절), 또는 허용 전체 쿼리 및 DML을 완전히 테이블 (LOCK = EXCLUSIVE 절)에 대한 액세스를 차단할지 여부를 선택함으로써 DDL 작업을 수행하는 동안 성능과 동시성 사이의 균형을 조정할 수 있습니다 테이블 (LOCK = NONE 절)에 액세스 할 수 있습니다. 당신이 LOCK 절을 생략하거나 LOCK = DEFAULT를 지정하면, MySQL은 작업의 종류에 따라 가능한 한 많은 동시로 할 수 있습니다.

   가능하면 오히려 테이블의 새 복사본을 만드는 대신 현재 위치에서 변경을 수행, 디스크 공간 사용 및 I 일시적인 증가를 피할 수 / O 오버 헤드 테이블을 복사하고 보조 인덱스를 재구성과 관련.

   MySQL의 5.6 참조 설명서 참조 -> 이노 더 많은 정보를 원하시면 온라인 DDL.

   그것은 MariaDB로도 주문 가능합니다 그 온라인 DDL을 보인다

   ALTER 표에 대한 MariaDB KB

4. ==============================

   4.페이스 북의 온라인 스키마 변경 도구를 참조하십시오.

   페이스 북의 온라인 스키마 변경 도구를 참조하십시오.

   http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932

   적합하지 않음; 하지만 일을 할 것입니다.

5. ==============================

   5.그 옵션이 있다면 나는 포스트 그레스를 추천합니다. 포스트 그레스와 함께 다음과 같은 절차에 다운 타임은 기본적으로 없다 :

   그 옵션이 있다면 나는 포스트 그레스를 추천합니다. 포스트 그레스와 함께 다음과 같은 절차에 다운 타임은 기본적으로 없다 :

   당신은 SQL 트랜잭션 내에서 전체 마이그레이션을 할 수 있도록 다른 큰 특징은 대부분의 DDL 문이 트랜잭션 있다는 것입니다, 뭔가가 잘못되면 전체 일이 롤백됩니다.

   나는 아마도 다른 장점에 좀 더 통찰력을 흘릴 수있는, 조금 전에이를 썼다.

6. ==============================

   6.당신이 다른 데이터베이스에 대해 질문 때문에, 여기에 오라클에 대한 몇 가지 정보입니다.

   당신이 다른 데이터베이스에 대해 질문 때문에, 여기에 오라클에 대한 몇 가지 정보입니다.

   그것은 단지 데이터 사전을 업데이트로 Oracle 테이블에 NULL 열을 추가하는 것은 매우 빠른 작업입니다. 이 시간이 매우 짧은 기간 동안 테이블에서 독점 잠금을 보유하고 있습니다. 그것은 그러나, 어떤 depedant에게이 자동으로 컴파일 얻을 것이다 등 저장 프로 시저, 뷰, 트리거, 무효화됩니다.

   거기에서 당신은 온라인 절을 사용하여 인덱스를 생성 할 수 있습니다 필요한 경우. 다시 말하지만, 매우 짧은 데이터 잠금을 사전. 이 인덱스에 일을 찾고 전체 테이블을 읽을 수 있습니다 만,이 일을하면서 누군가를 차단하지 않습니다.

   당신이 외부 키를 추가해야하는 경우, 당신은이 작업을 수행하고 오라클이 한 사항이 정확한지 당신을 신뢰 할 수 있습니다. 그렇지 않으면 전체 테이블을 읽고 (먼저 인덱스를 생성) 속도가 느려질 수 있습니다 모든 값을 검증 할 필요가있다.

   새 열의 모든 행에 기본 또는 계산 된 값을 넣어해야하는 경우 대규모 업데이트 나 새 데이터를 채 웁니다 아마도 약간의 유틸리티 프로그램을 실행해야합니다. 이 행이 자신의 블록에 많이 더 크고 더 이상 적합을 얻을 특히, 속도가 느려질 수 있습니다. 잠금이 과정에서 관리 할 수 ​​있습니다. 아직 실행중인 응용 프로그램의 이전 versino는,이 칼럼에 대해 알고하지 않기 때문에 당신은 비열한 트리거를해야 할 수도 있습니다 또는 기본값을 지정할 수 있습니다.

   여기에서, 당신은 코드의 새로운 버전으로 응용 프로그램 서버에 switcharoo을 할 수 있으며 실행하겠습니다. 당신의 비열한 트리거를 삭제합니다.

   양자 택일로, 당신은 이런 종류의 작업을 수행하기위한 블랙 박스입니다 DBMS_REDEFINITION을 사용할 수 있습니다.

   이 모든 것은 순전히 우리는 단지 우리가 주요 버전을 출시 할 때마다 일요일 아침 일찍 중단이 있는지 등, 테스트를 귀찮게한다.

7. ==============================

   7.응용 프로그램 업데이트를 할 때 당신은 당신의 데이터베이스에 대한 다운 타임 감당할 수없는 경우에 당신은 고 가용성을 위해 두 개의 노드 클러스터를 유지하는 것이 좋습니다. 간단한 복제 설정, 당신은 당신이 제안하는 것과 같은 거의 완벽하게 온라인으로 구조 변경을 할 수 있습니다 :

   응용 프로그램 업데이트를 할 때 당신은 당신의 데이터베이스에 대한 다운 타임 감당할 수없는 경우에 당신은 고 가용성을 위해 두 개의 노드 클러스터를 유지하는 것이 좋습니다. 간단한 복제 설정, 당신은 당신이 제안하는 것과 같은 거의 완벽하게 온라인으로 구조 변경을 할 수 있습니다 :

   그것은 항상 쉬운 일이 아닙니다하지만 0 다운 타임 일반적으로, 작품! 두 번째 노드는 수동 하나가 될 필요가 없습니다, 그것은 통계를 테스트하고 또는 대체 노드로 사용할 수 있습니다. 당신이없는 경우 인프라 복제 (MySQL의 두 인스턴스) 단일 시스템 내에서 설정할 수 있습니다.

8. ==============================

   8.아니. 단순 통해 모든 것을 hyperfast을 유지하기 위해 더 레코드 잠금이 없다, 그들은 단지 시도 - 당신은 나의 가장 친한 그들은 단지 테이블 잠금을 이해하는, MyISAM 테이블을 사용하는 경우. (다른 MySQL의 테이블이 다르게 작동합니다.) 어떤 경우에, 당신은 다른 테이블에 테이블을 복사를 변경하고 차이를 업데이트를 전환 할 수 있습니다.

   아니. 단순 통해 모든 것을 hyperfast을 유지하기 위해 더 레코드 잠금이 없다, 그들은 단지 시도 - 당신은 나의 가장 친한 그들은 단지 테이블 잠금을 이해하는, MyISAM 테이블을 사용하는 경우. (다른 MySQL의 테이블이 다르게 작동합니다.) 어떤 경우에, 당신은 다른 테이블에 테이블을 복사를 변경하고 차이를 업데이트를 전환 할 수 있습니다.

   이것은 내가 어떤 DBMS가 그것을 지원하는 것이 의심 것과 같은 엄청난 변화입니다. 이익이 첫번째 장소에있는 테이블의 데이터와 함께 할 수 있도록 간주됩니다.

9. ==============================

   9.임시 솔루션 ...

   임시 솔루션 ...

   다른 솔루션은 새로운 열의와 함께, 원래 테이블의 기본 키와 다른 테이블을 추가 할 수 있습니다.

   새 테이블에 새 열을위한 새로운 테이블 채우기 값에 기본 키를 채우고, 선택 작업이 테이블에 가입 쿼리를 수정하고 또한이 열 값에 대해 개별적으로 삽입, 업데이트 할 필요가있다.

   당신은 할 수 다운 타임을 얻을 때, 당신은, 원래의 테이블을 변경하여 DML 쿼리를 수정하고 새 테이블 앞에서 만든 놓을 수 있습니다

   그 밖에, 당신은 percona에서 방법, 복제, PT-온라인 스키마 도구를 클러스터링 갈 수있다

10. ==============================

    10.이노 디비 플러그인을 사용하여 만 추가하거나 보조 인덱스를 삭제 ALTER 표 문은 테이블을 다시 작성하지 않고, "빨리"즉를 수행 할 수 있습니다.

    이노 디비 플러그인을 사용하여 만 추가하거나 보조 인덱스를 삭제 ALTER 표 문은 테이블을 다시 작성하지 않고, "빨리"즉를 수행 할 수 있습니다.

    일반적으로 MySQL의에서, 그러나 말하기, 어떤 ALTER TABLE은 매우 시간이 오래 걸릴 수 있습니다 전체 테이블을 재건 포함 (즉, 테이블이 데이터의 유용한 금액이있는 경우).

    당신은 정말 ALTER 표 문을 정기적으로 수행 할 필요가 없습니다 그래서 응용 프로그램을 설계 할 필요가; 당신은 확실히 당신이 기다릴 수 있도록 준비하고 또는 작은 테이블을 변경하지 않는 한 어떤 ALTER 표는 응용 프로그램의 정상적인 실행하는 동안 수행하지 않습니다.

11. ==============================

    11.나는 두 가지 방법 중 하나를 추천 할 것입니다 :

    나는 두 가지 방법 중 하나를 추천 할 것입니다 :

    2A. 시스템이 다운 타임이있는 경우, 전체 사이트가 다운되지 않습니다 그래서, 중복 서버가 있습니다. 이는 전체 사이트 다운을 복용하지 않고, 지그재그 방식에서 당신의 업데이트 "롤"당신을 허용합니다.

    옵션 2 및도 2a는 가능하지 않을 수 있습니다; 그들은 단지 더 큰 사이트 / 작업 경향이있다. 그러나 그들은 유효한 옵션, 그리고 나는 개인적으로 여기에 제시된 모든 옵션을 사용했습니다.

12. ==============================

    12.사람이 여전히이 글을 읽고 나 여기 와서 발생하는 경우,이 MongoDB를 같이되는 NoSQL 데이터베이스 시스템을 사용의 가장 큰 장점이다. 나는 행과 높은 쓰기 수백만 큰 테이블에 추가 기능 또는 인덱스 중 하나를 추가 컬럼에 테이블을 변경 다루는 같은 문제가 있었다. 라이브 데이터베이스에서이 작업을 수행하는 사용자를 좌절 것입니다, 그래서 그것은 아주 긴 시간 동안 잠금 끝날 것입니다. 작은 테이블에 당신은 그것을 멀리 얻을 수 있습니다.

    사람이 여전히이 글을 읽고 나 여기 와서 발생하는 경우,이 MongoDB를 같이되는 NoSQL 데이터베이스 시스템을 사용의 가장 큰 장점이다. 나는 행과 높은 쓰기 수백만 큰 테이블에 추가 기능 또는 인덱스 중 하나를 추가 컬럼에 테이블을 변경 다루는 같은 문제가 있었다. 라이브 데이터베이스에서이 작업을 수행하는 사용자를 좌절 것입니다, 그래서 그것은 아주 긴 시간 동안 잠금 끝날 것입니다. 작은 테이블에 당신은 그것을 멀리 얻을 수 있습니다.

    나는 우리가 "을 변경하지 않도록 우리의 테이블을 디자인"해야한다는 사실을 싫어. 난 그냥 오늘의 웹 사이트 세계에서 그 일을 생각하지 않습니다. 당신은 사람들의 이유는 빠른 속도로 사용자의 피드백을 기반으로 일을 변경하는 것이 소프트웨어를 사용하는 방법을 예측할 수 없습니다. MongoDB를 사용하면 다운 타임없이 마음대로 "열"을 추가 할 수 있습니다. 당신은 정말도 추가하지 않습니다, 당신은 단지 새로운 열 데이터를 삽입하면 자동으로 수행합니다.

    가치 체크 아웃 : www.mongodb.com

13. ==============================

    13.일반적으로, 대답은 "아니오"가 될 것입니다. 당신은 잠재적으로 업데이트를 많이 필요합니다 테이블의 구조를 변경하고 더미 "열"과 나는 확실히 그 동의를 자주이 일을 기대한다면, 나는에 대한 대안을 제공 할 것입니다. "- 사용 뷰를 대신 데이터 선택. IIRC의 테이블, 뷰의 정의를 변경하는 것은 상대적으로 가볍고 쿼리 계획이 컴파일 될 때 뷰를 통해 간접 수행됩니다. 경비는 새 테이블에 열을 추가하고 만들어야 할 것입니다 뷰는 항목에 참여.

    일반적으로, 대답은 "아니오"가 될 것입니다. 당신은 잠재적으로 업데이트를 많이 필요합니다 테이블의 구조를 변경하고 더미 "열"과 나는 확실히 그 동의를 자주이 일을 기대한다면, 나는에 대한 대안을 제공 할 것입니다. "- 사용 뷰를 대신 데이터 선택. IIRC의 테이블, 뷰의 정의를 변경하는 것은 상대적으로 가볍고 쿼리 계획이 컴파일 될 때 뷰를 통해 간접 수행됩니다. 경비는 새 테이블에 열을 추가하고 만들어야 할 것입니다 뷰는 항목에 참여.

    당신이 삭제하고 이것 저것의 계단식 수행하는 외래 키를 사용할 수있는 경우 중은 물론이 경우에만 작동합니다. 다른 보너스는 데이터의 조합을 포함하는 새로운 테이블을 생성하고 클라이언트 사용을 방해하지 않고 그것에보기를 가리킬 수 있다는 것입니다.

    그냥 생각했다.

14. ==============================

    14.이 점에서 포스트 그레스와 MySQL 차이는 재 작성에 포스트 그레스가 없다는 것이다 테이블 있지만, 수정 데이터는 오라클 유사하다 사전. 여전히 다른 사람 위에 언급 한 바와 같이 매우 짧은 시간 동안 독점 DDL 테이블 잠금을 할당하는 데 필요한 것 동안 따라서, 동작, 빠릅니다.

    이 점에서 포스트 그레스와 MySQL 차이는 재 작성에 포스트 그레스가 없다는 것이다 테이블 있지만, 수정 데이터는 오라클 유사하다 사전. 여전히 다른 사람 위에 언급 한 바와 같이 매우 짧은 시간 동안 독점 DDL 테이블 잠금을 할당하는 데 필요한 것 동안 따라서, 동작, 빠릅니다.

    V. 5.6 이전의 MySQL DBA를위한 주요 고통이었다 트랜잭션을 차단하면서 MySQL의에서 작업이 새 테이블에 데이터를 복사합니다.

    좋은 소식은 MySQL의 5.6 릴리스 이후 제한은 대부분 해제되었으며 이제 MYSQL DB의 진정한 힘을 즐길 수 있다는 것입니다.

15. ==============================

    15.SeanDowney가 언급 한 바와 같이, PT-온라인 스키마 변경 사항은 여기에 질문에 설명 된 것을 할 수있는 가장 좋은 도구 중 하나입니다. 최근 라이브 DB에서 스키마 변경을 많이했고, 꽤 잘 갔다. http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/ : 당신은 여기 내 블로그 게시물에 대한 자세한 내용을보실 수 있습니다.

    SeanDowney가 언급 한 바와 같이, PT-온라인 스키마 변경 사항은 여기에 질문에 설명 된 것을 할 수있는 가장 좋은 도구 중 하나입니다. 최근 라이브 DB에서 스키마 변경을 많이했고, 꽤 잘 갔다. http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/ : 당신은 여기 내 블로그 게시물에 대한 자세한 내용을보실 수 있습니다.

16. ==============================

    16.당신은 확실히 PT-온라인 스키마 변화를 시도해야합니다. 나는 여러 노예와 AWS RDS에 대한 마이그레이션을 수행하기 위해이 도구를 사용하고 그것은 아주 잘 날 위해 일했다. 나는 당신을 위해 도움이 될 수있는 그 작업을 수행하는 방법에 대한 정교한 블로그 포스트를 작성했습니다.

    당신은 확실히 PT-온라인 스키마 변화를 시도해야합니다. 나는 여러 노예와 AWS RDS에 대한 마이그레이션을 수행하기 위해이 도구를 사용하고 그것은 아주 잘 날 위해 일했다. 나는 당신을 위해 도움이 될 수있는 그 작업을 수행하는 방법에 대한 정교한 블로그 포스트를 작성했습니다.

    블로그 : http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/

17. ==============================

    17.더미 열은 자신의 유형을 예측할 수 있다면 좋은 아이디어이다 (그들은 널 (NULL) 확인). 스토리지 엔진이 널 (null)을 처리하는 방법을 확인하십시오.

    더미 열은 자신의 유형을 예측할 수 있다면 좋은 아이디어이다 (그들은 널 (NULL) 확인). 스토리지 엔진이 널 (null)을 처리하는 방법을 확인하십시오.

    당신도 공항에서, 전화, 통과에 테이블 이름을 언급하는 경우의 MyISAM은 모든 고정됩니다. 그것은 단지를하지 ...

    그 존재는 잠금이 정말 큰 계약이 아닌, 단계; 만큼 당신이 모든 행에 새 열의 기본 값을 추가하려고하지만 널 (null)로 앉아 보자, 그리고 스토리지 엔진이 스마트 충분히 그것을 쓰는 이동하지 않는 것입니다하지 않는 한, 당신은 단지 인 잠금 확인을해야 충분히 메타 데이터를 업데이트하기 위해 개최했다. 새 값을 작성하려고 할 경우, 음, 당신은 토스트입니다.

18. ==============================

    18.TokuDB 추가 / 열을 삭제하고 "핫"인덱스를 추가 할 수 있습니다, 테이블은 과정 전반에 걸쳐 완벽하게 사용할 수 있습니다. 그것은 www.tokutek.com를 통해 볼 수 있습니다

    TokuDB 추가 / 열을 삭제하고 "핫"인덱스를 추가 할 수 있습니다, 테이블은 과정 전반에 걸쳐 완벽하게 사용할 수 있습니다. 그것은 www.tokutek.com를 통해 볼 수 있습니다

19. ==============================

    19.정말.

    정말.

    당신은 결국, 테이블의 기본 구조를 변경하고, 그 기본이되는 시스템에 매우 중요한 정보의 비트입니다. 당신은 디스크에 주위에 많은 데이터의 이동 (가능성)도있어.

    이 많은 일을 계획하는 경우, 당신은 더 나은 오프 단순히 미래를 사용할 수 있습니다 "더미"열이 테이블 패딩입니다.

from https://stackoverflow.com/questions/463677/alter-table-without-locking-the-table by cc-by-sa and MIT license