[ MySQL 아키텍처 ] 2. InnoDB 스토리지 엔진 아키텍처 (1)

InnoDB 스토리지 엔진 아키텍처 (1)
1. 프라이머리 키에 의한 클러스터링
2. 외래 키 지원
3. MVCC

 

InnoDB 스토리지 엔진 전체 구조

출처: Real MySQL  8.0

InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중 유일하게 레코드 기반의 잠금 기능을 제공하기 때문에 안정적으로 높은 동시성 처리가 가능하며, 성능 또한 우수하다.

 

1. 프라이머리 키에 의한 클러스터링

모든 InnoDB 테이블은 기본적으로 프라이머리 키(Primary key)를 기준으로 클러스터링되어 저장된다. 즉, 프라이머리 키 값의 순서대로 디스크에 저장되며, 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키 값을 논리적 주소로 사용한다.

 

쿼리 실행계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정된다. -쿼리 실행계획에서 다른 보조 인덱스보다 프라이머리 키가 선택될 확률이 높다-

 

반면, MyISAM 스토리지 엔진에서는 클러스터링 키를 지원하지 않기 때문에 MyISAM 테이블에서는 프라이머리 키와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다. 또한, MyISAM 테이블의 프라이머리 키를 포함한 모든 인덱스는 물리적으로 레코드의 주소값(rowid)을 가진다.

 

2. 외래 키 지원

'외래 키에 대한 지원'은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로 MyISAM이나 MEMORY 테이블에서는 사용할 수 없다.

외래 키는 데이터베이스 서버 운영의 불편함 때문에 서비스용 데이터베이스에서는 생성하지 않는 경우도 자주 있지만, 개발 환경의 데이터베이스에서는 좋은 가이드 역할을 수행할 수 있다.

 

InnoDB에서 외래키는 부모테이블이나 자식테이블 모두 해당 컬럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모테이블이나 자식테이블에 데이터가 있는지 체크하는 작업이 필요하므로, 잠금이 여러 테이블로 전파되고, 그로 인해 데드락이 발생할 때가 많으므로 개발할 때에도 외래키의 존재에 주의해야된다.

 

수동으로 데이터를 적재하거나 스미카 변경 등 관리 작업이 실패할 수 있다. 물론, 부모 테이블과 자식 테이블의 관계를 명확히 파악해서 순서대로 작업한다면 문제없이 실행 가능하지만, 외래 키가 복잡하게 얽힌 경우에는 간단하지 않다.

 

또한, 서비스에 문제가 있어 긴급한 어떤 조치를 취해야 하는데, 이런 관리 작업 실패 문제가 발생하면 당황, 조급해질 수 있다. 이런 경우에 foreign_key_checks 시스템 변수값을 OFF로 변경하면 외래 키 관계에 대한 체크 작업을 일시적으로 중단시킬 수 있다.

참고로, 외래 키 확인 작업이 OFF 되면, 외래 키 관계의 부모테이블에 대한 작업(ON DELETE CASCADE와 ON UPDATE CASCADE 옵션)도 무시하게 된다.

SET foreign_key_checks=OFF;

다만, 외래 키 확인 작업을 일시적으로 OFF 했다고 하더라도, 부모와 자식 테이블 간의 관계가 깨진 상태로 그대로 유지해도 된다는 것을 의미하진 않는다.

 

예를들면, 외래 키 확인 작업 OFF 상태에서 외래 키 관계를 가진 부모 테이블의 레코드를 삭제했다면, 반드시 자식 테이블의 레코드도 삭제해서 일관성을 맞춰준 후에 외래 키 확인 기능을 다시 활성화(ON)해줘야 한다.

 

3. MVCC(Multi Version Concurrency Control)

일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, MVCC의 목적은 잠금을 사용하지 않는 일관적 읽기 기능을 지원하는 것이다. InnoDB는 언두 로그(Undo log)를 이용해 이 기능을 구현한다. 여기서 멀티 버전이라 함은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미이다.

