MySQL 5.6 한글메뉴얼

14.6.5 InnoDB에서 AUTO_INCREMENT 처리

InnoDB 는 AUTO_INCREMENT 컬럼을 포함하는 테이블에 행을 삽입하는 SQL 문 확장 성과 성능이 크게 향상되는 최적화를 제공하고 있습니다. InnoDB 테이블에서 AUTO_INCREMENT 메커니즘을 사용하려면 테이블에서 최대 컬럼 값을 얻을 인덱스 SELECT MAX( ai_col ) 조회와 동일한 작업을 수행 할 수 있도록 AUTO_INCREMENT 컬럼 ai_col 을 인덱스의 일부로 정의해야 있습니다. 일반적으로 이것은 열을 어느 테이블 인덱스의 첫 번째 컬럼함으로써 실현됩니다.

이 섹션에서는 InnoDB 의 자동 증가 잠금 원래 ( "전통적인") 구현에 대한 배경 정보를 제공하고 구성 가능한 잠금 메커니즘에 대해 설명하고이 메커니즘을 구성하기위한 매개 변수를 표시 한 동작과 복제 과의 상호 작용에 대해 설명합니다.

기존의 InnoDB의 자동 증가 잠금

InnoDB 의 자동 증가 처리의 원래의 구현에서는 문 기반 복제 및 특정 복구 시나리오에서 바이너리 로그를 사용하면 발생하는 문제를 해결하기 위해 다음과 같은 정책이 사용되고 있습니다.

InnoDB 테이블에 AUTO_INCREMENT 컬럼을 지정하면 InnoDB 데이터 사전의 테이블 핸들 컬럼에 새로운 값을 할당 할 때 사용되는 자동 증가 카운터라고 불리는 특별한 카운터가 포함됩니다. 이 카운터는 디스크에 저장되지 않고, 메인 메모리에만 저장됩니다.

InnoDB 는 ai_col 라는 AUTO_INCREMENT 컬럼을 포함하는 테이블 t 에 자동 증가 카운터를 초기화하기 위해 다음과 같은 알고리즘이 사용됩니다. 서버 부팅 후에 테이블 t 에 처음 삽입을하기 위해 InnoDB 는 다음의 문과 동등한 것을 실행합니다.

 SELECT MAX (ai_col) FROM t FOR UPDATE;

InnoDB 는 명령문에서 취득 된 값을 증가시키고 그것을 테이블의 컬럼 및 자동 증가 카운터에 할당합니다. 기본적으로 값이 1 씩 증가됩니다. 이 기본값은 auto_increment_increment 구성 설정을 다시 정의 할 수 있습니다.

테이블이 비어있는 경우, InnoDB 는 값 1 이 사용됩니다. 이 기본값은 auto_increment_offset 구성 설정을 다시 정의 할 수 있습니다.

자동 증가 카운터가 초기화되기 전에 SHOW TABLE STATUS 문 t 테이블이 조사되는 경우, InnoDB 에 의해 값은 초기화되지만 증분되지 않고, 삽입에 사용하기 위해 저장됩니다 . 이 초기화는 테이블에서 일반 단독 잠금 읽기가 사용되는 잠금은 트랜잭션 끝까지 유지됩니다.

InnoDB 는 새롭게 생성 된 테이블에 대해 자동 증가 카운터를 초기화 할 때와 같은 절차를 따르십시오.

자동 증가 카운터가 초기화 된 후, AUTO_INCREMENT 컬럼의 값을 명시 적으로 지정하지 않는 경우, InnoDB 에 의해 카운터가 증가되고 새로운 값을 컬럼에 할당됩니다. 컬럼 값을 명시 적으로 지정하는 행을 삽입 할 때, 그 값이 현재의 카운터 값보다 큰 경우에는 카운터가 지정된 컬럼 값으로 설정됩니다.

사용자가 INSERT 에 AUTO_INCREMENT 컬럼에 NULL 또는 0 을 지정하면, InnoDB 는 값이 지정되지 않은 경우와 마찬가지로 그 행이 처리되고 새 값이 생성됩니다.

