INSERT IN을 개선하는 방법...잠금 동작 선택

INSERT IN을 개선하는 방법...잠금 동작 선택

프로덕션 데이터베이스에서는 매시간 실행되는 다음과 같은 유사 코드 SQL 배치 쿼리를 실행했습니다.

INSERT INTO TemporaryTable
    (SELECT FROM HighlyContentiousTableInInnoDb
     WHERE allKindsOfComplexConditions are true)

만, 하고 것을 알 수 .HighlyContentiousTableInInnoDb그냥 읽었을 뿐인데.그 결과 다른 매우 단순한 쿼리에는 최대 25초(다른 쿼리에는 최대 25초)가 소요되었습니다.

그런 경우 InnoDB 테이블은 실제로 SELECT! https://www.percona.com/blog/2006/07/12/insert-into-select-performance-with-innodb-tables/에 의해 잠겨 있다는 것을 알게 되었습니다.

하지만 OUTFILE을 선택하는 기사의 솔루션은 별로 마음에 들지 않습니다.이것은 해킹처럼 보입니다(파일 시스템의 임시 파일은 형편없어 보입니다).른른른른 른른른?InnoDB 테이블 전체를 이렇게 잠그지 않고 복사할 수 있는 방법이 있나요?그럼 그냥 복사해서HighlyContentiousTable다른 테이블로 이동하여 쿼리를 수행합니다.

이제 이 질문에 대한 답변이 훨씬 쉬워졌습니다. - 행 기반 복제 및 읽기 커밋 분리 수준 사용.

발생한 잠금이 사라집니다.

상세설명 : http://harrison-fisk.blogspot.com/2009/02/my-favorite-new-feature-of-mysql-51.html

다음과 같이 binlog 형식을 설정할 수 있습니다.

SET GLOBAL binlog_format = 'ROW';

영구적인 경우 my.cnf를 편집합니다.

[mysqld]
binlog_format=ROW

쿼리를 실행하기 전에 현재 세션의 분리 수준을 설정합니다.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
INSERT INTO t1 SELECT ....;

이렇게 해도 문제가 해결되지 않으면 현재 세션뿐만 아니라 격리 수준 서버 전체로 설정해야 합니다.

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

영구적인 경우 my.cnf를 편집합니다.

[mysqld]
transaction-isolation = READ-UNCOMMITTED

READ-UNCOMMITED를 더 나은 분리 수준인 READ-COMMITED로 변경할 수 있습니다.

면책사항:저는 데이터베이스에 대해 잘 모르기 때문에 이 아이디어가 실행 가능한지 잘 모르겠습니다.아니라면 정정해 주세요.

테이블 「」을하는 것은 ?HighlyContentiousTableInInnoDb2및, " " "AFTER INSERTetc는 하는 첫 번째 테이블에서 .etc " " " " 。 " " triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers triggers 잠글 수 .HighlyContentiousTableInInnoDb2모든 쿼리가 아닌 프라이머리 테이블의 트리거만 느려집니다.

잠재적인 문제:

• 2 x 데이터 저장
• 모든 삽입, 업데이트 및 삭제를 위한 추가 작업
• 트랜잭션적으로 건전하지 않을 수 있습니다.

일부 이상을 허용할 수 있는 경우 분리 수준을 가장 엄격한 분리 수준인 읽기 커밋되지 않음으로 변경할 수 있습니다.그러나 이 시간 동안 누군가가 당신의 행선지 테이블에서 읽을 수 있습니다.또는 수동으로 행선지 테이블을 잠글 수도 있습니다(mysql이 이 기능을 제공하고 있다고 생각됩니다).

또는 소스 테이블을 잠그지 않는 READ COMMITED를 사용할 수도 있습니다.그러나 커밋될 때까지 대상 테이블에 삽입된 행도 잠급니다.

저는 두 번째 걸 고를 거예요.

잠금(readlock)을 사용하는 이유는 병렬 트랜잭션이 현재 쓰고 있을 수 있는 "더러운" 데이터를 읽지 않도록 읽기 트랜잭션을 보호하기 위함입니다.대부분의 DBMS는 사용자가 수동으로 읽기 및 쓰기 잠금을 설정 및 해제할 수 있는 설정을 제공합니다.지저분한 데이터를 읽는 것이 문제가 되지 않는다면 이 방법은 흥미로울 수 있습니다.

복수의 트랜잭션이 있는 DBS에서 잠기지 않고 테이블에서 읽을 수 있는 안전한 방법은 없다고 생각합니다.

그러나 다음은 브레인스토밍입니다.공간에 문제가 없다면 같은 테이블의 두 인스턴스를 실행하는 것을 고려해 볼 수 있습니다. HighlyContentiousTableInInnoDb2및 "읽기/쓰기 트랜잭션"에 대해 합니다.HighlyContentiousTableInInnoDb2_shadow을 사용하다DBMS 내에서 트리거/루틴을 통해 섀도 테이블을 자동으로 채울 수 있습니다. 이렇게 하면 어디에서나 추가 쓰기 트랜잭션을 수행하는 것보다 더 빠르고 스마트할 수 있습니다.

또 다른 아이디어는 모든 트랜잭션이 테이블 전체에 액세스해야 하는지 여부입니다.그렇지 않으면 뷰를 사용하여 필요한 열만 잠글 수 있습니다.컬럼에 대해 연속 액세스와 일괄 처리가 분리된 경우 서로 잠기지 않을 수 있습니다!

Create View 명령을 사용할 수 있습니다(Create View 구문 참조).예를들면,

Create View temp as SELECT FROM HighlyContentiousTableInInnoDb WHERE allKindsOfComplexConditions are true

그런 다음 이 보기에서 삽입문을 사용할 수 있습니다.뭐 이런 거

INSERT INTO TemporaryTable (SELECT * FROM temp)

이건 제 제안일 뿐이에요.

익숙하지 MySQL과 .Snapshot ★★★★★★★★★★★★★★★★★」Read committed snapshotSQL Server를 사용합니다.이것들 중 하나를 사용하면 문제가 해결될 것입니다.

도 같은 .CREATE TEMPORARY TABLE ... SELECT ...SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction.

문의하신 내용에 , 제 는 신의 the the the the the, the the the the the the the the the the the the the the the the the the the the the the the the the the the the the the the the 를 잠그는 것으로 되었습니다.HighlyContentiousTableInInnoDb를 클릭합니다.

LOCK TABLES HighlyContentiousTableInInnoDb READ;
INSERT INTO TemporaryTable
    (SELECT FROM HighlyContentiousTableInInnoDb
    WHERE allKindsOfComplexConditions are true)
UNLOCK TABLES;

언급URL : https://stackoverflow.com/questions/2640898/how-to-improve-insert-into-select-locking-behavior