CREATE TABLE member (
    m_id INT NOT NULL,
    m_name VARCHAR(20) NOT NULL,
    m_area VARCHAR(100) NOT NULL,
    PRIMARY KEY (m_id),
    INDEX ix_area (m_area)
);

INSERT INTO member (m_id, m_name, m_area) VALUES (01, '김스벅', '서울');

COMMIT;

위 쿼리문을 실행하면 다음과 같은 InnoDB 버퍼풀과 데이터 상태가 된다.

출처: Real MySQL  8.0

 

이 상태에서 UPDATE 쿼리문이 실행될 때, 절차는 다음과 같다.

UPDATE member SET m_area='경기' WHERE m_id=01;

출처: Real MySQL  8.0

UPDATE 문장이 실행되면 COMMIT 실행여부와 관계없이 InnoDB 버퍼 풀은 새로운 값인 '경기'로 업데이트된다. 그리고, 디스크의 데이터 파일에는 체크포인트나 InnoDB의 Write 스레드에 의해 새로운 값으로 업데이트될 수도 있고, 아닐 수도 있다. - InnoDB가 ACID를 보장하기 때문에 일반적으로는 InnoDB의 버퍼 풀과 데이터 파일은 동일한 상태라고 가정해도 무방함 -

 

레코드 조회 시 데이터 출처

이때, 아직 COMMIT이나 ROLLBACK이 되지 않은 상태에서 다른 사용자가 다음 같은 쿼리문으로 작업 중인 레코드를 조회하면 어디에 있는 데이터를 조회하게 되는지 알아보자.

SELECT * FROM member WHERE m_id=01;

답은 "MySQL 서버의 시스템 변수(transaction_isolation)에 설정된 격리 수준(Isolation level)에 따라 어디에 있는 데이터를 조회하여 보여주는지 다르다"이다.

 

격리수준이 READ_UNCOMMITTED인 경우에는 InnoDB 버퍼 풀이 현재 갖고 있는 변경된 데이터를 읽어서 반환한다. - 커밋여부를 떠나서 변경된 상태 데이터를 반환함 -

 

반면, READ_COMMITTED나 그 이상의 격리 수준(REPEATABLE_READ, SERIALIZABLE)인 경우에는 아직 커밋되지 않았기 때문에 InnoDB 버퍼풀이나 데이터 파일에 있는 내용 대신 변경되지 이전의 내용을 보관하고 있는 언두 영역의 데이터를 반환한다.

 

이러한 과정을 DBMS에서는 MVCC라고 표현한다. 즉, 하나의 레코드에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보여지는지 여러 가지 상황에 따라 달라진다.

 

위 설명은 1개의 데이터(레코드)로 이해했지만, 실제로는 관리해야 될 예전 버전의 데이터는 무한히 많아질 수 있다. - 트랜잭션이 길어지면 언두에서 관리하는 예전 데이터가 삭제되지 못하고 오래 관리되어야 하고, 자연히 언두 영역이 저장되는 시스템 테이블 스페이스의 공간이 많이 늘어날 수 있음 -

 

여기까지 UPDATE 쿼리가 실행되면 InnoDB 버퍼 풀은 즉시 새로운 데이터로 변경되고 기존 데이터는 언두 영역에 복사되는 과정이고, 이 상태에서 COMMIT 명령을 실행하면 InnoDB는 더 이상 변경 작업없이 지금의 상태를 영구적인 데이터로 확정한다.

 

하지만, 롤백을 실행하면 InnoDB는 언두 영역에 있는 백업된 데이터를 다시 버퍼 풀로 복구하고, 언두 영역의 내용을 삭제한다. 커밋이 된다고 해서 언두 영역의 백업 데이터가 항상 바로 삭제되는 것이 아니다. 언두 영역에서의 데이터 삭제는 더 이상 언두 영역을 필요로 하는 트랜잭션이 없을 때 비로소 삭제된다.

 

---

출처: ⌜Real MySQL 8.0 (개발자와 DBA를 위한 MySQL 실전 가이드)⌟, 백은빈, 이성욱 지음, 위키북스, 2021.09.08 출간