InnoDB 소개

Mysql의 InnoDB는 높은 안정성과 고성능의 균형을 이루는 스토리지 엔집입니다.  Mysql 8.0 기준 InnoDB는 기본 Mysql 스토리지 엔진입니다. Engine= 절을 구성하지 않고 테이블을 생성할 경우, InnoDB테이블이 생성됩니다.

 

- DML 작업은 ACID 모델을 따르고 commit,rollback 그리고 응급복구기능을 갖추고 있는 트랜잭션으로 데이터를 보호합니다.

- Row level Lock과 Oracle 스타일의 Consistent reads 를 지원하여 동시성 및 성능을 향상합니다.

- 기본 키를 기반으로 디스크에 데이터를 정렬하여 쿼리를 최적화합니다.

- 데이터 무결성을 유지하기 위해 Foreign Key 제약조건을 지원합니다.

 

 

InnoDB Multi-Versioning

innoDB는 다중버전 스토리지 엔진입니다. 그렇기때문에 동시성과 롤백같은 기능을 지원하기 위해 바뀐 데이터의 예전버전을 저장하고 있습니다. 이것을 rollback segment라고 부릅니다.

내부적으로 innoDB는  각 행에 3가지 필드값을 추가합니다.

DB_TRX_ID(6 bytes)필드는 행을 insert 하거나 update한 마지막 트랜잭션에 대한 트랜잭션 식별자(ID)입니다. delete시에는 delete 되었다고 표시하는 특수 비트가 내부적으로 업데이트됩니다.

DB_ROLL_PTR(7bytes) 필드는 roll pointer라고 불립니다. roll pointer는 rollback segment에 쓰여진 undo log record입니다. 예를 들어 행이 업데이트 된 경우 undo log record는 그 행이 update되기 전의 내용을 rebuild하는데 필요한 정보를 포함하고 있습니다.

DB_ROW_ID(6 bytes) 필드는 새 row가 삽입될 때 증가하는 row id 입니다. innoDB가 클러스터형 인덱스를 생성하면 자동적으로 ROWID 값이 인덱스에 포함됩니다. 하지만 DB_ROW_ID 컬럼은 인덱스에 보여지지 않습니다.

 

rollback segment의 undo log는 insert 및 update undo log로 나누어 집니다. 

insert undo log는 오직 트랜잭션 rollback에만 필요하고 트랜잭션이 commit되면 즉시 버릴 수 있습니다.

update undo log는 consistent read에도 사용되지만 오직 해당 row에 대한 이전 update undo log가 있는 consistent read의 InnoDB가 스냅샷을 할당한 트랜잭션이 없을 때 버릴 수 있습니다.

 

consistent read 트랜잭션을 포함한 트랜잭션의 commit을 정기적으로 해주어야 합니다. 그렇지 않으면 InnoDB는 update undo log의 데이터를 버릴 수 없고 rollback segment의 용량은 커지게 되기 때문입니다.

 

rollback segment의 undo log record의 물리적 크기는 insert 나 update된 행보다 일반적으로 작습니다.

 

InnoDB 다중버전에서 행을 Delete 시, 그 행은 즉시 데이터베이스에서 물리적으로 삭제되지 않습니다.

InnoDB는 delete 시 작성된 update undo log record를 버릴 때 해당 행과 인덱스 record를 물리적으로 제거합니다. 이를 Purge라고 합니다. purge는 SQL로 Delete를 수행한 시간과 보통 동일한 시간이 소요됩니다.

만약 테이블 내에 계속해서 insert 나 delete가 발생하는 배치가 돈다면, Purge를 수행하는 쓰레드는 실행순위가 뒤쳐질수 있고 "Dead row"라고 불리는 죽은 데이터 때문에 테이블은 점점 더 커질 수 있습니다. 이런 경우에는  purge를 수행하는 쓰레드를 위한 자원을 더 할당하여야합니다. innodb_max_purge_log 파라미터를 수정합니다.

 

Multi-Versioning and Secondary Indexes

InnoDB 의 MVCC(MultiVersion Concurrency Control) 은  보조 인덱스(secondary index)와 클러스터드 인덱스(Clusterd index)의 처리방식이 다릅니다. 클러스터드 인덱스는 PK를 기반으로 생성한 인덱스입니다. 클러스터드 인덱스를 제외한 나머지 모든 인덱스를 보조인덱스라고 부릅니다.(보조인덱스는 넌 클러스터드 인덱스(non-clustered index)라고도 부릅니다)

클러스터드 인덱스의 레코드가 내부(in-place)업데이트되며, 숨겨진 시스템 컬럼이 이전 버전의 레코드의 undo log 항목을 가리키게 됩니다. 

보조 인덱스는 내부업데이트되는 시스템 컬럼은 포함하고 있지 않습니다. 보조인덱스의 컬럼이 update 될 때, 이전 레코드는 삭제 표시가 되고 새로운 레코드가 insert됩니다. 그리고 삭제 마크(deleted-marked)된 레코드는 Purge됩니다.

 

보조인덱스의 레코드가 삭제마크가 표시되거나 보조 인덱스 페이지가 최신 트랜잭션에 의해 업데이트되면 InnoDB는 클러스터 인덱스에서 데이터베이스 레코드를 찾습니다. 클러스터드 인덱스에서 DB_TRX_ID를 체크하고, 읽기 트랜잭션이 시작된 후 레코드가 수정된 경우 undo log로부터 올바른 데이터가 검색됩니다.

 

만약 보조인덱스의 레코드가 삭제 마크가 표시되거나 최신 트랜잭션에 의해 보조인덱스 페이지가 업데이트될 때 커버링 인덱스(covering index)기술이 사용될 수 없습니다. 인덱스 구조로부터 값을 반환하는 대신 InnoDB는 클러시터드 인덱스에서 레코드를 찾습니다.

(* 커버링 인덱스란? 쿼리를 충족시키는 데 필요한 모든 데이터를 갖고 있는 인덱스를 말합니다. 자세한 정리는 추후에 포스팅하면서 정리하겠습니다.)

 

그러나 index condition pushdown (ICP) 최적화가 활성화되어 있고 인덱스의 필드만 사용하여 where 조건의 일부를 평가할 수 있는경우,  Mysql 서버는 where 조건의 부분을 인덱스를 사용하여 평가되는 스토리지 엔진으로 푸시합니다. 일치하는 레코드를 찾을 수 없는 경우 클러스터드 인덱스 조회를 피할 수 있습니다. 삭제 표시 레코드 중에서 일치하는 레코드를 찾는 경우 InnoDb는 클러스터드 인덱스에서 레코드를 찾습니다.

 

 

// RDBMS에서 가장 많이 사용하는 InnoDB에 대한 기본 설명을 정리해보았습니다. 저도 아직 인덱스에 대한 부분이나, 이해가 다 되지 않는 부분이 존재하므로 계속해서 정리 및 공부하여 학습할 수 있도록 하겠습니다.

기본적으로는 Oracle의 테이블과 비슷하게 일반적인 DB 테이블이 할 수 있는 스토리지 엔진이라고 할 수 있겠습니다.

 

다음에는 아키텍쳐에 대해서 정리하고 공부해보도록 하겠습니다(꾸벅)

 

출처 : Mysql 8.0 reference Manual, myinfrabox.tistory.com/42?category=814732

[MySQL][InnoDB] Architecture