컬럼에 음수 값을 할당하거나 값이 지정된 정수형에 저장할 수있는 최대 정수보다 클 경우 자동 증가 메커니즘의 동작이 정의되지 않습니다.

자동 증가 카운터에 액세스 할 때 InnoDB 는 트랜잭션의 끝까지가 아니라 현재의 SQL 문이 끝날 때까지 존속하는 특별한 테이블 수준 AUTO-INC 락이 사용됩니다. AUTO_INCREMENT 컬럼을 포함하는 테이블에 삽입 동시성을 개선하기 위해 특별한 잠금 해제 정책이 도입되었습니다. 그럼에도 불구하고, 두 개의 트랜잭션이 동시에 AUTO-INC 락을 같은 테이블에서 가질 수 없습니다. 그러면 AUTO-INC 락이 장시간 유지되면 성능이 영향을받을 수 있습니다. 이것은 한 테이블에서 다른 테이블에 모든 행을 삽입하는 INSERT INTO t1 ... SELECT ... FROM t2 등의 문의 경우에 발생할 수 있습니다.

InnoDB 는 서버가 실행되고 있으면, 인 메모리의 자동 증가 카운터가 사용됩니다. 앞에서 설명한 바와 같이, 서버가 중지했다가 다시 시작되면 테이블에 첫 번째 INSERT 시, InnoDB 에 의해 테이블마다 카운터가 다시 초기화됩니다.

서버가 다시 시작되면 CREATE TABLE 및 ALTER TABLE 문 AUTO_INCREMENT = N 테이블 옵션의 효과도 취소됩니다. 이 옵션을 InnoDB 테이블로 사용하면 초기 카운터 값을 설정하거나 현재의 카운터 값을 변경할 수 있습니다.

카운터를 사용하여 숫자가 생성 된 트랜잭션을 롤백하면 AUTO_INCREMENT 컬럼에 할당 된 일련의 값에서 차이를 발견 할 수 있습니다.

구성 가능한 InnoDB의 자동 증가 잠금

이전 섹션에서 설명한 바와 같이, InnoDB 는 AUTO_INCREMENT 컬럼을 포함하는 테이블에 삽입 할 때, AUTO-INC 락이라는 특수한 테이블 수준 잠금이 사용됩니다. 이 잠금은 일반적으로 지정된 일련의 INSERT 문에 예측 가능하고 반복 가능한 순서로 자동 증가 번호가 할당되도록 (트랜잭션이 끝날 때까지이 아닌) 문이 끝날 때까지 유지됩니다.

문 기반 복제의 경우 이는 어떤 SQL 문을 슬레이브 서버에서 복제 될 때 자동 증가 컬럼에서 마스터 서버와 같은 값이 사용되는 것을 의미합니다. 여러 INSERT 문 실행 결과는 결정적이며, 마스터와 같은 데이터가 슬레이브에서 다시 생성됩니다. 여러 INSERT 문에 의해 생성 된 자동 증가 값이 인터리브 된 경우는 2 개의 병렬 INSERT 문의 결과는 비 결정적이며, 문 기반 복제를 사용하여 슬레이브 서버로 전달되는 때 안정성도 낮아질 수 있습니다.

이 점이 명확하게되도록 다음 테이블을 사용하는 예를 생각해 봅시다.

 CREATE TABLE t1 (
   c1 INT (11) NOT NULL AUTO_INCREMENT,
   c2 VARCHAR (10) DEFAULT NULL,
   PRIMARY KEY (c1)
 ) ENGINE = InnoDB;

실행중인 트랜잭션이 2 개 존재하고 각 AUTO_INCREMENT 컬럼을 포함하는 테이블에 행을 삽입하는 것으로합니다. 하나의 트랜잭션은 1000 행을 삽입하는 INSERT ... SELECT 문을 사용하고, 다른 하나의 트랜잭션이 행을 삽입하는 간단한 INSERT 문을 사용하고 있습니다.

 Tx1 : INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
 Tx2 : INSERT INTO t1 (c2) VALUES ( 'xxx');

