MySQL 테이블 잠금: 컬럼 또는 인덱스 추가 시 무슨 일이 벌어지나?

MySQL 테이블의 구조를 변경하는 작업은 종종 테이블 레벨 잠금과 관련된 문제를 야기합니다.

특히, 데이터량이 많은 테이블의 경우 비즈니스 시스템의 성능에 상당한 영향을 미칠 수 있습니다.

테이블 구조 변경 작업을 최적화함으로써 개발자는 테이블 잠금 시간을 피하거나 최소화하여 시스템이 원활하게 실행되도록 보장할 수 있습니다.

테이블 레벨 잠금 소개

테이블 레벨 잠금은 데이터 일관성을 보장하기 위해 특정 작업 중에 전체 테이블을 잠그는 것을 의미합니다.

구체적으로:

테이블에 대해 ALTER TABLE 작업을 실행할 때 MySQL은 기본적으로 테이블을 잠그고 ALTER TABLE 작업이 완료될 때까지 다른 트랜잭션이 테이블을 읽거나 쓸 수 없도록 차단합니다.

이러한 테이블 잠금 동작은 작은 테이블이나 낮은 동시성을 가진 시스템에는 거의 영향을 미치지 않습니다.

그러나 대용량 데이터 세트 또는 높은 동시성을 가진 비즈니스 시스템을 다룰 때 테이블 잠금은 심각한 성능 병목 현상을 일으키고 심지어 시스템 충돌을 초래할 수 있습니다.

테이블 잠금의 구체적인 영향

MySQL이 컬럼 추가와 같은 작업을 수행할 때 테이블 잠금은 다음과 같은 문제를 일으킬 수 있습니다.

쿼리 지연: 테이블에 대한 모든 SQL 쿼리는 테이블 잠금이 해제될 때까지 차단됩니다.
쓰기 작업 차단: 테이블에 대한 모든 쓰기 작업(INSERT, UPDATE, DELETE 등)은 잠금이 해제될 때까지 보류됩니다.
시스템 속도 저하: 테이블 잠금 작업이 완료되는 데 너무 오래 걸리면 비즈니스 시스템의 전반적인 성능이 크게 저하되어 시스템 중단을 초래할 수 있습니다.

그러나 최신 버전의 MySQL에서는 InnoDB 스토리지 엔진을 사용하여 테이블에 컬럼을 추가할 때 테이블 잠금이 반드시 발생하지 않을 수 있습니다.

InnoDB 스토리지 엔진은 테이블 잠금을 줄이고 동시 성능을 향상시키는 몇 가지 메커니즘을 제공합니다.

MySQL에서 테이블에 컬럼을 추가할 때 테이블을 잠그는지는 사용되는 스토리지 엔진과 MySQL 버전에 따라 다릅니다.

MySQL 5.6 이전

이전 버전의 MySQL에서는 ALTER TABLE 명령을 사용하여 컬럼을 추가하면 InnoDB 스토리지 엔진을 사용하는 경우 기본적으로 테이블이 잠깁니다.

이는 작업 중에 테이블이 잠겨 다른 읽기 및 쓰기 작업이 완료될 때까지 방지됨을 의미합니다.

이러한 전체 테이블 잠금 동작은 대규모 테이블에서 ALTER TABLE 작업을 실행할 때 긴 대기 시간과 애플리케이션 중단을 초래할 수 있습니다.

따라서 MySQL 5.6 이전에는 테이블 구조를 직접 수정하면 테이블이 잠깁니다.

구체적인 단계는 다음과 같습니다.

먼저 ALTER TABLE 명령을 사용하여 수정된 구조로 새 임시 테이블을 만듭니다.
그런 다음 원본 테이블의 데이터를 임시 테이블로 가져옵니다.
원본 테이블을 삭제합니다.
마지막으로 임시 테이블의 이름을 원본 테이블의 이름으로 바꿉니다.

MySQL 버전 5.6 및 8.0에서는 테이블 잠금 문제를 해결하기 위해 최적화를 도입했습니다.

MySQL 5.6

