테이블을 잠그지 않고 테이블을 변경하시겠습니까?

테이블을 잠그지 않고 테이블을 변경하시겠습니까?

MySQL에서 ALTER TABLE 문을 실행하면 전체 테이블이 읽기 잠금(동시 읽기는 허용하지만 동시 쓰기는 금지)됩니다.테이블이 크면 INSERT 또는 UPDATE 문이 오랫동안 차단될 수 있습니다.테이블이 프로세스 전체에서 계속 업데이트되도록 열을 추가하는 등의 "핫 변경"을 수행하는 방법이 있습니까?

주로 MySQL을 위한 솔루션에 관심이 있지만 MySQL이 할 수 없다면 다른 RDBMS에 관심이 있습니다.

명확하게 설명하자면, 저의 목적은 추가 테이블 컬럼이 필요한 새로운 기능이 실제 가동 환경에 푸시될 때 다운타임을 방지하는 것입니다.모든 데이터베이스 스키마는 시간이 지남에 따라 변화합니다.그것은 생활의 일부일 뿐입니다.이러한 변경으로 인해 다운타임이 불가피하다는 것을 왜 받아들여야 하는지 모르겠습니다.그것은 약할 뿐입니다.

의 수를 입니다.MS " " " " "...
새 - 새 테이블 만들기 - 새 테이블 만들기

그런 다음 오래된 테이블의 내용을 한 번에 청크로 복사할 수 있습니다.소스 테이블의 INSERT/UPDATE/DELETE에 항상 주의합니다.(트리거에 의해 관리될 수 있습니다).이로 인해 속도가 느려지지만 잠금이 아닙니다...)

완료되면 원본 테이블의 이름을 변경한 다음 새 테이블의 이름을 변경합니다.거래에서 더 바람직하다.

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

편집:

이 제한이 다소 미흡하다는 지적이 있었습니다.그래서 왜 그런지 보여드리기 위해 새로운 시각을 두려고 했는데...

• 새 필드를 추가하는 것은 각 행에서 하나의 필드를 변경하는 것과 같습니다.
• 필드락은 테이블락은 신경 쓰지 말고 로우락보다 훨씬 어렵습니다.
  
  
• 실제로 디스크의 물리적 구조를 변경하면 모든 레코드가 이동합니다.
• 이것은 실제로 전체 테이블에 대한 업데이트와 비슷하지만 더 큰 영향을 미칩니다.

Percona는 이를 가능하게 하는 pt-online-schema-change라는 도구를 만듭니다.

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

이것은 Dems가 위에서 제안한 방법과 비슷하지만, 이것은 자동화된 방식으로 이루어집니다.

일부 툴은 데이터베이스 접속이라는 학습 곡선을 가지고 있지만, 일단 이 곡선을 이해하면 좋은 툴이 됩니다.

예:

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

이 질문은 2009년에 나온 것입니다.이제 MySQL은 다음과 같은 솔루션을 제공합니다.

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

DDL(주로 ALTER TABLE) 작업 중 InnoDB 테이블의 성능, 동시성 및 가용성을 향상시키는 기능입니다.자세한 내용은 섹션 14.11 "InnoDB 및 온라인 DDL"을 참조하십시오.

자세한 내용은 작업 유형에 따라 다릅니다.경우에 따라 ALTER TABLE 진행 중에 테이블을 동시에 수정할 수 있습니다.테이블 복사를 실행하거나 특별히 최적화된 유형의 테이블 복사를 사용하지 않고도 작업을 수행할 수 있습니다.공간 사용량은 innodb_online_alter_log_max_size 설정 옵션에 의해 제어됩니다.

이 기능을 사용하면 테이블에 대한 액세스를 완전히 차단할지(LOCK=SHARED 절), 쿼리는 허용하지만 DML은 허용하지 않을지(LOCK=SHARED 절), 테이블에 대한 전체 쿼리 및 DML 액세스를 허용할지(LOCK=NONE 절)를 선택하여 DDL 작업 중 성능과 동시성 간의 균형을 조정할 수 있습니다.LOCK 절을 생략하거나 LOCK=DEFAULT를 지정하면 MySQL은 작업 유형에 따라 가능한 한 많은 동시성을 허용합니다.

가능한 경우 테이블의 새 복사본을 만드는 대신 내부 변경을 수행하면 테이블 복사 및 보조 인덱스 재구성과 관련된 디스크 공간 사용 및 I/O 오버헤드가 일시적으로 증가하는 것을 방지할 수 있습니다.