InnoDB 는 Tx1의 INSERT 문에서 SELECT 에서 검색되는 행 수를 미리 알 수 없기 때문에 그 문으로 계속 때 자동 증가 값을 한 번에 하나씩 할당합니다. 문이 끝날 때까지 유지되는 테이블 수준 잠금이 존재하고 있기 때문에 어느 시점에서 실행 가능한 INSERT 문은 테이블 t1 을 참조하는 하나의 문 뿐이며, 여러 문이 자동 증가 번호의 생성이 인터리브 되는 것은 아닙니다. Tx1의 INSERT ... SELECT 문에서 생성 된 자동 증가 값은 연속 한 번호입니다, Tx2의 INSERT 문에서 사용되는 (하나의) 자동 증가 값은 두 문이 먼저 실행되는지에 따라 Tx1에서 사용되는 모든 값보다 작거나 큰 값입니다.

(문 기반 복제 사용하거나 복구 시나리오) 바이너리 로그에서 재현 할 때 SQL 문이 같은 순서로 실행 한 결과는 Tx1과 Tx2가 먼저 실행되었을 때와 동일합니다. 따라서 문이 끝날 때까지 유지되는 테이블 수준 잠금이 존재하면 자동 증가를 사용하는 INSERT 문을 문 기반 복제로 안전하게 사용할 수 있습니다. 그러나 이러한 잠금은 여러 트랜잭션에서 삽입 문이 동시에 실행될 때 동시성 및 확장 성이 제한됩니다.

위의 예에서 테이블 수준 잠금이 존재하지 않는 경우 Tx2의 INSERT 에서 사용되는 자동 증가 컬럼의 값은 문이 실제로 실행되는시기에 따라 변경됩니다. Tx1의 INSERT 의 (실행 전이나 완료된 후가 아닌) 실행 중에 Tx2의 INSERT 가 실행 된 경우, 그 2 개의 INSERT 문에서 할당 구체적인 자동 증가 값은 비 결정적입니다 실행할 때마다 값 이 다를 수 있습니다.

InnoDB 는 행수가 미리 알고있는 경우 INSERT 문 클래스에 대해 테이블 수준 AUTO-INC 락이 사용되는 것을 방지 할 수 있지만, 문 기반 복제 결정적인 실행 및 안전성 은 계속 유지됩니다. 또한, 복구 또는 복제의 일부로 SQL 문을 재현하기 위해 바이너리 로그를 사용하지 않는 경우는 병렬성과 성능을 더욱 개선하기 위해 테이블 수준 AUTO-INC 락의 사용을 완전히 제거 할 수 있지만 문에 할당 된 자동 증가 수의 격차가 허용되어 병렬로 실행되는 문에 할당 된 숫자가 인터리브 될 수 있다는 희생이 따릅니다.

문 처리 개시 시점에 삽입 행수가 알고있는 것 같은 INSERT 문은 InnoDB 는 잠금을 일절 사용하지 않고 필요한만큼 자동 증가 값을 빠르게 할당합니다. 그러나 테이블 레벨 AUTO-INC 락을 이미 보유하고있는 병렬 세션이 존재하지 않는 경우에 한정합니다 (그 다른 문이 처리 중에 자동 증가 값을 하나씩 할당). 더 정확히 말하면,이 같은 INSERT 문은 문을 완료가 아닌 할당 처리 기간 만 유지되는 상호 배타 락 (경량 락)의 제어하에 자동 증가 값을 가져옵니다.

이 새로운 잠금 구성표를 사용하면 확장 성을 크게 개선 할 수 있지만 원래의 메커니즘에 비해 자동 증가 값이 할당 방법에 약간의 차이가 산견됩니다. InnoDB 의 자동 증가의 동작을 설명하기 위해 다음의 설명에서 몇 가지 용어를 정의하고 서버 시작시 설정할 수 innodb_autoinc_lock_mode 구성 매개 변수의 다양한 설정을 사용한 InnoDB 의 동작에 대해 설명합니다. 자동 증가 잠금 동작 설명 후에 추가주의 사항에 대해 설명합니다.

먼저 몇 가지 정의를 보여줍니다.