MySQL 5.6부터 InnoDB는 Online DDL 작업을 도입하여 테이블을 잠그지 않고도 특정 테이블 수정 작업을 수행할 수 있도록 했습니다.

컬럼 추가는 온라인 작업이며 전체 테이블 잠금을 피하기 위해 ALGORITHM=INPLACE를 사용할 수 있습니다.

ALTER TABLE your_table ADD COLUMN new_column INT ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM=INPLACE는 온라인 DDL 메커니즘의 일부인 제자리 알고리즘을 수정에 사용해야 함을 나타냅니다.
- MySQL에 테이블 구조를 제자리에서 수정하도록 명시적으로 지시합니다.
- MySQL은 가능하면 전체 테이블을 다시 만들지 않고 변경 사항을 적용하려고 시도합니다.
LOCK=NONE은 테이블을 잠그지 않아야 함을 의미하며 동시 쿼리에 대한 영향을 최소화합니다.
- 다른 세션이 테이블을 읽고 쓸 수 있습니다.
- 그러나 데이터 불일치 위험이 여전히 있을 수 있습니다.

이렇게 하면 실행 효율성이 크게 향상되고 테이블 잠금을 피할 수 있습니다.

그러나 이는 두 가지 경우로 나뉩니다.

Null을 허용하지 않는 컬럼 추가:

전체 테이블을 잠그지 않고 빠른 메타데이터 전용 작업이 수행됩니다.
다른 세션은 수정 중에 테이블에서 계속 읽고 쓸 수 있습니다.

Null을 허용하는 컬럼 추가:

전체 테이블을 잠그지 않고 빠른 메타데이터 전용 작업으로 수행됩니다.
다른 세션은 읽기 및 쓰기 작업을 계속할 수 있지만 수정 중에 짧은 행 레벨 잠금이 발생할 수 있습니다.

참고:

InnoDB 스토리지 엔진은 잠금을 줄였지만 ALTER TABLE을 실행하면 여전히 성능에 영향을 미칠 수 있습니다.

이는 내부 메타데이터 작업, 데이터 재구성 또는 로그 쓰기 때문입니다.

따라서 대규모 테이블의 구조를 수정할 때는 애플리케이션에 미치는 영향을 최소화하기 위해 사용량이 적은 시간에 작업을 수행하는 것이 좋습니다.

MySQL 8.0

MySQL 8.0에서는 테이블을 잠그지 않고도 대부분의 ALTER TABLE 작업을 수행할 수 있는 새로운 기능이 도입되었습니다.

간단히 말해서 Online DDL 작업의 기능을 향상시킵니다.

기본적으로 MySQL 8.0에서는 간단한 ALTER TABLE 작업(예: 컬럼 추가)은 일반적으로 테이블을 잠그지 않습니다.

특정 ALTER TABLE 작업이 테이블을 잠그는지 확인하려면 실행하기 전에 EXPLAIN 문을 사용할 수 있습니다.

EXPLAIN ALTER TABLE your_table ADD COLUMN new_column INT;

이 명령은 테이블이 잠기는지 여부를 포함하여 실행 계획 정보를 표시합니다.

다음은 MySQL 8.0의 몇 가지 구체적인 최적화 사항입니다.

Atomic DDL

MySQL 8.0에서는 Atomic DDL 작업이 도입되어 ALTER TABLE 문의 실행과 관련된 차단이 줄어듭니다.
컬럼을 추가할 때 Atomic DDL 메커니즘은 테이블 잠금 시간을 줄이고 다른 세션에서 데이터를 계속 읽고 쓸 수 있도록 합니다.

즉각적인 메타데이터 업데이트

MySQL 8.0에서는 컬럼이 추가될 때 전체 작업이 완료될 때까지 기다리지 않고 테이블의 메타데이터가 즉시 업데이트됩니다.
이렇게 하면 ALTER TABLE 작업이 더 빨리 완료되고 잠금 시간이 줄어듭니다.

InnoDB 엔진 최적화