자세한 내용은 MySQL 5.6 레퍼런스 매뉴얼 -> InnoDB 및 온라인 DDL을 참조하십시오.

마리아에서도 온라인 DDL을 이용할 수 있는 것 같습니다.DB

또는 ALTER ONLINE 탭을 사용할 수 있습니다.LE: ALTER TABLE이 동시 작업을 차단하지 않도록 합니다(잠금 없음).LOCK=NONE에 해당합니다.

Alter 탭에 대한 MariaDB KBLE

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

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

약자를 위한 것은 아니지만, 그것은 그 일을 할 수 있을 것이다.

Postgres를 추천합니다.포스트그림을 사용하면 다음 절차로 다운타임이 기본적으로 발생하지 않습니다.

• ALTER TABLE ADD Column(컬럼이 NULL일 수 있는 경우)
• 얼터 테이블 드롭 칼럼
• 인덱스 생성(CREATE Index를 동시에 사용해야 함)
• 드롭 인덱스

또 다른 뛰어난 기능은 대부분의 DDL 문이 트랜잭션이기 때문에 SQL 트랜잭션 내에서 전체 마이그레이션을 수행할 수 있으며, 문제가 발생하면 전체 이행을 롤백할 수 있다는 것입니다.

제가 조금 전에 쓴 글인데, 다른 장점들에 대해 좀 더 많은 통찰력을 줄 수 있을 것 같아요.

다른 데이터베이스에 대해 물어보셨으니 Oracle에 대한 몇 가지 정보를 알려드리겠습니다.

오라클 테이블에 NULL 열을 추가하는 것은 데이터 사전만 업데이트하므로 매우 빠른 작업입니다.이것은 매우 짧은 시간 동안 테이블을 독점적으로 잠근다.그러나 Dependant 저장 프로시저, 보기, 트리거 등이 비활성화됩니다.이것들은 자동적으로 재컴파일 됩니다.

여기서 필요에 따라 ONLINE 절을 사용하여 인덱스를 작성할 수 있습니다.다시 말씀드리지만, 매우 짧은 데이터 사전 잠금만 있습니다.인덱싱할 항목을 찾는 전체 테이블을 읽지만, 이 작업을 수행하는 동안 누구도 차단하지 않습니다.

외부 키를 추가해야 하는 경우 이를 통해 오라클이 데이터가 올바른지 신뢰하도록 할 수 있습니다.그렇지 않으면 테이블 전체를 읽고 속도가 느려질 수 있는 모든 값을 검증해야 합니다(먼저 인덱스를 만듭니다).

새 열의 각 행에 기본값 또는 계산된 값을 입력해야 하는 경우 대규모 업데이트 또는 새 데이터를 채우는 작은 유틸리티 프로그램을 실행해야 합니다.특히 행이 너무 커져서 블록에 들어가지 않는 경우 속도가 느려질 수 있습니다.이 프로세스 중에 잠금을 관리할 수 있습니다.아직 실행 중인 응용 프로그램의 이전 버전에서는 이 열을 인식하지 못하기 때문에 교활한 트리거 또는 기본값을 지정해야 할 수 있습니다.

거기서, 애플리케이션 서버상에서 새로운 버전의 코드로 전환하면, 계속 동작합니다.교활한 방아쇠 내려놔

또는 DBMS_REDEFINITION을 사용할 수도 있습니다.DBMS_REDEFINITION은 이런 종류의 작업을 수행하도록 설계된 블랙박스입니다.

이 모든 것을 테스트하는 것은 매우 번거로운 일이기 때문에 메이저 버전을 출시할 때마다 일요일 아침 일찍 중단이 발생합니다.

애플리케이션 업데이트를 수행할 때 데이터베이스 다운타임을 감당할 수 없는 경우 고가용성을 위해 2노드 클러스터를 유지하는 것이 좋습니다.간단한 레플리케이션 셋업으로 다음과 같은 거의 완전한 온라인 구조 변경을 실시할 수 있습니다.

• 모든 변경이 수동 슬레이브에 복제되기를 기다리다
• 패시브 슬레이브를 액티브 마스터로 변경하다
• 낡은 마스터의 구조 변경을 하다
• 변경 내용을 새 마스터에서 이전 마스터로 다시 복제
• 마스터 스와핑과 새 앱 배포를 동시에 다시 수행합니다.

항상 쉬운 것은 아니지만, 통상 다운타임이 0으로 동작합니다.두 번째 노드는 패시브노드만이 아니라 테스트, 통계 실행 또는 폴백노드로 사용할 수 있습니다.인프라스트럭처의 복제가 없는 경우는, 1대의 머신내에서 설정할 수 있습니다(MySQL 인스턴스 2개 사용).