" INSERT 같은 "문

INSERT , INSERT ... SELECT , REPLACE , REPLACE ... SELECT , LOAD DATA 등 테이블에 새로운 행을 생성하는 모든 문입니다.
"단순히 삽입"

삽입 행수를 사전에 (문의 초기 처리시) 결정할 수 문입니다. 여기에는 중첩 된 서브 쿼리가없는 단일 행 및 여러 줄의 INSERT 및 REPLACE 문이 포함되지만 INSERT ... ON DUPLICATE KEY UPDATE 는 포함되지 않습니다.
"대량 삽입"

삽입 행 수 (및 필요한 자동 증가 값의 수)이 사전에 모르는 문입니다. 여기에는 INSERT ... SELECT , REPLACE ... SELECT 및 LOAD DATA 문이 포함되지만, 간단한 INSERT 은 포함되지 않습니다. InnoDB 는 각 행을 처리 할 때 AUTO_INCREMENT 컬럼의 새 값을 한 번에 하나씩 할당합니다.
"혼합 모드 삽입"

이러한 새로운 라인의 일부 (전부는 아니다)의 자동 증가 값을 지정하는 "단순 삽입"문입니다. 다음의 예를 보여줍니다. c1 테이블 t1 의 AUTO_INCREMENT 컬럼입니다.
```
 INSERT INTO t1 (c1, c2) VALUES (1, 'a'), (NULL, 'b'), (5, 'c'), (NULL, 'd');
```
INSERT ... ON DUPLICATE KEY UPDATE 는 다른 유형의 "혼합 모드 삽입"에서 최악의 경우에는 실질적 INSERT 다음에 UPDATE 를 실행하는 것과 동일하지만 AUTO_INCREMENT 컬럼에 할당 된 값은 업데이트 단계에서 사용되는 가능성도 사용되지 않을 수도 있습니다.

innodb_autoinc_lock_mode 매개 변수는 다음의 3 가지 설정을 지정할 수 있습니다.

innodb_autoinc_lock_mode = 0 ( "기존"잠금 모드)

이 잠금 모드에서는 innodb_autoinc_lock_mode 가 존재하기 전의 작동이 제공됩니다. 모든 " INSERT 같은 "문은 특수 테이블 수준 AUTO-INC 락이 취득 된 문이 끝날 때까지 유지됩니다. 이렇게하면 특정 문에 의해 할당 된 자동 증가 값이 연속으로됩니다.

이 잠금 모드의 용도는 다음과 같습니다.
- 하위 호환성.
- 성능 테스트.
- "혼합 모드 삽입"에서 문제 해결 (나중에 설명하는 의미에 차이가있을 수 있기 때문에).
innodb_autoinc_lock_mode = 1 ( "연속"잠금 모드)

이것이 기본 잠금 모드입니다. 이 모드에서는 "대량 삽입"특별한 AUTO-INC 테이블 수준 잠금을 사용하여 잠금을 문이 끝날 때까지 유지합니다. 이것은 INSERT ... SELECT , REPLACE ... SELECT , LOAD DATA 의 모든 문에 적용됩니다. 한 번에 실행할 수있는 문은 AUTO-INC 락을 보관 유지하고있는 하나의 문뿐입니다.

이 잠금 모드에서는 "단순 삽입"(단)가 자동 증가 값을 할당 할 때 경량 상호 배타적 잠금이 사용되는 새로운 잠금 모델을 사용합니다. 다른 트랜잭션이 테이블 수준 AUTO-INC 락을 보관 유지하고 있지 않은 한, AUTO-INC 락은 사용되지 않습니다. 다른 트랜잭션이 AUTO-INC 락을 보관 유지하는 경우 "단순히 삽입"는 "일괄 잠금"뿐만 아니라 AUTO-INC 락을 대기합니다.

이 잠금 모드에서는 행이 사전에 모르는 (따라서 문을 처리하는 동안 자동 증가 번호를 할당) INSERT 문이 존재하는 경우에는 임의의 " INSERT 같은 "문에 의해 할당 된 모든 자동 증가 값이 반드시 연속적인 값이되기 때문에 그 처리는 문 기반 복제에 사용하는 것이 안전합니다.

