[MySQL][InnoDB] Architecture

MySQL은 스토리지 엔진이라 불리는 플러그인 방식으로 데이터베이스를 관리합니다. 이 스토리지 엔진은 MyISAM, Memory, Aria등 여러가지가 있는데 각 스토리지 엔진 특성에 따라 관리하는 방법이 틀립니다. 그중에서도 가장 많이 쓰이는게 InnoDB라는 스토리지 엔진인데 굉장히 많은 기능들을 지원하고 있습니다. 이 InnoDB 스토리지 엔진에 대해서 알아보겠습니다.

 

■ InnoDB Storage Engine 소개

- DML 작업은 ACID 모델을 따르며 커밋, 롤백 및 응급 복구 기능을 갖춘 트랜잭션으로 사용자 데이터를 보호합니다.

- Row-level 잠금 및 Oracle 스타일의 일관된 읽기는 다중 접속 및 사용자에게 동시성 및 성능을 향상시킵니다.

- InnoDB 테이블은 기본 키를 기반으로 쿼리를 최적화하기 위해 디스크에 데이터를 정렬합니다. 각 InnoDB 테이블에는 기본 키 조회를 위한 I/O를 최소화하기 위해 데이터를 구성하는 clustered Index라는 기본 키 인덱스가 있습니다.

- 데이터 무결성을 유지하기 위해 InnoDB는 FOREIGN KEY 제약 조건을 지원합니다. 외래 키를 사용하면 다른 테이블간에 불일치가 발생하지 않도록 삽입, 업데이트 및 삭제를 검사합니다.

 

■ InnoDB Architecture.

지금부터 InnoDB의 아키텍쳐를 알아보겠습니다.

 

 

 

■ ACID 모델 지원

다른 상용 데이터베이스와 마찬가지로 ACID 모델을 지원합니다. 원자성, 지속성, 고립성, 지속성을 나타내고 다른 Database의 일반적인이론과 같습니다

A: atomicity. (원자성)

C: consistency. (지속성)

I: isolation. (격리수준,고립성)

D: durability. (지속성)

 

+ Atomicity(원자성)

ACID 모델의 원자성 측면에는 주로 InnoDB 트랜잭션이 포함됩니다. 관련 MySQL 기능은 다음과 같습니다.

- Autocommit 설정.

- COMMIT 문법.

- ROLLBACK 문법.

- INFORMATION_SCHEMA 테이블들로부터 나오는 운영 데이터

 

+ Consistency(지속성)

ACID 모델의 일관성 측면은 주로 데이터 충돌을 방지하기 위해 내부 InnoDB 처리와 관련이 있습니다. 관련 MySQL 기능은 다음과 같습니다.

- InnoDB doublewrite buffer.

- InnoDB crash recovery.

 

+ Isolation (격리수준,고립성)

ACID 모델의 격리 측면은 주로 InnoDB 트랜잭션, 특히 각 트랜잭션에 적용되는 격리 수준과 관련됩니다. 관련 MySQL 기능은 다음과 같습니다.

- Autocommit 설정.

- SET ISOLATION LEVEL 문법

- InnoDB 잠금에 대한 상세 정보, 즉 성능 조정 중에 INFORMATION_SCHEMA 테이블을 통해 이러한 세부 사항을 볼 수 있습니다.

 

+ Durability

ACID 모델의 내구성 측면에는 특정 하드웨어 구성과 상호 작용하는 MySQL 소프트웨어 기능이 포함됩니다. CPU, 네트워크 및 스토리지 장치의 기능에 따라 많은 가능성이 있기 때문에 이 측면은 구체적인 지침을 제공하기에 가장 복잡합니다. (그리고 이러한 지침은“새 하드웨어”구매 형태를 취할 수 있습니다.) 관련 MySQL 기능은 다음과 같습니다.

- InnoDB 이중 쓰기 버퍼. innodb_doublewrite 구성 옵션으로 사용 및 중지.

- innodb_flush_log_at_trx_commit 옵션 설정.

- sync_binlog 옵션 설정

- innodb_file_per_table 옵션 설정.

- disk drive, SSD, raid array와 같은 스토리지 디바이스의 쓰기 버퍼.

- 스토리지 디바이스의 Battery-backed cache

- 운영 체제는 MySQL을 실행하는 데 사용되며 특히 fsync () 시스템 호출을 지원.

- MySQL 서버를 실행하고 MySQL 데이터를 저장하는 모든 컴퓨터 서버 및 스토리지 장치에 대한 전력을 보호하는 UPS (무정전 전원 공급 장치).

-백업 빈도 및 유형, 백업 보존 기간과 같은 백업 전략

- 분산 또는 호스팅 된 데이터 응용 프로그램의 경우 MySQL 서버용 하드웨어가있는 데이터 센터의 특정 특성과 데이터 센터 간의 네트워크 연결.

 

 

 

■ InnoDB Multi-Versioning(MVCC)

InnoDB는 다중 버전 스토리지 엔진으로, 동시성 및 롤백과 같은 트랜잭션 기능을 지원하기 위해 이전 버전의 변경된 행에 대한 정보를 유지합니다. 이 정보는 롤백 세그먼트 (Oracle에서 유사한 데이터 구조 이후)라는 데이터 구조로 테이블스페이스에 저장됩니다. InnoDB는 롤백 세그먼트의 정보를 사용하여 트랜잭션 롤백에 필요한 실행 취소 작업을 수행합니다. 또한 이 정보를 사용하여 일관된 읽기를 위해 이전 버전의 행을 작성합니다.

 