아니요. MyISAM 테이블을 사용하는 경우 테이블 잠금만 가능합니다.레코드 잠금은 없습니다.단순함을 통해 모든 것을 고속으로 유지하려고 합니다.(다른 MySQL 테이블은 다르게 동작합니다.)어떤 경우에도 테이블을 다른 테이블로 복사하여 변경한 후 전환하여 차이를 업데이트할 수 있습니다.

이것은 매우 큰 변경이기 때문에 어떤 DBMS에서도 지원할 수 있을지 의문입니다.애초에 표에 있는 데이터를 사용하여 이를 수행할 수 있는 것이 이점으로 간주됩니다.

임시 해결 방법...

다른 해결 방법은 원래 테이블의 기본 키가 있는 다른 테이블을 새 열과 함께 추가하는 것입니다.

기본 키를 새 테이블에 입력하고 새 테이블의 새 열 값을 채웁니다. 또한 선택한 작업에 대해 이 테이블에 참여하도록 쿼리를 수정하고 이 열 값에 대해 별도로 삽입 및 업데이트해야 합니다.

다운타임이 발생하면 원래 테이블을 변경하거나 DML 쿼리를 수정하거나 이전에 작성한 새 테이블을 삭제할 수 있습니다.

또는 percona에서 클러스터링 방법, 레플리케이션, pt-online-schema 툴을 선택할 수 있습니다.

한 번 해 보세요.pt-online-schema-change이 툴을 사용하여 여러 슬레이브와 함께 AWS RDS로 이행하고 있으며, 매우 잘 동작하고 있습니다.저는 당신에게 도움이 될 수 있는 방법에 대한 정교한 블로그 글을 썼습니다.

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

Innodb 플러그인을 사용하면 보조 인덱스만 추가하거나 삭제하는 ALTER TABLE 문을 "빠르게" 실행할 수 있습니다. 즉, 테이블을 재구축하지 않아도 됩니다.

그러나 일반적으로 MySQL에서 ALTER TABLE은 테이블 전체를 재구축하는 데 시간이 오래 걸릴 수 있습니다(즉, 테이블에 유용한 양의 데이터가 포함되어 있는 경우).

ALTER TABLE 문을 정기적으로 실행할 필요가 없도록 애플리케이션을 설계해야 합니다.대기할 준비가 되어 있거나 작은 테이블을 변경할 준비가 되어 있지 않은 한 ALTER TABLE 문을 일반 실행 중에 실행하지 않도록 해야 합니다.

다음 두 가지 방법 중 하나를 권장합니다.

1. 잠재적인 변경 사항을 고려하여 데이터베이스 테이블을 설계합니다.예를 들어 콘텐츠의 데이터 필드를 정기적으로 변경하는 콘텐츠 관리 시스템과 함께 작업한 적이 있습니다.초기 CMS 필드 요건에 맞게 물리적 데이터베이스 구조를 구축하는 것이 아니라 유연한 구조로 구축하는 것이 훨씬 좋습니다.이 경우 blob 텍스트필드(varchar(max) 등)를 사용하여 유연한 XML 데이터를 유지합니다.이로 인해 구조 변경의 빈도가 매우 낮아집니다.구조 변경에는 비용이 많이 들 수 있으므로 비용 측면에서도 이점이 있습니다.

2. 시스템 유지 보수 시간이 있습니다.시스템은 변경 중(월별 등) 오프라인으로 전환되며, 변경은 하루 중 트래픽이 가장 적은 시간(오전 3~5시 등)에 예약됩니다.변경은 실가동 롤아웃 전에 스테이징되기 때문에 다운타임에 대한 충분한 추정 기간을 얻을 수 있습니다.

2a. 시스템 다운타임이 발생해도 사이트 전체가 다운되지 않도록 다중 서버를 사용합니다.이렇게 하면 사이트 전체를 다운시키지 않고 업데이트를 단계적으로 "롤아웃"할 수 있습니다.

옵션 2와 2a는 실현 가능하지 않을 수 있습니다.대규모 사이트/운영에만 해당됩니다.단, 이러한 옵션은 유효한 옵션이며, 여기에 제시된 모든 옵션을 개인적으로 사용해 왔습니다.