간단히 말하면이 잠금 모드의 중요한 효과는 확장 성이 크게 향상입니다. 이 모드는 문 기반 복제에 사용하여도 안전합니다. 또한 "기존"잠금 모드의 경우와 같이, 어떤 진술에 의해 할당 된 자동 증가 번호가 연속됩니다. 이 모드에서는 "기존"모드와 비교하여 하나의 중요한 예외를 제외하고 자동 증가를 사용하는 문의 의미에 변화는 없습니다.

예외는 "혼합 모드 삽입"입니다. 이 삽입에서는 사용자가 여러 줄의 "단순 삽입"에서 명시적인 값을 전부가 아니라 일부의 행의 AUTO_INCREMENT 컬럼에 지정합니다. 이러한 삽입의 경우, InnoDB 는 삽입되는 행 수보다 큰 자동 증가 값을 할당합니다. 그러나 자동으로 할당되는 값은 연속적으로 생성되기 때문에 이전에 실행 된 문에 의해 생성 된 자동 증가 값보다 값이 커집니다. "정상"숫자는 손실됩니다.
innodb_autoinc_lock_mode = 2 ( "인터리브"잠금 모드)

이 잠금 모드는 테이블 수준 AUTO-INC 락을 사용하는 " INSERT 같은 '문은 더 이상 존재하지 않기 때문에 여러 문을 동시에 실행할 수 있습니다. 이것은 가장 빠르고 가장 확장 성이 뛰어난 잠금 모드입니다. 그러나 문 기반 복제를 사용하거나 복구 시나리오에서 바이너리 로그에서 SQL 문을 재현 할 때 안전하지 않습니다.

이 잠금 모드에서는 자동 증가 값은 고유이며, 병렬 실행되는 모든 " INSERT 같은 "문 걸쳐 단조롭게 증가하는 것이 보증됩니다. 그러나 여러 문이 동시에 번호를 생성하는 (즉 번호의 할당이 여러 문에 인터리브되는) 가능성이 있기 때문에 어떤 진술에 의해 삽입 된 행에 대해 생성 된 값 이 지속적인 않을 수 있습니다.

유일한 문 실행이 삽입되는 행 수를 미리 알고있다 "단순히 삽입"인 경우 "혼합 모드 삽입"을 제외하고 단일 문에서 생성되는 숫자에 차이가 없습니다. 그러나 "대량 삽입"가 실행되면 특정 문에서 할당 된 자동 증가 값에 차이가 발생할 수 있습니다.

innodb_autoinc_lock_mode 에서 제공하는 자동 증가 잠금 모드는 다음과 같이 사용에서 암시 적으로 가정 몇 가지 있습니다.

복제의 자동 증가 사용

문 기반 복제를 사용하는 경우 innodb_autoinc_lock_mode 을 0 또는 1로 설정하고 마스터와 슬레이브로 동일한 값을 사용하십시오. innodb_autoinc_lock_mode = 2 ( "인터리브") 또는 마스터와 슬레이브가 동일한 잠금 모드를 사용하지 않는 구성을 사용하는 경우는 마스터와 슬레이브로 자동 증가 값이 동일하게 보장 할 수는 없습니다.

열 기반 리플리케이션은 SQL 문 실행 순서에 좌우되지 않는 (혼합 형식은 문 기반 복제는 안전하지 않은 문을 열 기반 리플리케이션을 사용한다) 때문에, 행 기반 또는 혼합 형식 복제를 사용하는 경우, 모든 자동 증가 잠금 모드가 안전합니다.
"잃어버린"자동 증가 값과 순서 격차

모든 잠금 모드 (0, 1 및 2)는 자동 증가 값을 생성 한 트랜잭션이 롤백되면 이러한 자동 증가 값이 "사라집니다." " INSERT 같은 "문이 완료 여부 및이를 포함하는 트랜잭션이 롤백 된 여부에 관계없이 자동 증가 컬럼의 값은 한 번 생성되면 롤백 할 수 없습니다. 이러한 손실 된 값은 다시 사용되지 않습니다. 따라서 테이블의 AUTO_INCREMENT 컬럼에 저장되는 값에는 차이가있을 수 있습니다.
"대량 삽입"자동 증가 값의 차이

