테이블 잠금 없이 대규모 MySQL 프로덕션 테이블에서 인덱스를 생성하는 방법

문제 배경:

대규모 MySQL 테이블에 인덱스를 생성하는 것은 어려운 작업일 수 있으며, 특히 중단 없는 액세스가 중요한 프로덕션 환경에서는 더욱 그렇습니다. 기존 CREATE INDEX 문을 사용하면 전체 테이블 잠금이 발생하여 모든 동시 작업이 차단될 수 있습니다.

MySQL 버전 고려 사항:

• MySQL 5.6 이상에서는 인덱스 업데이트 온라인으로 수행되므로 인덱스 생성 중에 읽기 및 쓰기 작업을 계속할 수 있습니다.
• 그러나 InnoDB 및 MyISAM 테이블을 포함하여 MySQL 5.5 및 이전 버전에서는 인덱스 업데이트로 인해 테이블에 대한 쓰기가 차단됩니다.

순환 마스터 접근 방식:

5.6 이전 MySQL 버전의 경우 효과적인 접근 방식 중 하나는 순환 마스터 기술입니다.

1. 보조 마스터 설정( 마스터 B) 기본 마스터(마스터 A)에서 복제합니다.
2. 마스터 B에서 스키마 업데이트를 수행합니다(업그레이드 중에 뒤쳐지도록 허용).
3. 스키마 변경이 호환되는지 확인합니다. 마스터 A의 다운버전 스키마에서 명령을 복제합니다.
4. 모든 클라이언트를 마스터 A에서 마스터 B로 원자적으로 전환합니다.
5. 마스터 A의 스키마를 업데이트하고 이를 보조 마스터로 만듭니다.

Percona의 pt-online-schema-change 도구:

이 도구는 다음을 통해 순환 마스터 접근 방식을 자동화합니다.

• 새 테이블 만들기 업데이트된 스키마를 사용합니다.
• 트리거를 사용하여 새 테이블을 원본 테이블과 동기화합니다.
• 원본 테이블에서 행을 일괄 복사합니다.
• 원본 테이블 바꾸기

AWS RDS 고려 사항:

Amazon RDS에 호스팅된 MySQL 데이터베이스의 경우 "읽기 복제본 프로모션" 기능을 사용하여 테이블 잠금 없이 스키마가 변경됩니다. 여기에는 읽기 전용 슬레이브를 변경한 다음 새 마스터로 승격시키는 작업이 포함됩니다.

대체 기술:

• 임시 테이블: 새 인덱스로 임시 테이블을 생성하고, 원본 테이블의 데이터를 삽입하고, 원본 테이블을 임시 테이블로 바꿉니다.
• 테이블 분할: 테이블 분할 더 작은 파티션으로 분할하고, 각 파티션에 개별적으로 인덱스를 생성한 다음 파티션을 다시 병합합니다.
• 백그라운드 프로세스 사용: 테이블이 삭제되는 동안 점진적으로 인덱스를 생성하는 별도의 백그라운드 프로세스를 생성합니다. 정상적인 작업에는 계속 액세스할 수 있습니다. 이 접근 방식은 모든 MySQL 버전에서 지원되지 않습니다.