만약 누군가가 이것을 읽고 있거나 우연히 이곳에 온다면, 이것은 mongodb와 같은 NoSQL 데이터베이스 시스템을 사용하는 것의 큰 이점이다.수백만 행의 행과 쓰기 빈도가 높은 큰 테이블에서 추가 기능을 위한 열을 추가하거나 인덱스를 추가하도록 테이블을 변경할 때도 같은 문제가 있었습니다.장시간 잠기게 되므로 라이브 데이터베이스에서 이 작업을 수행하면 사용자가 불편을 겪을 수 있습니다.작은 테이블에서도 당신은 그것을 피할 수 있다.

저는 "테이블을 변경하지 않기 위해 테이블을 설계해야 한다"는 사실이 싫습니다.나는 그것이 오늘날의 웹사이트 세계에서는 통하지 않는다고 생각한다.다른 사람이 소프트웨어를 어떻게 사용할지 예측할 수 없기 때문에 사용자의 피드백에 따라 빠르게 변화합니다.mongodb를 사용하면 다운타임 없이 "columns"를 자유롭게 추가할 수 있습니다.실제로 추가하지도 않고 새 열로 데이터를 삽입하기만 하면 자동으로 추가됩니다.

www.mongodb.com에서 확인하실 수 있습니다.

일반적으로, 대답은 "아니오"가 될 것입니다.많은 갱신이 필요할 가능성이 있는 테이블의 구조를 바꾸고 있습니다."고고말말말말다다저는 전적으로 동의합니다.자주 있을 되시면 ' '더미' 컬럼을 VIEW" " " " " 대신"SELECT데이터를 입력한다.IIRC는 뷰의 정의를 변경하는 것이 비교적 가볍고 쿼리 계획이 컴파일될 때 뷰를 통한 간접 처리가 이루어집니다.은 새 .JOIN를 누릅니다.

물론 이것은 외부 키를 사용하여 삭제 등의 캐스케이드를 수행할 수 있는 경우에만 작동합니다.또 다른 장점은 클라이언트 사용을 방해하지 않고 데이터의 조합을 포함하는 새 테이블을 만들고 보기를 가리킬 수 있다는 것입니다.

생각일 뿐이야.

이 점에서 Postgres와 MySQL의 차이점은 Postgres에서는 테이블을 다시 만들지 않고 Oracle과 유사한 데이터 사전을 수정한다는 것입니다.따라서 동작은 고속이지만 다른 사용자가 설명한 바와 같이 매우 짧은 시간 동안 전용 DDL 테이블 잠금을 할당해야 합니다.

MySQL에서는 트랜잭션을 차단하면서 새로운 테이블에 데이터를 복사합니다. 이는 버전 5.6 이전 MySQL DBA에게 가장 큰 과제였습니다.

좋은 소식은 MySQL 5.6 출시 이후 제한이 대부분 해제되어 이제 MYSQL DB의 진정한 힘을 누릴 수 있다는 것입니다.

션 다우니가 언급했듯이pt-online-schema-change는, 이 질문에서 설명한 것을 실행하는데 있어서 최적인 툴의 1개입니다.최근에 라이브 DB에서 스키마 변경을 많이 했는데 잘 됐어요.자세한 것은, 이쪽의 블로그 투고를 참조해 주세요.http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/

더미 열은 유형을 예측할 수 있는 경우(및 null로 만들 수 있는 경우)에 적합합니다.스토리지 엔진이 Null을 처리하는 방식을 확인합니다.

MyISAM은 통과, 전화, 공항에서 테이블 이름까지 언급하면 모든 것을 잠급니다.그건 그냥...

단, 잠금은 그다지 중요하지 않습니다.모든 행에 새 열의 기본값을 추가하지 않고 늘로 유지하며 스토리지 엔진이 쓰기 작업을 수행할 수 없을 정도로 스마트한 상태라면 메타데이터를 업데이트할 수 있을 정도로만 잠금이 유지되어도 문제가 없습니다.만약 당신이 새로운 가치를 쓰려고 한다면, 음, 당신은 끝장입니다.

TokuDB는 열을 추가/폐기하고 인덱스를 "핫" 추가할 수 있으며, 이 테이블은 프로세스 전체에서 완전히 사용할 수 있습니다.www.tokutek.com 에서 구할 수 있습니다.

사실 그렇지 않아요.

결국 표의 기본 구조를 바꾸고 있습니다. 이는 기본 시스템에 매우 중요한 정보입니다.또, 대부분의 데이터를 디스크상에서 이동하고 있을 가능성이 있습니다.

이 작업을 많이 할 계획이라면 테이블을 나중에 사용할 수 있는 "더미" 열로 채우는 것이 좋습니다.

언급URL : https://stackoverflow.com/questions/463677/alter-table-without-locking-the-table