MySQL 8.0의 InnoDB 엔진은 대규모 데이터 테이블의 구조적 변경에 최적화되었습니다.
예를 들어, Null을 허용하지 않는 컬럼을 추가할 때 InnoDB는 더 이상 모든 테이블 데이터를 복사할 필요가 없습니다.
대신 더 가벼운 작업을 사용하여 새 필드를 추가하여 잠금 시간과 리소스 사용량을 줄입니다.

점진적인 메타데이터 업데이트

MySQL 8.0에서는 점진적인 메타데이터 업데이트가 도입되었습니다. 즉, ALTER TABLE 작업 중에 전체 테이블이 아닌 영향을 받는 메타데이터만 업데이트해야 합니다.
이렇게 하면 잠금 시간과 오버헤드가 줄어듭니다.

Online DDL

Online DDL은 데이터베이스가 실행 중인 동안 DDL(Data Definition Language) 작업을 실행하는 것을 의미합니다.

예를 들어 데이터베이스의 장기적인 잠금 또는 사용 불가를 유발하지 않고 테이블 구조, 인덱스 등을 생성, 수정 또는 삭제합니다.

기존 DDL 작업은 일반적으로 영향을 받는 테이블에 대한 배타적 잠금이 필요합니다.

이로 인해 다른 세션에서 테이블에 대한 읽기 또는 쓰기 작업을 수행하지 못하여 정상적인 데이터베이스 사용에 영향을 미칠 수 있습니다.

현재 세 가지 주요 알고리즘이 지원됩니다.

COPY: pre-MySQL 5.6 버전에서 사용되며 온라인 알고리즘이 아닙니다.
INPLACE: MySQL 5.6에서 도입되었습니다.
INSTANT: MySQL 8.0.12에서 도입되었습니다(Tencent DBA 팀에서 기여).

기본 원리

DDL 실행 중에는 사용되는 알고리즘에 관계없이 일반적으로 세 단계를 거칩니다.

준비 단계
실행 단계(DDL)
커밋 단계

차이점은 알고리즘에 따라 이러한 각 단계에서 적용되는 최적화에 있습니다.

구현 세부 정보는 공식 문서를 참조하십시오.

요약

MySQL 5.6부터는 컬럼을 추가하거나 테이블 구조를 수정하거나 인덱스를 추가하는 것만으로는 일반적으로 전체 테이블을 잠글 필요가 없습니다.

그러나 경우에 따라 MySQL은 여전히 전체 테이블을 잠가야 할 수 있습니다.

또한 많은 양의 데이터를 처리할 때는 성능 문제가 발생할 수 있습니다.

따라서 실제 작업 중에는 테이블의 데이터 양과 최종 크기(특히 인덱스 데이터)에 주의하십시오.

동시에 MySQL 버전이 비교적 오래되었거나 특정 이유로 온라인 DDL 작업이 지원되지 않는 경우 비즈니스 시스템에 미치는 영향을 최소화하기 위해 사용량이 적은 시간에 ALTER TABLE 작업을 수행해야 합니다.

Leapcell은 백엔드 프로젝트 호스팅을 위한 최고의 선택입니다.

Leapcell은 웹 호스팅, 비동기 작업 및 Redis를 위한 차세대 서버리스 플랫폼입니다.

다국어 지원

Node.js, Python, Go 또는 Rust로 개발하십시오.

무제한 프로젝트를 무료로 배포

사용량에 대해서만 비용을 지불하십시오. 요청도 없고 요금도 없습니다.

탁월한 비용 효율성

사용한 만큼 지불하고 유휴 요금이 없습니다.
예: $25는 평균 응답 시간 60ms에서 694만 건의 요청을 지원합니다.

간소화된 개발자 경험

간편한 설정을 위한 직관적인 UI
완전 자동화된 CI/CD 파이프라인 및 GitOps 통합
실행 가능한 통찰력을 위한 실시간 메트릭 및 로깅

간편한 확장성 및 고성능

쉬운 고 동시성을 처리하기 위한 자동 확장
운영 오버헤드가 없습니다. 빌드에만 집중하십시오.

설명서에서 자세히 알아보십시오!

X에서 팔로우하세요: @LeapcellHQ