innodb_autoinc_lock_mode 가 0 ( "기존") 또는 1 ( "연속")로 설정되어있는 경우, 테이블 레벨 AUTO-INC 락이 문이 끝날 때까지 유지되며 동시에 실행할 수있는 문은 이러한 하나의 문이다 때문에 어떤 문에 의해 생성 된 자동 증가 값은 간격없이 연속적인 것입니다.

innodb_autoinc_lock_mode 2 ( "인터리브")로 설정되어있는 경우 "대량 삽입"에 의해 생성 된 자동 증가 값에 차이가있을 수 있지만 병렬 실행중인 " INSERT 같은 "문이 존재 경우에 한합니다.

대량 삽입은 각 문에 필요한 자동 증가 값의 정확한 수를 알 수 없어 과대 평가 될 가능성이 있기 때문에 잠금 모드가 1 또는 2의 경우 연속 된 문 사이에 격차가 발생하는 수 있습니다.
"혼합 모드 삽입"에 의해 할당 된 자동 증가 값

"단순히 삽입"을 (전부가 아닌) 일부 결과 행의 자동 증가 값을 지정하는 "혼합 모드 삽입"을 검토합니다. 이러한 문의 동작은 잠금 모드 0, 1 및 2에 따라 다릅니다. 예를 들어, c1 테이블 t1 의 AUTO_INCREMENT 컬럼에서 자동 생성 된 시퀀스 번호의 최신 값이 100이라고합니다. 다음과 같은 "혼합 모드 삽입"문을 검토합니다.
```
 INSERT INTO t1 (c1, c2) VALUES (1, 'a'), (NULL, 'b'), (5, 'c'), (NULL, 'd');
```
innodb_autoinc_lock_mode 가 0 ( "기존")로 설정되어있는 경우 4 개의 새로운 행은 다음과 같이됩니다.
```
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+
```
자동 증가 값은 문 시작될 때 한 번에 모든 값을 할당하지 않고 한 번에 하나씩 할당되므로 다음 사용 가능한 자동 증가 값은 103입니다. 이 결과는 병렬 실행 (모든 종류의) " INSERT 같은 "문이 존재하는지 여부에 좌우되지 않습니다.

innodb_autoinc_lock_mode 이 1 ( "연속")로 설정되어있는 경우에도 4 개의 새로운 행은 다음과 같이됩니다.
```
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+
```
그러나이 경우 문이 처리 될 때 자동 증가 값이 4 개의 할당했지만, 그 중 2 개만이 사용 되었기 때문에 다음 사용 가능한 자동 증가 값은 103이 아니라 105입니다. 이 결과는 병렬 실행 (모든 종류의) " INSERT 같은 "문이 존재하는지 여부에 좌우되지 않습니다.

innodb_autoinc_lock_mode 2 ( "인터리브")로 설정되어있는 경우 4 개의 새로운 행은 다음과 같이됩니다.
```
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
|   x | b    |
|   5 | c    |
|   y | d    |
+-----+------+
```
x 와 y 의 값은 고유하며 이전에 생성 된 모든 행보다 커집니다. 그러나 x 와 y 의 구체적인 값은 병렬 실행중인 문에 의해 생성 된 자동 증가 값의 개수에 따라 다릅니다.

마지막으로 생성 된 최신 시퀀스 번호 값 4 일 때 다음 문을 발행 한 경우를 검토합니다.
```
 INSERT INTO t1 (c1, c2) VALUES (1, 'a'), (NULL, 'b'), (5, 'c'), (NULL, 'd');
```
어떻게 innodb_autoinc_lock_mode 을 설정해도 행 (NULL, 'b') 에 대해 5가 할당 행 (5, 'c') 의 삽입이 실패하기 때문에이 문에서 중복 키 오류 23000 ( Can't write; duplicate key in table )가 생성됩니다.