내부적으로 InnoDB는 데이터베이스에 저장된 각 행에 3개의 필드를 추가합니다. 6바이트 DB_TRX_ID 필드는 행을 삽입하거나 업데이트를 한 마지막 트랜잭션의 트랜잭션 식별자(트랜잭션 ID)를 나타냅니다. 또한 삭제는 row안의 특수 비트가 삭제된 것으로 표시되도록 설정합니다(즉 row안의 특수비트를 삭제값으로 업데이트 합니다). 이는 추후 내부적으로 처리됩니다.   

각 행에는 롤 포인터라는 7 바이트 DB_ROLL_PTR 필드도 있습니다. 롤 포인터는 롤백 세그먼트에 기록 된 Undo log record를 표시합니다. 행이 업데이트 된 경우 Undo log record에는 업데이트 전에 행의 내용을 다시 작성하는 데 필요한 정보가 포함됩니다. 6 바이트 DB_ROW_ID 필드에는 새 행이 삽입 될 때 단조롭게 증가하는 행 ID가 포함됩니다. InnoDB가 클러스터형 인덱스를 자동으로 생성하면 인덱스에 행 ID 값이 포함됩니다. 그렇지 않으면 DB_ROW_ID 열이 인덱스에 나타나지 않습니다.

 

롤백 세그먼트의 Undo log는 삽입 및 업데이트 실행 취소 로그로 구분됩니다. 삽입 실행 취소 로그는 트랜잭션 롤백에서만 필요하며 트랜잭션이 커밋되는 즉시 폐기 할 수 있습니다. 업데이트 undo log는 일관된 읽기에서도 사용되지만 

InnoDB가 스냅 샷을 할당 한 트랜잭션이 없으면 일관된 읽기에서 업데이트 실행 취소 로그에 정보가 있어야 이전 버전의 데이터베이스 행을 빌드 할 수있는 트랜잭션이없는 경우에만 버릴 수 있습니다.

 

일관된 읽기만 수행하는 트랜잭션을 포함하여 정기적으로 트랜잭션을 커밋하십시오. 그렇지 않으면 InnoDB가 업데이트 실행 취소로그(undo log)에서 데이터를 버릴 수 없으며 롤백 세그먼트가 너무 커져 테이블 스페이스를 채울 수 있습니다.

 

롤백 세그먼트에서 실행 취소로그(undo log) 레코드의 실제 크기는 일반적으로 해당 삽입 또는 업데이트 된 행보다 작습니다. 이 정보를 사용하여 롤백 세그먼트에 필요한 공간을 계산할 수 있습니다.

 

InnoDB 멀티 버전 관리 체계에서 SQL 문을 사용하여 행을 삭제하면 데이터베이스에서 행이 물리적으로 즉시 제거되지 않습니다. InnoDB는 삭제를 위해 작성된 업데이트 실행 취소로그(undo log) 레코드를 버릴 때 해당 행과 해당 인덱스 레코드 만 물리적으로 제거합니다. 이 제거 조작을 제거(Purge)라고하며, 일반적으로 삭제를 수행 한 SQL 문과 동일한 시간이 소요됩니다.

 

테이블에서 같은 비율로 작은 배치들에서 삽입과 삭제를 하면 Purge Thread가 뒤쳐지기 시작하고 모든 "dead" 행으로 인해 테이블이 커지고 커져서 모든 디스크 한계치가 꽉차게 되어서 굉장히 느려지게 됩니다.

이 경우 innodb_max_purge_lag 시스템 변수를 조정하여 새 행 조작을 제한하고 제거 스레드에 더 많은 자원을 할당하세요.

 

 

+ Multi-Versioning and Secondary Indexes

MVCC (InnoDB Multiversion Concurrency Control)는 보조 인덱스를 클러스터형 인덱스와 다르게 처리합니다. 클러스터형 인덱스의 레코드는 내부 업데이트(in-place)되며 숨겨진 시스템 열은 이전 버전의 레코드를 재구성 할 수있는 실행 취소로그(undo log) 항목을 가리 킵니다. 클러스터형 인덱스 레코드와 달리 보조 인덱스 레코드에는 숨겨진 시스템 열이 포함되어 있지 않으며 전체 업데이트되지 않습니다.

 

보조 인덱스 열이 업데이트되면 이전 보조 인덱스 레코드가 삭제 표시되고 새 레코드가 삽입되며 삭제 표시 레코드가 결국 제거됩니다. 보조 인덱스 레코드가 삭제 표시되거나 보조 트랜잭션 페이지가 최신 트랜잭션에 의해 업데이트되면 InnoDB는 클러스터형 인덱스에서 데이터베이스 레코드를 찾습니다. 클러스터형 인덱스에서 레코드의 DB_TRX_ID가 검사되고 읽기 트랜잭션이 시작된 후 레코드가 수정 된 경우 실행 취소 로그에서 올바른 버전의 레코드가 검색됩니다.

 

보조 인덱스 레코드가 삭제 대상으로 표시되거나 보조 인덱스 페이지가 최신 트랜잭션에 의해 업데이트되는 경우 커버링 인덱스 기술이 사용되지 않습니다. InnoDB는 인덱스 구조에서 값을 반환하는 대신 클러스터형 인덱스에서 레코드를 찾습니다.

 

그러나 인덱스 조건 푸시 다운 (ICP) 최적화가 활성화되어 있고 인덱스의 필드만 사용하여 WHERE 조건의 일부를 평가할 수있는 경우, MySQL 서버는 여전히 WHERE 조건의 이 부분을 인덱스를 사용하여 평가되는 스토리지 엔진으로 푸시합니다.  일치하는 레코드가 없으면 클러스터 된 인덱스 조회를 피할 수 있습니다. 삭제 표시 레코드 중에서도 일치하는 레코드가 발견되면 InnoDB는 클러스터형 인덱스에서 레코드를 찾습니다.

 

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※